Forum Discussion
Defragment not working
Hi,
As explained by cpwebb in this article: https://blog.crossjoin.co.uk/2022/11/14/why-you-should-defragment-your-fact-tables-if-youre-using-incremental-refresh-in-power-bi-premium/
I used "Defragment" for a table in a dataset and that significantly reduced the dictionary size.
I did the same for another table in a different dataset multiple times, but that did not change the dictionary size at all.
Any idea how I can reduce this size? Does it have anything to do with disabling MDX?
Thanks a lot!
11 Replies
If defragmenting your table doesn't reduce its size, then there probably isn't anything else you can do. Are you trying to reduce the size of your table? If so then you will need to look at things like splitting datetime columns into separate date and time columns or rounding numbers in decimal columns, both of which can reduce the cardinality of your columns. See articles like this for more details https://www.sqlbi.com/articles/optimizing-high-cardinality-columns-in-vertipaq/
Thanks a lot cpwebb
Yes, I'm currently trying to reduce the size of tables. Despite removing all unused columns, optimizing calculated columns, and even transferring some columns to PQ and SQL Server, I continue to face the following error, although DAX studio VertiPaq shows only 800MB used memory and we have 5GB RAM with our embedded capacity:
"Resource Governing: This operation was canceled because there wasn't enough memory to finish running it."
In the Power BI service, I've scheduled a refresh every 30 minutes with incremental refresh enabled. Interestingly, I sometimes don't see this error for up to 10 consecutive hours (which means 20 refreshes). But, intermittently, the refreshes begin to fail, sometimes even failing twice in a row.
These failed refreshes consistently occur between 3 minutes and 3 minutes 30 seconds. So, if a refresh exceeds 4 minutes, it usually completes successfully.
I'm struggling to identify the root cause of this issue. If it is indeed related to calculated columns, I will transfer more columns to SQL and Power Query. But, I'm really not sure where to look.
Thanks for your time!
You're running into something called the Command Memory Limit, which is documented here: https://learn.microsoft.com/en-gb/power-bi/enterprise/troubleshoot-xmla-endpoint#resource-governing-command-memory-limit-in-premium It's also something I will address in an upcoming blog post in a series I started here https://blog.crossjoin.co.uk/2024/04/28/power-bi-semantic-model-memory-errors-part-1-model-size/ (reading this post will provide some useful background).
If you're using an A2 with 5GB of RAM and your existing model is 800MB, then you have 5GB - (800MB plus some extra memory used by queries and sessions) left for your refresh, so say 4GB. The next thing you should do is run a Profiler trace against your model (see https://blog.crossjoin.co.uk/2020/03/02/connecting-sql-server-profiler-to-power-bi-premium/ for how to do this) and capture the Command End events when you run a refresh. From this you'll be able to see the peak memory used during the refresh as a whole and for just Power Query (see https://blog.crossjoin.co.uk/2023/04/30/measuring-memory-and-cpu-usage-in-power-bi-during-dataset-refresh/) and for just Power Query for individual table partitions (see https://blog.crossjoin.co.uk/2023/04/02/identifying-cpu-and-memory-intensive-power-query-queries-during-refresh-in-the-power-bi-service/). My guess is that the peak memory for the refresh as a whole is going over 4GB. If so, then you need to look at the Power Query memory usage number for individual partitions and see if there are any memory hungry Power Query queries - if so, they need to be tuned. If not then it's likely to be your calculated columns that are the problem still. Reducing the amount of parallelism during refresh may also help reduce the peak memory usage (see https://blog.crossjoin.co.uk/2022/10/31/speed-up-power-bi-dataset-refresh-performance-in-premium-or-ppu-by-changing-the-parallel-loading-of-tables-setting/).
If peak memory for the refresh is a lot less than 4GB then it's possible that the problem isn't the refresh but that users are running memory-hungry queries during the refresh. Running a Profiler trace during a refresh and looking for Query Begin/End events will tell you if queries are being run; there is a new Profiler event coming very soon which will tell you more about query memory usage but before that happens it's impossible to know how much memory a query consumes in the Service but you should see it happen easily in Power BI Desktop by looking at what happens in Task Manager when you view or interact with a report. If this is the problem then you'll need to tune your model and measures to reduce memory usage. This antipattern is a very common example of DAX that can cause memory spikes: https://xxlbi.com/blog/power-bi-antipatterns-9/
If you are able to post some screenshots of your Profiler traces and the memory usage numbers please let me know - I'm curious to see what you find!
HTH,
Chris
I guess the reason scenario (1) works is that, initially, there's no data in your model and since the tables are partitioned that will reduce the amount of parallelism indirectly. If you are ok with your model not being queryable during refresh you could set up incremental refresh but always do a refresh of type ClearValues (which deletes all the data from your model) first. That feels a bit hacky though and I suspect there's no going on here that needs investigation.
The Parallel Loading Of Tables setting in Power BI Desktop is the easy way to control the amount of parallelism at the table level: https://blog.crossjoin.co.uk/2022/10/31/speed-up-power-bi-dataset-refresh-performance-in-premium-or-ppu-by-changing-the-parallel-loading-of-tables-setting/ Setting this to One will ensure all your tables are refreshed sequentially.What do you mean by "child" tables? Are these dimension tables? Are you deriving them from your fact tables somehow, maybe in Power Query?
Thanks again cpwebb , I followed the same steps to update the Parallel Loading Of Tables setting in Power BI Desktop.
Since the PeakMemory for the refresh as a whole is around 3.5 GB and I still face Memory capacity errors (even after tuning the power query for a table with very high MashupPeakMemory) , I looked into the calculated columns again and found something interesting:
We have 2 fact tables, and each fact table in linked to multiple dimention tables:
Fact table A with 350,000 rows.
Fact table B with 9 million rows.
There was a calculated column in Fact table A which was referencing both Fact table B and a dimension table linked to the table B.Before removing that specific calculated column:
PeakMemory for the refresh as a whole: 3.5 GB
PeakMemory for Fact table A: 1.95 GB
PeakMemory for Fact table B: 2.85 GB
PeakMemory for the dimension table: 1.8 GB
After removing that specific calculated column:
PeakMemory for the refresh as a whole: 2.4 GB
PeakMemory for Fact table A: 1.3 GB
PeakMemory for Fact table B: 1.4 GB
PeakMemory for the dimension table: 175 MB
Considering the PeakMemory of 2.4GB, I still sometimes face memory errors despite being on an A2 SKU with 5GB of memory.
(I have defined an incremental refresh policy for the last 6 months with maxParallelism set to 3).
Since each table uses around 150-200MB of peak memory, and we have only 2 tables with a peak memory usage of around 1.4GB, could the parallel loading of tables be causing this error?
I believe that maxParallelism = 3 applies only to the partitions, not the tables. Is that correct? It would be great if we could schedule a refresh to refresh each table sequentially, similar to how we process the whole table in SSMS Analysis Services.
I even created a PowerShell script in Azure Runbooks to schedule a refresh with Logic Apps, but when I add "maxParallelism = 3" to the script, I get an error.
Thanks a lot!
No, maxParallelism applies to any individual refresh job inside a single Batch/Sequence command, so it can apply to tables or partitions. The setting I mentioned here https://blog.crossjoin.co.uk/2022/10/31/speed-up-power-bi-dataset-refresh-performance-in-premium-or-ppu-by-changing-the-parallel-loading-of-tables-setting/ controls the parallelism of refresh for tables, so setting this to 1 should help reduce the peak memory.
Can you also share the definition of the calculated column you were talking about?
