Friday 29 June 2012

Difference between Star & Snowflake Schema

Star Schema: It has single fact table connected to dimension tables like a star. In star schema only one join establishes the relationship between the fact table and any one of the dimension tables.A star schema has one fact table and is associated with numerous dimensions table and depicts a star.
Snowflake Schema: It is an extension of the star schema.In snowflake schema, very large dimension tables are normalized into multiple tables. It is used when a dimensional table becomes very big.In snow flake schema since there is relationship between the dimensions Tables it has to do many joins to fetch the data.Every dimension table is associated with sub dimension table.
The main difference between star schema and snowflake schema is that
  • The star schema is highly denormalized and the snowflake schema is normalized. So the data access latency is less in star schema in comparison to snowflake schema. As the star schema is denormalized, the size of the data warehouse will be larger than that of snowflake schema.
  • Performance wise, star schema is good. But if memory utilization is a major concern, then snow flake schema is better than star schema.
  • A dimension table will not have parent table in star schema, whereas snow flake schemas have one or more parent tables.
  • The dimensional table itself consists of hierarchies of dimensions in star schema,whereas hierarchies are split into different tables in snow flake schema. The drilling down data from top most hierarchies to the lowermost hierarchies can be done.

4 comments:

  1. will you please give star and snowflake schema for financial services data warehouse which fact and dimensions table have to use

    ReplyDelete
  2. This solution is somewhat correct.... :(

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis