article 19637

Another Way To Get The SQL Generated By Power BI DirectQuery Mode

If you’re performance tuning a DirectQuery mode semantic model in Power BI, one of the first things you’ll want to do is look at the SQL that Power BI is generating. That’s easy if you have permissions to monitor your source database but if you don’t, it can be quite difficult to do so from Power BI. I explained the options for getting the SQL generated in DirectQuery mode and why it’s so complicated in a presentation here, but I’ve recently found a new way of doing this in Power BI Desktop (but not the Service) that works for some M-based connectors, for example Snowflake.

The trick is to use Profiler and the M Data Provider Events/Execute Source Query event. To demonstrate this I created a DirectQuery semantic model in Power BI Desktop using Snowflake as a source. I then connected SQL Server Profiler to Power BI Desktop (the easiest way to do this is to install it as an External Tool as described here, although you can connect by finding the diagnostics port as described here) and selected the following events:

I then added a card visual to my report that contained a simple SUM measure.

Here are the events generated in Profiler:

As you would expect there’s a single DirectQuery Begin/End event pair generated because a single SQL query is sent to Snowflake by the card visual. However, the TextData column shows the following SQL – which is definitely not Snowflake SQL:

SELECT SUM([t0].[fare_amount])
 AS [a0]
FROM [NYCTAXIDATA] AS [t0] 

This is because for some data sources, Power BI emits TSQL and the connector used then converts this to the actual dialect of SQL used by the source database.

This is where the Execute Source Query event comes in. The TextData column for this event shows the actual SQL sent to Snowflake for this query, which is:

select sum("fare_amount") as "C1"
from "NYCTAXIDB"."PUBLIC"."NYCTAXIDATA"

Interestingly this event is generated after the Query End event for the whole query, but you can associate this event with its parent DAX query by matching the value in the RequestId column with the RequestId for the other events generated by the query.