Recently Microsoft has announced a preview of an additional way to access read-only data in CDS. The TDS endpoint for CDS allows read-only access to the system for reporting and analytics. The Tabular Data Stream (TDS) Protocol endpoint is managed by an administrator.
So far, the CDS OData endpoint served as the single access point for all types of operations. In this preview feature, the TDS endpoint is limited to read-only actions. As TDS implies, Power Platform is providing access to CDS data in a tabular format. With the addition of TDS, we have the benefit of allowing additional applications access to CDS data.
However, current support is limited to SSMS (v18.4 and higher) and Power BI applications.
Prerequisites:
Before we can start using this preview feature, we need to ensure that TDS endpoint is enabled as without it we can’t use this feature via Power BI or SSMS.
- Sign in to Power Apps, and then select the appropriate sandbox environment. (Don’t try preview features in Production Environment).
- Verify that your environment has at least version 9.1.0.17437.
- Enable the TDS endpoint feature in Power Platform admin center.
If you don’t have the Analyze in Power BI option in your Power Apps environment, you don’t have access to the SQL connection feature yet.
View CDS Entity Data in Power BI
- Sign in to Power Apps, and then select the appropriate environment from the top-right corner.
- On the left navigation pane expand Data, select Entities, and then select Analyze in Power BI on the command bar.
It will download the pbids file for your environment. - Open the .pbids file to access it in Power BI Desktop.
- In the SQL Server database dialog box, select Microsoft account, select Sign in, and then in the browser window that appears enter your credentials.
SQL options, such as a T-SQL queries aren’t supported.
Query CDS Data using SSMS
- You can also use SQL Server Management Studio (SSMS) version 18.4 or later with the CDS endpoint SQL connection.
- The CDS endpoint SQL connection uses the CDS security model for data access. Data can be obtained for all entities to which a user has access to in Common Data Service.
- Only Azure Active Directory authentication is supported. SQL authentication and Windows authentication are not supported.
If you are familiar with the Dynamics 365 On-Premises, you might know the difference between the query on Filtered Views (like Filteredaccount) and direct tables (like account).
Now, the tables exposed via TDS protocol seems to be similar to Filtered view as it provides all the features like Optionset Names, Datatime fields in Local time and so on. Check out the sample queries in below screenshot.
Row-level security works with TDS endpoint protocol too.
For example, if a user has a Security Role in CDS that enabled them to only read/view their own contacts, that rule would be still in place even though they are accessing through Power BI and SSMS.
Field Level security works as expected as well.
Field Level security assignments with Read-Access set as “No” will have NULL values when queried through Power BI and SSMS.
The list of supported SQL operations includes:
- Batch operations
- SELECT
- Aggregation functions (i.e., Count() and Max() functions)
- UNIONs and JOINs
- Filtering
Well, I hope everyone would like to try out this feature and analyse it against the OData connection.
As it is in the Preview mode, we can expect some changes in future before release.
Till next time, Cheers 😉
