OBIEE making it "aggregate aware"

In a previous posting (http://obiee101.blogspot.com/2008/11/obiee-huge-dimensions-lets-split-them.html) I showed a way to split huge dimensions to bring down the response time of OBIEE. Can we bring down the response time even more? Of course we can, let’s make OBIEE aggregate aware.

The aggregate tables

First we have to change our ETL so that we get aggregate table(s) in the form:

ag_fact_table_level_1; dim_level_1, fact_aggregate_level_1
ag_fact_table_level_2; dim_level_2, fact_aggregate_level_2
ag_fact_table_level_3; dim_level_3, fact_aggregate_level_3
ag_fact_table_level_4; dim_level_4, fact_aggregate_level_4

Mapping the physical layer

Next we have to map our dimension table(s) to our aggregate table(s):

Mapping the bussiness model

Your fact mapping should look like this:

Be sure to check to level on the datasource of the aggregate table:


Check that the Aggregation content is on "Logical level"
If you are not sure about the level use the Check levels functionality (press the more button)

Check if each level of the dimension(s) is mapped correctly:

Checking the results:
Level 1

Log:

select distinct T36.LEVEL_01 as c1, T307.F_FACT_VAL as c2 from DIM_LEVEL_01 T36, F_FACTS_1 T307 where ( T36.LEVEL_01 = T307.LEVEL_1 ) order by c1

Level 2

select distinct T38.LEVEL_01 as c1, T38.LEVEL_02 as c2, T310.F_FACT_VAL as c3 from DIM_LEVEL_02 T38, F_FACTS_2 T310 where ( T38.LEVEL_01 = ‘A’ and
T38.LEVEL_02 = T310.LEVEL_2 ) order by c1, c2

Level 3

select distinct T41.LEVEL_01 as c1, T41.LEVEL_02 as c2, T41.LEVEL_03 as c3,
T314.F_FACT_VAL as c4 from DIM_LEVEL_03 T41, F_FACTS_3 T314 where ( T41.LEVEL_01 = ‘A’ and T41.LEVEL_02 = ‘AA’ and T41.LEVEL_03 = T314.LEVEL_3 ) order by c1, c2, c3

And so on…..

This article was orinally written for the ciber knowledge blog :

http://knowledge.ciber.nl/weblog/?p=148

Till Next Time