Snowflake vs star

Question

Hi everyone, can I ask a generic dimensional modelling question. I know we should choose star schema if we can as star schema offers better query performance and simplicity. My question is when to use snowflake? when dimension table is too large? or when we want to solve many to many relationship? What's the difference between a snowflake and using a bridge table or mini table then? Thanks in advance.

cesar.advincula.o · Answer

@Thomas Dodds Thanks for the warning

Thomas Dodds · Answer

Ralph Kimball dealt in physical architecture - not in the logical workings of OBIEE ... so his points are valid - but with caution.

cesar.advincula.o · Answer

Just a comment to add

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/owb/owb10gr2_gs/owb/lesson3/starandsnowflake.htm

When do you use Snowflake Schema Implementation?

Ralph Kimball, the data warehousing guru, proposes three cases where snowflake implementation is not only acceptable but is also the key to a successful design:

Large customer dimensions where, for example, 80 percent of the fact table measurements involve anonymous
visitors about whom you collect little detail, and 20 percent involve reliably registered customers about
whom you collect much detailed data by tracking many dimensions

Financial product dimensions for banks, brokerage houses, and insurance companies, because each of
the individual products has a host of special attributes not shared by other products

Multienterprise calendar dimensions because each organization has idiosyncratic fiscal periods,
seasons, and holidays

Ralph Kimball recommends that in most of the other cases, star schemas are a better solution. Although redundancy is reduced in a normalized snowflake, more joins are required. Kimball usually advises that it is not a good idea to expose end users to a physical snowflake design, because it almost always compromises understandability and performance.