Database Reference
In-Depth Information
■
Remember that whether you choose to use a star or snowflake design, the data in your report is exactly
the same. Even if you do not make the “best” choice, your design will still work.
Tip
In summary, it is more efficient to store data in a snowflake design, but it is more convenient to store it in
a star design. Many data warehouses end up as a hybrid with some of the dimension tables designed in the star
design pattern and other dimension tables designed in the snowflake design pattern. Nothing says you can't have
both!
If you still can't decide which to use, follow this advice: when possible, use the star design for simplicity. If
you come across a circumstance where you need to reduce redundancy, change the design to a snowflake. In the
end both are simply tools that will enable you to get the job done, so choose the tool that is appropriate for the
job.
Comparing Designs
Figure
4-7
compares the tables used in a star design and a snowflake design. If you design the Stores and States
tables according to the star design philosophy, you would take the StateName and Abbreviation columns from
the States table and collapse them into the Stores table (as shown in DimStores_StarVersion). Conversely, if you
design the same two tables according to the snowflake philosophy, you leave it in two separate tables (as shown
in the DimStores_SnowflakeVersion and the DimStates_SnowflakeVersion).
Figure 4-7.
Star versus snowflake designs
■
For learning purposes, we use a hybrid approach in our exercises, to provide experience with both.
This means that some dimensions are designed as a snowflake and others as a star.
Tip
