Get Real-Time Insights with Dynamics 365 & Power BI
It’s now possible to get real-time advanced insights for manufacturing sales, service, and marketing teams using the Dynamics 365 in-app experience with the latest updates to the Power Platform.
At the most recent Microsoft Business Applications Summit, a new method was announced for getting data from Microsoft Dynamics 365, or Common Data Service (CDS), into Power BI using the new Tabular Data Stream (TDS) endpoint – and it’s now available to preview. The Microsoft documentation is available here for your review.
It really is just another way to connect to the CDS data, but it gives us some new advantages to get at-scale large data sets to drive actionable insights as they happen:
Real-time Data through DirectQuery to CDS – Your data is updated live using this method, meaning you no longer have to wait hours/days for your numbers to refresh. Factories can use real-time insights to improve production, intervene if there is an issue, improve sales insights for calls, and much more.
Security Roles in CDS Respected – With this feature, users will only see data for which their CDS or Dynamics security role grants them rights to out of the box. You no longer have to maintain multiple versions of the same report for security regions or implement Power BI’s row-level security, and administrators no longer have to worry about data leakage from their reports, streamlining data access and saving capital..
Relationships Automatically Fit Schema – Relationships in the Power BI report are automatically built based on the CDS data base schema, improving report accuracy and saving significant time previously spent building tables, mapping fields, and building reports.
User Friendly column names for option sets and lookup fields – This feature removes the steps of translating database values to user-friendly names, which saves a great deal of time and empowers more novice users. These new user-friendly labels that represent lookup fields and names, as well as option sets, are ready to use and remove an entire step in the data preparation process. Not only does it speed up the process of making reports, it also simplifies the data transformations needed to make a user-friendly data model.
How to 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.
Note that this feature is available in CDS version 18.104.22.16837 and above. However, I am doing this testing on version 22.214.171.12462, so you may be able to get started sooner if your region has not been updated just yet.
This feature is in preview and available for testing only. Please note that your test reports may break once the production-ready version is released.
Create 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 “,5558” at the end.
In the Navigator, select the entities you are interested in.
I am going to select Account, Contact, Opportunity, and Incident
If you have seen a presentation of mine in the past, I always say “never, 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.
Just 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 have already been built, based on the actual relationships.
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 gives so a big step up. However, there are 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 as it gives the best results from Bing Maps searching for the location.
Regarding Type is very important to have so it is easy to filter for activities for a specific entity, i.e. 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 still be nice to have.
For testing I created 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, 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 data set settings, open Datasource settings and click on Edit Credentials.
Make sure you are logged in as the user you intend. Also, pay very close attention to the checkbox at the bottom.
If checked/enabled, 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. So, for this report, security, if needed, will not have to be managed separately.
If not checked, the feature will not be enabled and any user you share the report with will see data under the credentials of the user that owns the dataset, which is the standard way Power BI handles 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, click on the pin icon and add the visual to a Power BI dashboard.
Now that the Top Customers visual is on the dashboard, go to the model-driven app and create a new dashboard.
Click the little Power BI Tile to add the visual we just created. If the 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.
You can see the standard chart on the left and the Power BI visual on the right.
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 a advanced chart 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, setup with drill-down capabilities and links to the individual records. 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. So, I’m guessing the feature will only get better.
I am very impressed with 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 things like managing names of lookup fields.
From a data usage perspective, this option is better than the Common Data Service connector, but still lacks a couple of nice features 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 with 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 and continue testing this new option.