In this webinar, our experts showcase a variety of demo use cases of how different components of the...
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.
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.
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.
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.