Dataverse to Power BI with T-SQL: DirectQuery for Performance!

In this insightful session, Manuel Quintana from Pragmatic Works explores how to optimize Power BI performance when working with Microsoft Dataverse by leveraging T-SQL and DirectQuery. The video walks through common challenges, practical workarounds, and a powerful alternative using SQL Server Management Studio (SSMS).

Understanding the Challenge

Dataverse is a robust relational storage engine within the Power Platform, often used for storing both system and custom tables. However, when connecting Dataverse to Power BI using the default connector, users may encounter performance issues—especially with complex tables containing hundreds of columns and polymorphic relationships.

Common Issues with the Dataverse Connector

• Excessive columns in system tables like systemuser can cause preview errors in Power BI.
• Lookup and polymorphic columns may introduce complexity and slow down data loading.
• Users cannot select specific columns during the initial connection, leading to inefficiencies.

Workaround Using Power Query

Manuel demonstrates how to bypass the preview error by manually editing the Power Query M code:

1. Open the Advanced Editor in Power BI.
2. Use Table.SelectColumns to specify only the needed fields (e.g., fullname and systemuserid).
3. This reduces load time and avoids errors caused by unsupported data types.

Introducing the TDS Endpoint

To further streamline the process, Manuel introduces the Tabular Data Stream (TDS) endpoint—a feature that allows users to connect to Dataverse using SQL tools like SSMS.

Steps to Enable and Use TDS

1. Navigate to the Power Platform Admin Center.
2. Select the environment and go to Settings > Product > Features.
3. Enable the TDS Endpoint option.
4. Use the environment URL to connect via SSMS using Microsoft Entra authentication.

Benefits of Using T-SQL with Dataverse

• Faster and more responsive querying experience.
• Ability to write precise SQL queries to retrieve only necessary data.
• Read-only access ensures data integrity while exploring datasets.
• Queries can be reused directly in Power BI using the SQL Server connector.

Example Use Case

Manuel walks through a real-world example: retrieving a list of users, their teams, and assigned security roles. This involves joining five tables using SQL, which is significantly easier and faster in SSMS compared to Power BI’s default interface.

Final Thoughts

This session highlights the power of combining Dataverse with T-SQL for enhanced performance and flexibility in Power BI. Whether you're dealing with complex system tables or simply want more control over your queries, enabling the TDS endpoint and using SSMS can be a game-changer.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.