When you are optimizing your DirectQuery model and you have done all the optimizations on the model already, you might want to run the queries generated by Power BI by your DBA. He then might be able to do some index tuning or even suggest some model changes. But how do you capture them? There are a few simple ways that I will describe here.
1 Use DAX studio and Power BI desktop
We will start by opening the report and enabling the performance analyzer. This allows us to get each individual query base on duration so we could optimize them one by one. Click Start recording and refresh visuals to load the entire page and get all the queries send.
Once it has run you can copy the query. This is a DAX query so now we need to get the SQL query.
We can do this by running the query in DAX studio which will give us the SQL Query:
That’s option 1. This is a one by one approach.
1B Use DAX studio and Power BI desktop
I call this option 1B as you can change the flow a bit and just capture all the queries all at once in DAX studio too. Just turn on All Queries in DAX studio. This will show all the queries used for the page. For each query you can double click, execute it and get the SQL Query. Saves you copy and pasting but the result is the same.
2 Use SQL Profiler
Another option is to use good old SQL Profiler. You can connect this to Power BI desktop as well and get all the SQL queries in one swoop. Just connect to the diagnostic port of PBI desktop and capture all the “DirectQuery End” (or begin) event class.
This option is probably best if you want to get all the queries and give them to the DBA all at once.
3 Use log analytics
Now this one only works for Azure Analysis services at the moment but you can also capture the same trace events from profiler with Log Analytics as I described here. Just make sure you querythe same “DirectQuery End” event.
That’s it, several ways to get all the SQL queries generated by Power BI.
The post Get the SQL Query used in DirectQuery mode appeared first on Kasper On BI.
