Connecting Dynamics CRM 2016 On Premise to Power BI for better reporting

As we all know, Power BI is a great tool for generate reports. Best part is, it allows you to consume data from various data sources. Dynamics CRM is one of those many sources. True that CRM already provide reporting capabilities, but with Power BI, you can extend that capability. For instance, you can provide more interactive Dashboards with Power BI. Here, I am going to explain how to establish a connection to your CRM 2016 on premise via Power BI desktop. Let’s follow the steps.

First click on the “Get Data” from the ribbon, and select “Odata feed” from the drop down menu.

crmbi1

Next you will be asked to provide the URL for the OData feed. You can find the “Instance Web API URL” under the Developer resources of your CRM instance.

crmbi2

Then you will be asked to provide the credentials. Since I am using a all in one developer box, I have provided the Windows Authentication.  Note that I have selected “User my current credentials” option, since this is a developer box.

crmbi3

Once the connection is established the “Navigator” window will be loaded with all the entities available in your solution.

crmbi4

You also have the option to select the related records by clicking on “Select Related Tables” towards the bottom left corner of the Navigator window. You must keep in mind, based on the amount of data and complexity of your entity model, it will take some time to load everything.

crmbi5

Once you click on the “Load” button, it will evaluate the entities.

crmbi6

Then it will move to “Creating connection in model…” state for all the entities you have selected. This is the time consuming part. So it is always better to identify and design your data set before following these steps.

crmbi7

When data model is successfully loaded, it will be listed under the filters tab.

crmbi8

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s