Power BI Native Query and Query Folding

By: Scott Murray   |   Updated: 2016-11-30   |   Comments (4)   |   Related: > Power BI

Problem

What is the Power BI Native Query and how is it used?

Solution

The fairly new Native Query functionality in Power BI is a welcome addition to the Power BI development process. It provides us with a glimpse of what is going on "behind the scenes" when we create and transform certain datasets in Power BI Desktop. It is often good to see the actual query which will be running against a database which will in turn allow us to design the most efficient way to retrieve the needed data. The initial versions of Power BI were, sort of, a Black Box when attempting to identify exactly what query was being executed for a dataset. The reason behind wanting to examine the queries being executed by a Power BI Desktop model center around a concept called Querying Folding.

Koen Verbeeck produced an excellent tip on the concept of Query Folding and its benefits. Thus, we will not go into the full details of the process, but we will say that query folding provides a way for a dataset to be filtered before the data arrives back at the Power BI Desktop client. Power BI actually adds criteria to the Where clause based on transformations and filters that are applied to a query. So for instance, if you only want to see red cars in a query, you would add a filter to the cars table based on a color of red. When Power BI initialed such a data pull, it would add a criteria line to the query which would say "color = 'Red' ". Of course, for a small dataset, that may not be important, but for larger datasets, it could make the difference between 1,000 rows and 20 million rows. Unfortunately, query folding is limited to a specific set of operations or actions performed against a query or table (more on this item later in the tip). As you will note in Koen's tip though, he mentions the only way to find out what Power BI was "doing" under the hood was to run a tool like Profiler. The problem with that idea is that Profiler is specific to SQL Server; that is where Native Query comes in.

To get started with the Native Query functionality, the first step is to download the latest edition of Power BI Desktop (PBID). Additionally, you need to do a few things to get everything setup for access including signing up to access Power BI. Note that you must use a work or school address to sign up for Power BI.  Gmail, Yahoo, and similar accounts will not work.  Once you have your account setup, you are ready to start exploring the features available in Power BI including using Query Folding and Native Query tools.

Letting a Query Do What It Does Best

Our first steps in the design process is to open Power BI Desktop and select the Get Data option. Next, as shown in the example below, we select a sundry of different tables from the AdventureWorks database.

Once Power BI Desktop has added all our selected tables, we can begin to transform the data.

After clicking on OK, the filter step gets added to the Applied Steps panel, as shown below.

Now, if we right click on the Filtered Rows step, we are given an option to show Native Query.

The Native Query window opens, illustrated below, and we can clearly see that the query that Power BI sends to our AdventureWorks database is limited by the color criteria.

Next we add a calculated column to our query, as shown below for a discounted list price.

Once again, right clicking on the Applied Step for the customer calculated column displays an updated query with the 80% applied to the query (in scientific notation).

Let us use the Split Column function on the Size field in the Product's table.

Now when we right mouse click on the Split Column Applied Step, the Native Query option is grayed out. That means that the Query Fold and Native Query functionality stopped at the prior step. Once these functions are disabled, no further "down the list" transformations will allow you to view the Native Query function nor will Query Folding be applied. However, and this point is important, if you go to the PRIOR step, the Native Query options is still enabled. As you can see, the order of your transformation steps is very relevant and important.

On a positive point, the Native Query functionality is not just available for SQL Server, but it is also available for other data sources that allow custom SQL. The following screen shot shows the Native Query results against a Teradata database.

However, the same Native Query functionality is not available against an Excel data source, for instance.

Generally, if custom SQL queries cannot be run against the source, the Native Query function is not available. Other sources which do not have a Native Query option include: text files, streaming and other online sources, and most data sources in beta. Additionally, if no "SQL" version of a function or transformation is readily available, generally, the Native Query function will be disabled at that point. Unfortunately, I have been unable to discover a reliable source for the list of functions, sources, and transformations which disable Native Query display (and Query Folding).  Even so, the availability of this tool is a giant step forward in producing efficient Power BI data sources and troubleshooting both performance and other issues with data sets in Power BI.

Conclusion

This tip focused on the recently made available Native Query tool; this tool provides a preview of the query that Power BI Desktop will actually run to get the data for the Power BI model. The query preview is a huge step forward in validating what Query Folding is occurring in a query sent from Power BI which in turn ultimately can assist in testing the performance of a Power BI dataset retrieval.

Next Steps

• Review all the Power BI Tips on MSSQLTips.com

Learn more about Power BI in this 3 hour training course.

About the author

Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips