Vidas Matelis Analysis Services Blog

Years ago, while I was using Analysis Services 2000, I had a strange problem. I migrated one of the cubes to be loaded from a SQL Server View instead of the actual table, and I noticed that the cube size almost doubled. It took me some time to find what was causing this. My original table had a few dozens fields with type decimal(19, 2). They were loaded into analysis services database with type double. After I migrated my fact table into this view, I also migrated some calculations. So I was multiplying my fields of decimal(19,2) type be another rate field with a decimal(9,8) type. As my rate field was valued between 0 and 1, I thought that this multiplication would not increase the size of the original field. That was a wrong assumption, as I significantly increased scale of the numbers. Instead of loading into cube values like 123.45, I was loading values 123.450123456789. All these extra numbers where not significant, but they were using a lot of space in the analysis services database. I quickly fixed my problem by converting calculated value to field with a size I was interested in: CONVERT(decimal(19, 2), Field * Rate) AS Field.

Now with SSAS 2005, I decided to test how much of a size impact different numeric type values have.

In SQL Server I created a table with 2 int type dimension keys and one measure Decimal(19,2). Into this table I loaded 1mln records. In the measure field I loaded random values with average value of 50mln. To test storage impact, I created a view that converts original amount into different presentations:

CREATE VIEW testView as
SELECT CustomerKey, ProductKey
, Amount — Original value
, Amount * 0.987654321 AS Amount1 — Multiplied by rate
, CONVERT(decimal(19, 2), Amount * 0.987654321) AS Amount2 — multiplied by rate but size specified
, CONVERT(int, Amount) AS Amount3 — Converted value to integer
FROM Sales

Here 0.987654321 is just a rate value that still makes Amount fields value close enough to what it was, but at the same time increases its scale.

Based on this relational structure I created an Analysis services database. In the test cube I created just a single measure. During my test I was replacing source of the measure with a different Amount field and then testing the cube size.

Here are my results:

Amount (As double in AS storage) – 7.22MB
Amount1 (As double in AS storage) – 11.0MB
Amount2 (As double in AS storage) – 7.22MB
Amount3 (As integer in AS storage) – 6.26MB

As you can see Amount1 measure storage takes 50% more space in the cube. That is quite a big difference. So if you do any calculations while loading data into your cubes, make sure you convert your results to a data type that is reasonable for the case.