How to Capture Power BI Queries sent to Dynamics 365

Colin Maitland, 10 December 2019

In this article I briefly demonstrate how to capture the queries that are sent by Power BI to Dynamics 365 (Online) when refreshing a table in a Power BI Data Model.

The example used in this article is based on an Account query. This query includes steps to filter, select, sort and expand the retrieved Account records. This image shows the Applied Steps in the Power Query Editor.

Power BI Desktop includes a Query Diagnostics tool. This tool is currently in preview but can be enabled from “Options and Settings, GLOBAL, Preview Features”.

Power BI Desktop, unlike the Power BI Service, executes additional queries to refresh the data preview used by the Power Query Editor. Therefore, before using the Query Diagnostics tool, it is recommended that you first disable background download of the data preview. This can be disabled from “Options and Settings, CURRENT FILE, Data Load”.

Other Data Load options such as those displayed here may also have an impact under certain conditions of what queries are sent by Power BI Desktop to Dynamics 365.

For this demonstration, I have configured the Options and Settings as shown in the previous images.

The following steps describe how to capture the queries sent by Power BI to Dynamics 365 when the Account table is refreshed:

1. Open the Power Query Editor.

2. Click Start Diagnostics on the Tools tab.

3. Minimise the Power Query Editor window.

4. From the main Power BI Desktop window, right-click on the Account table and select Refresh Data.

5. After the Account table has been refreshed, maximise the Power Query Editor window.

6. Click Stop Diagnostics on the Tools tab.

A Diagnostics Group and two related Diagnostics queries will then be added to the list of queries in the Power Query Editor.

The Diagnostics_Detailed query contains detailed Query Diagnostics information and the Diagnostics query contains summarised Query Diagnostics information. This information includes the queries that were sent by Power BI to Dynamics 365 (Online). Neither of these queries are loaded into the Power BI Data Model.

The Diagnostics_Detailed query contains columns such as Query, Step, Category, Data Source Kind, Operation, Start Time, End Time, % Exclusive Duration, Exclusive Duration, Resource, Data Source Query and others.

This image shows the queries that were sent to the https://training.api.crm6.dynamics.com/api/data/v9.1 Dynamics 365 Web API URL.

This image shows the queries that were sent to the https://training.api.crm6.dynamics.com/api/dta/v9.1/accounts Dynamics 365 Web API URL.

The first of these two Accounts queries is shown in the following image. I have reformatted this captured query for clarity. The second Accounts query was the same as the first, except that it did not include the “top=1000” clause. I suspect that query that includes the “top=1000” clause is used for Column Profiling.

A review of these queries shows that the first seven Applied Steps of the Account query in the Power Query Editor have been folded by Power BI into a single query that was then sent to Dynamics 365. This query includes the filter, select, sort and expand steps.

In conclusion, the Query Diagnostics tool in Power BI Desktop simplifies the task of capturing the queries that are sent by Power BI to Dynamics 365. The captured queries and related information can then be analysed, and the results of the analysis can be used for optimising and performance tuning the source queries and Applied Steps in the Power Query Editor.