I have Large Dimension in My Tabular
If you are serious about SSAS Tabular development you must follow http://www.sqlbi.com. One of the topics to bear in mind is SSAS DB structure and especially the relationship and cardinality of relations.
I have potentially 5 dimensions in my model which has a size of over 1 million rows, so I must do a test.
Let’s do an experiment
So, let’s compare the following two scenarios.
1 – Having one big Product dimension with 1.9 Million products having several attributes with low granularity (30 and 40)
2 – Propagate key of the Attributes from product and wrap them into separated dimension linked directly to Fact Table.
You can see the scenario in the following figure.
We had the following conditions for my real-life demo:
- 200 million rows in fact table
- 40 distinct combinations of Product Code, Type and Color in Facts
- 1.9 million products.
- 2 rows in dimensions of Product Types
- 8 rows in dimension of Product Category
- 10 rows in Product Color
- My test query was generated from Excel – consists of making cross join of all three attributes.
Result of the test:
As you can see in my article, you could save almost 40% of you query time by changing the architecture of your Tabular model. Of course, such a change has some other consequences in ETL and Data Mart layer but that’s a different topic from I wanted to focus on in this article.
This devide-and-conquer approach works for most situation very well, but you discard the fact that you loose insight in the devided dimensions interlationsship, without the facts that happened to them. The outcome if you select something from all your small dimensions is equal to the same selection from the large dimension if you combine it with a measure from the fact table. BUT you can not use the product dimension anymore to create the distinct list of possible combinations of those small dimensions, only the ones which are in the data of the fact table will show…. I.e. your approach makes it impossible to answer the question: which combination of Product Category and Product Color doesn’t sell?