Discuss the advantages & Disadvantages of star & snowflake schema?

Editorial / Best Answer

Answered by: swati

  • Nov 5th, 2005


star schema consists of single fact table surrounded by some dimensional table.In snowflake schema the dimension tables are connected with some subdimension table.

In starflake  dimensional ables r denormalized,in snowflake dimension tables r normalized.

star schema is used for report generation ,snowflake schema is used for cube.

The advantage of snowflake schema is that the normalized tables r easier to maintain.it also saves the storage space.

The disadvantage of snowflake schema is that it reduces the effectiveness of navigation across the tables due to large no of joins between them.

Showing Answers 1 - 4 of 4 Answers

prasad nallapati

  • Aug 25th, 2005
 

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.

  Was this answer useful?  Yes

Lenin

  • Nov 1st, 2005
 

In a STAR schema there is no relation between any two dimension tables, whereas in a SNOWFLAKE schema there is a possible relation between the dimension tables.

  Was this answer useful?  Yes

swati

  • Nov 5th, 2005
 

star schema consists of single fact table surrounded by some dimensional table.In snowflake schema the dimension tables are connected with some subdimension table.

In starflake  dimensional ables r denormalized,in snowflake dimension tables r normalized.

star schema is used for report generation ,snowflake schema is used for cube.

The advantage of snowflake schema is that the normalized tables r easier to maintain.it also saves the storage space.

The disadvantage of snowflake schema is that it reduces the effectiveness of navigation across the tables due to large no of joins between them.

  Was this answer useful?  Yes

Richard

  • Feb 2nd, 2006
 

It depends upon the clients which they are following, whether snowflake or star schema.

  Was this answer useful?  Yes

halgai

  • Oct 7th, 2007
 

Snowflakes are an addition to the Kimball Dimensional system to enable that system to handle hierarchial data.  When Kimball proposed the dimensional data warehouse it was not first recogonized that hierarchial data could not be stored. 

Commonly every attempt is made not to use snowflakes by flattening hierarchies but when either this is not possible or practical the snowflake design solves the problem.

Snowflake tables are ofter called "outlyers" by data modelers because they must share a key with a diminsion that directly connects to a fact table.

SD2 can have "outlyers" but these are very difficult to instantiate.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions