Difference between Star Schema and Snow Flake Schema
Star Schema | Snow Flake Schema |
The star schema is the simplest data warehouse scheme. | Snowflake schema is a more complex data warehouse model than a star schema. |
In star schema each of the dimensions is represented in a single table .It should not have any hierarchies between dims. | In snow flake schema at least one hierarchy should exists between dimension tables. |
It contains a fact table surrounded by dimension tables. If the dimensions are de-normalized, we say it is a star schema design. | It contains a fact table surrounded by dimension tables. If a dimension is normalized, we say it is a snow flaked design. |
In star schema only one join establishes the relationship between the fact table and any one of the dimension tables. | In snow flake schema since there is relationship between the dimensions tables it has to do many joins to fetch the data. |
A star schema optimizes the performance by keeping queries simple and providing fast response time. All the information about the each level is stored in one row. | Snowflake schemas normalize dimensions to eliminated redundancy. The result is more complex queries and reduced query performance. |
It is called a star schema because the diagram resembles a star. | It is called a snowflake schema because the diagram resembles a snowflake. |
posted by Jeyakumar @ Friday, December 02, 2011,
Post a Comment
Note: only a member of this blog may post a comment.