DAX Studio - Table Size Returning Unexpected Results

Power BI
1

Hi there,

First attempt with DAX Studio here, and I can’t seem to understand the behaviour of the VertiPaq Analyzer Metrics with this very basic example. Any insights or guidance would be appreciated. Apologies in advance if this topic was discussed previously.

The situation:

I created a simple one-column table with dates (from 1 Jan 2015 to 31 Dec 2021). The size of the table in DAX studio is 14,584.

I add a quarter column to the date table (Q1/Q2/Q3/Q4, ie cardinality = 4), therefore expecting the size of the table not to grow that much, because of the HASH compression. To my surprise, the size of the table jumped to 1,080,320. The size of the quarter column is 1,065,736. How is that possible?

By adding a third dummy column which only contains the value “1” for each row. The size of the entire table then drops to 33,180 (the size of the quarter column is now 17,184). I also tested that the table doesn’t go back to 1,080,320 if I then delete the dummy column. Is this behaviour expected? I attached my PBIX, but the problem sometimes only happens when the steps above are recreated.

DAX Studio
DAX Studio752×349 111 KB

DAXStudio Testing.pbix (43.0 KB)

2

@Stevoh When data is loaded first time in the Vertipaq storage the engine doesn’t have a correct estimate of how large the dictionary will be so it assigns around 1 megabyte to dictonary of each column for Power BI and Power Pivot models and 8 megabytes for SSAS. After the data is loaded the columns are compressed and then the size of the dictionary can be reduced or increased and for the exact numbers you have to save the file and re-open it.

Since you are working with a Calculated table, any modification in the code will refresh the data and will re-create the dictionary of 1 MB each time you make a change.

5 Likes
3

Thanks mate for your answer!

1 Like
4

@AntrikshSharma ,

And my daily goal to learn at least one new thing about Power BI gets checked off the to-do list at 9:54 AM.

This is great info – I had no idea about that initial dictionary assignment.

Thanks for sharing your expertise.

– Brian

2 Likes