Dynamics 365 CRM and Power BI

Dynamics 365 CRM and Power BI

Business intelligence, or BI, has been a hot buzz word for several years now. A lot of businesses are looking to take advantage of it but getting only limited traction. There are a number of tools out there to help – each one having its strengths and weaknesses.

Microsoft introduced Power BI a few years ago, and it has exploded in popularity. It’s a terrific tool that can make visualizing small and large amounts of data very easy for the average person. Because the tool can be used to connect to so many sources of data, it has been a real challenge for company administrators to pull the data they need correctly. In other words, it can be really tough to set up Power BI properly, but once you have the appropriate data connected and configured, it’s super easy to use.

As a side note, it’s actually very easy to set-up and use if you don’t care about speed, efficiency or doing it “right”. If you have hundreds of thousands of records of data and don’t mind waiting hours for the data to refresh, then there is no need to continue reading. You are all set! Otherwise, read on …

TopLine Results has invested heavily in using Power BI and Dynamics 365 CRM correctly by utilizing the CRM WebAPI feature. By using the out-of-the-box Dynamics 365 connector for Power BI, you can certainly pull data using the WebAPI, however it will likely pull way more data than you need. If you need data from multiple entities, then you’ll need to pull data from each of those entities individually and manually join them in Power BI. This is highly inefficient and the reason why refreshing the data can take hours.

A better solution would be to query the CRM database using a FetchXML statement. This allows you the ability to filter your data, join multiple entities, sort and more, while taking a fraction of the time to refresh the data and eliminating the need to link the entities manually.

But Power BI doesn’t allow querying your data source using a Dynamics 365 FetchXML statement, right?

This is where TopLine Results comes in. We have developed a way to do this with minimal effort. We are able to translate our extensive knowledge of FetchXML into a M language Power Query that reduces:

  • the amount of time it takes us to develop the dashboard
  • the amount of time it takes to administer the dashboard, and
  • the amount of time it takes to refresh the data.

Graph 1

How does it work? We simply have a few variables that are set using the Power Query editor.

First, we set the WebAPI entity name that we are querying.

Then we provide a FetchXML statement.

Then we provide a mapping of field names from the JSON output to field display names. This allows us to customize the field names that are available in your dataset.

Graph 2

We repeat this for multiple queries as needed.

Graph 3

If you have the data, TopLine Results has the means to put it to use. Talk to us about your CRM data visualization needs: 1-800-880-1960 or info@toplineresults.com. We can help you harness your data and turn it into useful business intelligence to inform decision-making, develop action plans, and ultimately drive results.


Graph 4

Leave a comment!

You must be logged in to post a comment.