The
snowflake schema is an extension of the star schema, where each
point of the star explodes into more points. In a star schema, each dimension
is represented by a single dimensional table, whereas in a snowflake schema,
that dimensional table is normalized into multiple lookup tables, each
representing a level in the dimensional hierarchy.
Sample snowflake schema
For
example, the Time Dimension that consists of 2 different hierarchies:
1.
Year → Month → Day
2. Week → Day
2. Week → Day
We
will have 4 lookup tables in a snowflake schema: A lookup table for year, a lookup
table for month, a lookup table for week, and a lookup table for day. Year is
connected to Month, which is then connected to Day. Week is only connected to
Day. A sample snowflake schema illustrating the above relationships in the Time
Dimension is shown to the right.
The
main advantage of the snowflake schema is the improvement in query performance
due to minimized disk storage requirements and joining smaller lookup tables.
The main disadvantage of the snowflake schema is the additional maintenance efforts
needed due to the increase number of lookup tables.
Ref : http://www.1keydata.com/datawarehousing/snowflake-schema.html
