User Tools

Site Tools


concepts:snow_flake
  • Bookmark "Snow Flake Schema" at del.icio.us
  • Bookmark "Snow Flake Schema" at Digg
  • Bookmark "Snow Flake Schema" at Furl
  • Bookmark "Snow Flake Schema" at Reddit
  • Bookmark "Snow Flake Schema" at Ask
  • Bookmark "Snow Flake Schema" at Google
  • Bookmark "Snow Flake Schema" at Netscape
  • Bookmark "Snow Flake Schema" at StumbleUpon
  • Bookmark "Snow Flake Schema" at Technorati
  • Bookmark "Snow Flake Schema" at Live Bookmarks
  • Bookmark "Snow Flake Schema" at Yahoo! Myweb
  • Bookmark "Snow Flake Schema" at Facebook
  • Bookmark "Snow Flake Schema" at Newsvine
  • Bookmark "Snow Flake Schema" at Yahoo! Bookmarks
  • Bookmark "Snow Flake Schema" at Twitter
  • Bookmark "Snow Flake Schema" at myAOL
  • Bookmark "Snow Flake Schema" at Slashdot
  • Bookmark "Snow Flake Schema" at Mister Wong

Snow Flake Schema

The snow flake schema is a specific type of a dimensional data model used in data warehouses. The model is a normalized structure, which means that redundant data is not stored in the dimension table, but is stored in more tables – in the snowflake to help with performance1).

The snow flake schema as a dimensional data model

Description

The characteristic of a dimensional data model is a set of detailed business facts surrounded by multiple dimensions that describe those facts2). Dimensional data models are consist of one table with a multipart key, called the fact table, and a set of smaller tables called dimension tables. Each dimension table has a single part primary key that corresponds exactly to one of the components of the multipart key in the fact table. This standard structure of a dimensional data model is often called a Star Schema3). The snowflake schema is a specific dimensional data model which has been developed by normalizing dimension tables in the star schema. The method of normalizing dimension tables is often called “snowflaking”4). While in the star schema the dimension tables are directly connected to the fact table, in the snowflake schema one or more dimension tables do not join directly to the fact table but must be joined through other dimension tables5). A dimension is “snowflaked” when the low cardinality fields in the dimension have been removed to separate tables and linked back into the original table with artificial keys6). An example of a “snowflaked” product dimension table is shown in following picture7):

How to snowflake

“Snowflaking” or normalization of the dimension tables can be achieved in a few different ways. By “snowflaking” the contents and the normal usage of each dimension table has to be examine. The following options indicate the different methods which may be considered for normalization of the dimension tables:

  • Partially normalize only a few dimension tables, the other dimension tables are leaving intact.
  • Partially or fully normalize only a few dimension tables, leaving the rest of the dimension tables intact.
  • Partially normalize every dimension table.
  • Fully normalize every dimension table.

When all the dimension tables have been completely normalized, the resultant structure resembles a snowflake with the fact table in the middle8).

Advantages and disadvantages of “snowflaking”

Advantages of the snow flake schema:

  • small savings in storage space,
  • normalized structures are easier to update and maintain.

Disadvantages of the snow flake schema:

  • schema less intuitive and end-users are put off by the complexity,
  • ability to browse through the contents difficult,
  • degraded query performance because of additional joint9).

Conclusion

“Snowflaking” is not generally recommended in a data warehouse environment. Query performance takes the highest significance in a data warehouse and “snowflaking” makes the performance worse10). In some situations when the technique of “snowflaking” is properly used, the performance and user understandability can be improved11). For example improvement of reporting performance for some business intelligence query tools, which prefer snowflake designs.
“Snowflaking”provide opportunities to separate out a set of attributes and form a subdimension in a database12).
Snowflake schemas support ease of dimension maintenance because they are partially or fully normalized. They reduce storage requirements, since dimensions have high volumes of data13).

1) , 13) Bowman, David. “Data Warehouse Snowflake Schema.” Information Management. Web. 09 Jan. 2011. <http://www.information-management-architect.com/snowflake-schema.html>.
2) , 5) “Data Warehouse Design Considerations.” Microsoft TechNet: Resources for IT Professionals. Web. 09 Jan. 2011. <http://technet.microsoft.com/en-us/library/aa902672%28SQL.80%29.aspx#sql_dwdesign_topic4>.
3) Kimball, Ralph. The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing, Developing, and Deploying Data Warehouses. New York: Wiley, 1998, p. 78. Print.
4) , 8) Ponniah, Paulraj. Data Warehousing Fundamentals for IT Professionals. 2nd ed. Hoboken, NJ: John Wiley & Sons, 2010, p. 259ff. Print.
6) Kimball, Ralph. The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing, Developing, and Deploying Data Warehouses. New York: Wiley, 1998, p. 91. Print.
9) , 10) , 12) Ponniah, Paulraj. Data Warehousing Fundamentals for IT Professionals. 2nd ed. Hoboken, NJ: John Wiley & Sons, 2010, p. 261. Print.
11) Kimball, Ralph. The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing, Developing, and Deploying Data Warehouses. New York: Wiley, 1998, p. 82. Print.
  • Bookmark "Snow Flake Schema" at del.icio.us
  • Bookmark "Snow Flake Schema" at Digg
  • Bookmark "Snow Flake Schema" at Furl
  • Bookmark "Snow Flake Schema" at Reddit
  • Bookmark "Snow Flake Schema" at Ask
  • Bookmark "Snow Flake Schema" at Google
  • Bookmark "Snow Flake Schema" at Netscape
  • Bookmark "Snow Flake Schema" at StumbleUpon
  • Bookmark "Snow Flake Schema" at Technorati
  • Bookmark "Snow Flake Schema" at Live Bookmarks
  • Bookmark "Snow Flake Schema" at Yahoo! Myweb
  • Bookmark "Snow Flake Schema" at Facebook
  • Bookmark "Snow Flake Schema" at Newsvine
  • Bookmark "Snow Flake Schema" at Yahoo! Bookmarks
  • Bookmark "Snow Flake Schema" at Twitter
  • Bookmark "Snow Flake Schema" at myAOL
  • Bookmark "Snow Flake Schema" at Slashdot
  • Bookmark "Snow Flake Schema" at Mister Wong
concepts/snow_flake.txt · Last modified: 2014/10/19 13:38 (external edit)