Forum Discussion
Incremental refresh and memory usage/DAX Studio
Hi amir_mm ,
Typically reducing the data volume would result in decreased memory usage. However, in the context of Power BI’s incremental refresh, during a full refresh operation, the service maintains a snapshot of the model in memory until the refresh operation is complete. This can use as much as double the amount of memory required by the model alone.
With incremental refresh, the service dynamically partitions and separates data that needs to be refreshed frequently from data that can be refreshed less frequently. This means that even if you reduce the refresh policy to 6 months, the historical data (past 5 years) is still maintained in the memory.
Therefore, while it might seem intuitive that reducing the refresh policy would decrease memory usage, the actual memory usage depends on a variety of factors including the size of the model, the partitioning of the data, and the data loading process. It’s possible that these factors are contributing to the consistent memory usage you’re observing.For more you may refer to：
Solved: Re: Dataset incremental refresh end memory consump... - Microsoft Fabric Community
Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn
Best regards.
Community Support Team_CaitlynOh that`s cool.
Yes, pls check your model for unexpected impacts.
Pls check filter interaction regarding speed for rendering your visuals.
Be careful to disable MDX in your whole model.
MDX will have an impact in usage in Excel. Hopefully you don`t have that much excel users.
Try by your own which impact disabling MDX has for you in excel.
This article is a very cool one for that topic: https://data-mozart.com/hidden-little-gem-that-can-save-your-power-bi-life/
Another approach to reduce a bit the size is disabling Auto Date/Time table(s) (hidden tables).
They could have an impact on your size.
Remove unused columns (I think you did it already).
Regards
Thank you sergej_og
We have a PPU (embedded) license with A2 capacity which is 5GB memory limit.
Previously, during refreshes, DAX Studio showed a memory usage of 1.95GB, consistently resulting in memory limit errors. To resolve this, I transferred some calculated columns to the SQL server and some others to Power Query, and removed many unused columns. Now, with these transformations in place alongside incremental refresh, the memory usage during a refresh has decreased to 1.35GB. While refreshes are generally successful, we still face some failures throughout the day (as we refresh the dataset every 30 min).
I don't know what additional steps I can take to further reduce memory usage.
A2, that`s a pity.
So you don`t have the XMLA capabilities.
Via Tabular Editor (using XMLA endpoint) you could shrink your model size by disabling MDX.
don't have the tool (DAX Studio) to hand currently. Not sure about possibility to tweek a bit the size of columns in your model without having XMLA capabilities.
Regards
Thanks again sergej_og
Yes we do have the XMLA endpoint capabilities as well.
Disabling MDX seems to be great! I just disabled it for all columns in some large tables through tabular editor, and the memory usage in last refresh dropped from 1.35GB to 1040MB which is great, but I'll have to check the reports and the visuals to make sure it does not have any negative effects on them.
And if you know any other methods to further reduce memory usage through XMLA endpoint,I would greatly appreciate it if you could share it with me. Thanks!
Oh that`s cool.
Yes, pls check your model for unexpected impacts.
Pls check filter interaction regarding speed for rendering your visuals.
Be careful to disable MDX in your whole model.
MDX will have an impact in usage in Excel. Hopefully you don`t have that much excel users.
Try by your own which impact disabling MDX has for you in excel.
This article is a very cool one for that topic: https://data-mozart.com/hidden-little-gem-that-can-save-your-power-bi-life/
Another approach to reduce a bit the size is disabling Auto Date/Time table(s) (hidden tables).
They could have an impact on your size.
Remove unused columns (I think you did it already).
Regards