Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Snowflake vs star

Received Response
11
Views
5
Comments
BIAP
BIAP Rank 4 - Community Specialist

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.

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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
    nm.Mani Rank 6 - Analytics Lead

    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
    cesar.advincula.o Rank 6 - Analytics Lead

    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:

    image

    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

    image

    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

    image

    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
    Thomas Dodds Rank 8 - Analytics Strategist

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

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    @Thomas Dodds Thanks for the warning