Obiee Define Star Schema Over The Snowflake Data Model Data Source

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:
example snowflake data model
From the data model, We have the knowledge that the relationship of Region,Countries, Locations and Department is parent-child and typical snowflake.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:snowflake data model

We could just highlight and drag columns from different physical tables into source area of Department in Business layer:Business layer

Now the these columns appear in the Department tablesDepartment tables

Now we could define dimension and hierarchy as usually for the department dimension.department dimension

And we re finished with using star schema over the physical snowflake!