veepee

• Aug 11th, 2005

 

star schema and snowflake both serve the purpose of dimensional modeling when it come to datawarehouses. 
star schema is a dimensional model with a fact table ( large) and a set of dimension tables ( small) . the whole set-up is totally denormalized. 
however in cases where the dimension table are split to many table that is where the schema is slighly inclined towards normalization ( reduce redundancy and dependency) there comes the snow flake schema. 
 
the nature/purpose of the data that is to be feed to the model is the key to your question as to which is better.

senkumar79

 Profile Answers by senkumar79 Questions by senkumar79

• Oct 7th, 2005

 

Star schema contains the dimesion tables mapped around one or more fact tables.

It is a denormalised model.

No need to use complicated joins.

Queries results fastly.

Snowflake schema

It is the normalised form  of Star schema.

contains indepth joins ,bcas the tbales r splitted in to many pieces.We can easily do modification directly in the tables.

We hav to use comlicated joins ,since we hav more tables .

There will be some delay in processing the Query .

ravi kumar guturi

• Mar 9th, 2006

 

Star Schema means

A centralized fact table and sarounded by diffrent dimensions

Snowflake means

In the same star schema dimensions split into another dimensions

Star Schema contains Highly Denormalized Data

Snow flake  contains Partially normalized

Star can not have parent table

But snow flake contain parent tables

Why need to go there Star:

Here 1)less joiners contains

2)simply database

3)support drilling up options

Why nedd to go Snowflake schema:

Here some times we used to provide seperate dimensions from existing dimensions that time we will go to snowflake

Dis Advantage Of snowflake:

Query performance is very low because more joiners is there

Enjoy n all the best

Sumit Banerjee

• Apr 27th, 2006

 

veepee Wrote: star schema and snowflake both serve the purpose of dimensional modeling when it come to datawarehouses. 
star schema is a dimensional model with a fact table ( large) and a set of dimension tables ( small) . the whole set-up is totally denormalized. 
however in cases where the dimension table are split to many table that is where the schema is slighly inclined towards normalization ( reduce redundancy and dependency) there comes the snow flake schema. 
 
the nature/purpose of the data that is to be feed to the model is the key to your question as to which is better.

sreedhar

• Dec 14th, 2006

 

Both represent the dimensional model, in case of star schema the dimensons does not split ....where as in the case of snowflake u can see the further split in dimension for eg: if u r using more than one telephone at ur desk and it is available to more than one and at the same time the telephone gives the facility of usage more than one member then in this case we need  further split in the table, because we need in depth analysis..

Chris

• Mar 29th, 2007

 

Star Schemas generally contain fact tables with fully denormalised dimension tables connecting directly to the fact table. A snowflake schema is a modification of this approach whereby the dimension tables will contain elements of normalisation and be broken down into multiple tables. The main dimension will still link directly with the fact but it will also contain a "helper" dimension relationship. General rule of thumb is to avoid the snowflake as it goes against the goals of a data warehouse. There are however situations where the snowflake schema makes sense. Typical example is a dimension that contains customers. Lets say we have 50 attributes for customers and 80% of the time these are not captured. Then it is definitely a better design to snowflake the customer dimension. Typically though the snowflake is an exception to the rule and should only be implemented when absolutely necessary taking into consideration 2 goals - improve performance and increase understanding. If you can do that then absolutely the snowflake can be used.

justins707

 Profile Answers by justins707 

• Jan 11th, 2010

 

When we refer to Star and Snowflake Schemas, we are talking about a dimensional model for a Data Warehouse or a Datamart.  The Star schema model gets it name from the design appearance because there is one central fact table surrounded by many dimension tables.  The relationship between the fact and dimension tables is created by PK -> FK relationship and the keys are generally surrogate to the natural or business key of the dimension tables.  All data for any given dimension is stored in the one dimension table.  Thus, the design of the model could potentially look like a STAR.

On the other hand, the Snowflake schema model breaks the dimension data into multiple tables for the purpose of making the data more easily understood or for reducing the width of the dimension table.  An example of this type of schema might be a dimension with Product data of multiple levels.  Each level in the Product Hierarchy might have multiple attributes that are meaningful only to that level.  Thus, one would break the single dimension table into multiple tables in a hierarchical fashion with the highest level tied to the fact table.  Each table in the dimension hierarchy would be tied to the level above by natural or business key where the highest level would be tied to the fact table by a surrogate key.  As you can imagine the appearance of this schema design could resemble the appearance of a snowflake.

Personally, I would avoid the Snowflake schema unless there is a significant performance gain from using it or the snowflake is an association of related dimensions like Geography and Store.