which schema is best for data modeling betwen star and snowflake?

[SunilChauhan]

Dear all,

which schema is best for data modeling betwen star and snowflake?

do i have more than one fact table in snow flake schema?

Regards

Tom

[#2]

Hi Tom,

to answer the questions you asked is it possible to fill a whole book. Nevertheless take a look at my attached data modell. It is a snowflake schema derived from a SAP/BW - infocube. You can see the blue facts table in the middle. The light blue dimension tables are around the facts table. Up to this and not regarding the grey tables the modell is a star schema.

Adding the grey tables makes the whole thing to a snowflake schema. Now think about the demand of showing an amount for a specific article. The name is stored in TMATERIAL.TXTMD (left upper corner). You have to go via SID_YMAT_FIN and KEY_AB_EURO1B to your factstable and vice versa. Long way, isn't it ? And now our demand is per article and profit_center. Ooops! In SAP/BW snowflake schemas are used very intensivly, this is one reason of the "great" perfomance of SAP/BW.

For Qlikview a star schema works fine. Normally you will have one factstable in the middle and some dim around it. Two or more fatstables are unusual for me, with the QV-Join and QV-Concatenate funtionality you should be able to build a star schema with one, even huge, factstable. OK so far?

Regards, Roland

[#3]

Tom,

Star schema is better, but in real environment it is very tough to keep your application in star schema.

Maximum time we need to go with snow-flake schema.

No Only one fact table is correct in definition of snow-flake, if some condition results in generation of more than one fact table in data model then try to merge the fact tables. Only level of Dimension tables can be more than one in snow-flake schema.

Ideally for one domain only one fact table should exist, ex if you are making Sales application then sales details can be your fact table and Customer, Branch, Region etc.. can be your dimension tables.

Regards,

Shubhu

[Anonymous]

Hi,

I think Star schema is the fastest solution in terms of performance (accessing memory, memory occupance,synthetich key,ecc..), but Snow Flakes schema is the fastest developement schema in qlikview.

By the Snowflake schema you can improve the normalization in your DB and use it to build your qv application...

The correct usage of primary and foreign keys allow you to build a qv application in a fast and nice way specially if the project is open to modification in the time (in this situation adding info may be simpler..)