Shine with PowerBI round the clock with TDS for Common Data Service

TDS for CDS in PowerBI DirectQuery Connection Mode

The new Tabular DataStream (TDS) endpoint for Common Data Service, which is in preview mode, is not just another access point addition for CDS but it addresses several shortfalls for live and secure reporting in Power Platform. With DirectQuery option in PowerBI, the live reporting is possible and it also respects the current user access and roles, when we use TDS connection.

Wait for data refresh and secure it

Early this awesome year of 2020, one of my customer who had very finely crafted the security in their model-driven app using roles and organisational hierarchy want to use PowerBI for reporting. To their surprise (as it is hard to buy), in PowerBI we need to configure row-level security (RLS) and then have to assign roles to the people at PowerBI service level to present only the authorised data. One un-addressed question was if PowerBI is part of Power Platform why it does not understand the roles and security defined in the CDS. I remember there was no single forum or a session where same question was raised and the speaker reminds PowerBI does not follows the permissions of users.

Recently run into another scenario, where customer was using SharePoint for the data storage (Yes, it is another issue) and connecting with PowerBI for producing some reports. Their requirement was to have live reporting and the users were confused because of reporting inconsistency and refresh delay for their system. SharePoint lists does not support direct query which means you have up to rely on data refresh schedule which gets more tighter if you are not having premium capacity as only 8 data refreshes are allowed in a 24 hours day. Direct Query was also not an option for Dynamics 365 or CDS before TDS and has the same pain point.

We are live and secure

With TDS, connecting to CDS is as simple as bringing the data from SQL Server and then put the report on top of it. It will execute the query every time by-passing the issue of data refresh interval and it also will show the data only authorised for the logged in user that means CDS roles and permissions are fully applied. One more advantage, that we get with this is option sets and look up values are also available and there will be no need to bring (and then keep maintaining them) the values and IDs inside the data set and then use tricky expressions.

If you want to explore it further and see the steps how to connect to CDS data source using TDS DirectQuery connection, keep reading through and follow the screenshots below.

1- Enable TDS endpoint for your environment through the Power Platform admin portal. Environment >> Settings >> Features

Turn On TDS endpoint for your environment

2- Connect SQL Server Management Studio to CDS to write and prepare your query. In the Server name type your environment URL and the port no 5558 is fixed for every environment. For User name and password, use your system administrator account.

Connect to CDS environment and Database with SSMS

3- Write your query as you want, yes joins and aggregates are supported in the query.

SSMS to write the SQL Query we want to use.

4- In PowerBI desktop, click Get Data from SQL Server and select DirectQuery in Data Connectivity mode. In Server, put the same value, we used in SQL Server Management Studio. In Database, use the same database name that we connect to when executing the query. In SQL statement, you can write the SQL query or, use the same query prepared in above step.

SQL Server DirectQuery mode
Connection credentials

5- With Data loaded in PowerBI, you can use it the visualisations the way you want and the last step that is required is the dataset settings after your publish the PowerBI reports. The checkbox that says “Report viewers access this data source with their own PowerBI identities in DirectQuery mode.” must be checked.

Report viewers access to the data source.

Currently only SQL Server Management Studio and PowerBI are allowed to be used as client to connect using TDS. If you are thinking about some other use cases, you still have to use WebAPIs or OData end point and the SDK assemblies.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: