1146
Part IX: Business Intelligence
when designing dimensions a new column, a surrogate key, will be added to each table. This
column is usually an identity column, which is used to relate the fact to the dimensions.Assume you are required to replace your current Student Information System and all
StudentIDs will be changed. If you were using the primary key to relate your facts
and dimensions, this could require several changes in your data warehouse to stu-
dent dimensions, and the entire fact table needs to be updated. In addition, potential
confl icts may be introduced if the new system reuses existing keys from the old sys-
tem. By using surrogates you can avoid confl icts and correct the student dimension
with a few updates.Designing Your Data Warehouse Using a
Snowfl ake Schema
In some cases it may be necessary to limit the amount of denormalization done when
building your dimensions. This results in a slight modifi cation to the star schema.
Instead of the star schema, it may be referred to as a snowfl ake schema. This method is
often effective for large complex dimensions. This approach becomes relevant when the
loading process involves consistency or sequencing. Figure 51-2 shows a snowfl aked prod-
uct dimension.FIGURE 51-2
Snowfl ake Dimension.c51.indd 1146c51.indd 1146 7/31/2012 10:28:42 AM7/31/2012 10:28:42 AM
http://www.it-ebooks.info