Query Folding & Incremental Refresh

[shaunwilks]

Ok stick with me here.

We are updating our reports to utilise PowerBI Pro now being able to utilise Incremental Refresh (Yay!)

We made all the required changes, parameter editions filtered rows entries - everything.

When we setup incremental refresh we get the warning..

Unable to confirm if the Query can be folded.

We proceed and publish to the PowerBI Service and on refresh via an On Premise Gatewya and received the following error

Something went wrong  Unable to connect to the data source undefined.

Please try again later or contact support. If you contact support, please provide these details.

Underlying error code: -2147467259

Underlying error message: Resource name and Location need to match. Resource name: Table Name.

Firstly if this error related specifically to Query Folding then......

1) Why couldnt the PowerBI Desktop prevent Incremental Refresh from being configured

2) Why wouldnt the powerbi service just report that the incremental refresh parameters could not be added to the database query

Anyway I digress. Documentation suggests that query folding is only recognsied when the "View Native Query" context menu option is available in the Query Editor for that particualr query.  None of our queries have this option available as - for good reason - our queries are in the structure...

let
    Source = Sql.Database(#"Microsoft SQL Server Name", #"Database Name", [Query="SELECT * FROM TABLE", CreateNavigationProperties=false])
in
    Source

Everything in our queries are in the "Sql.Datbase" query. Whenever you right click and try to select "View Native Query" for these queries - PowerBI does not provide you that option - and I therefore suggest it would be unable to query fold that query also.

3) When there is a [Query] element to the Sql.Database command I would have thought this would be easy - but it wouldnt appear as if that this has been taken into consideration.

So I have to look at what works for incremental refresh and look to restructure the queries so the native query can be accessed and subsequently the incremental refresh work without error.

So for View Native Query to work - it has to look more like...

let
    Source = Sql.Database(#"Microsoft SQL Server Name", #"Database Name", [CreateNavigationProperties=false]),
    dbo_TABLE = Source{[Schema="dbo",Item="TABLE"]}[Data]
in
    dbo_TABLE

This is great and all but how do we pass a "Where" Clause to the query that is executed so only the a limited number of records are read into the PBI report dataset ? As I look through examples it would appera you are meant to do this via a "FilteredRows" step. 

Kudos to https://blog.pragmaticworks.com/power-query-controlling-m-query-functions-with-user-driven-parameter...

This results in the query looking more like something below (in an example the query is to only ever get records where the title is a MR)

let
    Source = Sql.Database(#"Microsoft SQL Server Name", #"Database Name", [CreateNavigationProperties=false]),
    dbo_table = Source{[Schema="dbo",Item="TABLE"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_TABLE, each [TITLE] = "MR")
in 
    #"Filtered Rows"

This still works and I can still see through the View Native query option that it appends a "WHERE" to the SQL query to be executed. So this looks fine in theory - HOWEVER when I run a trace on my SQL Server I can see that the SQL Server while it executes a query including the WHERE clause, there is a seperate execution of code that does not append the WHERE or ORDER BY clauses (if a sort order was specified also in a seperate step).

So here is my great worry.  I have a transactions table with 2billion records in it.

In the FIlteredRows step I am telling the Native Query to just get 50 rows matching a criteria.

If PBI does what I am seeing and still executes a SELECT without the WHERE clause its going to get messy very quickly.

That is even before i get to Incremental Refresh.

4. If the View Native Query includes a WHERE clause because of a Filtered Rows step - Can we guarantee that the WHERE clause would be included in every Refresh ??

[edhans]

I'm not sure you are understanding query folding, or perhaps how to read the trace on the SQL server. You want to connect to your data base without any advanced "SELECT * WHERE" clauses. Just connect to the table and start filtering as desired. For example, If I connect to the Adventureworks DB on my SQL server, and connect to the Sales Order Detail table, it has 121,000 records.

But if I filter as follows:

1. Filter to only show records modified after Aug 31, 2012
2. Only pull line totals that are between $5K and $6K
3. Remove all but 5-6 columns

Power Query generates the following M code:

let
    Source = Sql.Databases("localhost"),
    AdventureWorks2017 = Source{[Name="AdventureWorks2017"]}[Data],
    Sales_SalesOrderDetail = AdventureWorks2017{[Schema="Sales",Item="SalesOrderDetail"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Sales_SalesOrderDetail, each [ModifiedDate] > #datetime(2012, 8, 31, 0, 0, 0)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [LineTotal] >= 5000 and [LineTotal] <= 6000),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"SalesOrderID", "SalesOrderDetailID", "CarrierTrackingNumber", "OrderQty", "ProductID", "SpecialOfferID", "LineTotal"})
in
    #"Removed Other Columns"

But it will 100% fold. Power Query doesn't do each step. It reads the entire query, then folds everything it can in this case, all if it, and sends the following SQL query to the server for me:

select [_].[SalesOrderID],
    [_].[SalesOrderDetailID],
    [_].[CarrierTrackingNumber],
    [_].[OrderQty],
    [_].[ProductID],
    [_].[SpecialOfferID],
    [_].[LineTotal]
from [Sales].[SalesOrderDetail] as [_]
where ([_].[ModifiedDate] > convert(datetime2, '2012-08-31 00:00:00') and [_].[LineTotal] >= 5000) and [_].[LineTotal] <= 6000

So the server does all of the work, and I get back 709 records.  You can see it includes the WHERE clause.

If I add anything after that that breaks folding, it will still return the 709 records up to the point folding breaks, then do the rest in the local Power Query/Gateway engine.

I have joined up to a dozen tables and done a lot of transformations, and Power Query will generate SQL statements a few hundred lines long. I think the biggest I've done was a SQL statement just over 800 lines.

In your example of 2B records and you are returning 50, if that happens very fast, it is getting fully folded. I assure you it would take a LONG time for 2B records to be read into the PQ engine then all but 50 discarded.

[shaunwilks]

Thanks for your detailed response.

I am happy that that is what it is doing especially in a normal refresh from the report design page.

Have you traced a "Refresh Preview" in the query editor against SQL Server ?

Its definitely executing a full statement without the WHERE clause after its Native Query with the WHERE clause. That is what has made me nervous.

I also have complexities around transformations and date formats that are going to make the process difficult to re-structure my queries to suit the generation of a Native Query so it can be used in incremental refresh as its parameters demand a datetime. 

[edhans · Accepted solution]

Previews limit what is shown and returned and I wouldn’t trust those to reflect what really happens on a full refresh. Only analyze that kind of data.

You can use YYYYMMDD as a date for incremental refresh. See https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh

Just do the incremental refresh before any transformations that you know are going to break folding. It is all about optimizations.

[edhans]

by the way @shaunwilks - take a look at this Guy in a Cube video. There are times the "View Native Query" option is grayed out yet folding is still happening! He shows how to use Power Query diagnostics to get a detailed list of the communications between PQ and your SQL Server.