Power BI Microsoft Dynamics CRM 2013 – Part 5

Colin Maitland, 21 October 2014

In this series of blogs I am providing a simple overview of how to connect Microsoft Excel to a Microsoft Dynamics CRM Online Organisation and how to use some of the Power BI tools in Microsoft Excel, such as Power Query, Power View, PowerPivot and Power Map, to create some nice looking, interactive and usefully informative Account and Opportunity related reports. 

In my previous blog I described how to create relationships between the AccountSet, ContactSet and OpportunitySet OData Sets loaded into the Data Model from the Microsoft Dynamics CRM Online Organisation. In this blog I will begin to demonstrate how to modify these OData Sets using Power Query so that they are ready to be used to create Power View, PowerPivot and Power Map reports in Microsoft Excel. 

Microsoft help states, “The data that you work on … is stored in an analytical database inside the Excel workbook, … a powerful local engine loads, queries, and updates the data in that database. Because the data is in Excel, it is immediately available to PivotTables, PivotCharts, Power View, and other features in Excel that you use to aggregate and interact with data. All data presentation and interactivity are provided by Excel 2013; and the data and Excel presentation objects are contained within the same workbook file. Power Pivot supports files up to 2GB in size and enables you to work with up to 4GB of data in memory.” ^[1]

The high-level steps for editing the OData Set queries in Power Query are: 

•  Setting Properties
•  Reducing the Columns
•  Reducing the Rows
•  Sorting
•  Transforming
•  Combining

 

Additional steps also include Expanding and Duplicating selected columns. I will explain and demonstrate these steps in a subsequent blog. 

When completed, you may choose share your queries with others, or share them with yourself for use in other Microsoft Excel workbooks, by sending them to the Power BI Data Catalogue in Microsoft Office 365. Microsoft help states, “… you can share information about the query in the Power BI Data Catalog with all users or a specific set of users within the organization. Shared queries appear in search results when users perform an online search in Power Query. This enables the users to easily discover and reuse the shared queries and their underlying data for their data analysis and reporting. ^[2]

The follow demonstrates how to access the Query Editor and how to set the Properties, i.e. Name and Description, of the selected Microsoft Dynamics CRM Online Organisation OData Set query.

To access the Query Editor:

1.  If required, select Workbook Queries from the POWER QUERY tab of the ribbon bar to display the Workbook Queries pane. 



 

2.  Right-click on the AccountSet and select Edit to open the Query Editor window.

 


The Query Editor windows displays a ribbon bar, Navigator pane, worksheet area and a Query Settings pane. The fact that some columns display the value Record or Table rather than actual data will be covered when I write about Reducing Columns and Expanding Columns.

  

The Navigator pane, when expanded shows a list of the loaded OData Sets:

 

The worksheet area shows a preview of the data for the selected item. In this case this a preview of the records selected by the AccountSet query. Notice the details displayed in the formula bar: = Source{[Name="AccountSet"]}[Data]

  

The Query Settings pane displays the Properties and a list of Applied Steps for the selected OData Set. The Applied Steps in particular are of interest to us as I proceed, over the next couple of blogs, to demonstrate the applying steps to reduce the columns and rows, sort and transform the query. At this point of time there are only two applied steps. These are Source and Navigation.

  

Selecting Source displays the available OData Sets from the connection to our Microsoft Dynamics CRM Online Organisation. Notice the details displayed in the
formula bar: = OData.Feed("https://october2014powerbitraining.crm5.dynamics.com/XRMServices/2011/OrganizationData.svc")

 

Finally to configure the Properties of the Query: 

1.  Click Properties on the Home tab of the ribbon bar:
 

 

2.  From the Properties window you can enter a different Name and provide a Description for the query:
 

 

In my next blog I will demonstrate how to reduce the columns for the AccountSet, ContactSet and OpportunitySet. Reducing the columns is an important initial step for reducing the size of the data set, improving performance and removing columns that are not necessary for analysis and reporting purposes. 

^[1] http://office.microsoft.com/en-nz/excel-help/power-pivot-powerful-data-analysis-and-data-modeling-in-excel-HA102837110.aspx 

^[2] http://office.microsoft.com/en-nz/office365-sharepoint-online-enterprise-help/share-queries-in-the-power-bi-data-catalog-HA104079182.aspx