What is the difference between snow flake and star 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.
|
No comments:
Post a Comment