JOPX on Microsoft Business Applications and Azure Cloud: Using Microsoft Power BI Desktop to build Dynamics CRM Online reports Part 3

Saturday, October 17, 2015

Using Microsoft Power BI Desktop to build Dynamics CRM Online reports Part 3

This is the third part in a series of blog posts about Power BI and Dynamics CRM Online – for the previous posts check out the links listed below:

Using Microsoft Power BI Desktop to build Dynamics CRM Online reports – Part 1 – Introduction
Using Microsoft Power BI Desktop to build Dynamics CRM Online reports – Part 2 – Using option sets in reports

In this section we will explore how you can define relationships between different tables as well as how you can use the map control in Power BI. This example is based on the Traviata CRM for Insurance Carriers which provides a solution tailored to fit the unique processes of CRM users in the insurance industry. The Traviata CRM solution extends the standard CRM metadata model and supports both a direct and indirect sales model for insurance policies. A customer can purchase an insurance directly our using a broker (typically work with multiple insurers) or an agent (people who work on behalf of the insurance company).

In this example I will outline how you can track insurance sales and show sales on a map using the address of the insurance broker. The base table to use is the SalesOrderSet which contains all sold insurance products. Next we need to know which channel was used to sell the product, the SalesOrderSet contains a field rdiac_Intermediary.Id which contains the identifier of the intermediary for which the details can be found in the AccountSet.

In PowerPivot for Excel you could create relationships by dragging a link from one table in your model to another – in Power BI Designer you should use the “Manage RelationShips” button in the ribbon – next you define the type of relationship in the dialog window – by selecting the field in the source table and next selecting the field in the destination table. Afterwards we do the same to show the types of intermediary (broker or agent).

The AccountSet table contains a city field and a country field which can be used to show data on a map, but to make the geocoding of CRM records more accurate I added a separate custom column “Location”, which contains a concatenation of the City and Country Field.

So now we can start designing the report – let’s drag the “Location” field and the “Yearly Premium” field on the design canvas. By default it will show in a table format but can you switch to “Map” format by selecting the map icon. I expected the values to be plotted nicely on a map but the Belgian city names where not recognized. It is essential that you tell Power BI that the new column contains a text value which can be used as input for geo-encoding – this can be done by specifying the data category: city (See Data Categorization in Power BI)

After applying this change, I also added the intermediary type (Agent or Broker) to the legend so that we can distinguish between the different intermediary types. I noticed that on the map all of the dots had the same size which was not really what I expected since the size should be bigger, the larger the value. But when I looked at the data set I noticed that the location was blank for a large number of brokers which kind of skewed the representation so I added a page filter to filter out the brokers which have no location. I also added a slicer to make it easier to filter revenue by year. I was surprised that you can’t create a horizontal slicer – please leave a comment on “Create different slicers” if you think this would be an interesting feature.