Fixing Query Folding with SQL Server in Power BI for Incremental Refresh

The pitfalls of using Native Database Queries to import data from SQL Server

Photo by ROMAN ODINTSOV from Pexels

Incremental refresh on the Power BI datasets has three significant benefits; faster refreshes, more reliable refreshes, and reduced resource consumption. Incremental refresh is designed for data sources that support query folding. This might give an impression that the query will fold regardless of how the data transformation has been set up.

This assumption, however, is far from the fact: transformation steps need to be implemented appropriately to ensure query folding. When troubleshooting, transformation steps are the usual suspects that developers look at. Nevertheless, one of the foremost reasons is using native SQL queries to import the data.

This article looks at how to fix query folding to set up incremental refresh when a native database query is involved in importing the data from the SQL Server.

Please read our terms of use before proceeding with this article: https://dhyanintech.medium.com/disclaimer-disclosure-terms-of-use-fb3bfbd1e0e5

Query folding is Power Query's ability to generate a single query expression to retrieve and transform source data. Most SQL-based data sources such as SQL Database, Azure Synapse, Oracle, PostgreSQL, and Teradata support Query Folding. The Power Query mashup engine strives to achieve query folding whenever possible to maximize efficiency and performance.

Power Query can run native database queries to import data from some relational sources. This feature can save you the time to build queries using the Power Query Editor and reusing the already existing complex queries.

However, one crucial consideration while using a native database query is to bear in mind that incremental refresh can't use a native SQL query. So, it would force the Power Query mashup engine to retrieve all the rows from the source and then apply filters to determine the incremental changes.

Why can't incremental refresh use native queries?

As mentioned earlier, incremental refresh is designed for data sources that support query folding. After a model is published to the Power BI service, the service automatically overrides the range parameters to query the data defined by the refresh period specified in the incremental refresh policy settings. For incremental refresh to work, Power BI should be able to send a final constructed query to the source with the appropriate date filters applied.

Determine when a query can be folded

There are at least three different ways to determine whether a query can be folded or not:

• Using the Power Query Editor window
• Query Diagnostics
• SQL Profiler

We will stick to the first method as it's the most straightforward and widely used. It's possible to determine when a query can be folded in the Power Query Editor window. In the Query Settings pane, when you right-click the last applied step, if the View Native Query option is enabled, then the entire query can be folded. It's worth mentioning that this method is also the least reliable of all the three methods. The View Native Query option may be greyed out in some instances, even though folding occurs in the backend.

Power Query Editor: Check query folding (Image by author)

Setup

I will use the Wide World Importers sample Azure SQL database as the data source for this article. In my Power BI report, I have a query that uses a native SQL query to import a table from the source. I have also defined and applied RangeStart and RangeEnd parameters and filters to the query to implement the incremental refresh.

Power Query Editor: Query Setup (Image by author)

Power Query Editor: Advanced Editor (Image by author)

A quick check in the Power Query Editor shows that Power Query can't achieve query folding.

Power Query Editor: Query not folding (Image by author)

If I try to configure the incremental refresh policy, I get a warning as Power BI can't verify the query folding. Note that Power BI doesn't prevent me from configuring the incremental refresh. Nevertheless, it isn't significant without query folding.

Power BI: Incremental Refresh- Query folding warning (Image by author)

Fixing Query Folding

There are two ways to fix the query folding in this case. First, the good old way: eliminate the native database query, use Power Query Editor to build the query, and apply only the transformation steps that support folding. Sounds exhausting, right? Second, use the Value.NativeQuery M function to enable query folding for the native queries.

Value.NativeQuery

Evaluates query against target using the parameters specified in parameters and the options specified in options.

Value.NativeQuery(target as any, query as text, optional parameters as any, optional options as nullable record)

• target provides the context (e.g., a reference to the SQL server) for the operation described by query.
• query describes the query (e.g. a T-SQL statement) to be executed against target.
• The optional parameters may contain either a list or record as appropriate to supply the parameter values expected by query.
• The optional options may contain options that affect the evaluation behavior of query against target. These options are specific to target.

There's an option EnableFolding that enables query folding for the native database queries. It's documented here for the PostgreSQL connector, but it also works for the SQL Server connector.

The next step is to make changes in your query to redesign the steps to use the Value.NativeQuery() function and set EnableFolding = true in the fourth parameter. I removed the T-SQL query from the Source step. Added a new step Run Native Query with Value.NativeQuery().

Below is the redesigned query and some explanations of what’s happening here:

• The Source step returns a reference to the Azure SQL database and is passed as thetarget
• The original T-SQL query is the query to be executed
• I have no parameters in the T-SQL query hence parametersis null
• EnableFolding = true is one of the options

Power Query Editor: Advanced Editor- Using Value.NativeQuery (Image by author)

A quick check and Power Query can now determine that query can be folded.

Power Query Editor: Query folding fixed (Image by author)

Here's the folded query, the native database query I entered turned into a subquery. Power Query added the range filters as conditions and built the main query. As simple as that. I'm now free to set up the incremental refresh without any warnings or issues.

Power Query Editor: Final folded query for native query (Image by author)

A word of warning, this doesn't mean that all the native database queries can be folded now, e.g., the query won't fold when passing parameters in the native database query or any other undocumented scenarios. Unfortunately, if your complex query doesn't fold with this method, you have to go back to method one and eliminate the native query.

For the sake of completeness, I want to show how does the final folded query for method one (i.e., using Power Query Editor to create the query) differ from using the Value.NativeQuery() function.

Power Query Editor: Final folded query for Power Query query (Image by author)

That's all, folks. Let me know if you have any cases where this method doesn't work.

Like this post? Connect with Dhyan

Let’s be friends! You can find me on LinkedIn or join me on Medium.