Power BI Microsoft Dynamics CRM 2013 – Part 9

Colin Maitland, 04 November 2014

As a part of this series on creating Power BI reports for a Microsoft Dynamics CRM Online Organisation, I demonstrated in my previous blog how to expand the opportunity_customer_accounts relationship column in AccountSet. After being expanded however, an error occurred when the updated AccountSet was reloaded to the Data Model.

This could be resolved by adding AccountId to the Removed Columns step of the Applied Steps. The following screenshot shows how this can be done manually using the Advanced Editor. However, this would have the side effect of also removing the AccountSet.AccountId to OpportunitySet.ParentAccountId relationship previously added to the Data Model.

Therefore, my solution was to:

1.  Remove the AccountId from the AccountSet.

2.  Rename the AccountSet to AccountOpportunitySet.

3.  Add a new copy of the AccountSet, retrieved from the Microsoft Dynamics CRM Online Organisation, to the Data Model.

4.  Use the Advanced Editor to copy and paste all the Applied Steps, except for the Expand opportunity_customer_accounts steps, from the AccountOpportunitySet to the AccountSet taking particular care not to include AccountId in the list of removed columns in the new AccountSet. Refer to the following screenshot.

5.  Expand the PrimaryContactId.Id in the AccountSet, ParentCustomer.Id in the ContactSet and ParentAccount.Id in the OpportunitySet.

6.  Recreate the AccountSet, ContactSet and OpportunitySet relationships in the Data Model using the expanded Id columns; e.g. AccountSet.AccountId to ContactSet.ParentCustomerId.Id and AccountSet.AccountId to OpportunitySet.ParentAccountId.Id.

As a result there are now four ODataSets to work with when building Power BI reports: AccountOpportunitySet, AccountSet, ContactSet and OpportunitySet.

My intention is to have a useful set of ODataSet queries that may be used singly or in combination for building reports using the Power BI tools in Microsoft Excel such as PowerPivot, Power View and Power Map.

A final point in this blog, is that the difference between the relationships defined between ODataSets in the Data Model and those used when a Parent/Child relationship columns within a specific ODataSet is expanded is:

•  The first approach is to build the reports using multiple related ODataSets, such as a parent AccountSet and a child OpportunitySet, using the relationships defined in the Data Model. In this case the relationships between the AccountSet and OpportunitySet use a Left Inner Join.

•  The second approach is to build the reports using a single ODataSet that contains both parent entity and child entity columns, such as Account and related Opportunity columns. In this case the relationships between the Account and Opportunity entities in the AccountOpportunitySet use a Left Outer Join.
 

The following screenshot shows a sample Power View report, in design mode, listing Opportunities by Account Name and Opportunity Name. The top section of the report uses the AccountSet and OpportunitySet, the bottom section of the report uses the AccountOpportunitySet. You will notice the available ODataSets in the Power View Fields pane on the right-hand side of the image. Notice that the bottom section of the report includes an Account named Sample Account, which has no related Opportunity, while the top section of the report does not include this Account. This is because a Left Inner Join is used in the top section of the report and a Left Outer Join is used in the bottom section of the report.

In my next blog I will demonstrate the steps for reducing and sorting rows in the ODataSets using the Power Query Editor. Once the design of the ODataSet queries are completed we can share them with others in the Power BI portal and we can use them to build Power BI reports.