DirectQuery: Checking generated SQL queries
If you are working with DirectQuery, Dual-mode or hybrid tables you will inevitably hit your back-end server with loads of queries, some more performant than others. Being able to analyze what queries are sent out comes quite handy - and doing so is quite simple.
I got motivated by Chris Webb's recent blog post about query tuning with the new DAX window functions. So while I was playing around and analyzing different SQL queries outputted from INDEX, OFFSET and WINDOW I decided to document the steps of how to check those queries and their results on the actual SQL server in Azure Data Studio or SSMS.
This method works for SQL Server (Microsoft), Oracle and Teradata. If your data is loaded from a different SQL-based source i’d recommend one of two alternative solutions:
1) DirectQuery model troubleshooting in Power BI Desktop - Power BI | Microsoft Learn
2) Chris Webb's BI Blog: Capturing SQL Queries Generated By A Power BI DirectQuery Dataset Chris Webb's BI Blog (crossjoin.co.uk)
Performance analyzer
The simplest way to fetch the native queries is through the native Performance analyzer, which can be found in the view tab.
Performance analyzer button
After clicking the button a new pane will expand allowing to click “Start recording” to begin recording all queries, DAX as well as SQL.
Finally locate the visual you wish you analyze and click the little new “Analyze this visual” button.
Finally you’ll be able to expand the table log and click "Copy query” to copy the full query log to your clipboard.
The query and running it on your SQL server
Content of the copied query
The content will include both an overall DAX query and the generated SQL code. We may wish to test this out in SQL and the only thing stopping us is some comments not aligned with SQL syntax.
A quick find-and-replace should do the trick:
(Find // replace with --)
And voila! The resulting tables from the SQL code.
