Snowflake schema

From Wikipedia, the free encyclopedia

All or part of this article may be confusing or unclear.
Please help clarify the article. Suggestions may be on the talk page. (December 2006)

The introduction to this article provides insufficient context for those unfamiliar with the subject.
Please help improve the article with a good introductory style.

A snowflake schema is a way of arranging tables in a relational database such that the entity relationship diagram resembles a snowflake in shape. At the center of the schema are fact tables which are connected to multiple dimensions. When the dimensions consist of only single tables, you have the simpler star schema. When the dimensions are more elaborate, having multiple levels of tables, and where child tables have multiple parent tables ("forks in the road"), a complex snowflake starts to take shape. Generally, whether a snowflake or a star schema is used only affects the dimensional tables. The fact table is unchanged.

The star and snowflake schema are most commonly found in data warehouses where speed of data retrieval is more important than speed of insertion. As such, these schema are not normalized much, and are frequently left at third normal form or second normal form.

The decision on whether to employ a star schema or a snowflake schema should consider the relative strengths of the database platform in question and the query tool to be employed. Star schema should be favored with query tools that largely expose users to the underlying table structures, and in environments where most queries are simpler in nature. Snowflake schema are often better with more sophisticated query tools that isolate users from the raw table structures and for environments having numerous queries with complex criteria.

1 Data normalization and storage
2 Benefits of "snowflaking"
3 See also
4 References
5 External links

[edit] Data normalization and storage

Normalization splits up data to avoid redundancy (duplication) by moving commonly repeating groups of data into a new table. Normalization therefore tends to increase the number of tables that need to be joined in order to perform a given query, but reduces the space required to hold the data and the number of places where it needs to be updated if the data changes.

From a space storage point of view, the size of the dimensional tables are typically small compared to that of the fact tables. This often removes the storage space benefit of snowflaking the dimension tables.

[edit] Benefits of "snowflaking"

If a dimension is very sparse (i.e. most of the possible values for the dimension have no data) and/or a dimension has a very long list of attributes which may be used in a query, the dimension table may occupy a significant proportion of the database and snowflaking may be appropriate.
A multidimensional view is sometimes added to an existing transactional database to aid reporting. In this case, the tables which describe the dimensions will already exist and will typically be normalized. A snowflake schema will hence be easier to implement.
A snowflake schema can sometimes reflect the way in which users think about data. Users may prefer to generate queries using a star schema in some cases, although this may or may not be reflected in the underlying organization of the database.
Some users may wish to submit queries to the database which, using conventional multidimensional reporting tools, cannot be expressed within a simple star schema. This is particularly common in data mining of customer databases, where a common requirement is to locate common factors between customers who bought products meeting complex criteria. Some snowflaking would typically be required to permit simple query tools to form such a query, especially if provision for these forms of query weren't anticipated when the data warehouse was first designed.

[edit] See also

[edit] References

Anahory, S.; D. Murray. Data Warehousing in the Real World: A Practical Guide for Building Decision Support Systems. Addison Wesley Professional.
Kimball, Ralph (1996). The Data Warehousing Toolkit. John Wiley.