In which scenario snow flake is good

[#1]

hi can any body tell me in which scenario should we go for snow flake schema

[its_anandrjs]

It is good when you have dimension and sub dimension then it is use like snow flake schema but normally we try to use star flake schema because in snow flake schema we have to refer to its child tables.

[jagan]

HI,

If your database is normalized then snowflake is used, but there are performance issues using this schema, because at the number of joins are increased.  That is why in Qlikview Star Schema is preferred, snowflake is converted to star by joining the Fact and Sub Fact tables.  For less data you can't the performance issues, but when the data is huge you can notice the performace issues.

SnowFlake : Region (Table) -> Country(Table) -> City (Table) (All are separate fact tables)

Star : One table has all the fields (by joining all the tables)

Hope this helps you.

Regards,

Jagan.

[its_anandrjs]

Suppose you have data for date not in single table but in different table

Date(Table) >> Month(Table) >> Quarter(Table) >> Year(Table)

so in above if you want to refer to year you need to refer to all sub tables and then you get info about the year like same way other for month also.

so if all in single table like

CalendarTable:

Date,Month,Quarter,Year

Will be easy and performance wise is good but if data is huge you feel the performance.

Regards