Create a Power BI report using data from Dataverse
Effective November 2020:
- Common Data Service has been renamed to Microsoft Dataverse. Learn more
- Some terminology in Microsoft Dataverse has been updated. For example, entity is now table and field is now column. Learn more
This article will be updated soon to reflect the latest terminology.
Microsoft Dataverse allows you to connect directly to your data using Power BI Desktop to create reports and publish them to Power BI. From Power BI, reports can be used in dashboards, shared to other users, and accessed cross platform on Power BI mobile apps.
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
Find your Dataverse environment URL
Open Power Apps, select the environment you're going to connect to, select Settings in the top-right corner, and then select Session details.
In the Power Apps session details dialog box, select Copy Details.
Paste the session details somewhere such as Notepad so that you can copy only the Instance url. The instance URL is the unique URL for your environment. The URL will be in the format:
https://yourenvironmentid.crm.dynamics.com/. Make sure you remove https:// and the trailing / from the URL before pasting it to connect to your environment. Keep this somewhere handy so you can use it when creating your Power BI reports.
Connect to Dataverse using the connector
Open Power BI Desktop. Select File > Get Data > Power Platform.
Select one of the following connectors, and then select Connect.
- Dataverse: This connector is the most recent version and uses the tabular data stream (TDS) protocol.
- Common Data Service (Legacy): This is the earlier version of the connector. Use this connector for large datasets that are greater than 80 MB. This version also supports paging of the query results and building reports that use the image data type.
In the dialog box that appears, paste in your Dataverse environment URL into the Environment domain box, in the format org.crm.dynamics.com. Don't include the https:// or ending /. More information: Find your Dataverse environment URL
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.
The Navigator displays all tables available for your environment, such as the account and contact tables. Select the account table to see a preview of your data in the right pane. Select Load.
Your table is now loaded into your report, and you can begin building reports, or repeat the previous steps to add additional tables. For example, in the Columns pane, select the name column and then select the numberofemployees column. In the Visualizations pane, select Pie chart. This adds a new visualization to your report canvas.
Special column types
Choices are used in tables to provide a drop-down list of values to a user in apps and flows. When using the Power BI connector choice columns will be presented as two columns to show both the unique value, and the display value.
For example, if you had a choice column on your table called
approvalstatus, you would see two columns in Power BI:
approvalstatus- This will show a unique integer value for each item in your choice.
approvalstatuscan help when you apply filters so the filters will not be impacted when you make future changes to the display name.
approvalstatusname- This will show the friendly display name of the item and is most commonly used when presenting the option in a table or chart.
1 Submitted 2 In Review 3 Approved 4 Rejected
Lookup columns use a many-to-one (N:1) table relationship between the table you’re working with and the target row type defined for the lookup. Lookups are presented in Power BI Desktop as two columns, lookupid and lookupid-name.
Relationships in Dataverse require you to create a relationship within Power BI desktop between the two tables using a GUID column, this is a system-generated unique identifier that ensures relationships are created for the create rows where ambiguity or duplication may exist with other columns. You can read more about managing relationships in Power BI desktop here.
While some relationships may be automatically created, you can still review and ensure the correct relationships are established when creating your report:
- The lookup column on the table will contain the GUID of the row in the related table.
- The related table will have a column in the format "[tableName]ID" that contains the GUID, for example
- Using the Power BI desktop Manage Relationships feature, you would create a new relationship between your lookup column, and the ID column on the related table.
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.