Prerequisite: Power BI Desktop Tool, Power BI Online Service Purpose of the setup: Purpose of this blog is to demonstrate USERNAME () function with Row Level Security (RLS) using Power BI to get the dynamic security when users login. Procedure: To demonstrate this, we have created a Power BI Desktop file and published it to Power BI Online service account. Now the basic steps for creating role based security is explained in our previous blog on RLS in Power BI. You can refer the steps in the link given below: https://www.cloudfronts.com/row-level-security-using-power-bi/ In the above mentioned link we used DAX function as [Sales Manager] = “Ashish” i.e. we have hard coded the DAX Function to filter data only for specific records. The drawback is that we need to hard code for every user in the organization no matter how many users are present. So, now we can make use of USERNAME() function which will automatically filter the data depending on the username used to login to Power BI service account. You can read more about this update in the Power BI blog given in the link below. https://powerbi.microsoft.com/en-us/blog/using-username-in-dax-with-row-level-security/ Here, USERNAME() is used to get the current logged in user and filter the data accordingly so as to enable him to view his own data. Below is the screenshot of dataset used. Fig 1: Dataset used for Performing RLS   In the Example, Ankita is the CEO (i.e. the one who has Admin rights) so she can see all the data in the dashboard. Whereas Ashish and Prakash are the two Sales Manager who when logs in can view only their data i.e. data for them and the Sales Person in their team. Fig 2: CEO/Admin Dashboard   Now if the CEO wants to restrict the Sales Manager and Sales Person to view only at their level i.e. the Sales Manager can view data under his team and a Sales Person can view only his data as an owner. For achieving this the CEO can share the dashboard to the person and then can assign the role. The steps are similar to the steps demonstrated in the previous Blog the only thing to change is the DAX function. The DAX function used is [Sales Manager] =USERNAME () Fig 3:  Creating Role   Now we are done with RLS in Power BI with USERNAME () function. So when person named Ashish logs in with his Power BI account with username as ajagdale@cloudfronts.com, he will only be able to view his own data. Here, data in same RLS_USERNAME_SECURITY dashboard shared with Ashish displays only data for ajagdale@cloudfronts.com as the Sales Manager. Fig 4: Sales Manager (Ashish’s) Dashboard   Note:  The column on which we are applying the USERNAME () function has to be the Login id (or username) which is used to log in to Power BI account so that the function can filter depending on username. It may happen that the result is not visible immediately. So for that we can refresh the Power BI dashboard once. Similarly, we can add another person to the same role created and he will also be able to view his own data. For example, another Sales Manager named (say) Prakash logs in to Power BI with his email as pkavar@cloudfronts.com then he will also be able to view pkavar@cloudfronts.com as Sales Manager. Fig 5: Sales Manager (Prakash’s) Dashboard   In this way as many users we will add in the role we can create Row level security for them.  

Consider a scenario where we want the Invoice generated in Microsoft Dynamics CRM to be saved in an ERP. In this article, we will migrate the invoice that is generated in CRM to Dynamics NAV using TIBCO Cloud Integration as Integration Tool. Prerequisite: CRM Online environment Dynamics NAV TIBCO Cloud Integration subscription TIBCO Cloud Integration On-Premise Agent on the server where Dynamics NAV is installed Step 1: Login to TIBCO Cloud Integration Login to TIBCO Cloud Integration (https://online.scribesoft.com/) in the server where Dynamics NAV is installed Go to Agents and create Agent from NEW option Install On-Premise Agent and copy the Agent key as well After installation, you can provide the name for the agent Step 2: Creation of Source and Target connection. Creating a new connection for CRM environment: Select ‘Microsoft Dynamics CRM’ as the Type. Provide the connection details and select ‘Cloud Agent’ for CRM Online environment as shown in screenshot below. Fig. Connection for Dynamics CRM   Creating a connection for Dynamics NAV: Select Microsoft Dynamics NAV as the Type. Provide the connection details and select the On-Premise Agent which is installed on the server where Dynamics NAV is installed. Refer below screenshot for the same. Fig. Connection for Dynamics NAV   Step 3: Creating solutions for maps Create a new solution. Select the On-Premise Agent. Create Advanced Map.Note: There will be two Advanced Maps in the solution. One for ‘Account’ entity migration from CRM to NAV and one for ‘Invoice’ entity migration from CRM to NAV. In the Map for Account migration: Select source connection as the CRM connection In the Query block select ‘Account’ entity Select the target connection as the Dynamics NAV connection In the Create block of the target connection select ‘NAVCustomer’ entity In ‘Create’ block map the required fields. Also we need to use the ‘Update’ block to update the ‘No’ field generated for a Customer in NAV to the ‘accountnumber’ field in CRM. We use the ‘accountid’ field as the matching criteria in the update block. Fig. TIBCO Cloud Integration Map for Account migration   In the Map for Invoice Migration: Select source connection as the CRM connection Select ‘Invoice’ entity in the query block Add invoicedetail as ‘Related entity’ in the Query block Select the target connection as Dynamics NAV connection The flow for creating the Invoice and its related invoice product is as follows: Fig. TIBCO Cloud Integration Map for Invoice migration Step 1: Lookup on account entity in the source and fetch its accountnumber field (which will contain the No that we have updated in the Account migration map). Step 2: Lookup on ‘NAVCustomer’ entity by using the accountnumber fetched in Step 1 as matching criteria and fetch its Name. Step 3: Use ‘Create’ block to create the Invoice entity in NAV. Select ‘NAVInvoice’ entity in the create block. Map the fields required. Fig. Mapping for Create block in NAVInvoice   Step 4: Use ‘For each child’ block for fetching the products present on that particular invoice. Step 5: Use create block in the ‘for each child’ block and select ‘NAVInvoiceSalesLines’ entity. Fig. Mapping for Create block in NAVInvoiceSalesLines   Step 5:Execution of maps for account and invoice entities. Account is updated in CRM. Fig. Before execution of CRM account Fig. After execution the AccountNumber is populated with the No that is generated in NAV   Customer is created in NAV. Fig. Customer in Dynamics NAV   Create an invoice with this customer in CRM and Run the Map. Fig. Invoice in Dynamics NAV   An invoice is created with that customer in NAV.  

Prerequisite: Power BI Desktop Tool, Power BI Online Service Purpose of the setup: Purpose of this blog is to demonstrate Row Level Security (RLS) using Power BI Procedure: To demonstrate this, we have created a Power BI Desktop file and published it to Power BI Online account. Before starting, let us assume a scenario where there is a Sales Organization. A Sales organization uses CRM data. They need to provide Sales data by Role to different person as per their job profile or control. CEO of a Sales organization can view data for all the opportunities. Sales Manager can view only his related sales data i.e. opportunities created by the Sales Person under his team. Sales Person can view only his sales opportunities. Fig 1: Scenario   This can be achieved using RLS in Power BI. Steps Here we have created a Dashboard with sample data for a Sales Organization that deals with Opportunity data from CRM. Fig 2: CEO Dashboard   This is the Opportunity dashboard that shows the complete CRM Opportunity data of the organization. Now if the CEO wants to restrict the Sales Manager and Sales Person to view only at their level i.e. the Sales Manager can view data under his team and a Sales Person can view only his data as an owner. For achieving this the CEO can share the dashboard to the person and then can assign the role. Step 1: Select the dataset from which the row level security has to be applied. There is a new option called SECURITY, which we find once we click on the ellipses (…) next to the dataset. Fig 2: Security Option   Step 2: Now click on Create New Role then add the email address of the person (which he uses as login id for his Power BI account) and then click on Add. Fig 3: Adding Members to RLS   Step 3: After adding the members click on Rules to provide the filtering of data based on DAX filters. In Rules we have to select the table(s) on which we have to apply filter and then write the DAX Function in the DAX Input box. Finally click on SAVE. Fig 4:  Rules Option   Step 4: Now we are done with RLS in Power BI, to view the result we can go to the person’s Power BI account and find the result. Note: It may happen that the result is not visible immediately. So for that we can refresh the Power BI dashboard once. In this case the dashboard has been already shared with the Sales Manager who according to the scenario is the Sales person in our case. Fig 5: Sales Manager Level Dashboard   Similarly, CEO can apply RLS for a sales person to restrict to his data on dashboard. Fig 6: Sales Person Level Dashboard   This is how RLS is achieved using Power BI. Since, this is in Preview there are few limitations too. Limitation: You can define RLS only on the datasets created using Power BI Desktop client If you make a change to your PBIX file, and republish to the service, you will need to re-create RLS Defining RLS in a group workspace is not supported You cannot add security groups or distribution lists to the member list Q&A and Cortana is not supported with RLS  

Prerequisite: Power BI Desktop tool, SQL Server Database Purpose of the setup: Purpose of this blog is to demonstrate connecting SQL as a data source to Power BI Desktop Tool using Direct Query Mode. Procedure: To get this done we have created a Power BI Desktop Tool file with source data of SQL file connected using Direct Query mode. (a)   (b) Fig 1: Power BI Desktop Tool file using Direct Query Mode As we know, previously there were many restriction in using Direct Query Mode like we could not view the relationship, cannot create calculated columns and measures etc. But now, there are many updates in Direct Query mode few of which will be demonstrated below. Ability to create calculated columns in Direct Query. Ability to create measures in Direct Query. Ability to change the datatype when connected to Direct Query. Ability to assume Referential Integrity on relationships in Direct Query Ability to create calculated columns in Direct Query Now we can create calculated column in Direct Query using DAX function. Right click on the table area and select calculated column then write the required DAX query to get the column. Below is an example where we have created a calculated column in Direct Query. Fig 2: Create New Column option in Direct Query Mode   Fig 3: Created calculated column in Direct Query Mode   Ability to create measures in Direct Query Now we can create measures in Direct Query using DAX function. Right click on the table area and select measures then write the required DAX query to get the column. Below is an example where we have created a measure in Direct Query. Fig 4: Create new Measure option in Direct Query Mode   Fig 5: Created Measured in Direct Query Mode   Ability to change the datatype when connected to Direct Query Now we can also change the datatype in Direct Query. Fig 6: Change Datatype option in Direct Query Mode   Ability to assume Referential Integrity on relationships in Direct Query A new setting has been added to the Edit Relationship dialog: a checkbox to “Assume Referential Integrity”. This setting is applied to imported and created data model relationships. To access the setting select the Home tab -> Manage Relationships, select a relationship, and click Edit. Here we have added few tables to our Direct Query file to create some relationship between the tables and selected one relationship to get the Referential Integrity option. Fig 7: Assume Referential Integrity in Direct Query Mode   This is an advanced setting, and is only enabled when connecting to the data in Direct Query mode. It will enable more efficient queries to be generated when it is known that: The From column of the relationship is never Null/blank For every value of the From column, there is a corresponding value in the To column By setting the property in these cases, it allows more efficient queries to be sent to the backend database using INNER JOINs instead of OUTER JOINs. This will result in faster loading of dashboards for the end user.

Prerequisite: Power BI Desktop Tool, Power BI Online, CRM Online and HTML Purpose of the Setup: Purpose of this setup is to embed interactive Power BI reports in CRM Online Dashboard so that it can be viewed by end users directly within CRM Online instead of login into Power BI account. Find the steps given below for publishing Power BI reports to CRM Online. Step 1: Create a new Web Resource in CRM Online as shown below   Step 2: Give this Web Resource a name and select the HTML file which contains the necessary iframe code to access Power BI reports. Save and publish Web Resource created. You can find more about steps for creating an HTML webpage for accessing Power BI reports here.   Step 3: Once you create Web Resource, you can find the same in the list of Web Resources as shown in the image below   Step 4: Now navigate to Dashboard. Create a new Dashboard and give this Dashboard a name.  Select Web Resource to be inserted in Dashboard as shown below Step 5: After that select the Web Resource created and uncheck restrict cross frame scripting option and hit OK   Step 6: Once you save and publish Dashboard, you can see Power BI reports as shown below directly from your CRM Online  

Prerequisite: Power BI Online report and a sample HTML webpage Purpose of the Setup: Purpose of this setup is to embed interactive Power BI visualizations in your blog or website and share the stunning visuals through your emails or social media communications and reach millions of users on any device, any place, for an engaging experience. Find the steps given below for publishing Power BI reports into your webpage or blog. Step 1: Create a dummy webpage with some content, as shown below so that we can go to iframe page.   Step 2: Now, let’s move on to the Power BI Online Service account part. Firstly, open your Power BI Online Account and select the report to be published. Once you select the report, you click on File menu there you will find a new option as Publish to Web. Select the option to proceed further.   Step 3: Now, it will show option to Create embed code, click on that and then it will display a warning with Publish option. Click on Publish.     Step 4: Then you will get a Success message! The message window will show 2 options i.e. Link you can send in email and Html you can paste into your blog or website.   Thus, Power BI has automatically created the link and embed code which can be used to get the dashboard in our webpage. We get different pixel size option also. You can use the link to share with others or you can paste the html code generated here into your blog or website. Step 5: Now, we can paste the embed code into the webpage html code. We have given sample code below: <! DOCTYPE html> <html> <head> </head> <body> <iframe width=”800″ height=”600″ src=”https://app.powerbi.com/view?r=eyJrIjoiZjAzYTNjYzQtNjI1ZC00NTA2LWEyYjQtZjM3YWQ2OGNlYzBiIiwidCI6IjI2YzRiMmU0LWVjMDctNGM3Yi05MmU1LTk3ZjUyODY1ZTk4YiIsImMiOjEwfQ%3D%3D” frameborder=”0″></iframe> </body> </html> Step 6: Now, with this code, when we click on the Power BI Demo button “Demo Registration” on the webpage created initially we will get the report directly on the our webpage.   This is the final output that we get. Thus, we have published Power BI report to our webpage. Now, we can view the code and link again through setting icon and clicking on Manage embed codes. We can also delete the report published. To view the code: Setting -> Manage embed codes -> Right click on ellipses against the report -> Get Code   </>Get Code will again display the link and embed code.   And Delete option will confirm the deleting action and the delete the embed code for the particular report.  

Prerequisite: Microsoft Excel, Power BI Online Service, Power BI Publisher for Excel Power BI Publisher for Excel: With the help of introduction to Power BI publisher for Excel, we can now easily share key Excel insights directly to Power BI online. Using this feature PivotTables, charts, cell range from Excel sheet can be easily shared across different location to a single location in Power BI Dashboard. Note: Power BI publisher for Excel is still in preview mode. To download the Power BI publisher for Excel, click here. Once downloaded and installed, following screen will be shown when you open Excel:   You can then click on Sign up and start using the publisher as shown in screenshot below:   Login to Power BI using your account   You can find the publisher installed on top ribbon under Power BI tab as shown below:   Steps for sharing Excel data to Power BI online dashboards: Step 1: Select desired data from the Excel spreadsheet and click the Pin button on the Power BI ribbon above.   Step 2: When you clicked on Pin, you will be prompted whether to pin to an existing dashboard or create a new dashboard and then pin. You can also view the preview at the right side to see how this will look.   Step 3: Once you log in to your Power BI Online account, you will be able to see the pinned Excel data in Power BI Dashboard.   Step 4: You can also manage tiles that have been already pinned using Pin Manager on top of the ribbon as shown below.   Using Pin Manager, you can update tile data for which you have already pinned to dashboard or also you can remove the tile pinned on Power BI dashboard.  

Prerequisite: Power BI Desktop Tool and knowledge on DAX functions Purpose of the Setup: To learn how we can leverage DAX functions to create complex Power BI reports. Here we will be looking at different DAX functions for the below mentioned reports in Power BI; Calculate and compare Sales Figure by Day Calculate and compare Sales Figure by Date Calculate and compare Sales Running Total Source File Details: To demonstrate this concept, we have created an excel file with dummy data and connected it to Power BI Desktop Tool. In the Excel source file, we have created few sheets along with sample data as shown below: Fig: Modified Sales

Prerequisite: Power BI Desktop Tool, PostgreSQL Database, pgAdmin III, Visual Studio 2008 or higher Introduction Power BI supports connectivity to different databases such as SQL Server, MySQL, Oracle and many more (list of all supported databases given here ). By providing necessary details such as server name, database name, username & password, you can easily connect to database and start exploring data using different visualizations in the form of reports/dashboards. This article will walk you through the necessary steps required to configure in your system before establishing connection of Power BI Desktop Tool to PostgreSQL Database. After installation of PostgreSQL database in your machine, if you try connecting Power BI Desktop Tool to PostgreSQL Database, you will get following error as shown in the screenshot below:   Following are the steps required to connect Power BI Desktop Tool to PostgreSQL Database: Setup PostgreSQL Client Connect Power BI Desktop Tool to PostgreSQL Steps to set up PostgreSQL Client: Step 1: Download and unzip the Npgsql package from the link mentioned here You will find two DLL files named as Npgsql.dll and Mono.Security.dll as shown in screen capture below:   Step 2: You need to have Visual Studio 2008 or later. Open visual studio command prompt using administrative rights as shown in the screen capture below:   Step 3: Locate and Open machine.config from the location given below: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config \machine.config .Net framework and Npgsql version might vary depending on the version of installation. Add the following below <DbProviderFactories> tag: <add name=”Npgsql Data Provider” invariant=”Npgsql” description=”.Net Framework Data Provider for PostgreSQL Server” type=”Npgsql.NpgsqlFactory, Npgsql, Version=2.0.1.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7″/> Find the screenshot given below for this step:   Step 4: Now in your visual studio command prompt, use the following command to successfully move DLL files to C:\windows\assembly gacutil -I “C:\Users\ajagd_000\Downloads\Npgsql2.0.1-bin-ms.net3.5sp1\Npgsql2.0.1-bin-ms.net3.5sp1\bin\Npgsql.dll” Where C:\Users\ajagd_000\Downloads is the file location for the Npgsql package downloaded. Similar command you can run for Mono.Security.dll file gacutil -I “C:\Users\ajagd_000\Downloads\Npgsql2.0.1-bin-ms.net3.5sp1\Npgsql2.0.1-bin-ms.net3.5sp1\bin\Mono.Security.dll” Step 5: Once this is done, you can now try connecting Power BI to PostgreSQL. However you will notice below error   Once you restart your machine, you can now connect to PostgreSQL and start playing with your data.   Steps for connecting Power BI Desktop Tool to PostgreSQL: Step 1: Open pgAdmin III interface to create database, table and few records to display in your report as shown in screen capture below   Step 2: Open Power BI Desktop Tool and click ‘Get Data’ and select PostgreSQL Database as shown below   Step 3: Enter server name (in our case it is localhost), database name, username and password as shown in below screen capture   Step 4: Select required table from database and click load as shown in screen capture below   Step 5: Once data is loaded in model, you can start playing around with the data and create beautiful visualizations/reports as shown in image below and later publish it to Power BI Online Service.  

Purpose of this blog is to showcase latest Power BI updates. Prerequisite: Power BI Desktop Tool, Power BI Personal Gateway and Power BI online service account. Purpose of the setup: To learn about latest Power BI updates from October month. Here we will be looking the below mentioned Power BI updates; Read-only Members in Power BI Groups Semi-select support for DAX formulas in Data view   Read-only Members in Power BI Groups: Power BI have introduced a very good feature in Power BI online Service using which the user can create group in Power BI Online account with multiple users and these users can be assigned role as ‘Admin’ or ‘Member’.Let’s have a look. With such Power BI groups the user and his colleagues can come together to collaborate, communicate and connect with their data. The user can create a group either in Power BI or Office 365. Then, the user can invite co-workers into this group workspace where he can collaborate on his organization’s shared dashboards, reports and datasets. Up until now, all members had all the rights. Many of us required read-only membership to the groups so that the users you want to keep up to date but not have edit permissions on our dashboards and reports. So, this update exactly helps us to do the same; let’s have a look on how to do this. Now to begin with, first we’ll create our group by clicking the “+” icon near Group Workspaces. Now let’s add a few users to our group. FYI, Power BI Groups have two roles: Admins and Members. Now here, we will set one user to Admin and the remaining users to Members. We will click on Save to create the group. Now, let’s take a look what difference take place when an Admin and a member login to Power BI Online account and use the created group. As you can see, when person with Admin rights, login, than that user has right to ‘Explore’, ‘Schedule Refresh’, ‘Rename’, ’Delete’ etc. options available. Whereas, when a person with member rights login can only view the reports and Dashboard. Member of the group do not have the Dataset access and thus, cannot perform any edit operation. Thus, now we have option introduced for us to quickly switch a user from Member to Admin and vice-versa which will be very helpful.   Semiselect support for DAX formula in Dataview : Power BI have introduced another very good feature in Power BI Desktop Tool using which the user can create new column using DAX function by directly clicking on the column. Let’s have a look First we have to open the Dataview Screen. Now, before this update user had to manually type the table and the column name to write the DAX function. With this update the user can directly click on the column name and create the DAX function as per the requirement.