Monthly Archives: January 2014

Dimensional Schema Design

Hey guys! Whenever we need to do Dimensional Modeling for building DWs, the very first question that comes to our mind is to follow which Dimensional Schema Design: Star or Snowflake. I have complied list of differences between the two designs and you can decide which one best suits your requirements.

S.No. Star Snowflake
1. Single Central Fact surrounded by dimension tables One fact connected to many dimensions which further connects to other dimension tables
2. Uses de-normalized data that introduces redundancy Use normalized data thus eliminating redundancy
3. Business Hierarchy is not maintained via Referential Integrity. Dimensions directly refer to Fact table Business Hierarchy is maintained via Referential Integrity
4. Difficult to maintain and change Easy to maintain and change
5. Involves less complex queries Involves complex  queries
6. High performance\ less query execution time due to foreign keys Low performance\ more query execution time due to foreign keys
7. Preferred when relationships are 1:1 or 1:Many Preferred when relationships are Many:Many
8. ETL Design is simpler and can have parallelism  dimensions and facts loading is independent of each other ETL Design is complex and no parallelism since dimensions and facts loading is dependent

 

Note: Selection of Dimension Schema depends on Business Requirements. If you think star schema best fits the business needs then go for it and same with snow flake schema.

 

If you like the post please provide constructive comments.

Have Fun!

Advertisements