Microsoft SQL Server 2012 Bible

Designing Your Data Warehouse Using a Snowflake Schema

In some cases it may be necessary to limit the amount of denormalization done when building your dimensions. This results in a slight modification to the star schema. Instead of the star schema, it may be referred to as a snowflake 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 snowflaked product dimension.

Figure 51.2 Snowflake Dimension.

Instead of placing the product, productcategory, and productsubcategory into one dimension, each individual table was included in the data warehouse schema. This approach does add some slight complexities when building an OLAP cube. Therefore, careful consideration should be taken before implementing this modeling approach.