1. 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.

1. 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:

1. Conclusion

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.