Semantic Model Refresh Slow and Buggy for Large Amount of Models

[Vroom_Master3]

Hey everyone. I work in an environment with about 14 Workspaces, each averaging 3-4 reports with a matching semantic model. There are 2-3 Workspaces with 10+ Reports as well. What has me confused and worried is that the data sometimes takes 30-50 minutes to refresh when 90% of these data sets are under 1GB. We use scheduled refresh and everything routes to 2 gigantic servers (which have from my knowledge way more resources than this should take). We have the premium service for powerBI and really havent had too many problems up until the new year, and it has now become unbearable. To my knowledge (I made 95% of these reports and models) there are no high amounts of DAX and ETL Language in these models to slow them down that way. Most reports take 10-20MB of SQL Tables and imports them. I am sure I am not covering every single piece of information relevant, but I do not know what to add atm. Please ask and I will answer to help my situation. Or someone can tell me that 10 MB SQL Tables are supposed to take 50 minutes to refresh, which I doubt. 

Thanks ahead of time. 

[aj1973 · Accepted solution]

Hi @Vroom_Master3 

Through times Simantic models increase in size after refreshes. on the other hand if you don't increase the CU capacity of the workspace then the Slow-down at refresh will occure at some point. You can use incremental refresh if increasing capacity is not an option.

[v-yilong-msft · Accepted solution]

Hi @Vroom_Master3 ,

I think you can use incremental refresh, it reduces the refresh time because it can update only the changed data instead of refreshing the whole dataset every time. You can check this documentation below: Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn

Of course if you use incremental refresh. It will also check out for query folding for you, you can check out query folding related content at this link: Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn

Even though you mention that DAX and ETL are minimally used, it's still worth looking at the model to see if there are any unnecessary columns, tables or complex calculations that could be simplified. Simplifying the model as much as possible will reduce refresh time.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[R1k91 · Accepted solution]

have you ever verified when model go slow which table/partition took much/more?

Visualise your Power BI Refresh - Phil Seamark on DAX

maybe you've calculated tables/comlumns slowing down processing as your datasets become bigger or you could discover which table/view has more "floating performance".

since it's happening randomly it should be something related on the resources available (on the source, capacity or gateway if there's one there) and not on the processing phase.

as @aj1973 said capacity metrics app could help you verifying if an high pressure on your capacity is throttling your refreshes.

[v-yilong-msft · Accepted solution]

Hi @Vroom_Master3 ,

I think you can use incremental refresh, it reduces the refresh time because it can update only the changed data instead of refreshing the whole dataset every time. You can check this documentation below: Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn

Of course if you use incremental refresh. It will also check out for query folding for you, you can check out query folding related content at this link: Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn

Even though you mention that DAX and ETL are minimally used, it's still worth looking at the model to see if there are any unnecessary columns, tables or complex calculations that could be simplified. Simplifying the model as much as possible will reduce refresh time.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aj1973 · Accepted solution]

Hi @Vroom_Master3 

Through times Simantic models increase in size after refreshes. on the other hand if you don't increase the CU capacity of the workspace then the Slow-down at refresh will occure at some point. You can use incremental refresh if increasing capacity is not an option.

[Vroom_Master3]

How would I view the CU of a workspace? 

[aj1973]

You can install Premium Metrics App

https://www.youtube.com/watch?v=BmAuABIwhc0&t=465s