Semantic Model Refresh - Memory limit

[p_da]

Hello everyone,

I have a doubt regarding Semantic model refresh in the Power BI Service. For example, I have a FT1 Capacity (Trial) and I am trying to refresh a semantic model which is connected to a data lake. The table in the datalake, has more than 40GB of size. However, I am getting the following error:

Data source error: Resource Governing: This operation was canceled because there wasn't enough memory to finish running it. Either reduce the memory footprint of your dataset by doing things such as limiting the amount of imported data, or if using Power BI Premium, increase the memory of the Premium capacity where this dataset is hosted. More details: consumed memory 25726 MB, memory limit 25597 MB, database size before command execution 2 MB. See https://go.microsoft.com/fwlink/?linkid=2159753 to learn more. Table: sales.

That being said, how the refresh memory works ? I can only refresh semantic models that has the size less than the max memory available for the capacity ?

Thanks

[hackcrr · Accepted solution]

Hi, @p_da 

While dataset size is important, it's the memory required during refresh that counts. Even if your dataset is 40GB, it doesn't mean that Power BI will need the full 40GB of memory because it uses data compression. However, it still requires a lot of memory resources, especially during transformations and when working with complex queries or large tables in DirectQuery mode.
Reduce dataset size: Consider filtering your data to include only relevant periods (for example, focusing only on the last 12 months).
Incremental refresh: If applicable, implement incremental refresh to avoid loading the entire dataset each time. Incremental refresh only allows new or changed data to be loaded, reducing memory usage.
Complex calculated columns and measures can increase memory usage during refreshes. Simplify these calculations as much as possible or move them to a data source.
: For very large datasets, consider using the DirectQuery mode, which keeps the data in the source system and extracts the data only when needed. This can help avoid memory consumption during refreshes, but it also has other limitations (for example, reporting slower performance during interactions).

[tharunkumarRTK · Accepted solution]

@p_da 

I would suggest you to checkout this article to understand the memory consumption for data refresh operation 

https://blog.crossjoin.co.uk/2023/04/30/measuring-memory-and-cpu-usage-in-power-bi-during-dataset-re...

I addition to the @hackcrr suggestion, I would like to suggest one more approach, you can use enhanced refresh operation keep the commit mode as 'partialBatch', it can reduce the memory consumption.

https://blog.crossjoin.co.uk/2024/05/12/the-partialbatch-commit-mode-in-the-power-bi-enhanced-refres...

Please read the complete blog before testing. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[p_da]

Thanks for sharing the links, I will have a look on it to better understand the refresh consumption for Power BI semantics.

[hackcrr · Accepted solution]

Hi, @p_da 

While dataset size is important, it's the memory required during refresh that counts. Even if your dataset is 40GB, it doesn't mean that Power BI will need the full 40GB of memory because it uses data compression. However, it still requires a lot of memory resources, especially during transformations and when working with complex queries or large tables in DirectQuery mode.
Reduce dataset size: Consider filtering your data to include only relevant periods (for example, focusing only on the last 12 months).
Incremental refresh: If applicable, implement incremental refresh to avoid loading the entire dataset each time. Incremental refresh only allows new or changed data to be loaded, reducing memory usage.
Complex calculated columns and measures can increase memory usage during refreshes. Simplify these calculations as much as possible or move them to a data source.
: For very large datasets, consider using the DirectQuery mode, which keeps the data in the source system and extracts the data only when needed. This can help avoid memory consumption during refreshes, but it also has other limitations (for example, reporting slower performance during interactions).

[p_da]

Thanks, I really appreciate your feedback. I will work on some test.