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\<YOURUSERNAME>\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\<YOURUSERNAME>\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.
Nice.
Before copy the TRC file to my desktop folder, I need to turn off the ‘Enable tracing’.
hmm it will be still writing data when you click around but yeah this could be possible.
Nice Post. Getting this error message back.
Any ideas?
https://uploads.disquscdn.com/images/339c00f77b02affedb42248e0491df4e5274417963624082941a16d67ad646cc.png
I guess your version of profiler might be out of date. Can you download the latest SSMS and try again?
After enabling tracing one would expect to find a .trc file in the traces folder shown 🙁
yes that would be nice, I gave that feedback to the team as well.
For complex reports, it might be useful to know which visual generated each query, is that displayed here? If not, is there any way of seeing that? (Visual Name, SQL, RowCount, ExecutionTime)
Unfortunately not 🙁 I am pushing the team to give us this !
It’s fairly surprising something so obviously useful still isn’t available, here’s hoping it comes soon.
Considering that, in the case of DirectQuery against Azure SQL specifically, is there any advantage/disavantage to using this approach vs examining the Query Store?
Does the actual load of data into power bi (DQ or Import) create a query that is traceable?
Can we load SSAS .trc to powerBI directly as a source build Dashboard?