Database Reference
In-Depth Information
Figure 4-6.
Star and snowflake design patterns
Keep in mind that when you are designing your tables, they may not necessarily display in a circular pattern
as they do here, but it does not matter. The terms
snowake
and
star
are simply descriptive words that portray
how they are connected. Additionally, a single data warehouse can contain both design patterns.
Next, let's take a look at scenarios where one design pattern may be a better choice than the other.
Performance Considerations
In most cases, a set of tables can simply be collapsed into a single star dimension table. One argument in favor of
the star design is that single tables are simpler to work with, even if they decrease storage efficiency by containing
redundant values. The opposite argument is that by reducing redundancy, you increase storage efficiency;
therefore, using separate tables by normalizing them is a better choice. Kimball and Inmon disagree on this
subject as well, with Kimball siding with the single table star design and Inmon taking the snowflake stance.
Keep in mind that performance is relative to action, and the performance related to the action between the
functionality of stars versus snowflakes is no exception. If you were to create a report based on star-designed
tables, you would have fewer tables to connect to in your SQL joins. However, the redundancy of data in the
tables means that there is more to transfer from the hard drive into memory before your query's results are
assembled. The action of SQL Server finding and linking two tables decreases performance, but the hard drive
I/O performance is lowered because of the reduction of redundancy data. From this single example, you can see
that performance considerations are not as straightforward as they may appear at first.
In general, the simplified joins of a star design give the best performance for smaller tables (few columns
and thousands of rows), whereas the snowflake design has better performance for larger tables (many columns
and millions of rows).
