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.
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.
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.
In this way, We can use Dataverse connector in Power BI.
If you like this article, feel free to share it work others who might find it helpful. If you have any questions, feel free to get in contact with me.