The snowflake schema is nothing but normalized star schema; some dimensions of star schema are normalized and extended by creating associated details table. As opposed to star schema where each dimension is represented by single dimension table in snowflake schema dimension table is normalized by associating detail or lookup table to dimension table which also helps to represent dimensional hierarchy.
e.g. in Snowflake diagram as you can Customer Dimension table has detail Address table associated with one-to-many relationship
This will help to get the customer address data when report required it avoiding un-necessary attributes in query and making Customer Dimension table lightweight. Also with this design we can also track the customer address history as Address table is a SCD.
Advantages of Snowflake Schema:
1. The biggest advantage of snowflake schema is improved query performance.
2. Reduces dimension table size.
Disadvantage of Snowflake Schema is additional maintenance efforts needed due to the increase number of lookup or detail tables.
Subscribe to Email to receive all the FREE updates and Business Objects Interview Questions and Answers FREE!!
Related posts:
{ 1 comment… read it below or add one }
Snowflake schema will decrease the performance of the queries as more number of joins will be required to retrieve the required information.