Power BI Native Query and Query Folding

By:   |   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.

Getdata from SQL Server in Power BI

Specify the Server and Database

Select Tables in the SQL Server database

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

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.

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

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

View Native Query in Power BI

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.

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.

Add a calculated column to our Power BI query

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).

right clicking on the Applied Step for the customer calculated column displays an updated query with the 80% applied to the query

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

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.

View Native Query is grayed out after the split column apply step is completed
 

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.

Native Query functionality is not just available for SQL Server

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

Native Query functionality is not available against an Excel data source

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

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


Click here to start the Power BI course






sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Scott Murray 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



Comments For This Article




Thursday, April 26, 2018 - 7:49:56 AM - Scott B Murray Back To Top (75785)

 

 Ron, My understanding is that it will be enabled for those steps, but not available to run for those steps.  That could have changed in the 2 years since writing this tip.  The final step in the process is to run the "ultimate" query with all possible steps applied (at whatever place it stops folding).


Thursday, April 26, 2018 - 5:57:45 AM - Ron Back To Top (75784)

 Scott,

Thanks for the good info. I have two questions/comments:

1. You say that once query folding stops, no subsequent query folding will take place as indicated by the absence of Native Query in the subsequent steps. Although I understand that once it stops it cannot resume, but nonetheless I see Native Query enabled following steps for which Native Query is not enabled?!

2. I assume that each of the Native Queries seen in each of the steps are combined to create a single query which is then sent to the data source - is this correct?

 

Thanks in advance,

Ron


Monday, July 10, 2017 - 7:27:04 AM - Scott Murray Back To Top (59186)

 

Generally the query folding takes place if there is a readily available function to complete the task (transformation ) at hand.  I do not have an exact list.  

You can use the design editor in SQL Management Studio to help you write your SQL.


Saturday, July 8, 2017 - 11:48:07 AM - Travis Back To Top (59089)

Hi Scott,

First Question....Do you know why the native Query folds after you perform any transformation to a column. I.e when I split a column based on a common delimiter, the native Query folds... I know you can write SQL code in SSMS to perform this task, but wanted to know why Power Query folds at this step? Is this a limited feature for Power Query, are there workarounds for this tasks that do work with Native Query, and don't fold?

Second question...,I want to be able to use Power Query to write SQL code for me... but am seeing some of the limitations. Are there any other solutions like Power Query out there that will write SQL code for you?

thanks!

 















get free sql tips
agree to terms