Forum Discussion
Initial Incremental Refresh Fail
How long did your dataset take to refresh before the incremental refreshing?
Whilst you can upload it with all the data, the first refresh will always do a complete refresh in order to set up the incremental refresh policies.
Thanks for your response GilbertQ
It takes roughly 6 hours to load on the Desktop. Thanks for confirming that uploading a large portion of the data won't actually help with the first initial refresh. So this leads me to think that Incremental Refresh is great for growing a dataset into a large one and especially for speeding up refreshes -- the key part of that word being "re" . However, Incremental Refresh must not necessarily be the right/best way to backload a very large amount of data, which is what I've been trying to do. Would you agree with this thought?
If so, then I guess my only option is to limit my historical data to an amount that will initially refresh in a reasonable amount of time. So I've got to lose some history, which is unfortunate.
What's strange maybe is why am I facing this on a dataset that's less than 200 million rows and under 4GB. Haven't larger datasets by others been setup on scheduled and/or incremental refresh?
- GilbertQ6 years agoSuper UserHi there
Are you using Power BI Premium?
It would appear so if your dataset is under 4GB which is larger than what is available on Power BI Pro.
What happens if you look at the Premium Capacity metrics App?
Also what is the bandwidth speed like between your source and Power BI?
I have had experiences where the datasets take VERY long to refresh because the bandwidth is very slow. - v-kelly-msft6 years agoCommunity Support
Hi robarivas ,
Pls see below:
Publish to the service
You can now refresh the model. The first refresh may take longer to import the historical data. Subsequent refreshes can be much quicker because they use incremental refresh.
Query timeouts
The troubleshooting refresh article explains that refresh operations in the Power BI service are subject to timeouts. Queries can also be limited by the default timeout for the data source. Most relational sources allow overriding timeouts in the M expression. For example, the expression below uses the SQL Server data-access function to set it to 2 hours. Each period defined by the policy ranges submits a query observing the command timeout setting.
Here is the reference.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!- robarivas6 years agoPost Patron
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.
- GilbertQ6 years agoSuper UserHi there
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?