Snowflake vs star schema in obiee 12c RPD — Oracle Analytics

Oracle Analytics Cloud and Server

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

Snowflake vs star schema in obiee 12c RPD

Received Response
227
Views
2
Comments
Sunny86
Sunny86 Rank 6 - Analytics Lead

Dear All,

Snowflake vs star schema in obiee 12c RPD

Current scenario

Snowflake with 5 tables

Region category and country fetched through 3 tables snowflaked with the dimension customer.

through ETL we can load the country region and category information to the customer table and make it a star schema.

Can somebody help me to understand the advantages and disadvantages in terms of performance and other aspects. It would be a great help

pastedImage_11.png

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    You can leave the snowflake at the physical layer and model the logical layer as a star (which obiee requires to function).   So you can have the logical dimension with 4 tables as logical table sources then  join that logical dimension to your fact.  You'd see the snowflaking in the physical SQL sent back to the DB, but providing tuning and architecture there it shouldn't be an issue.   So the answer to the either/or question is BOTH!

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    To expand on 'how' in the business model layer you would expand the LTS source folder and drag the outer limb of the star onto the already modelled inner dimension. This will combine the two tables via a join showing one LTS and you can then change the nature of the join if you wish.

    This means your snowflake is flattened into a star in the logical layer.

    Does it perform as well as Fact -> Dimension - well no, to combine both into a single physical table would be better, but as the learned gentleman says with adequate tuning and architecture it will not be bad.

    Best star

    Second best snowflake

    Worst 3NF

    See also ->  https://thebipalace.com/2015/02/10/convert-snow-flake-into-star-schema-from-multiple-sources-in-obiee-combine-dimensions…