Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications

|

Leveraging Dynamics 365 Customer Engagement Data via the TDS Endpoint with Power BI and SQL Queries

Post Author: Cory Herring |

The following article will show you how to enable the TDS endpoint, interact with your data via Power BI and SQL Server Management Studio (SSMS), and take advantage of query folding in Power BI. The new TDS (Tabular Data Stream) endpoint is a great resource to build Power BI reports and write SQL queries against your Dynamics 365 Customer Engagement (D365 CE) data. The TDS endpoint is a part of the Dataverse ecosystem, and it can allow for some near real time analysis use cases. It offers read-only SQL-like capabilities with the benefit of doing transformations at source in your Power BI data model.

References

Query Size Limitations for Power BI

There is an 80-MB size limit for query results returned from the Dataverse endpoint. Consider using data integration tools such as Export to data lake and dataflows for large data queries that return over 80 MB of data.

Requirements

  • Power BI Desktop
  • SQL Server Management Studio
  • Microsoft Dataverse
  • Environment System Admin

Process

First things first, let us enable TDS in our environment.

1. Open Power Platform Admin Center and navigate to Environments -> Your Environment -> Settings -> Product -> Features -> Enable TDS Endpoint

Next, lets connect to the TDS endpoint using the Dataverse Connector

2. Open Power BI Desktop -> Get Data -> Power Platform -> Dataverse

3. Enter your environment url without the preceding https:// and trailing / Ex. testorg.crm.dynamics.com

4. Sign in with your organizational account and select Connect

Now, a new window with a table listing should appear. Select your required tables and build your Power BI report! To test, you could pull in a table or two and build a couple sample charts. I have built a sample report using the opportunity table.

With the new TDS endpoint, you can view the native SQL queries being passed to the backend. This is useful for those with a SQL background. It also allows you to pass filters into the native query and filter at source for a reduced overhead cost. Example below!

Using our Power BI report from the previous step, navigate to Transform Data in the Home ribbon. This will bring up our Power Query Editor.

Here, we can see the tables we used in our report. We can also see the Power Query applied steps on the far right. Right-click on the last applied step to View Native Query.

Now we can see the source SQL query!

Once you have reviewed the code, close the native query window, and apply a filter to one of the columns of your selected table. I am using the opportunity table and will filter for statuscode = 3.

Once the filter is applied, check out the View Native Query on our new filter applied step. We can see the “where clause” includes our change in filtering.

This can be helpful for getting smaller more manageable datasets or improving model performance. We recently had a Power BI report where the account table load time was cut from 50 minutes to 2 minutes because we filtered the data at source!

Now let us connect to the TDS endpoint with SSMS and write a simple query against it. I find prototyping or investigating the data to be easier with SQL queries so this can be a great utility for a data analyst.

  1. Open SSMS -> Connect To Server
  2. Enter your environment URL without the preceding https:// and trailing / Ex. testorg.crm.dynamics.com
  3. You will select Azure Active Directory – Password for Authentication type
  4. Enter your organizational account information and click Connect

On the left, in the object explorer, you should see the new connection.

You can open the database and your tables. Expand the database and tables. Right-click a table and run a Select Top 1000 Rows to test your query capabilities!

Summary

The TDS endpoint offers us the ability to connect to the Dataverse backend for our Dynamics 365 Customer Engagement / Power Platform environment. We can use it in a SQL-like manner, and we can even direct query it for access to fresh data. The TDS endpoint offers near real time capabilities but be aware of service limits. The TDS endpoint is a great connector to build reports and do ad-hoc data analysis in Power BI and SQL Server Management Studio. The Power BI Dataverse Connector (TDS endpoint) is generally available as of 12/2020.

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

Leave a Reply

Your email address will not be published. Required fields are marked *

PowerObjects Recommends