While a snowflake schema can result from normalization of the dimension tables, it can also arise through dimension table reuse, particularly when there are relationships between dimension tables. For example, a relational data warehouse might contain a Customer dimension table and a Geography dimension table; either table could be related directly to a fact table and the resulting model might then be a star schema. Another alternative would be to relate the Geography dimension to the Customer dimension, so that the Customer dimension table contains a foreign key reference to the Geography dimension (a customer resides at a specific geographic location), and the result would be a snowflake schema. In this case, the Customer and Geography dimension tables might only be normalized to second normal form as in a star schema.

Regardless whether the snowflake schema results from normalization of the dimension tables or from relationships between the dimension tables, one advantage of a snowflake schema is that the dimension tables are more versatile. For example, if a Time dimension is normalized into several dimension tables - a Year table, a Month table, and a Date table - then it is possible to have multiple fact tables each of which stores facts at a different level of granularity with respect to time; one fact table could relate to the Date table, while another could relate to the Month table or the Year table. As another example, if the warehouse contains a Customer dimension and a Geography dimension, then the Geography dimension can be used to describe facts directly (e.g. the fact took place in a specific Geography) and can also be used to describe Customers (e.g. a Customer resides at a specific location).

See Also

• SQL Server Analysis Services (SSAS)
  • Unified Dimensional Model
• SQL Server Overview

Other Languages

This article is also available in the following languages:

Brazilian Portuguese (pt-BR)

• Snowflake Schema (pt-BR)