Dearest Gentle Reader, (is anyone else hopelessly addicted to Bridgerton lately?) while my typical content revolves around extending Business Central with Power BI, I often utilize other connections as well.

In this post, I wanted to cover the ins and outs of connecting Dynamics CRM with Power BI. While this process can be rather straight forward using the native Dynamics 365 (Dataverse) connector, I have noticed some limitations related to custom extensions being visible to Power BI.

Connecting CRM to Power BI using the native connector

If you are attempting to connect Power BI to Dynamics CRM, I recommend that you first begin by using the native connector. This connector is available out of the box in Power BI and provides a rather seamless experience for connecting to and extracting your data.

To connect CRM to Power BI using the connector:

1. Open Power BI Desktop or Utilize Power BI Service to create a Semantic Model.
2. Go to “Get Data” on the Home tab
3. Search or scroll for “Dynamics 365 (Dataverse)”
4. Next a dialogue box will pop up, prompting you to enter credentials. Ensure that you have “Organizational Account” selected. Once you enter your credentials, you may be prompted to use multifactor authentication depending on your organization’s requirements.
5. After credentials are authenticated, you will then have access to entities available in CRM.

This is where some limitations exist. If you are just looking to expose standard CRM data, this solution will suffice for your purposes. However, if you are interacting with a highly customized environment, the entities you are looking to expose may not be visible using the standard connector. Which leads us to our secondary method of exposure.

NOTE: You may notice that there are two Dynamics 365 Connectors available. I recommend using the “Dynamics 365 (Dataverse) Connector, but here are the differences between that and the “Dynamics 365 (Legacy) Connector. Depending on your version one or the other may be better utilized.

Dynamics 365 Online (Legacy) vs Dynamics 365 (Dataverse)
The “Dynamics 365 Online (Legacy)” connector is based on older web service technologies, primarily serving Dynamics 365 CRM modules with limited features and performance. It lacks support for advanced functionalities and has slower data processing, making it less suitable for modern cloud environments. As a legacy connector, it may receive limited future support.

In contrast, the “Dynamics 365 (Dataverse)” connector utilizes the modern Common Data Service (CDS) infrastructure, now known as Microsoft Dataverse. It offers a richer feature set, improved performance, and scalability by using newer API endpoints. This connector supports a wider range of applications and data models, including Power Apps and other solutions built on Dataverse. It ensures better integration with Power Platform components and aligns with Microsoft’s future roadmap, making it the preferred choice for new implementations and migrations.

Connecting CRM to Power BI using OData

For many of us, the idea of using OData to connect to services is not a foreign one. In the case of CRM, I have found that using OData enables me to pull customized views from within CRM, making certain entities visible (that were previously not with the native connector). The process for connecting CRM to Power BI using OData, is a bit more complex, but once complete provides a very standard experience for viewing entities and choosing which to utilize for your ETL process.

To connect CRM to Power BI using OData:

1. Starting in CRM, we need to find the Root OData URL. This will be used later in Power BI. Go to your Settings Cog in the upper right-hand corner and select “Advanced Settings”.
2. From here, hit the drop-down arrow next to “Settings” and under “Customizations” select “Customizations”
3. From the Customizations Menu, select “Developer Resources”
4. From here, you will copy the URL under the section titled “Connect your apps to this instance of Dynamics 365”. It should look something like this:

Taking your copied OData Link, you will then open Power BI Desktop (or Service) and do the following:

1. Choose “Get Data”
2. Instead of the Dynamics 365″ Connector, we will instead search for “OData Feed”
3. Paste your OData URL.
4. From here you will be prompted to enter your credentials and possibly be prompted to authenticating using MFA.
5. Once credentials are verified, you get a very familiar experience of selecting tables (or entities). Select relevant entities.
   • NOTE: if you are searching for a custom view, go to that page in CRM, in the browser URL, look for the following text:
     • list&etn=Ext Tag_EntityName
     • Whatever is after “list&etn=” will be your service name in Power BI (it will be what you search for when extracting data)
6. Perform your ETL process as normal

NOTE: when loading data from CRM, I have often noticed that you get quite A LOT of data, and a lot of columns that may not be relevant to your report. You may want to perform some OData Filtering (another post on this coming soon) or you may want to “Remove Other Columns” (which I would consider a best practice anyways to keep your dataset clean and user friendly).

Overall, the process of extracting data from CRM can be extremely beneficial for organizational reporting. It can be very powerful alone or in conjunction with other data, such as Business Central or Excel.

Have you extracted data from CRM? What was your experience?