Microsoft announce that Dataverse connector is available for Power BI and tabular data stream(TDS) endpoint. Power BI users have more options when connecting to Dataverse environments, including Dataverse for Teams. The endpoint enables better relationship discovery in the Power BI dataset model. The new connector supports Direct Query providing real-time data and support for the Dataverse security model. In addition, complex data types, such as lookups, choices, and currency are flattened for easy consumption in the model.

Prerequisites

To use Power BI with Dataverse, you need the following items:

• Download and install Power BI Desktop, which is a free application that runs on your local computer. You can download Power BI desktop here.
• Dataverse environment with maker permissions to access the portal and read permissions to access data within tables.
• You must have the appropriate Power BI license to build and share Power BI reports.
• To use the Dataverse connector, the Enable TDS endpoint setting must be enabled in your environment. More information: Manage feature settings

Connect to Dataverse using the connector

Open Power BI Desktop > Home > Dataverse

Click on Dataverse

If you selected the Dataverse connector, select from the following Data Connectivity mode options:

• Import: We recommend that you import data to Power BI wherever possible. With this mode, data is cached in the Power BI service and imported on a scheduled interval.
• DirectQuery: Connects directly to the data in Dataverse. Use this mode for real-time data retrieval. This mode can also more strictly enforce the Dataverse security model. More information: DirectQuery model guidance in Power BI Desktop.

Select OK. You might be prompted to sign in using the same credentials you use to connect to Power Apps and Dataverse. Select Connect.

Select the environment from https://admin.powerplatform.microsoft.com/environments

Add URL in Power BI

Format of Environment domain :

[orgname].[crm].dynamics.com   ( Dynamics 365 CE)

[orgname].[crm4].dynamics.com ( Dynamics 365 HR)

Click OK to load entities

In this case, I selected department entity

Add some data to visual. Later you can add more tables from Dataverse.

Below is the sample data in the report based on Numbers of positions in the department.

Troubleshooting

Error message: Return records size cannot exceed 83886080

This error can occur with the Dataverse connector when you run or design a report with a result set greater than 80 MB. TDS has a result set size limit of 80 MB.

 To work around this limit, optimize the query adding filters and dropping columns so that the query returns less data.

Error message: Unable to connect (provider Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

When this error message occurs, the connector fails to connect to the TDS endpoint. This can occur when the URL used with the connector includes https:// and/or the ending /.

Remove the https:// and ending forward slash so that the URL is in the form orgname.crm.dynamics.com.

Reference link:

https://powerapps.microsoft.com/en-us/blog/microsoft-dataverse-support-for-power-bi-direct-query-reaches-general-availability/

https://docs.microsoft.com/en-us/powerapps/maker/data-platform/data-platform-powerbi-connector

https://docs.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance

In this way, We can use Dataverse connector in Power BI.

If you like this article, feel free to share it with others who might find it helpful! If you have any questions, feel free to reach out to me.