Snowflake Schema

Snowflake Schema - Specific organization of a database, often used in data warehouses. It is similar to a Star Schema except that the dimension tables are decomposed into a tree structure with potentially many nesting levels. This dimensional hierarchy, which is normalized into a set of smaller dimension tables, can be depicted with a SHAPE / Shape similar to a snowflake.

[Category=Data Governance ]

Source: The Data Governance Institute, 25 December 2009 10:27:05, http://www.datagovernance.com/glossary-governance/

---

These advertisers support this free service

---

Snowflake Schema - A snowflake schema is a set of tables comprised of a single, central fact table surrounded by normalized dimension hierarchies. Each dimension level is represented in a table. Snowflake schema implement dimensional data structures with fully normalized dimensions. Star schema are an alternative to snowflake schema.

[Category=Information Management ]

Source: Information-Management.com, 07 July 2010 09:44:55, http://www.information-management.com/glossary/m.html

---

Snowflake Schema - A snowflake schema is a set of tables comprised of a single, central fact table surrounded by normalized dimension hierarchies. In a snowflake schema, different hierarchies in a dimension can be extended into their own dimensional tables. Therefore, a dimension can have more than a single dimension table. Snowflake schema implement dimensional data structures with fully normalized dimensions. Star schema are an alternative to snowflake schema.

[Category=Data Warehousing ]

Source: Aexis Business Intelligence, 25 December 2010 09:55:54, http://www.aexis.eu/DataWarehouse-Glossary/

---

Snowflake Schema - A snowflake schema refers to the arrangement of tables in a multi-dimensional data model where dimensions hanging off the fact table also have tables hanging off of them. The links in the data model start to resemble a snowflake with multiple branches hanging off the dimension tables and not the fact table. This occurs with normalized models in a multi-dimensional data store.

[Category=Business Intelligence ]

Source: Deanna Dicken, 09 November 2012 09:22:28, http://www.databasejournal.com/features/mssql/article.php/3919011/Business-Intelligence-Terminology-101.htm

---

snowflaking (snowflake schema) - In data warehousing, snowflaking is a form of dimensional modeling in which dimensions are stored in multiple related dimension tables. A snowflake schema is a variation of the star schema.

Snowflaking is used to improve the performance of certain queries. The schema is diagramed with each fact surrounded by its associated dimensions (as in a star schema), and those dimensions are further related to other dimensions, branching out into a snowflake pattern.

Data warehouses and data marts may use snowflaking to support specific query needs. Snowflaking can improve query performance against low cardinality attributes that are queried independently. Business intelligence applications that use a relational OLAP (ROLAP) architecture may perform better when the data warehouse schema is snowflaked.

A star schema stores all attributes for a dimension into one denormalized (“flattened”) table. This requires more disk space than a more normalized snowflake schema. Snowflaking normalizes the dimension by moving attributes with low cardinality (few distinct values) into separate dimension tables that relate to the core dimension table by using foreign keys. Snowflaking for the sole purpose of minimizing disk space is not recommended, however, because it can adversely impact query performance.

Cases for snowflaking include:

   * Sparsely populated attributes, where most dimension member records have a NULL value for the attribute, are moved to a sub-dimension.

   * Low cardinality attributes that are queried independently.  For example, a product dimension may contain thousands of products, but only a handful of product types.  Moving the product type attribute to its own dimension table can improve performance when the product types are queried independently.

   * Attributes that are part of a hierarchy and are queried independently.  Examples include the year, quarter, and month attributes of a date hierarchy; and the country and state attributes of a geographic hierarchy.

Related glossary terms: conformed dimension, data modeling / data modelling, star schema, dimension, predictive modeling