Power BI Microsoft Dynamics CRM 2013 – Part 3

Colin Maitland, 19 October 2014

In my previous blog I demonstrated how to create a connection in Microsoft Excel Professional Plus 2013 to a Microsoft Dynamics CRM Online Organisation for use by the Microsoft Excel Power BI tools such as Power Query, PowerPivot and Power Map.  In this blog I will describe how to configure some Power Query Options, how to select the ODataSets (such as Account, Contact and Opportunity) you want to work with and how to create/configure the Relationships between the selected Data Sets. 

Power Query Options

It is important to be aware of the following Power BI options which are accessed by clicking Options on the POWER QUERY tab in Microsoft Excel.

The following image shows the default options. Hovering the mouse of the information icon next to any of the options displays and informative description of what the options do.

 

i.e.

•  Use standard Query Load Settings: … loads queries to a new worksheet when loading a single query and loads multiple queries to the data model when loading multiple queries at the same time. You can override these settings for a given query using the Load To dialog.

 

•  Data Load Cache Management Options: Power Query stores a copy of query preview results to your local disk for faster viewing later. You can clear this cache or configure how much disk space Power Query is allowed to use.

 

•  Maximum Allowed MB: The default limit is 4096 MB. We recommend not going below 32 MB.

 

•  Diagnostic Options: Power Query Tracing will affect all currently open Excel documents. Tracing will be automatically disabled when you close Microsoft Excel.

 

My recommended for these options are: 

•  Set Default Query Load Settings to Load to Data Model only. This prevents the size of the Microsoft Excel Worksheets from becoming too large due to them containing the data as well as the Power View and other reports that you develop in Microsoft Excel. If you do choose to use Load to Worksheet then there is a limit of 1,048,567 rows of data.

•  Choose not to load data to both the Data Model and to Worksheet. This removes confusion as to which loaded Data Set is being selected and used for the reports you create using the Microsoft Excel Power BI tools.

 

Selecting and Loading Microsoft Dynamics CRM Online Organisation ODataSets
In my example I going to select the AccountSet, ContactSet and OpportunitySet from the list of available ODataSets.  

1.  If Required: To display the Navigator Bar by clicking Recent Sources on the POWER QUERY tab then selecting your Microsoft Dynamics CRM Organisation’s Data Source from the list of Recent Data Sources to display a list of the available ODataSets.

 

2.  Optional: To select and load a single ODataSet, such as the AccountSet, locate and select it and click Load.
 

3.  Optional: To select and load multiple ODataSets select the Select Multiple Items option; locate and select the ODataSets such as AccountSet, ContactSet and OpportunitySet; and click Load.

In either case, clicking Load will cause the selected ODataSet(s) to be loaded to the default Load To location, e.g. Worksheet and/or Data Model. You can override this by selecting the drop-down menu icon on the Load button and the selecting the required Load To option(s). There are several combinations of options to choose from; e.g. 

• Table
• Table and Add This data to the Data Model
• Only Create Connection
• Only Create Connection and Add this data to the Data Model

 

Choosing only to Create Connections, results in the selection of the ODataSets for the current Microsoft Excel workbook but without the data being loaded.

Choosing to Load To the Data Model or Worksheet results in the data for each ODataSet being download.








If desired you can preview, view properties, edit some of the properties or edit the queries for each of the downloaded ODataSets by hovering the mouse over them as shown in the following example.  

The second of the Workbook Queries related screenshots, above, shows a Detecting relationships step being executed by the load process. In my next blog I will demonstrate how to view the relationships that were detected, if any, and how to configure these manually. I will also explain why the Detecting Relationships step did not actually result in any relationships being detected for these ODataSets. In a subsequent blog I will also demonstrate how to then edit the queries and how to make those queries available for others to use via the Power BI Portal in Office 365 using SEND TO DATA CATALOG.