Snowflake vs star

BIAP

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.

Thomas Dodds

Physically or logically?   Logical snowflake in OBIEE will be your undoing.

Physical:

1 - snowflake is smaller in terms of size of data (less data more joins) - star is not normalized (redundant key data); business relationships are maintained through referential integrity in the data; tough to bulk load

2 - star is faster in terms of performance (less joins more data) - dimensional hierarchies are direct joined to fact tables; fact maintains the business relationships; easier to bulk load

Given that ^ I still see people advocate for a snowflake because they want to browse the dimensions -- a star can still allow for that given appropriate factless fact tables.

Star wins ... it performs, it is easy to load and can give you the answers you need from the data

nm.Mani

Hi

With Star model, we can achieve the better performance and easy for maintenance and modeling for new Aggregates for any new requirements. this is my experience where it is difficult to maintain in snow flake modelling.

Thanks

MM

cesar.advincula.o

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.

Thomas Dodds

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

cesar.advincula.o

@Thomas Dodds Thanks for the warning