Query folding on SQL Summary Table ?

[itsmebvk]

Hi Folks,

I am trying to implement Incremental refresh on one of my Summary Table which I built using SQL ( Using  Azure Sql Server as DB).

my query looks as follows:

select 

column1,

Column2,

sum(Column3)

from Facttable

where Date Between cast('1/1/2021' AS Datetime) and cast('1/1/2021' AS Datetime)

I just used this query for summary table in Power query there are no transformations ( steps) done on it. But when I right click in source it is not enabling "view negative query" option. 

Now the question I have :

1) Is query folding happens on sql query where I dont have any transformations ( steps) done on power query?

2) When I check my SQL profiler I can see this query is being executed when I refresh my query, in this case can we consider query folding is happening or not?

The reason for these questions is, without query folding there is no point of using incremental refresh in my opinion!

if you think query folding wont happens in above query, please suggest what action needs to be taken to get query folding.

Thank you.

[watkinnc · Accepted solution]

If you are sending SQL to the source, with no Power Query transformations, then by definition it's in the source's native language and will fold. Additionally, if you wrap that SQL in Value.NativeQuery, with a parameter record as [EnableFolding = true], then any subsequent steps that would normally fold will fold into your original SQL. But ONLY if you use Value.NativeQuery. Here is Chris Webb's post explaining it:

https://www.google.com/amp/s/blog.crossjoin.co.uk/2021/02/21/query-folding-on-sql-queries-in-power-q... 

Value.NativeQuery("SELECT...WHERE...", null, [EnableFolding = true])

Then just go about your business!

--Nate

[edhans]

If you put that in the Advanced Editor that is why it is broken. Ideally you would redo those steps above in Power Query so everything would be folded. You can use the Value.NativeQuery function though to try and enable it. See this article. There are limitations, and it may not work with all data sources. Your steps above are pretty simple though. The issue I see is the CAST function. If your dates are text and you are converting to Date, that may be an issue and best handled by the server in a View you connect to. Also, incremental refresh requires a datetime field, not a datefield, so you can try to CAST it as datetime (it will just add 12:00:00 to it) and see if that works.

[edhans]

Hey @itsmebvk - was wondering if the Value.NativeQuery function I linked to in the article above helped you out here.