Tuesday, March 19, 2013

What is Snowflake Schema?

In star schema, If the dimension tables are spitted into one or more dimension tables
The de-normalized dimension tables are spitted into a normalized dimension table
  1. It increases the number of joins and poor performance in retrieval of data.
  2. In few organizations, they try to normalize the dimension tables to save space.
  3. Since dimension tables hold less space snow flake schema approach may be avoided.
  4. Bit map indexes can not be effectively utilized
Important aspects of Star Schema & Snow Flake Schema
  1. In a star schema every dimension will have a primary key.
  2. In a star schema, a dimension table will not have any parent table.
  3. Whereas in a snow flake schema, a dimension table will have one or more parent tables.
  4. Hierarchies for the dimensions are stored in the dimensional table itself in star schema.
  5. Whereas hierarchies are broken into separate tables in snow flake schema. These hierarchies help to drill down the data from topmost hierarchies to the lowermost hierarchies.

No comments:

Post a Comment