PowerBI and Dynamics 365 CRM – a first integration

In this blog I would like to share some tips with people who are relatively new to PowerBI and make the connection to a Dynamics 365 environment for the first time.

 

 PowerBI is a BI tool that belongs to the Microsoft stack and integrates easily with other Microsoft apps/programs as well as a myriad of other programs. It is free to use for everyone but requires a license to share within and outside your company.

While it is a very powerful tool it seems that all the information about it is related to excel and other ‘easier’ data sources. Connecting to Dynamics 365 is quite easy and straightforward, but knowing Dynamics has lots of out of the box relationships, things can get quite messy. I hope the following tips help you in setting up a PowerBI report based on a Dynamics 365 environment.

Dynamics 365 Related tips

  1. Creating the right data model is the bulk of your time investment

If you make an estimation for creating reports in PowerBI realize that 80% of the time will be needed to create the right data model and relationships. Creating visuals in PowerBI is relatively easy in comparison to setting the relationships and creating the model the way you need it. The relationships will define if your slicers work and if you see the correct data in your visuals.

  1. Do not use the default relationship finder

PowerBI has a default option to find relationships, while it is a very decent tool for tables such as excel, I would advise against using it for Dynamics 365. The reason is that it creates relationships based on the names of columns and links tables together. Unfortunately, the relationship finder does not have different rules when connecting to Dynamics 365  as a data source. This means that if you let the system define your relationships you will have a lot of unwanted relationships between tables due to the fact that every entity in Dynamics 365 has the fields owner, status, status reason, owning business unit, … Often PowerBI assumes a relationship which is not there because of that.

  1. Prevent circular relationships, add the relationships manually and unidirectionally

If you have circular relationships in Dynamics 365 (lookups going from A –> B, B–> C and C –> A) PowerBI will not know how to handle this. It is because it does not know how to apply filters in this case. If A filters B, B filters C and C filters A the circle restarts and it does not know what has to filter what, so it does not end up in an infinite loop. It is often best to create the relationships manually and try single direction filtering first. Very often this is enough and bidirectional relationships just create unnecessary and poorly understood complexity.

e.g.

  • the account filters the related contacts
  • the contacts filter the cases connected to them
  • the cases filter the accounts connected to the cases
  • the accounts filter the related contacts (and the loop continues)

  1. Add the tables one by one

Try adding the tables to your model one by one, in this way it is easy to identify when the visuals break or when something goes wrong, if you add everything at once it will be a hazardous task to find the issue you encounter.

  1. Learn DAX

Get a basic understanding of DAX it will be very useful in deciding if you use calculated columns or measures and will help you to adapt the data you have in Dynamics 365 so you can more easily use it. A very clear and free beginners’ course is provided by SQLBI, for those needing more in-depth knowledge follow up courses can be bought from them as well. https://www.sqlbi.com/learn/introducing-dax-video-course/

  1. Use the XRM tool to get your optionset labels

Unfortunately, option sets from Dynamics 365 do not automatically return the labels, instead it returns the values of the option set. Of course, this is not what you want and is of no use in your visuals. Fortunately, GAP Consulting created a free to use tool for the XRM Toolbox that allows you to  easily use the labels in PowerBI instead of the values. The way to work with this tool can be found in this blog: https://community.dynamics.com/crm/b/365lyf/archive/2017/05/13/how-to-use-dynamics-optionset-labels-within-powerbi

General PowerBI Tips (unrelated to Dynamics 365 as a data source)

       a. Create your own tooltip

 Tooltips based on report pages (use a graph or measure as tooltip instead of the standard label). Possible downside: it uses this report page for every place in your report where you have the selected filter. You cannot have one visual connected to the report page tooltip and one to that standard tooltip (as far as I know/have found out). https://docs.microsoft.com/en-us/power-bi/desktop-tooltips

      b. Backup your database to prevent performance issues

Taking a backup of your Dynamics 365 database into a SQL database prevents the possibility that Dynamics 365 might get slow from the PowerBI queries and allows you to use the direct query functionality.

     c. Use a separate date table

Using a separate date table is useful for slicers and date calculations.

    d. Get inspired by the data stories gallery

You can look at the data stories gallery to see what others built in PowerBI, it is a great place to get some inspiration and often you can also download the file to see how it was set up, a great way to learn! The site also offers lots of other useful PowerBI information. https://community.powerbi.com/t5/Data-Stories-Gallery/bd-p/DataStoriesGallery

So, these were the tips I came up with after my first project where I connected PowerBI to Dynamics 365 CRM. There will definitely be more projects to follow this one as the PowerBI functionality is so useful.  Are there any other tips you have? Please feel free to share them with us.