Power BI Microsoft Dynamics CRM 2013 – Part 8

Colin Maitland, 03 November 2014

In my previous blog in this series on Power BI and Microsoft Dynamics CRM 2013, I demonstrated how to expand Option Set, Lookup and Money columns in the Power Query Editor. In this blog I will demonstrate how to expand Parent/Child relationship columns using the opportunity_customer_acocunts relationship column in the AccountSet previously retrieved from a Microsoft Dynamics CRM Online Organisation. 

As with Option Set Lookup and Money columns the opportunity_customer_accounts relationship columns is expanded by clicking the Expand icon in the top right-hand corner of the column heading. From here the process differs as shown the following screenshots. There are two options. These are Expand and Aggregate.

 



Expand allows you to select one or more columns from the related child entity which in this case is the Opportunity entity. This results in additional columns and rows being added to the AccountSet. Each selected Opportunity column will be added. One row will be returned for each Account with no related Opportunities, and one row will be returned for each Account with related Opportunity combination. For instance, if there are 11 Accounts and 11 Opportunities, of which one Account does not have a related Opportunity, one Account has two related Opportunities and the remaining 9 Accounts have one related Opportunity each, the total number of rows returned will be 12. 

When expanded, a Left Outer Join is used. This is shown in the following screenshot of the updated AccountSet that now displays the Name and AccountNumber from the Account and the Name and StatusCode.Value from the related Opportunities. Here, the Sample Account has no related Opportunities and A. Datum Corporation has two related Opportunities.

 

Note: After selecting Option Set, Lookup or Money columns from the related child entity they also need to be expanded so that their Value or Name columns are displayed such as opportunity_customer_ccounts.StatusCode.Value etc.

It is important to beware aware that expanding the opportunity_customer_accounts Parent/Child relationship in the AccountSet does not alter the OpportunitySet previously download. It just updates the contents of the AccountSet.

Aggregate allows you to select one or more calculated Average, Count or Sum totals, derived from the related child records, such as the AccountSet’s related child Opportunities, to be added to the AccountSet. This action does not multiple the number of records as only calculated totals rather than related records are being selected. The Aggregate option is limited however in that it does not provide any sums for Money columns such as Sum of ActualValue or Sum of EstimatedValue etc. These Money columns are not yet expanded and so there Values are not available for totalling by this step. They can however be expanded in subsequent steps and totals can then be calculated from these when developing Power BI reports. 

 


I have chosen to select the Name, EstimatedValue, EstimatedClose, ActualValue, ActualCoseDate, StatusCode and OwnerId from the child Opportunity. After this I then also expanded the ActualValue, EstimatedValue, StatusCode and OwnerId columns to display their Value or Name. The following screenshot shows the resulting list of Applied Steps: 
 

After expanding the opportunity_customer_accounts column, the following error was displayed when the updated AccountSet was reloaded to the Data Model:

In my next blog I will explain how I resolved this. In subsequent blogs will then press on with demonstrating how to designing the ODataSet queries so that they can be used in reports created using the Power BI tools such as PowerPivot, Power View and Power Map.