Power BI - Checking Query Folding with View Native Query

A really great feature that was silently added into June update of the Power BI Desktop is a feature called View Native Query. This feature is integrated into Power BI Query Editor and appears to give you the ability to see the queries that are running against your data source when Query Folding is taking place. I’ll get back to what View Native Query does in a moment, but first for those that are unfamiliar let’s talk a bit about what Query Folding is and why it’s important.

Query Folding

The idea behind Query Folding is to push the logic that you built into a Power BI query back to the data source server and execute it there in it’s native language instead of doing a client side transform of the data.  Why is this important?  Let me give you an example.  Say you have a 2 billion row SQL Server table you need to connect to in Power BI, but you want to filter to only return the last year of data.  With Query Folding the filter of that data is done on the SQL Server side instead of on the client side. If Query folding did not take place than that would mean all 2 billion rows would be brought across the network only to then filtered out on the client workstation.  So clearly the ideal situation is that all your queries get folded for the best possible performance, but Query Folding only works in certain scenarios.

Here’s the scenarios where Query Folding does not take place:

• You are using an unsupported data source. This usually makes sense. For example, if you’re connecting to a flat file that there is no backend server that the queries can be run against.
• You are using an unsupported transform type.  Generally this makes sense too.  For example, if you’re connecting to a SQL Server data source and you select a tranform in the Query Editor that doesn’t exist in SQL Server.  Maybe you choose the transform Capitalize Each Word.  Now there are ways to accomplish this in SQL Server but there is not a native T-SQL function that can capitalize the first letter of every word in a field.
• You write your own source query. When you establish a connection in Power BI to a database (let’s say SQL Server for this discussion) you’re provided an option, under Advanced Options, to write your own SQL Statement to define how the data will be imported.  Once you choose this option Query Folding is no longer available on this query. That means if you plan on writing a query to import your data you better know ever transform you would like to apply to the dataset and write it inside the SQL Statement because if you add any additional transforms in the Query Editor later they will not be folded.

The good news is Query Folding is not all or nothing.  You can have partial folding that takes place. The order of your tranformations can make a difference in how much is folded back to the server. For example, a Group By transform should appear before a transform like Capitalize Each Word because Group By would be supported on the source system while capitalize each word would not be. So the Group By statement would be pushed back to the server to perform that work while the Capitalize Each Word is done on the client machine.  If the Capitalize Each Word transform came first then everything after it would not be folded no matter what type of transform it was.

View Native Query to the Rescue

In the past it’s been fairly difficult to determine if your query was truly folded or not.  One option was if your data source was SQL Server you could use a tool like SQL Server Profiler to view a trace of all the queries running against your server.  While this helped it only worked for SQL Server data source and maybe more importantly it’s hardly a tool that the typical Power BI user would know.

Now with the latest Power BI Desktop update you have a new option called View Native Query, which appears to follow the rules stated earlier about when Query Folding can and cannot take place.  To use it simply use the Query Editor like you normally would and then right-click on a step in the Applied Steps section.  Assuming you have followed the rules of Query Folding then you will see an option called View Native Query.

When you select this option a Native Query window will appear that shows you the exact query that is being run against your data source.  In this example I connected to a SQL Server database then connected to the Product table and applied a simple filter of the data using the Query Editor transforms.

Notice this Query Editor filter is converted to a native SQL statement and run against the server instead of my client machine.

Now, if I introduce a new transform like Capitalize Each Word, which we know Query Folding doesn’t support, notice that the option to View Native Query is disabled.

This also means any transform that we add after this one will not be folded and the View Native Query option will continue to be disabled.  However, don’t forget that queries can be partially folded, which means any transform that was applied prior to this unsupported function will still be folded and will allow you to use the View Native Query option.

Below shows when you right-click on the previous step that the View Native Query is supported up until Capitalize Each Word is used.

I’m really excited about this new feature because it will be a huge help in troubleshooting performance problems and I hope this was helpful to you!