(2MB) PBI Refresh via Webservice - Extrem slow

[DataNerd6000]

Hi all,

I have a Datamodel (2 MB) which takes sometimes 30 minutes to refresh via the Webserivce.

The Model is very simple and contains 5 Tables, all Tables are stored in Azure SQL DB. The refresh via the Desktop App takes like 15 seconds.

It appears to be random how long the refresh via the Web Service takes, sometimes a minute and on most of the days >20 Minutes.

The refresh of the Data Model is triggered via API with Azure DataFactory & Logic App).

Does anyone have an idea what the problem could be?

-> The Database has 200 DTUs and the workload is <5% during the refresh

-> I´m using a Pro Licence 

Thanks in advance!

[v-angzheng-msft]

Hi, @DataNerd6000 

Note that the configured refresh time might not be the exact time when Power BI starts the next scheduled process. Power BI starts scheduled refreshes on a best effort basis. The target is to initiate the refresh within 15 minutes of the scheduled time slot, but a delay of up to one hour can occur if the service can't allocate the required resources sooner.

refer:

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data#configure-scheduled-refresh

If you needs the report be ready by certain time, schedule the refresh ahead of the time or execute on-demand refresh.

You can also monitor gateway performance by viewing gateway logs and perform performance analysis to check for performance failures.

To monitor performance, gateway admins have traditionally depended on manually monitoring performance counters through the Windows Performance Monitor tool. We now offer additional query logging and a Gateway Performance PBI template file to visualize the results. This feature provides new insights into gateway usage. You can use it to troubleshoot slow-performing queries.

Also, if there is a deeper reason here, then I would recommend to open a support ticket.  If you have a Pro account it is free.  In my experience they are pretty good at getting back to you in a timly manner. 

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-angzheng-msft]

Hi, @DataNerd6000 

This is a situation that may be encountered when refreshing.

Based on my research, it turns out that the start time for schedule refresh shown on the refresh history is not exact time the refresh started. It's the time that Power BI started to prepare for the refresh, and that's why additional requests are sent(for preparation)

I explain it in more detail in the following thread:

Scheduled update taking 2 hours on the power bi gateway

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataNerd6000]

Hi @v-angzheng-msft ,

thanks for your reply but I´m not satisfied with your potential solution, here is why:

- I´m not using a Gateway for the Dataset

- I don´t have any other Dataset which is refreshing in that timeslot

- I´m already using the API Trigger to avoid the refresh rush hours, since the scheduled refresh is only possible at *:30 & *:00H.

- I dont use complex queris, the Datamodel only contains SELECT statements, without Joins and further logic

I need the Report to be refreshed before 09:00, but cannot schedule it earlier, since I need Data which is only available after 08:00.

Best,

DataNerd6000

[Burningsuit]

Hi @DataNerd6000 

Is the Dataset you are refreshing on a Pro, PPU or Premium Capacity Workspace ?

The answer may shed some light on why the refresh is taking so long.

Stuart

[DataNerd6000]

sorry, forgot to mention that information - I´m using a Pro Workspace.

[Burningsuit · Accepted solution]

OK, @DataNerd6000 That's probably the issue.

If you're on a Pro workspace, you are sharing the Pro capacity with all other Pro users on the MIcrosoft Cloud. You don't see them or their data, and neither do they see you, but you're all using the finite resources Microsoft have put in place to service Pro users. I don't know how many users that is (Microsoft don't release numbers) but in 2016 it was over 5 million. That's why when you schedule a refresh your request is queued and may be run up to 1 hour later. It's not just your refresh that the service is running, but ALL the refreshes requested by ALL the Pro users. (Yes Microsoft splits this up geographically, but we don't know how many are on each Geography, just like we don't know how many in total). Thats why Refreshing on a Pro workspace takes a variable amount of time. Microsoft add servers and improve the service, but more users join and more features are provided that use up Server time. I guess for Microsoft it's a compromise between having millions of Servers standing idle just in case, or Users complaining that Refreshes take more than an hour.

Your alternatives are to use a Premium Capacity or Premium Per User (PPU) backed Workspace. Premium Capacity can be thought of as renting your own server from Microsoft in the cloud. It costs more, but you get a dedicated capacity that only you use, so you don't have these service contention problems. PPU offers many benefits of Premium Capacity, but at a lower per-user cost. PPU runs on faster servers and has fewer users, so in many cases will give you a faster refresh.

All of what @v-angzheng-msft is correct. To speed things up your options are to upgrade to a PPU or Premium Capacity workspace,  it really depends how much you need that refresh to complete quickly.

Hope this helps

Stuart

[DataNerd6000]

@Burningsuit thanks for your reply.

I was ware of the shared resources, but didnt expect such a delayed input. Anyway, thanks for your help!

Best,

[MarkSL]

Hi, not sure if this is related, but we are also seeing all of our datasets refreshing extrmely slowly (and some failing) this morning.  

Example below, average run time 3-5 minutes, today 40 mins:

And another, avg time 1 minute, today 50 mins:

Every dataset is the same.  All usually run fine, today all still running, have failed or completing > 10 times slower.

Running Premium P1,

Mark