Stagger scheduled updates on WEB csv data refresh

Question

I have a server that struggles with the way Power BI pulls data from it via a URL.

Let's say I have 5 separate data sources with 5 different URLs. All these data sources are in one dataset.

When I refresh all the data, Power BI starts to request an update from all 5 URLs simultaneously which freaks out the server.

If I manually refresh each data source individually, I don't get this issue.

Is there a way to set a staggered schedule update on this report in Power BI service so that it at least waits 10 seconds between each URL refresh? Perhaps some query parameter?

Thank you!

gilbertq · Answer

Hi there
&nbsp;
The easiest way to do this, is you could disable the parallel loading of tables in your PBIX.
&nbsp;
If you go into Options and Settings, then click on Options.
Then under CURRENT FILE, then click on Data Load you can remove the tick from "Enable parallel loading of tables" as shown below.

&nbsp;

workhard · Answer

Thanks!&nbsp;This looks like it would be a great solution if it had a way to set a short delay.&nbsp;From my observation, it looks like it doesn't download them all at once but it still "requests" them all at once.So it's hitting the server with 2 requests for each data set. Once to check if the server is alive (all at once) and the link is valid, and the second time is to load the data (one by one).&nbsp;Any other suggestions?

gilbertq · Answer

Hi thereI do have a blog post, where you can change how long you want to wait between each call to a Web APIYou could stagger each one if you know roughly how long they would take.Here are the details below: https://www.fourmoo.com/2018/07/17/using-the-power-function-invokeafter-to-determine-how-long-to-wait-between-api-calls/

workhard · Answer

Thanks but I feel like we are talking about two separate things here:&nbsp;In the blog post you have, you are using a custom function to stagger the number of requests to the same API, in the same function, on the same table.&nbsp;My situation is different. I have 5 queries that create 5 separate tables. Each query/table has a URL source, 5 total. How would you join all 5 queries in one and then enumerate through each with a delay? I don't even know where to start here. Not to mention that each of these URLs has authentication, different amounts of columns, and I would somehow have to connect them all in one function and then split them into 5 tables.&nbsp;My issues isn't that I'm being limited by the API, the server hands over the entire csv file in one request. And completes the request in ~2 seconds.During these 2 seconds, Power BI "spams" the server with 4 more requests (the other 4 URLs) which freaks out the server because it didn't even finish hading over the first but Power BI asks for another 4.&nbsp;&nbsp;&nbsp;If there's a way to enumerate through my existing queries, without rewriting the query from scratch and just set a delay between each query, that would be ideal.&nbsp;&nbsp;&nbsp;

gilbertq · Answer

Hi thereAs far as I know you would need to re-write the queries for your use case.

Forum Discussion

Stagger scheduled updates on WEB csv data refresh

7 Replies

Related content

update scheduling

MySQL Scheduled Update

Scheduled pipelines failing

Updating Schedule refresh for Power BI Report

App not updating