more about star &snowflak schema

[dataquery]

i'd like to ask whether we can connect 2 dims in a snowflak schema and whether we can communicate 2 fact tables directly in a schema or via dims.plz explain it via examples

 

[blom0344]

Connecting dimensiontables can be possible if the relationship between facttable:dimtable1 is n:1 and dimtable1:dimtable2 is n:1.
Consider storing products in a warehouse. The facttable contains information about where products are stored (standardized locations) dimtable1 stores information on all locations. There are 1000 locations, but just 5 types.
The second dimtable holds information on the type (length,width etc) Running one SQL on such a schema will not cause any problems...

T. Blom
Information analyst
tbl@shimano-eu.com

 

[dwgeek]

If people choose Snowflake Model why and if people choose a Star Schema model, then why?

Whats the actual differnece between the two architectures?

Explain it with an example please.

 

[blom0344]

For a nice , comprehensive oversight look at:

http://www.accessarchive.com/FAQs/Modelling/II_02_Datawarehouse_Design.htm

Put very bluntly snowflake schema is a starschema with the set of dimension tables normalised. This offers the best way to control dimensional data (no redundancy) but will give lower SQL performance. (more joins to handle)

So, it will boil down to requirements in order to choose between one or the other..............

T. Blom
Information analyst
tbl@shimano-eu.com

 

[monsky]

A very rough differentiation:

A star schema has several dimension tables branching out from a fact table.

A snowflake has some (if not all) of dimensions having another table linked to it.

 

[P1553D]

I suggest you always choose a STAR schema over a SNOWFLAKE unless there are very very convincing arguments not too,which is rare in this age of cheap disk.