Star vs Snowflake
Star schema and snowflake is probably the most common used data model in data analysis applications.
With the FACT table in the middle , star schema has leafs which is called Dimension to describe the fact table in a specific view.
Unlike the star, snowflake model has more than one level of leafs, It is most common in the transaction system where you store relationship between tables rather than store all the data in one dimension table.
With OBIEE, you could use physical layer to record this complex snowflake model, But for the business model and mapping layer(BMM), we could only store star relationship. That s why we have to use star schema over snowflake data model. And this technique is possible due to the capability of OBIEE that we could use multiple physical table with one logical table.
The example snowflake data model
We will use below snowflake data model for illustration:
From the data model, We have the knowledge that the relationship of Region,Countries, Locations and Department is parent-child and typical snowflake.
Steps of defining star schema over the snowflake data model source
Because business model and mapping layer could only store star relationship. We have delete the Region , Countries and Location tables from the business layer diagram and move the correspondent columns into the Department tables to form a star schema like this:
We could just highlight and drag columns from different physical tables into source area of Department in Business layer:
Now the these columns appear in the Department tables
Now we could define dimension and hierarchy as usually for the department dimension.
And we re finished with using star schema over the physical snowflake!