SSAS Tabular in memory compression

2
votes

I am testing SSAS tabular on my existing data warehouse. I read that compression of data in memory will be fantastic, up to 10 times. The warehouse weights about 600MB, analytical model has about 60 measures (mostly row counts and basic calculations). In sql server management studio I checked what is the esimated size of analytical database: ~1000MB. Not what I expected (was hoping for 100MB at most).

I check memory usage of msmdsrv.exe process using a simple Resource Monitor. To my surprise, after full processing of the database memory consumption of the msmdsrv process jumped from 200MB to 1600MB. I deployed second instance of the same model connected to the same source and it grew to over 2500MB. So estimated size was in fact correct.

Data Warehouse is quite typical - star schema, facts and dimensions, nothing fancy.

Why was the data not compressed in any way? How is it possible that it takes even more memory than the uncompressed source warehouse?

I will be most grateful for any tips on this mystery :)

1
ssasssas-tabular

1 Answers

1
votes

You should read and watch Marco Russo materials about vertipaq analyzer. You can find what part of your model take most of your memory.

https://www.sqlbi.com/articles/data-model-size-with-vertipaq-analyzer/ https://www.sqlbi.com/tv/checking-model-size-using-vertipaq-analyzer-in-dax-studio/

And maybe this can get you some light: https://www.microsoftpressstore.com/articles/article.aspx?p=2449192&seqNum=3

Tabular Model is based on column Store that mean if you have many unique value in column then you get lower compression (for eg. incremental ID column like transactionID).
-> Omit high-cardinality columns where possible -> Try to split columns when possible If you have DateTime columns, you should split them into two parts (date and time). You have then more reapeted values -> Sort Order of data in partitions may have affect to compression rate [Run Length Encoding (RLE)] -> Use measure (it takes no space) instead of a calculated column (it takes up)

Run Length Encoding (RLE)