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
Anonymous
Not applicable

Scheduled update taking 2 hours on the power bi gateway

I have a panel in which updates to the power bi desktop take about 7 to 10 minutes.

When publishing the dashboard in the power bi service and performing the configuration of the data sources in the gateway, the update takes around 2 hours to complete.

 

There is some configuration that can be done on the gateway?

 

The gateway is a "standard mode" gateway installed on a computer with 8 GB of memory, SSD and i7 processor with 8 cores.

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @Anonymous 

 

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)

 

That said, there are MANY reasons why this can take longer.

To set expectations, Microsoft states that while the system tries to refresh at the requested time, it may be delayed for internal reasons (queues, resource allocation, etc).  That could be part of the delay.

Another delay could be if you are using a gateway and have some sort of internal network (or internet) issues (security, too much traffic , etc) that slows the ability of the refresh to be effective.  

Also, the queries themselves could be written in such a way that it takes a long time to refresh (the service refresh and the desktop refresh use different engines and react differently so it is quite reasonable that a desktop refresh is fast and a service refresh is slow).

It also could be that when you manually hit the refresh button, there is less concurrent requests.  For example, if you have 10 of your reports all set to refresh at 0900 then you are creating your own backup as all of those are potentially hitting the same datasource (may or may not be the same dataset) and that will cause delays.

 

The reason scheduled refresh takes longer is because PBI smooths scheduled refresh so AS Engine and Gateway is not flooded with request. PBI doesn't do smoothing for on-demand refresh.

 

Such as if you set scheduled refresh at 9:00 am, it may start at 9:14 am, and end in 9:16 am, so seems it refresh 10 - 15 min, but if execute an on-demand refresh at 9:00 am, it will start right now and may end in only few minutes. This document also mentioned : 

Note also 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.

 

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.

 

For reference:

Monitor and optimize on-premises data gateway performance

Troubleshoot the on-premises data gateway

 

Configure scheduled refresh

My dataset is taking too long to refresh!

 

Schedule vs Manual Refresh !!

Scheduled refreshes are slow, manual refresh is quick

 

 

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.

View solution in original post

5 REPLIES 5
v-angzheng-msft
Community Support
Community Support

Hi, @Anonymous 

 

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)

 

That said, there are MANY reasons why this can take longer.

To set expectations, Microsoft states that while the system tries to refresh at the requested time, it may be delayed for internal reasons (queues, resource allocation, etc).  That could be part of the delay.

Another delay could be if you are using a gateway and have some sort of internal network (or internet) issues (security, too much traffic , etc) that slows the ability of the refresh to be effective.  

Also, the queries themselves could be written in such a way that it takes a long time to refresh (the service refresh and the desktop refresh use different engines and react differently so it is quite reasonable that a desktop refresh is fast and a service refresh is slow).

It also could be that when you manually hit the refresh button, there is less concurrent requests.  For example, if you have 10 of your reports all set to refresh at 0900 then you are creating your own backup as all of those are potentially hitting the same datasource (may or may not be the same dataset) and that will cause delays.

 

The reason scheduled refresh takes longer is because PBI smooths scheduled refresh so AS Engine and Gateway is not flooded with request. PBI doesn't do smoothing for on-demand refresh.

 

Such as if you set scheduled refresh at 9:00 am, it may start at 9:14 am, and end in 9:16 am, so seems it refresh 10 - 15 min, but if execute an on-demand refresh at 9:00 am, it will start right now and may end in only few minutes. This document also mentioned : 

Note also 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.

 

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.

 

For reference:

Monitor and optimize on-premises data gateway performance

Troubleshoot the on-premises data gateway

 

Configure scheduled refresh

My dataset is taking too long to refresh!

 

Schedule vs Manual Refresh !!

Scheduled refreshes are slow, manual refresh is quick

 

 

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.

GilbertQ
Super User
Super User

Hi @Anonymous 

 

This could possibly be either the gateway server is having CPU/Memory pressure?

 

Or the network connectivity is not very fast between your Gateway server and the Power BI Service. 

 

If you can monitor this when the refresh is running to see which one is slower or consuming a lot of resources?





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

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

CPU/Memory is not overloaded, and in the case of the internet, I couldn't detect any instability in the tests performed.

 

Was there anything else I could be looking into?

 

Thank you in advance for your support.

@Anonymous : If you are sure about CPU/Memory and Network . I am suspecting your Power BI capacity, are you using premium capacity ? if so what is the size of the node (SKU)?, what is your model size?  how many tables are in your model ?. Your refresh will be throttled based on number of concurrent refreshes. If you have lot of other reports refreshing at the same time, everything will take longer to refresh.

Anonymous
Not applicable

I managed to improve my code using the Table.Buffer() function.

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