Forum Discussion
table size from vertipaq analyzer
I'm trying to figure out the slow refreshing of a PB report. In my model, there are two calendar tables: Dim_Date is created via DAX code, Dim_SlicerDate is a copy of Dim_Date (DAX expression: Dim_SlicerDate = Dim_Date). Dim_SlicerDate is not connected to any other table, so I assume the table size of both should be the same. But when I have run the vertipaq analyzer, it shows the total size of Dim_SlicerDate is much bigger than Dim_Date. I couldn't figure out the reason since they are exactly the same table.
Hi Jeanxyz ,
This is a great observation — and yes, it can be confusing at first.
Even though Dim_SlicerDate = Dim_Date creates a copy of the table, Power BI treats it as a separate physical table in the model. That means it will have its own storage, encoding, and dictionary structures — even if the data is identical.
Now, regarding the size difference:
Unused relationships
Since Dim_SlicerDate is not connected to any other table, the engine might not optimize its storage the same way it does for Dim_Date, which is likely part of active relationships and used in visuals/measures.Encoding differences
VertiPaq may choose different encoding strategies for each table depending on how it's used. Even if the data is the same, the context (e.g., usage in visuals, filters, slicers) can lead to different compression results.Column usage
If Dim_SlicerDate is used in slicers or visuals with high cardinality columns (like full date or datetime), it can increase memory usage significantly.
What you can try:
- Use TREATAS() or USERELATIONSHIP() instead of duplicating the table if you only need it for slicers.
- Or, create a slimmed-down version of the slicer table with only the necessary columns (e.g., just Date or Month).
- You can also try using calculated columns or disconnected tables with fewer columns to reduce memory footprint.
Let me know if you want help restructuring the model — happy to assist.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI
3 Replies
Hi Jeanxyz ,
This is a great observation — and yes, it can be confusing at first.
Even though Dim_SlicerDate = Dim_Date creates a copy of the table, Power BI treats it as a separate physical table in the model. That means it will have its own storage, encoding, and dictionary structures — even if the data is identical.
Now, regarding the size difference:
Unused relationships
Since Dim_SlicerDate is not connected to any other table, the engine might not optimize its storage the same way it does for Dim_Date, which is likely part of active relationships and used in visuals/measures.Encoding differences
VertiPaq may choose different encoding strategies for each table depending on how it's used. Even if the data is the same, the context (e.g., usage in visuals, filters, slicers) can lead to different compression results.Column usage
If Dim_SlicerDate is used in slicers or visuals with high cardinality columns (like full date or datetime), it can increase memory usage significantly.
What you can try:
- Use TREATAS() or USERELATIONSHIP() instead of duplicating the table if you only need it for slicers.
- Or, create a slimmed-down version of the slicer table with only the necessary columns (e.g., just Date or Month).
- You can also try using calculated columns or disconnected tables with fewer columns to reduce memory footprint.
Let me know if you want help restructuring the model — happy to assist.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AIThanks for sharing. In my case, the difference is mainly caused by "mark as date table". If I mark Dim_SlicerDate as date table, the total size shrink 10+Mb to 0.3MB.
Thanks for the follow-up — that makes sense.
Yeah, marking the table as a date table can definitely help with storage, especially if it enables optimizations like auto-generated date hierarchies being skipped or better encoding.
Good catch — might help others running into similar memory issues with disconnected slicer tables.
Let me know if you test any other tweaks, always curious to learn more from real cases.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
