Power BI Microsoft Dynamics CRM 2013 – Part 4

Colin Maitland, 20 October 2014

In my previous blog I demonstrated how to configure Power Query options and how to select and load one or more ODataSets from a Microsoft Dynamics CRM Online Organisation using Power Query in Microsoft Excel. In this blog I will demonstrate how to view and configure the relationships between the downloaded ODataSets so that these relationships can be used when creating reports in Microsoft Excel using Power BI tools such as Power View.

The steps are as follows:

1.  If required, open your Microsoft Excel spreadsheet that contains the loaded ODataSets to your Microsoft Dynamics CRM Organisation.  

2.  Select the POWERPIVOT tab on the Microsoft Excel ribbon bar.

3.  Click the Manage command button in the Data Model group on the POWERPIVOT tab to open the Data Model. Note: Ignore the Detect command button in the Relationships group as that command applies to detecting the relationships for a selected PivotTable rather than for the Data Model.

  

4.  Each of the ODataSets previously loaded to the Data Model appear as tabs in the Data Model window; i.e. AccountSet, ContactSet and OpportuntiySet.
 

5.  Click the Diagram View command button in the View group on the Home tab of the ribbon bar to display a diagram of the Data Model: 

 

6.  The following image shows the diagram view of the Data Model with the AccountSet, ContactSet and OpportunitySet displayed. You will notice that there are no relationships shown. This is because no relationships could be detected when these ODataSets where loaded.
 

 

7.  To manually create a relationship from Account Primary Contact to Contact locate the PrimaryContactId field in the AccountSet and the ContactId field in the ContactSet, and then drag-and-drop the PrimaryContactId onto the ContactId.
 

  

8.  To manually create the relationship from Account to Opportunity locate the AccountId field in the AccountSet and the AccountId field in the OpportunitySet, and then drag-and-drop the AccountId onto the AccountId.

 
 

9.  To manually create the relationship from Contact to Opportunity locate the ContactId field in the ContactSet and the ContactId field in the OpportunitySet, and then drag-and-drop the ContactId onto the ContactId.
 

 

10.  Select Close from the first tab on the Data Model window to return to the Microsoft Excel workbook. 

 

A couple of observations from the completion of these steps are: 

•  Regardless of which direction you link the AccountSet and the ContactSet to the OpportunitySet the direction of the relationship is always automaticaly updated to be from the OpportuitySet.

•  When creating the relationship from the ContactSet to the OpportunitySet the status of the relationship was set to Inactive as shown by the dotted line. The following information is provided if you attempt to activate the relationship: You cannot activate the relationship because a set of active relationships already exists between tables OpportunitySet and ContactSet. However, if you deactivate the relationship between the AccountSet and the ContactSet you can then activate the relationship between the ContactSet and the OpportunitySet as shown in the following diagram:

 

To view the properties of a relationship and to set its state to active or inactive, simply double-click the relationship in the diagram to open the Edit Relationship window. You will notice that the Edit Relationship window displays an Active option which may be selected activate or unselected to deactivate the relationship.

 

There are also Create Relationship and Manage Relationships commands in the Relationships group on the Design tab of the Data Model ribbon bar. These may also be used to create and manage the relationships between the ODataSets.

 

An important point to be aware of is that the relationships are Inner Joins. There is no option create or configure Outer Joins. 

In my next blog I will describe how to edit the ODataSet queries using the Power Query Editor prior to being used for creating views, tables and charts in Microsoft Excel using the various Power BI tools such as Power View and Power Map.