DirectQuery CDS from Power BI

Last year I wrote a lengthy blog post comparing the three main methods of getting Dynamics 365, or Common Data Service data into Power BI for analysis. Now, at the most recent Microsoft Business Applications Summit, a new method was announced that I was finally able to try out. The method is using the new TDS endpoint. TDS is a Tabular Data Stream. The Microsoft documentation is available here: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/view-entity-data-power-bi

The TDS endpoint has gathered a lot of attention as it allows you to create SQL based reports on CDS. However, I am going to focus on the impact the TDS endpoint has for using Power BI with CDS.

Here is a guide to how to get started along with my initial thoughts on how it stacks up against the existing methods.

Why is the TDS Endpoint for CDS exciting for Power BI?

It is really just another way to connect to the CDS data, but it gives us:

  • DirectQuery to CDS – i.e. real-time data (no refreshes to wait for)
  • Security Roles in CDS can be respected (Fun fact: I checked some old notes from my first Power BI session in February of 2015. One sentence read “security is not applied – might be coming soon”)
  • Relationships automatically fits CDS schema
  • Lots of name columns for option sets and lookup fields – no need for creating your own based on the IDs and values

Get started with TDS for CDS

First, this is a preview feature which needs to be enabled.

Navigate to admin.powerplatform.microsoft.com, select your sandbox environment, go to settings, and select features.

Here, enable the TDS preview endpoint feature. For good measure, enable Power BI embedding at the same time if you have not already.

According to the documentation, this feature is only available in CDS version 9.1.0.17437 and above. However, I am doing this testing on version 9.1.0.17162, so you may be able to get started sooner if your region has not been updated just yet.

In case you missed it, this is a preview feature that may have breaking changes when fully released. Use for testing purposes only.

Create a Power BI report

Open the Power BI Desktop client and make sure you are logged in as the user you want to query the report as.

Click on Get Data.

Select the SQL Server Database option.

For the SQL Server connection, type your Dynamics 365, or CDS URL and add “,5558″ at the end.

Example: YOUORG.crm.dynamics.com,5558

Click connect.

In the Navigator, select the entities you are interested in.

I am going to select Account, Contact, Opportunity, and Incident

Click Load.

If you have seen a presentation of mine in the past, I always say “never ever click Load.” Always click Transform Data.” However, this is a DirectQuery, so we can bypass the transformation step.

If you do click on Transform Data, you may see some errors like this.

Disregard these and click Close & Apply to get back to the report.

In the Power BI Desktop, click the relationship diagram icon on the left panel. On the relationship model, notice that all the relationships between the selected entities has already been built, based on the actual relationships in CDS. Very cool!

For example, accountid is correctly mapping to parentcustomerid on the contact. This is a relationship that the auto-detect relationship feature in Power BI would likely miss.

To see what we kind of data we get to work with I added some fields from the opportunity entity to a table.

The data availability looks really good.

  • Lookup fields – Both ID and name available
  • Customer fields – Both ID and name available
  • Owner fields – Both ID and name available
  • Regarding fields – Both ID and name available
  • Option Sets/Statuses/Two Options – Both value and label available

This is much more than what the Common Data Service connector provides, so a big step up and less data preparation is needed. However, there a few data items which would be great to see included in the future.

  • Address Composite field is not available
  • Regarding Type is not available
  • Owner Type is not available
  • Multi-Select option sets labels are not available – only values

The address composite field is very useful when adding data to a map that does not have latitude and longitude. It provides the best results from Bing Maps when searching for the location. Workaround is to create your own concatenated field from the various address fields.

Regarding Type is helpful as well so it is easy to filter for activities for a specific entity. For example, only include activities regarding opportunities.

Same goes for the Owner Type, although this information can be derived from the Owning User, Owning Team fields, it would still be nice to have.

Create Chart/Report

For testing I am just going to create a very simple chart. Top customers by Sum of Est. Revenue.

Once it is done, publish it to the Power BI Service. Open a browser and go to www.powerbi.com and sign in.

Navigate to the workspace you selected and find the report and dataset you just created.

Go to your Dataset settings, open “Data source credentials” and click “Edit credentials”.

Make sure you are logged in as the user you intended. Also, pay very close attention to the checkbox at the bottom.

If checked, the dataset will use the credentials of the viewing user in the query. This effectively applies all Dynamics 365/CDS security roles and logic to the Power BI report.

If not checked, the feature will not be enabled and any user you share the report with, will see all data under the credentials of the user that owns the dataset. I.e. the standard way Power BI handles data security.

Embed in Model-Driven App/Dynamics 365

Now it is time to add the visual to a dashboard inside my model-driven app. Before leaving Power BI, go to the report and click on the pin icon for the visual and add it to a Power BI Dashboard.

Now that the Top Customer visual is on a Power BI Dashboard, go to the model-driven app and create a new Dynamics 365 dashboard.

Click the little Power BI Tile to add the visual we just created. If the Power BI Dashboard only has one tile, then the tile drop-down does not need to be populated.

In this case, I added the Power BI visual right next to the similar Top Customers chart that comes out-of-the-box.

Standard chart on the left. Power BI visual on the right.

And BOOM!!!!!, there it is!! A real-time Power BI visual in CDS respecting the security roles!!

Here is an animated gif to compare the load times between the two.

The Power BI visual loads within a few seconds of the standard chart which is rather impressive. It is of course also a very simple chart with a very small dataset. Since it is DirectQuery, the standard chart and the Power BI visual will always have the same values. From a latency perspective, the new CDS TDS endpoint will beat even the best Data Export Service databases.

Can it replace the built-in charts?

This is likely not the intention, but it is getting close. They can fill the gap where more advanced charts or functionality is needed. Built-in charts are faster, but a more advanced Power BI visual is probably worth waiting a few extra seconds for. Of course, there is a lot more work to getting a Power BI report created, set it up with drill-down capabilities and links to the individual records, etc. A lot of that comes built-in with the standard charts.

This is a first impressions post, so take everything with a grain of salt. It is a preview feature and I’m running it on an environment that is technically not supported at this time. I’m guessing the feature will likely only get better. Not worse.

However, I am very impressed with the connector so far. It seems like it has good performance, and it has many of the columns that were sorely missed in the CDS connector, so we can soon wave goodbye to managing names of lookup fields, etc.

I’m looking forward to doing some additional testing and revisiting the “Three Main Ways to Get Data Into Power BI” blog post. From a data usage perspective, this option is better than the Common Data Service connector, but still lacks a couple of nice-to-haves like the composite address field in comparison to using the FetchXML option. It is a little too early to make conclusions about best practices for using Power BI using CDS data, but it looks like this new option may move in close to, or at the top of the list of connection methods to use. Stay tuned as I dig in more to this new option and continue testing it.

Leave a Reply

Your email address will not be published. Required fields are marked *