Data refresh suddenly slow in BI Service

[amallen125]

I've got a fairly basic PBI dataset; source data is imported not live connected, source is several small excel files in SharePoint, pretty straight forward queries.  Our company has premium.  In the past it would consistently refresh in about 5 minutes in the service and a little longer in desktop.  Starting yesterday (February 23 2024) the published/service refreshes are taking several hours, the shortest was 30 minutes, but those are rare.  I pulled down the PBIX, and refreshed in desktop, and it takes 9 minutes, which is excepted becaue by local RAM isn't great.

I'm at a loss.  Other reports don't seem to be impacted.  Nothing has changed in the import or the source files.  Although I'm sure I could tune my queries, it seems like the BI service is the problem.

Please share any ideas or insights.  Thanks,

[amallen125 · Accepted solution]

I ended up publishing the same PBIX to a non premium workspace and saw a refresh time of 5 minutes.  This confirmed some sort of premium throttling.  The metrics didn't show any indication of system stress.  I ended up turning off the refresh schedule (luckily it was the weekend).  1.5 days later and it worked fine, no model changes, no source data changes.  bizarre stuff.  Thanks for everyone's input on this.

[christinepayton]

Ohhh you're probably just out of resources. In premium, you only have a set amount of resources to use based on the pricing scale. 

[christinepayton]

Are you using the SP Folder connector, or connecting to a specific file? The folder connector performance can vary quite a bit depending on how much content is in the site, so if someone uploaded a bunch of files elsewhere it could slow down your query depending on how it was set up. 

SharePoint will also throttle you if you try to make too many queries, which I THINK is on a per-user basis, so if you are doing a lot of other work on SharePoint sources elsewhere with the same user account you could be getting throttled. There's a thread here with workarounds for throttling:

https://community.fabric.microsoft.com/t5/Service/Avoid-throttling-when-loading-from-SharePoint-fold...

[lbendlin]

I can confirm that Binary.Buffer on SharePoint documents does help with the process. Not only does it seem to be faster, it also reduces the chattiness and largely avoids getting nasty 429's

[aj1973]

Hi @amallen125 

In premium capacity you can always find an explanation to what caused your issue. If you have access to the Premium Metrics App then follow this video to understand

Making sense of the Power BI Premium Gen2 Metrics App (youtube.com))

Understand the metrics app compute page - Microsoft Fabric | Microsoft Learn

If you don't have access then The Admin of the tenant should be able to explain it to you.

[lbendlin]

Since you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi