DirectQuery CDS from Power BI

Ulrik (CRM Chart Guy) 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.

NOTE: Microsoft has temporarily disabled the TDS endpoint for all organizations. If you get an error that the endpoint is disabled even though you are certain that you enabled in the settings, then this issue has not yet been fixed.
Is now back online!

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.

Thanks for reading. Please sign up for my newsletter and or follow me on Twitter

36 thoughts on “DirectQuery CDS from Power BI”

  2. Will be interested to see if anything is done to make working with dates easier. Typically you have a Date Dimension table in PBI. As Direct Query doesn’t support transformations/calculated columns you have to have a Date only field in CDS to join this to ( unless I am missing something!)

    Reply

    • Hi Neal. Thank you very much. I could be wrong, but I believe it is the new TDS endpoint that also enables the TSQL capabilities, so in terms of capabilities of the connection itself, they should be similar, if not the same.

      I may be very biased here, but I think the really exciting side of the new TDS capabilities lies in creating live Power BI visual in an operational context of Power Apps. That’s some serious Power Platform synergy, so I wouldn’t be surprised if that is the main driver for MSFT. But that’s just me 🙂

      Reply

  4. This is very helpful article.

    I have followed all the steps mentioned and I am able to connect to Power BI desktop and create visuals.

    Once I publish the report, and try to edit the credentials I am getting error as below,

    “PowerBI service client received error HTTP response. HttpStatus: 400. PowerBIErrorCode: PowerBINotAuthorizedException”

    Checked the roles in CDS and sql server. Looks like I am having all required roles.

    Has anyone experienced the similar error? Please help me here

    Reply

  5. I followed the steps in “Create a Power BI Report” section, I am getting error while connecting to CDS. I chose “Microsoft account” and provided email address.

    Note: TDS endpoint is already enabled in my environment.

    Error:
    Details: “Microsoft SQL: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was – [Pre-Login] initialization=353; handshake=29644; ”

    Can you help me to fix the error.

    Reply

    • Hi Anilesh – I have seen this error and in my case it was caused by a VPN. The environment that I am using for this post does not require a VPN. I have not been able to resolve this issue yet, but will update here if and when I do. If you have any luck resolving, then definitely please share.

      Reply

  8. Hi Ulrik, thanks for a great walkthrough. Been trying to set up a report to test replacing the Dynamics dashboards, but I seem to be unable to use transform data. I have no problem connecting, data loads and I can build reports, but when opening transform data I get the error below. Have you come across the same type of error?

    Unexpected error: An item with the same key has already been added.
    Details:
    Microsoft.Mashup.Evaluator.Interface.ErrorException: An item with the same key has already been added. —> System.ArgumentException: An item with the same key has already been added. —> System.ArgumentException: An item with the same key has already been added.
    at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
    at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
    ++ multiple more lines of error messages for each column in the table

    Reply

  11. This method worked really well for us for the past couple of months. As of this morning the couple of reports that I was using DQ to CDS started failing. To test I created a simple query to use DQ to a single entity in our CDS environment. When I publish it to the service and configure credentials, I get the following error:

    Underlying Error: PowerBI service client received error HTTP response. HttpStatus: 400. PowerBIErrorCode: QueryUserError
    QueryUserError: Index was outside the bounds of the array.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action.

    Anyone else seeing failures? Any idea how to fix this?

    Reply

      • I never did find a solution, and I ended up reverting my entity loads back to an import mode. The report I wanted to use this with turned out to be fairly important for our campus, so I couldn’t rely on this preview feature. I will be patiently waiting for DQ-CDS integration to move into production.

  12. I used this method over a month and everything was working perfectly, this morning a Power BI Pro user report this error :
    Cannot load model
    Couldn’t load the model schema associated with this report. Make sure you have a connection to the server, and try again.
    Please try again later or contact support. If you contact support, please provide these details.

    When I refresh the report in the desktop app everythings work fine, so I’m confused about this , could be the connector? Has Anyone have this failed before?

    Reply

    • We have contacted MS and they found out its connection error. They escalated ticked and Fixed the issue.
      At this moment all working for us as it used to be. I assume this was global bug and hope it solve your you issue too.

      Reply

      • We filed a ticket and MS told us essentially, tough luck, about a week and a half before you posted this. It seems to be working again. Did they happen to mention anything about when it could be released officially? it seems safe to use but I’m a little hesitant to have that happen again, as we had just cleared QA and were about to push to Prod. Do you mind if I ask, did they provide any more info, or are you still using it? Thanks again, Bill

      • Hey Peter. Literally had this happen about a week and a half before you posted this, MS told us essentially, tough luck. We had to change everything last minute. So they said it was a bug and fixed it? It does seem to be working now. May I ask, are you still using it? I’ve been able to publish some new items and it seems top be working but once bitten twice shy. Any chance they give you any indication when it’s going to be released? I heard october but can’t see it on the roadmap.

  13. Ulrik – thanks for the article. Does it require SQL Server environment, though not mentioned anywhere as it expected to be connecting directly to CDS – I am able to connect when I put my CRM URL, but when I add “5558” – it fails with the message
    Details: “Microsoft SQL: The target principal name is incorrect. Cannot generate SSPI context.”
    Any suggestions/ guidance, as I need to explore the DirectQuery (allowing data refresh in real time) for Power BI for data updated from Canvas Driven App, both using CDS – Entity as data source.

    Reply

  14. Hi Ulrik, good article. I followed the steps, but the connectivity does not work with the below error – would you have any insight or encountered this ever?
    Details: “Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)”

    Reply

  15. Hi Ulrik,

    I have encountered the following error.

    Error:
    Details: “Microsoft SQL: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was – [Pre-Login] initialization=353; handshake=29644; ”

    Can you help me to fix the error.

    Reply

  16. Hi Urik,
    I connected to CRM from SSMS and able to access CRM database(read-only).
    But it looks like Microsoft has set the query timeout for a connection = 2 mins hence any query that goes beyond it , Failing.

    verified the session timeout on CRM portal , there the default session timeout = 24 hours (default).

    How to apply the same settings to SQL Server connection (TDS) as well ? from 2 mins to Beyond?

    Thanks,
    Srinivas

    Reply

  17. Has anyone tried creating a PowerBI report using TDS as the data source and used Oauth2 with privacy level “Organizational” ? I am getting an error saying credentials arent valid. Any clue on this?

    Reply

  18. Dear Ulrik,

    great article, thanks! The connection worked well, following your steps.

    When I did this the first time, everything worked perfect. However, when I build a second report and connected to a different CDS, the optionset names have not been shown anymore, but values were all null.

    Have you experienced this and do you have any idea on how to solve this issue?

    Thank you very much and best regards,
    Sascha

    Reply

    • You’re generally need to do the Joins AFAIK , or use the OptionSetBuilder in the xrmToolbox which will generate them for you. I don’t believe they show up automatically, the same with Lookup values, you’ll need to write the Join (at least in my experience)

      Reply

    • I’m not sure if my last one went through. I think with Lookups and Optionsets, you have to do the Joins and reference accordingly in SQL, or you can use the OptionSetBuilder in xrmtoolkit, which will put them there in named format in advance. I don’t believe the joins are being done otherwise although it may be in the pipeline. If you can, the OptionSetBuilder is really helpful and gets it done with very little effort

      Reply

    • Asif, Im guessing you’re using an old driver or don’t have the right authentication set, or something is wrong there. Just b/c I’ve run into a few problems, have you verified that TDS is enabled in Make.powerapp? If so, can you send me a screen shot of your Login screen with Mgt studio (I don’t need to see the instance, just make sure you use ,5558 at the end, no https at beginning etc and use Azure with MFA

      Reply

  20. Any idea why the Categories table is not available via the TDS endpoint. Its causing us some major issues with Data Migration Testing.

    Reply

Leave a comment