Forum Discussion
Initial Incremental Refresh Fail
Hello GilbertQ and v-kelly-msft
I am using Premium. After beating my head aginst the wall on this I think I see a problem but can't figure out what's causing it or how to get past it. I notice that when I set up incremental refresh on the desktop there is a warning about how it cannot verify if query folding will take place. So that's a bad sign. However, I can't see what I might doing wrong for that error/warning to appear. I'm not supplying raw SQL, the View Native Query menu is not greyed out, I'm using a source that supports folding (SQL Server), and the field I'm using for Incremental Refresh is datetime as is the RangeStart and RangeEnd paramater. So it seems to me I'm doing everything right yet I'm still getting the error/warning about Query Folding. Here are the steps in my query:
let
Source = Sql.Database("server", "database", [CommandTimeout=#duration(0, 2, 0, 0)]),
dbo_table = Source{[Schema="dbo",Item="table"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_table, each [LAST_UPDATED] >= RangeStart and [LAST_UPDATED] < RangeEnd)
And when I select View Native Query on the last step here is the query that seems to be going back to the source (which suggests to me that I should expect query folding to be happening):
select [_].[Field1],...,[_].[FieldN]
from [dbo].[table] as [_]
where [_].[LAST_UPDATED] >= convert(datetime2, '2019-10-01 00:00:00') and [_].[LAST_UPDATED] < convert(datetime2, '2019-10-03 00:00:00')
Could it be that subsequent steps after these ALSO need to maintain query folding? Or is there something wrong with the steps above? Or is something else going on?
By the way I did try to upload a version of the .pbix file with a very small refresh policy (store only 1 month and refresh only last day) and that has been running for over 8 hours. So I do think the problem might have to do with the query folding instead of my initial concern around data size.
Can you use SQL Profiler when the refresh starts to see if the query is running back to the source and being used?
Also on your table does it have indexes to ensure that it returns the data quickly?
How long does the above query take to run when you run it on the SQL Server source?