DSXchange

Hi All

I wanted to know if there is a reason or a situation, when one needs to snowflake instead of star.

Snowflaking looks like normalized operational design.

Please suggest.

Thanks
Dsedi

There are reasons, but they should be rare. Allow me to quote from The Data Warehouse Lifecycle Toolkit by Kimball et al:

Generally snowflaking is not recommended in a data warehouse environment, Snowflaking almost always makes the user presentation more complex and more intricate. Database designers often like this intricacy, but users are often intimidated by the detail. Snowflaking also makes most forms of browsing among the dimensional attributes slower.

Then ask who's paying your salary?

I agree with Ray.. (and/or Mr. Kimball) I tend to avoid snowflake designs if at all possible. I've worked with a lot of architects for data warehouse designs and anytime I've seen snowflake designs implemented it lead to problems as soon as the end users in the BI tool started accessing the data.

If your "end users" are all database / BI consultants that are pre-building the entire BI front-end for the business users, then you can get away with using snowflakes (though there is a performance penalty of course).