Fetch Dynamics 365 data into Microsoft Power BI services

 Integrating data from Microsoft Dynamics 365 into Microsoft Power BI services can transform your organizational reporting, enabling real-time insights and data-driven decision-making. However, navigating the integration process, especially for non-technical users, can be challenging due to varied connector options, authentication requirements, and performance considerations. In this guide, we'll explore the advantages, limitations, and step-by-step processes of connecting Dynamics 365 to Power BI through both OData Feed and Dataverse connectors. 

®   OData Feed connector for connecting Microsoft Dynamics 365 to Power BI

To retrieve data from Microsoft Dynamics 365 in Power BI using OData connectors, follow these structured steps:

• Click on the Get Data button located on the Home ribbon.

• From the dropdown select “More”, then you will see a lot of connectors available. Click on the “Others” from the left panel

•  Choose OData Feed and enter the URL of your CRM API then Click “OK”.

•  If your API is of an organization, then click on the “Organizational Account” from the left panel.
•    Then sign in to your Organizational account of Microsoft Dynamics 365 and select which level to apply these settings to for your API.  

•      Once you click on the “Connect” button you will see the list of tables available from which you can select the tables you want in your report and click on “Load”. 

®    Pros: 

1.        Detailed Control: OData offers the ability to query exactly what you need. This means you can filter out unnecessary data, making your reports more efficient. 
2.       Up-to-Date Data: OData provides real-time access to your Dynamics 365 information, ensuring your Power BI reports reflect the most current data available. 

®    Cons:

· Connection Issues: Joining OData or web sources can be slow, sometimes causing the connection to fail. To avoid this, use Table.Buffer on the smaller query before merging, or try switching the join order.

·  Authentication: OData connectors may not support OAuth/Microsoft Entra ID for all services. If you encounter authentication errors, contact the service owner to modify the setup or use a custom connector.

·     Maximum URL Length: SharePoint and Project Online connections have a URL limit of around 2,100 characters. To avoid exceeding this, start with the root OData endpoint and filter within Power Query.

®   Dataverse connector for connecting Microsoft Dynamics 365 to Power BI

By utilizing this connector, you can access all tables available within the organizational account to which you have access.

•      Click on the Get Data button located on the Home ribbon.

·         From the dropdown, select More, you will see a lot of connectors available. Click on the “Power Platform” from the left panel and click on “Dataverse”.

·         Then Sign in with your Organizational Account

 ·  After successfully signing in, click the 'Connect' button and you will be presented with a list of all the tables accessible to you within your organization. Select the tables and click 'Load' to import the tables in your report.

Pros: 

·         TDS Speed: The Dataverse connector uses the TDS (Tabular Data Stream) endpoint which enables faster data retrieval from Dynamics 365 than the Dynamics 365 (Online) connector.

·         Custom Queries: Power Query’s Value.NativeQuery allows custom SQL queries, offering greater control over data extraction.

Cons:

·         Throttling: Both Dataverse and OData connectors face performance limits (~500 rows/sec). Use Export to Data Lake or TDS for faster retrieval.

·         Connection Issues: SQL Server connection errors may result from blocked TCP ports (1433 or 5558); check network settings.

·         Query Limit: Dataverse connector has an 80 MB query limit, lower than OData’s.

In conclusion, the Dataverse connector is the preferred option for integrating Microsoft Dynamics 365 with Power BI. It provides faster data retrieval, greater control with custom SQL queries, and access to all organizational tables, along with simplified authentication and no URL length limitations. Overall, its performance and flexibility make it ideal for optimizing your integration efforts.


For organizations seeking expert guidance, MicrosoftPower BI consulting services are available. A Microsoft Power BI Consultant can assist with customized solutions, ensuring you harness the full potential of Power BI. Reach out to Inkey IT Solutions Pvt. Ltd. for specialized support in optimizing your Power BI integrations with Microsoft Dynamics 365.