How to Query Microsoft Dataverse Data using SQL from Power BI or SSMS
Microsoft in recent months made available a much requested feature by many with the additional way to access read-only data in Microsoft Dataverse. The TDS endpoint for Microsoft Dataverse allows read-only access to Dataverse for reporting and analytics requirements. The Tabular Data Stream (TDS) Protocol endpoint functionality is managed by an administrator with access to the Admin center. This feature becomes a game changer as many Microsoft customers who required this feature and often went down the root of the Data Export Service can now start exploring this capability
Prerequisites:
To get started using this feature, you will need to ensure that TDS endpoint is enabled as this is required to be able to use this feature via Power BI or SSMS.
Sign into the Power Platform Admin Center :(https://admin.powerplatform.microsoft.com/environments), and then select the appropriate sandbox environment.
Note: Its always good practice to test new features in a Sandbox Environment
Validate that your environment is at least version 9.1.0.17437 or above.
Enable the TDS endpoint feature in Power Platform admin center.(https://admin.powerplatform.microsoft.com/environments) then Select your Environment name > Settings > Features.
Microsoft Dataverse OData endpoint served as the single access point for all types of operations. In this feature, the TDS endpoint is limited to read-only actions. As TDS implies, Power Platform is providing access to Microsoft Dataverse data in a tabular format. With the addition of TDS, we have the benefit of allowing additional applications access to Microsoft Dataverse data.
Viewing Table Data in Power BI
Sign in to Power Apps, (https://make.powerapps.com/environments) and then select the appropriate environment from the top-right corner.
On the left navigation pane expand Data, select Tables (remember its not Entities anymore its Tables), and then select Analyze in Power BI on the command bar.
You will receive a download of 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.
After the step above you are good to start building some cool Power BI dashboards however If you don’t have the Analyze in Power BI option in your Power Apps environment dont panic, this likely that access to the SQL connection feature has not been rolled out in your region however and its definitely on the way.
Using SQL Server Management Studio (SSMS) to query CDS data
Another option Microsoft have provided is the ability SQL Server Management Studio (SSMS) version 18.4 or later with the Microsoft Dataverse endpoint SQL connection.The Dataverse endpoint SQL connection uses the Microsoft Dataverse security model for data access. Data can be obtained for all tables to which a user has access to in Microsoft Dataverse.
Note: Current support is limited to SSMS (v18.4 and higher) and Power BI applications.
For those of you who that worked with Dynamics 365 On-Premises, you remember the difference between the query on Filtered Views (like Filteredaccount) and direct tables (like account).
Tables exposed via TDS protocol look similar to Filtered view as it provides all the features like Optionset Names, Datatype columns in Local time.
*For up to date information and limitations on the above and Supported operations and data types check out the Microsoft link below:
https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query
Thank you for sharing such a useful article. It will be useful to those who are looking for knowledge. Continue to share your knowledge with others through posts like these, and keep posting on
ReplyDeleteBig Data Solutions
Advanced Data Analytics Services
Data Modernization Solutions
AI & ML Service Provider