A while ago I wrote about some tricks to reduce the size of a tabular model, in which I experienced some strange results for SSAS Tabular Date Compression. I am lucky enough to be sat in Marco Russo’s Mastering DAX course (a benefit of helping organise the course) and it all became clear.
How are datetimes stored in Vertipaq?
Under the hood, datetimes are stored in tabular as floats – with the date portion as an integer and the time portion as the floating point component (i.e. the bits after the decimal point).
This explains my results:
- Dates compress well as they are treated as integers
- Datetimes compress terribly as they are treated as floats
- The less time precision you have (e.g. s vs. ms) the better the compression is as the float has lower precision
- Baselining a datetime to a specific date to isolate the time doesn’t work as you are still compressing a float
So the recommendations stand for handling date columns, i.e.:
- Load the date portion and time portions separately
- Load the date portion as a date
- Load the time portion as an integer of the unit of measurement since midnight
- Reduce the precision of the time portion to the lowest acceptable to the business