Query Folding and Access Native Query for Incremental Refresh

[JackFrench]

I am using data from Teradata and I have it brought in using Import mode. The table is huge with 100s of millions of rows at 50+ columns. I want to set up incremental refresh for this table to optimize efficiency. As I am watching these videos I see that one of the first steps you need to do is make sure Native Query is appearing to ensure query folding is active. However, when I right-click my steps, Native Query is greyed out for the majority of my queries. I tried to go into the advanced editor and change the M to something like:

let

Source = Teradata.Database("database_name", [HierarchicalNavigation=true]),

#"Run Native Query" = Value.NativeQuery (

Source,

"Select sub.*, 
#(lf)(Current_Date) as DateRefresh, 
#(lf)1 AS RowCountIndicator#(lf) from (Select * FROM table) AS sub;", 

null, 
[EnableFolding = true]),


#"Reordered Columns" = Table.ReorderColumns(#"Run Native Query",{"RowCountIndicator", "IA_rxItemPriceException_Id", "Purchase_NDC", "Supplier_Name", "Wholesaler_Identifier", "Account_Num", "DateRefresh"})


in

#"Reordered Columns"

This returns an error saying: Expression.Error: 'EnableFolding' isn't a valid option for this function. Valid options are: (none)

I came across an article that told me to remove the "EnableFolding = true])" and this loaded the data in the Power Query editor the new "Run Native Query" step and the "Reordered Columns" step now show a Native Query option. However, when I add another step to the mix it does not give me the option so I don't think this actually works. Any solutions???

[ToddChitt]

What happens if instead of fancy M logic like this, you query the table directly and then build your transformation steps in Power Query? Does that allow query folding at ALL levels? Does it even do query folding at the top SELECT step?

[JackFrench]

My thought process was to do all transformations prior to loading it into PBI to lessen the load. Then just load the query in using import mode and apply incremental refreshing to the report to make it even swifter

[ToddChitt]

That is what Query Folding IS! It pushes data transformation steps (like filtering) and many others down to the source , letting those operations be handled by the server. The result is that the server is probably better equipped to handle the steps natively, and it hopefully results in fewer rows returned by the query, and less processing overhead by the client (Power BI).

Below is a very simple example of query folding on my Customer table for customers in the city of Buffalo:

Let the query folding happen naturally. Don't try to force it. If your source is capable (things like Excel and flat files are NOT capable) then it just happens automagically.

Hope that helps.

[JackFrench]

Gotcha. Well, some of my queries have some pretty complex transformations. I'd like to keep these transformations as is in the SQL. However, I want to add incremental refreshing to my table which requires query folding to be enabled. I was hoping to just add a filter for the Date range ontop of my original SQL and have this filter be equal to parameters labeled "RangeStart" and "RangeEnd".

[ToddChitt]

>> some of my queries have some pretty complex transformations<<

How complex? I don't know about Teradata, but SQL Server can handle a lot of the transformations listed, even GROUPING and PIVOT/UNPIVOT. I chokes on things like splitting a field based on the instance of a delimiting character, or things like FILL DOWN.

If you absolutely MUST have your custom transformations and they preclude query folding in Power BI, then the best bet is to wrap all that in a view in the source database and simply reference the view instead of a base table. 

But Power BI needs A) Query Folding and B) appropriate date column in order to support Incremental Refresh.

[Anadi_Anant]

Hi, I was reading this and I wonder In case of DB like AS400/DB2 which doesnt support Value.NativeQuery, what if we pass Parameters (Start & End) as strings to native sql (complex) to be used in where clause. For sure the the model will refresh, but would it be able to create proper partition and proper incrmenatal setup?

RS = "'" & DateTime.ToText(RangeStart, "yyyy-MM-dd HH:mm:ss") & "'",

RE = "'" & DateTime.ToText(RangeEnd, "yyyy-MM-dd HH:mm:ss") & "'",

and inside native query we apply:
WHERE T.SNAP_DT >= " & RS & " AND T.SNAP_DT < " & RE & "