Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
robarivas
Post Patron
Post Patron

Initial Incremental Refresh Fail

Set up my first Incremental Refresh. It ran for 8 hours on the Service and then failed with the following error message:

 

{"error":{"code":"DM_GWPipeline_Gateway_TimeoutError","pbi.error":{"code":"DM_GWPipeline_Gateway_TimeoutError","parameters":{},"details":[],"exceptionCulprit":1}}}

 

Would it help if I initially upload the .pbix with most of the data loaded (thru the Desktop) already? In other words, if, in the Desktop, I set the RangeStart and RangeEnd dates to encompass most of the data would the Service still try to refresh that large range on its first refresh or would it instead only go after the data after the RangeEnd parameter? I hope that makes sense.

 

Or is there something else entirely going on here?

12 REPLIES 12
GilbertQ
Super User
Super User

Hi there

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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?

 

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,
Kelly
Did I answer your question? Mark my post as a solution!

 

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

 

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

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?

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.

Support Ticket.gif

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

Anonymous
Not applicable

@robarivas Did you find a solution for this? I'm facing the same issue

Hello @Anonymous I am still working with Microsoft on a ticket for this. However, I tried changing the (SQL) server name in the M query on the dektop file (pbix) from uppercase to lowercase to match how I have that SQL server connection setup on the gateway and I also, for now, removed joins/merges to some dataflows. One of these changes seems to have allowed the report to successfully execute incremental refresh. Either one is ridiculous in my opinion. If/when I can confirm which (if either) was the resolution I'll post another response.

@robarivas ,

 

I was wondering if you managed to resolve this already. 

 

I have the same error with several reports. Some of them with incremental refresh and others without. 

 

The reports are refreshed without errors in desktop but I'm getting the same error as you do in the service. 

 

Thanks in advance. 

Hi 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?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi 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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors