Build Real-Time Dynamics 365 Reports in PowerBI: Leveraging the Tabular Data Stream (TDS) endpoint for Dataverse – Part 1

by | Nov 22, 2020 | Business Analytics, Business Intelligence, Microsoft Power BI

As you may have already heard Common Data Service (CDS) has been renamed to Microsoft Dataverse. Some terminology has also been updated. For example, entity is now table and field is now column. This article will attempt to reflect the most current terminology as of it’s writing.

 

In a previous tutorial I’ve written about the different methods of connecting to your Dynamics 365 data. If you are interesting in seeing that video, you can find it here.

 

In this article we will be discussing the newest method available that was just announced by Microsoft. The Tabular Data Stream (TDS) endpoint can now be used to create real-time reports on data sets by leveraging DirectQuery. In addition to allowing you to build reports that automatically get updated with the newest information, it even provides the native security of Dataverse across your PowerBI reports.

 

NOTE: As of this writing (November 2020) the feature has been re-enabled in the majority of regions after being deactivated worldwide for a brief period after its release.

 

What advantages does the DirectQuery provide for PowerBI

 

As previously mentioned, there were already several ways to connect to Dynamics 365 data sets, either utilizing standard connectors, or custom FetchXML queries built with the XRM Toolbox PowerQuery Builder. These methods are great and very reliable, however, there was always one big issue in my opinion: REPORTS HAD TO BE MANUALLY UPDATED!

 

*Well, that is not entirely true. There was a way you could schedule automatic updates by configuring an On-Premise Gateway. This meant, however, that you had to install the gateway on a machine that was always on and able to be udpated. This made it feasible but not at all practical, especially if you had multiple dashboards to update.

 

Here’s what the TDS endpoint now provides us:

 

  • DirectQuery to Dataverse – this means real-time data refreshes
  • Security Roles in Dataverse are observed
  • Relationships automatically fits Dataverse schema
  • Lots of name columns for option sets and lookup fields – no need for creating your own based on the IDs and values

 

 

How to get started using TDS

 

You will need to activate this feature by navigating to admin.powerplatform.com and selecting the appropriate environment.

 

You will need to verify that your environment is at least updated to version 9.1.0.17437.

Enable the TDS endpoint and additionally ensure that the option for PowerBI visualizations is activated.

In part two I will show you how to connect to your Dataverse Data in PowerBI and start building your reports!

 

Post Views: 2,059