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.<\/p>\n
Now with SSAS 2005, I decided to test how much of a size impact different numeric type values have.<\/p>\n
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:<\/p>\n
CREATE VIEW testView as
\nSELECT CustomerKey, ProductKey
\n, Amount — Original value
\n, Amount * 0.987654321 AS Amount1 — Multiplied by rate
\n, CONVERT(decimal(19, 2), Amount * 0.987654321) AS Amount2 — multiplied by rate but size specified
\n, CONVERT(int, Amount) AS Amount3 — Converted value to integer
\nFROM Sales<\/p>\n
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.<\/p>\n
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.<\/p>\n
Here are my results:<\/p>\n
Amount (As double in AS storage) – 7.22MB
\nAmount1 (As double in AS storage) – 11.0MB
\nAmount2 (As double in AS storage) – 7.22MB
\nAmount3 (As integer in AS storage) – 6.26MB<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[4],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/10"}],"collection":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=10"}],"version-history":[{"count":0,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/10\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=10"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=10"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=10"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}