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.

jaggu_mandya

 Profile Answers by jaggu_mandya 

• Jan 16th, 2008

 

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

ghoshkunal123

 Profile Answers by ghoshkunal123 

• Feb 4th, 2008

 

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.

DWAlchemist

 Profile Answers by 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.