|
What is Snowflake Schema? |
|
|
|
Written by Roceller Alvarez
|
|
Monday, 13 March 2006 |
|
A snowflake schema is a star schema structure normalized through the use of dimension tables. For example dimension table hierachies are broken into simpler tables. In star schema example we had 4 dimensions like customer, time, store, product and sales fact table.
As a general rule, normalization benefits a regular OLTP (Online Transaction Processing) system by reducing redundancy and thus reducing the update/insert anomalities and the time taken by these queries. In contrast, normalization can kill the performance of a data warehouse. Regular OLTP systems have pre-fixed queries to deal with. Business queries on the data warehouse are not fixed - they change with time. Not only does introducing normalization here mean that the table traversal is repeated, but it also increases the time taken to fetch the data. You will need to traverse say, 2 tables where a de-normalized data schema would have taken only 1 table to traverse.
However, there are situations where you need to take care of eliminating redundancy - to take care of the load window or facilitate partitioning. (Wikipedia).
Star Schema Diagram:
Related Articles:
- Star and Snowflake Schema
|
