Microsoft Dynamics CRM and Power BI – Part 1

Power BI is a suite of business analytic tools by Microsoft to analyze data and share insights. It is a cloud-based business analytic service that helps create live operational dashboards from on-premises and cloud data in one central location that you can access across a range of devices. Power BI helps you stay up to date with the information that matters to you. You can connect to multiple data sets to bring all of the relevant data together in one place. Power BI dashboards help you keep a finger on the pulse of your business.

In this series of posts we will see how you can use the Power BI Desktop to create reports on top of data that is stored inside Microsoft Dynamics CRM. First we will build queries to extract data from different entities of Dynamics CRM and then we will use the reporting capabilities to create some reports on top of the extracted data.

Lets start by opening the Power BI desktop.You will see the following window.

We will use the Query Editor for transformation of our data throughout in this series. Click on the “Edit Queries” in the ribbon and click on “New Source” option in Query Editor window. Choose Dynamics CRM Online option in the source.You will see a URL dialog window for Dynamics CRM Online Organization.

Enter your URL in the following format.

https://your-org-url/XRMServices/2011/OrganizationData.svc

Just replace “your-org-url” with the url of your Dynamics CRM Online. Click OK and enter your CRM login credentials when asked. Once you are signed in you will see the Navigator window from which you can select the entity set you would like to import. Lets just choose AccountSet for now.It will load all the Account fields in the Editor.

Now we will perform different transformation on this loaded data.First of all rename the AccountSet to Accounts in the properties sidebar on the right. Next we need to remove all the extra columns from it and just keep Name, AccountId, Address1_Coutry,Address1_City, Address1_StateOrProvince and StateCode fields. For this select the mentioned columns by click on them and then use Remove Columns -> Remove Other Columns option in the ribbon. We ‘ll be left with the following columns in the editor.

Notice that the StateCode field (which is an OptionSet inside Dynamics CRM) comes in as a column of type Record. If you click on the Expand button in the column header you can select the field that you want to have the Value field shown

For all the Active Accounts the StateCode.Value field will be 0, for the Inactive ones it will display a value of 1. Since this is not very user friendly we will need to replace these to values with the labels Active and Inactive. For this we can use the Replace Values transformation of Power Query. But first you will need to use the Change Data Type transformation to make the StateCode.Value column of type Text. For this go to “Transform” tab in the ribbon and then change Data Type to “Text”.After this the Rename Column transformation can be used to give all the columns a decent name.

See Also : Map Dynamics CRM Option Set Values into Labels in Power BI

The final query looks as follows:

In the next posts we will see how you can import different CRM entities and its selected fields with an OData URL,  and also how you can add relationships between them.

See also : Microsoft Dynamics CRM and Power BI – Part 2