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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s