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 tables 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!

Contents extracted from http://iniu.net...

To add on above discussion we can create alias tables on all physical tables in physical model like in above example of country, location and department so we will create 3 alias tables in physical model.

now in BMM layer (or logical model) create single logical table then in logical table source define desired columns from of all 3 alias tablesJ and define relationship from logical table to fact table as required. NOTE: joins in BMM layer is complex join, basically expression is not allowed here means we tell OBIEE that user wants to analyze data but in physical model can define relation in physical model of PK/FY keys.