Is Snowflake design good?

SQL Lion » BI Solution

Is Snowflake design good?

(5 posts) (4 voices)

Started 1 year ago by maharshi
Latest reply from snt_cool

Activate e-mail notification in your profile settings to get updates. [Help ?]

Tags:

maharshi
Member

In an online service project where currently I am into it, there is couple of situations where I have to consider Snowflake design to be the best for implementation.
But as I have come across many BI whitepapers where snowflake is not highly recommended. I think this is a good topic for discussion.

Posted 1 year ago #
anikonave
Member

Hi,
I got an issue while designing Snowflake Schema for my data warehouse.

Assume the Abbreviations to be like FK ->Foreign key and PK -> Primary key

My Fact is linked to Year dim and then to Month and then to Day.
Other dimension that i used are location, items, product.

I am not sure if this is a correct schema!!

I tested it using Cube Designer but getting errors i.e. Pivot Component Error.

Can anybody help me?
how many Joins should i go for?

Posted 1 year ago #
maharshi
Member

@anikonave, Could you post the error you receive in the log file and also the version of SQL Server that you are using.

That will be helpful in tracing the error.

Posted 1 year ago #
susanth
Member

A snowflake schema is a variation on the star schema, and the main advantage here is that we can use it in case of very large dimension tables where it can be normalized into multiple tables. Dimensions with hierarchies can be decomposed into a snowflake structure when you want to avoid joins to big dimension tables in case you are using an aggregate of the fact table.

For example, for an employee dimension which has departmental information in it can be separated to another table in order to avoid complexity in the employee table. Or like in another example, for a product dimension if you have brand information associated with it, the brand information can be separated creating an another table (snowflake) that consists of single row for each brand and hence can reduce the no. of rows in the product dimension significantly.

Posted 1 year ago #
snt_cool
Member

Hi this is sachin..

I can say.. if you think about the snow flake schema..it is normalized.. so when large volume of data in the dimension and we have to think about the update, insert or delete anamoly then only we should go for this. Because as you know when we query to a snowflake design.. internally it creates many joins within the dimensions..

hence a snowflake schema degrade performance...

...
If not required... we always go for star schema.. as it is de normalized.. .. and it gives fast access of data..

Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.