Power BI is a powerful reporting tool that is really user friendly and supports various data sources. It has evolved so rapidly from time to time, and the recent Gartner report has positioned Microsoft as the leader for Business Intelligence and Analytics Platform. This blog post will provide a simple guide on how to get started with Power BI Desktop to work with Dynamics CRM Online data.
From quite recent updates from the Power BI team, a new connector for Dynamics CRM Online is created to enable Power BI to quickly connect to the Dynamics CRM Online instance.
It will then prompt your Dynamics CRM Online instance detail. (Bear in mind this is still pointing out to the Dynamics CRM 2011 OData endpoint while the new OData V 4.0 is released for Dynamics CRM 2016; it is still advisable to stick with the connector’s default.)
Note: If you know a specific entity that you would like to get and the query criteria, my blog post from some time ago might be useful.
Now the connector wizard will display the list of the entities that will be part of the data source. For brevity of the example, I’ll use Lead entity to create a simple Lead dashboard.
By default, the query will return all fields and records, so to get a filtered result, click on Edit button to update the query as required. The most common function is to Choose Columns to select the columns that are needed.
Now then the Power BI query editor will trim down the unselected fields.
Now you probably would notice that there are some fields showing as “Record” and not the actual value of the column. To see the actual value, click the button and then click Ok.
It will give a better value. For Option Set Labels, there is a post on how to get the values here.
Once finished with the query editing, click Save & Apply button to apply the transformation that we have made to the data source. Now then we can start building our dashboard by clicking the Visualizations component.
Then drag and drop the fields to the right column/row to produce the chart.
To complete the dashboard, you could repeat the above steps with the appropriate data source. And assemble your insightful dashboard.
In my next post I’ll discuss more on how to deploy the report and schedule the refresh for the Power BI report.
I hope this helps!