Create a Power BI report using Dataverse connector


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.

TLS record limit exceeded error message 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 /.

Unable to connect error message 

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.

3 thoughts on “Create a Power BI report using Dataverse connector

Add yours

  1. I did the same a created a customized reports from native entities now need to add more fields but from virtual entities but virtual entities are not listed in dataverse, is there any configuration required in order to get those virtual entities, followed each and every steps of Configuration of Virtual Entities Working on Dynamics HR.

    Like

    1. You have to follow the documentation for configuration of virtual entity. Are you enabled in your environment.

      Like

      1. Yes I enabled virtual entities it is showing in solution entities and in powerapps.com Data->Tables as well, is there any thing related to visibility with virtual entities?

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑