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.
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.
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.
Once the connection is established the “Navigator” window will be loaded with all the entities available in your solution.
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.
Once you click on the “Load” button, it will evaluate the entities.
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.
When data model is successfully loaded, it will be listed under the filters tab.