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
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.
will you please give star and snowflake schema for financial services data warehouse which fact and dimensions table have to use
ReplyDeleteThis solution is somewhat correct.... :(
ReplyDeleteFollowing site for 6pls yrs
ReplyDeletenice information ...................!
ReplyDeletemicro strategy certification training