OBIEE identifying the hierarchy level

On the OTN forum I was asked how you can identify the hierarchy level which the user is currently on (Did he select Year, Quarter,Month etc?).

One way of doing this is "using" the aggregate awareness availability of a column. First you create a "dummy" aggregate view for each hierarchy:

• year_level: select distinct d_year, 4 AS DIM_LEVEL from d_date
• quarter_level: select distinct d_quarter, 3 AS DIM_LEVEL from d_date
• etc.

These tables you physically join to your dimension:

Update 2010/06/28:

Add Dummy joins to your other dimensions:

 

Set the expression to 1=1

In your fact table you add an extra column named dim_date_level:

Next add the sources to your fact table:

Add an inner join on your core fact table to your original dimension:

On each level source map to the column:

and set the aggregation content level:

set the aggregation level for the level column to MIN:

Test your Report:

Be sure to check all your dimension levels!

Till Next Time

OBIEE Leap Year Challenge

Have a look at these tables of February 2008 and 2009: (remember 2008 was a "leap" year)

If in February 2009 we use the ago function of OBIEE:

AGO(BM_AGO_TEST.F_FACTS.F_FACT_VAL, BM_AGO_TEST.DIM_DATE_TESTDim."YEAR", 1)

On a month level we get:

which is correct. But if we do it on a day by day level we get:

Which is not quite correct since it is missing the "AGO" data for FEB 29 2008 (1232). Now you could argue that this is correct since there is no FEB 29 2009. Most accounting systems agree that in such a case the data for FEB 29 should be added to the data of FEB 28. One way of solving this is adding a Year Ago DATE_ID to your date dimensions with a double entry for FEB 28 2009:

If connect an alias of our fact table to the DT_ID_YAGO we get:

 

We see that the data for FEB 28 and FEB 29 2008 is now summarized for FEB 28 2009.

An other advance for working with fixed ago column is your time dimension is that the query costs are about 80 % percent lower then using the OBIEE AGO function. Is this example 187 versus 853 on as Oracle 10Gr2 database.

Till Next Time

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

OBIEE Huge dimensions? Let’s split them!

OBIEE is (like most other tools) based on the Kimball methodology of representing date in a star model, using dimension and fact tables. The problem with dimension tables is that if they have several levels they can easily become huge and therefor take a long time to load. And if there is one users don't want is waiting.....


An example: a dimension with 5 levels and 25 categories on each level can give you 25^5 = 9.765.625 possibilities. An entry on level 1 would generated a "select distinct level_1 from dim_table". No mater how good your indexing is, a select distinct on almost 10 million rows is always slower then a select distinct on 25 rows.


How can we make this faster?


Step 1 is to organize your ETL is such a way that you get a dimension table for each level. (In practices you will probably group 2 or 3 levels together.)
Each dimension level table should also have the predecessor columns in them:

dim_table_level_1; level_1
dim_table_level_2; level_1, level_2
dim_table_level_3; level_1, level_2, level_3
dim_table_level_4; level_1, level_2, level_3, level_4
dim_table_level_5; level_1, level_2, level_3, level_4, level_5

Now we could map this in OBIEE as dim_table_level_1 => dim_table_level_2 => dim_table_level_3 => dim_table_level_4 => dim_table_level_5 => fact_table. But then again we would you loose the benefit of "small" dimension table by introducing costly joins.


Example:

select T36.LEVEL_01 as c1, sum(T56.F_FACT_VAL) as c2from
DIM_LEVEL_01 T36, DIM_LEVEL_02
T38, DIM_LEVEL_03 T41,
DIM_LEVEL_04 T45, DIM_LEVEL_05
T50, F_FACTS T56where ( T36.LEVEL_01 = T38.LEVEL_01 and T36.LEVEL_01 = T56.LEVEL_1 and T38.LEVEL_02 = T41.LEVEL_02 and T38.LEVEL_02 = T56.LEVEL_2 and T41.LEVEL_03 = T45.LEVEL_03 and T41.LEVEL_03 = T56.LEVEL_3 and T45.LEVEL_04 = T50.LEVEL_04 and T45.LEVEL_04 = T56.LEVEL_4 and T50.LEVEL_05 = T56.LEVEL_5 ) group by T36.LEVEL_01order by c1

Let OBIEE make the decision!


If you really want to maximize the speed of OBIEE you can use the "federated query" functionality. Beside splitting the dimension table we also split the dimension key in the fact table. (In a later article I will show you how you make your query's "aggregate aware" and speed them up a lot!.)
Your fact table will look like: level_1, level_2, level_3, level_4, level_5, fact_1
If you map this in physical layer as: dim_table_level_1 => fact_table, dim_table_level_2 => fact_table, dim_table_level_3 => fact_table, dim_table_level_4 => fact_table and dim_table_level_5 => fact_table and put all the dimension_tables in the same datasource in the business model layer:

Next we make the dimension accordenly:


You will see that OBIEE automatically choices the correct dim_table while drilling down:

level 1:
select T36.LEVEL_01 as c1,
sum(T56.F_FACT_VAL) as c2from DIM_LEVEL_01
T36, F_FACTS T56where ( T36.LEVEL_01 = T56.LEVEL_1
) group by T36.LEVEL_01order by c1

level 2:
select T38.LEVEL_01 as c1, T38.LEVEL_02 as
c2, sum(T56.F_FACT_VAL) as c3from
DIM_LEVEL_02 T38, F_FACTS
T56where ( T38.LEVEL_01 = 'A' and T38.LEVEL_02 = T56.LEVEL_2 ) group by
T38.LEVEL_01, T38.LEVEL_02order by c1, c2

level 3:
select T41.LEVEL_01 as c1, T41.LEVEL_02 as
c2, T41.LEVEL_03 as c3,
sum(T56.F_FACT_VAL) as c4from DIM_LEVEL_03
T41, F_FACTS T56where ( T41.LEVEL_01 = 'A' and
T41.LEVEL_02 = 'AA' and T41.LEVEL_03 = T56.LEVEL_3 ) group by T41.LEVEL_01,
T41.LEVEL_02, T41.LEVEL_03order by c1, c2, c3

level 4:
select T45.LEVEL_01 as c1, T45.LEVEL_02 as
c2, T45.LEVEL_03 as c3,
T45.LEVEL_04 as c4, sum(T56.F_FACT_VAL) as c5from
DIM_LEVEL_04 T45, F_FACTS
T56where ( T45.LEVEL_01 = 'A' and T45.LEVEL_02 = 'AA' and T45.LEVEL_03 =
'AAF' and T45.LEVEL_04 = T56.LEVEL_4 ) group by T45.LEVEL_01, T45.LEVEL_02,
T45.LEVEL_03, T45.LEVEL_04order by c1, c2, c3, c4

level 5:
select T50.LEVEL_01 as c1, T50.LEVEL_02 as
c2, T50.LEVEL_03 as c3,
T50.LEVEL_04 as c4, T50.LEVEL_05 as
c5, sum(T56.F_FACT_VAL) as c6from
DIM_LEVEL_05 T50, F_FACTS
T56where ( T50.LEVEL_01 = 'A' and T50.LEVEL_02 = 'AA' and T50.LEVEL_03 =
'AAF' and T50.LEVEL_04 = 'AAFG' and T50.LEVEL_05 = T56.LEVEL_5 ) group by
T50.LEVEL_01, T50.LEVEL_02, T50.LEVEL_03, T50.LEVEL_04, T50.LEVEL_05order by c1,
c2, c3, c4, c5

Edit: OBIEE guru Stijn Gabriels pointed out to me that you also have to set the levels for each dimension column.

(When you start with a "virgin" repository OBIEE will do automaticly, but you should always check them!)

This article was orinally written for the ciber knowledge blog :
http://knowledge.ciber.nl/weblog/?p=147

Till Next Time