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.
Hi robarivas ,
I'm guessing it's query folding issue,you can turn to the similar thread below:
https://community.powerbi.com/t5/Desktop/Query-Folding-disable/m-p/797007
Kelly
- robarivas6 years agoPost Patron
Thanks again v-kelly-msft for your response. However, that link has to do with disabling Query Folding, which I'm sure must be the opposite of what I would want. My problem, I suspect, might be that Query Folding isn't happening whereas it ought to be. Or maybe something else entirely is going on here. I don't know. Hopefully the Community and/or Microsoft employees can assist.
GilbertQ I do not have familiarity (nor access I believe) to run SQL Profiler. The entire data set (180+ million rows) takes roughly 6 hours to load in to the Power BI data model on the desktop. I do not believe indexing has been set up on the SQL Server table (yet).
I'd just really like to know why I receive the Incremental Refresh Query Folding warning. None of the possible reasons for why that warning should/would show up appear to exist in this case. I suspect that warning might help explain and/or have something to do with the exceedingly slow initial refresh problem that originally motivated this thread.
I do know that steps after the Incremental Refresh parameter filter break query folding but my understanding was that as long you've got query folding maintained up through the step where the RangeStart and RangeEnd filters are applied you should be good to go. Might that not be the case?
- v-kelly-msft6 years agoCommunity Support
Hi robarivas ,
I cant reproduce your scenario,so better create a support ticket via below link:
https://powerbi.microsoft.com/en-us/support/
Here is the reference about how to create the support ticket.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!- Anonymous6 years agoNot applicable
robarivas Did you find a solution for this? I'm facing the same issue