Explain the situations where snowflake is better than star schema

Showing Answers 1 - 0 of 0 Answers

Ram

  • Nov 23rd, 2007
 

If the table space is not enough to maintain a STAR schema then we should go for Snowflake instead of STAR schema. i.e The table should be spilted in to multiple tables. Ex. if you want to maintain time data in one table like year, month, day in one table in star schema, you need to split this data into 3 tables like year, month, day in Snowflake schema.

  Was this answer useful?  Yes

In Star Schema
When we try to access many attributes or few attributes from a single dimension table the performance of the query falls. So we denormalize this dimension table into two or sub dimensions. Now the same star schema is transformed into snow Flake schema.
By doing so the performance improves

  Was this answer useful?  Yes

star schema is good for simple queries and logic. But snowflake schema is good for complex queries and logic. Snowflake schema is nothing but an extension of the star schema in which the dimension tables are further normalized to reducy redundancy. Hope this helps.

  Was this answer useful?  Yes

DWAlchemist

  • Apr 11th, 2008
 

A snowflake schema is a way to handle problems that do not fit within the star schema.  It consists of outrigger tables which relate to dimensions rather than to the fact table.

The amount of space taken up by dimensions is so small compared to the space required for a fact table as to be insignificant.   Therefore, tablespace or disk space is not a considered a reason to create a snowflake schema.

The main reason for creating a snowflake is to make it simpler and faster for a report writer to create drop down boxes.  Rather than having to write a select distinct statement, they can simply select * from the code table.

Junk dimensions and mini dimensions are another reason to create add outriggers.  The junk dimensions contain data from a normal dimension that you wish to separate out, such as fields that change quickly.  Updates are so slow that they can add hours to the load process.  With a junk dimension, it is possible to drop and add records rather than update.

Mini dimensions contain data that is so dissimilar between two or more source systems that would cause a very sparse main dimension.  The conformed data that can be obtained from all source systems is contained in the parent dimension and the data from each source system that does not match is contained in the child dimension.

Finally, if you are unlucky enough to have end users actually adding or updating data to the data warehouse rather than just batch loads, it may be necessary to add these outriggers to maintain referential integrity in the data being loaded.

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