Overview

In SQL Server Analysis Services (SSAS), snowflake schema describes a popular data model for relational data warehouses. Similar to a star schema, a snowflake schema contains fact and dimension tables, with the fact table storing facts about business transactions and some columns of the fact table containing foreign key references to the dimension tables. Snowflake schemata differ from star schemata in their level of normalization; the dimension tables of a snowflake schema are typically normalized to third normal form (3NF) or higher. As a result, the data for a given dimension is spread out over multiple tables, and a diagram of the database resembles a snowflake.

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)