Just a quick post today. Power BI desktop has a feature to turn on the SSAS profile traces that allows you to see all the queries (DAX and SQL in DirectQuery mode) generated by the reports. I couldn’t find any documentation to turn it on so let’s take you through the steps here.

I start with a very simple PBI desktop file that points to a SQL Azure Database in Direct Query mode:

To see which queries are being run I turn on Tracing by going to File -> Options & Settings -> Options and enable tracing:

Now I press OK and click refresh on the report to make sure the query gets send to the database. Now a trace file has been generated. I can go to “C:\Users\\Microsoft\Power BI Desktop Store App\AnalysisServicesWorkspaces\AnalysisServicesWorkspace1459206073\Data” and find the “FlightRecorderCurrent.trc” file there. This contains all the information.

Note: the “AnalysisServicesWorkspace1459206073” part will be different for you as this is unique for my workbook

Note2: I am using the Power BI desktop from the windows store. If you use the downloaded version you can find it at “C:\Users\\AppData\Local\Microsoft\Power BI Desktop\”

Copy the TRC file to a different folder and open it with SQL Server Profiler (if you don’t have it installed, it comes with SQL Server Management Studio).

This will show you a very detailed list of queries and operations that have been done with the data model:

The most interesting events are Query Begin, DirectQuery Begin and End that shows you the actual queries that are being generated, it also the contains other data like query duration. That allows you, for example, to find the query that took the longest to return.

This profile functionality Power BI gets for free since as it is based on the SSAS engine. We can find more information on this in the documentation for SSAS: https://docs.microsoft.com/en-us/sql/analysis-services/instances/monitor-an-analysis-services-instance

Hope this helps for any potential debugging.

Let’s block ads! (Why?)

Kasper On BI