Star Schema v/s Snowflake Schema:
Star Schema is a relational database schema for
representing multidimensional data. It is the simplest form of data warehouse
schema that contains one or more dimensions and fact tables. It is called a star schema because the
entity-relationship diagram between dimensions and fact tables resembles a star
where one fact table is connected to multiple dimensions. The center of the star schema consists of a large
fact table and it points towards the dimension tables. The advantage of star
schema are slicing down, performance increase and easy understanding of data.
* Steps in designing Star Schema Identify a business process for analysis(like sales).
* Identify measures or facts (sales dollar).
* Identify dimensions for facts(product dimension, location dimension, time dimension, organization dimension).
* List the columns that describe each dimension.(region name, branch name, region name).
A snowflake schema is a term that describes a star schema structure normalized through the use of outrigger tables. i.e dimension table hierachies are broken into simpler tables. In star schema example we had 4 dimensions like location, product, time, organization and a fact table(sales).
* Steps in designing Star Schema Identify a business process for analysis(like sales).
* Identify measures or facts (sales dollar).
* Identify dimensions for facts(product dimension, location dimension, time dimension, organization dimension).
* List the columns that describe each dimension.(region name, branch name, region name).
A snowflake schema is a term that describes a star schema structure normalized through the use of outrigger tables. i.e dimension table hierachies are broken into simpler tables. In star schema example we had 4 dimensions like location, product, time, organization and a fact table(sales).
In OLAP, this Snowflake schema approach increases
the number of joins and poor performance in retrieval of data. In few
organizations, they try to normalize the dimension tables to save space. Since
dimension tables hold less space, Snowflake schema approach may be avoided.
Important aspects of Star Schema & Snow Flake Schema:
In a star schema every dimension will have a
primary key.
* In a star schema, a dimension table will not have any parent table whereas in a snow flake schema, a dimension table will have one or more parent tables.
* Hierarchies for the dimensions are stored in the dimensional table itself in star schema whereas hierachies are broken into separate tables in snow flake schema. These hierachies helps to drill down the data from topmost hierachies to the lowermost hierarchies.
* In a star schema, a dimension table will not have any parent table whereas in a snow flake schema, a dimension table will have one or more parent tables.
* Hierarchies for the dimensions are stored in the dimensional table itself in star schema whereas hierachies are broken into separate tables in snow flake schema. These hierachies helps to drill down the data from topmost hierachies to the lowermost hierarchies.
No comments:
Post a Comment
Thank you :
- kareem