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?
The easiest way to do this, is you could disable the parallel loading of tables in your PBIX.
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.
This looks like it would be a great solution if it had a way to set a short delay.
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).
Any other suggestions?
Thanks but I feel like we are talking about two separate things here:
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.
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.
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.
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.
Do you think I could go into each Query and just add a "Function.InvokeAfter" instead?
Query 1: Function.InvokeAfter 10 seconds
Query 2: Function.InvokeAfter 20 seconds
Query 3: Function.InvokeAfter 30 seconds
Query 4: Function.InvokeAfter 40 seconds
Query 5: Function.InvokeAfter 50 seconds
If that would work, would this be the correct syntax?
Query 1 current:
let Source = Csv.Document(Web.Contents("httpsURL"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), in #"Promoted Headers"
Query 1 modified with Function.InvokeAfter
let Source = Function.InvokeAfter(Csv.Document(Web.Contents("httpsURL"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),#duration(0,0,0,10)), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), in #"Promoted Headers"