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:
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.


Thursday, October 08, 2015

Understanding non-interactive users in Dynamics CRM Online

A non-interactive user is a user account in Microsoft Dynamics CRM Online that will only be used for programmatic access to CRM (using the web services layer) such as as for integration with an ERP system or other LOB systems. A non-interactive user can not use the user interface. You can create up to  5 free non-interactive user accounts in Dynamics CRM Online.
The benefit of doing so is that non-interactive users do not require a license. Go to Settings>Security>Users – on the Administration tab, you will see the Access mode for a specific user – in Dynamics CRM Online you will see the 3 different types of access modes.

Non-interactive users are apparently something specific for Dynamics CRM Online – if you open the same screen in Dynamics CRM 2015 – you don’t see the non-interactive access mode option.

Tags van Technorati: ,,,

Renewed as SharePoint MVP for 2015–11 times in a row

I must admit that I was surprised that I got renewed as SharePoint MVP (For those who don’t know what a Microsoft MVP is check out ) for the 11th time in a row on October 1st. The last couple of months I have been working – and also blogging - mostly on  Big Data and advanced analytics solutions, Dynamics CRM and Office 365 in general. But I’m still proud to be part of such a great community of MVPs who got renewed (Welcome nearly 1000 New and Renewed MVPs!)

After 10 years on the board of BIWUG – the Belgian SharePoint User Group ( ) – I also said farewell as an active board member but I’m convinced that Andy, Elio, Gerrit,Ben, Bram, Jim and Thomas will continue to make it worthwhile for all of the 800+ Belgian members of BIWUG. Don’t forget to register for the next BIWUG session on October 29th about Azure AD Authentication and Developing SharePoint Online applications using PAAS building blocks.