Home

OBIEE - Define star schema over the snowflake data model data source

Submitted by iniunet on Thu, 09/10/2009 - 04:13

Please read this article before reading this one.

Although We could build report easily for snow flake data model with OBIEE, We still lack the capability of a typical 'interactive' report which user could drill down or roll up along a dimension.

We will use our last article 's data model to illustrate this concept:

OBIEE snow flake data model

From the data model, We have the knowledge that the relationship of 'Region','Countries', 'Locations' and 'Department' is parent-child and could be a good candidate for 'drill down' and 'roll up' operation.

parent child relationship

however, each of them is stored in different tables and this is the typical situation for 'snow flake' data model, how could we setup OBIEE to drill down these parent-child relationship?

And this is one of OBIEE 's advantage - we could still model the business layer into star schema even if the physical data source is in 'snow flake' data model.

 

Define star schema over the snowflake data model source

When we 're talking 'star schema' is the best model for data analysis , We 're actully require the business layer of OBIEE to be organized in star schema model. and Business layer of OBIEE is totally independant of the physical data source, Columns of one table in business layer could be mapped to different tables on the real physical tables, and this is the technique we use to turn the 'snow-flake' physical data source model into star schema in business layer of OBIEE which we could define dimension hierarchy for the 'drill down' and 'roll up' operations.

Back to our examples and we will illustrate how to define star schema over the snowflake data model.

We will delete the 'Region' , 'Countries' and 'Location' tables from the business layer and move the correspondant columns into the 'Department' tables to form a star schema like this:

star schema data model

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

business layer and physical layer

Now the dragged columns appear in the 'Department' tables

OBIEE logical table

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

OBIEE define dimension hierarchy

And we could do 'drill down' easily in the report:

before drill down

After drill down

OBIEE After drill down

Hmm

Submitted by Anonymous on Wed, 10/12/2011 - 23:33.

Hello

I fail to understand why you want to make it a star before you make a hierarchy?
Why not just drag the snowflaked tables to the Business model, and then build your Hierarchy?

kind regards
Øyvind Strøm
EDW Designer
(DM/OBIEE+)

help

Submitted by Anonymous on Sat, 03/06/2010 - 03:06.

Very nice article.
I try to do the same, but dosen't work. When I drag the table into another table I've got 4 logical table sources and then I can't build the dimension.

Can you help me ?

Thanks

You can still build hierarchy...

Submitted by Anonymous on Mon, 10/10/2011 - 05:00.

You can still build the hierarchy regardless of the number of Logical Table Sources (LTS). When you right click on DEPARTMENTS table and then Create Dimension, it creates the hierarchy, DEPARTMENTSDim and moves only the DEPARTMENTS table fields under this hierarchy, and other fields are still under DEPARTMENTS table. Next step is, you select all the required fields from DEPARTMENTS table and drop then under DEPARTMENTSDim. Now you have all the necessary fields under DEPARTMENTSDim and you can easily create Parent/Child relationship. I hope this helps.