Friday, March 6, 2009

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:

image

Update 2010/06/28:

Add Dummy joins to your other dimensions:

image 

Set the expression to 1=1image

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

image

Next add the sources to your fact table:

image

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

image

On each level source map to the column:

image

and set the aggregation content level:

image

set the aggregation level for the level column to MIN:

image

Test your Report:

image

image

image

image

Be sure to check all your dimension levels!

Till Next Time

17 comments:

Pete Scott said...

John - I think a typo in the select code to build your levels table - shouldn't the constant change at each level?

John Minkjan said...

Pete,

You are right! Changed it!

regards

John

Anonymous said...

John,
do you have an idea why the level column is sometimes empty when other hierarchical dimensions are also selected ?

Best Regards, Stefan

John Minkjan said...

@Stefan,

Have a look in the log file. This might occur if you are using outer joins, then the MIN for the level will be null.

Anonymous said...

Dear John,

thanks for this information.
I'm not very experienced in using such constructs in OBI.
In the repository I have only the inner join as described above.
The column with the level did not work on the dashboard where some more filters are placed for global
reduction.
Is it possible to use another aggregation which works in any case

Best regards again,

Stefan

Chris Hanley said...

Hi,

Shouldn't
* year_level: select distinct d_year, 4 AS DIM_LEVEL from d_date
* quarter_level: select distinct d_year, 3 AS DIM_LEVEL from d_date
* etc.

Be changed to:
* quarter_level: select distinct d_quarter, 3 AS DIM_LEVEL from d_date

John Minkjan said...

@chris,

thanks for noticing, I chanched it.

regards

leo said...

Hi John

i am getting same problem what stefan faced... is there any solution to solve this... i have checked in log file where it is making dim_level as null, i am using inner join but still it is dim_level is coming as null.

thanks for your kind help

John Minkjan said...

@Leo,

Try setting the aggrgation to MAX instead of MIN, check your hiearchy levels.

Regards

leo said...

hi john

thanks for reply but this is also not helping to solve that issue.... if i don't include columns from another dimension then hierarchy level works perfectly.....

any suggestion on this

regards

John Minkjan said...

Hi Leo,

Check the logical levels of both your dimensions and fact tables, it's working fine here.

Regards

John Minkjan said...

Hi Leo,

Seems there was a join part in the blog article in the aricle missing, you have to add some dummy joins, I have updated the article.

regards

John

leo said...

hi john

thanks for your help... could you please help me to create dummy joins.... where i need to do that and how?

regards

John Minkjan said...

@Leo,

The level column is basicly an aggregate table, first check if you have added all join hiearachy levels to your logical fact table. Next do a consistency check on your RPD. For each table which is reportedc back with an error add an 1=1 join in the physical layer.

Regards

John

leo said...

hi john

do i need to create phyical join from dimension table to my times dimesnsion table or my fact table?

if i need to create physical join from dimension to fact table then it is already join with primary key, how to create this dummy join... i have tried doing that as well but getting this error:--

[nQSError] only columns, designated predicated and operators are allowed

John Minkjan said...

No, drop me an email at first . lastname at ciber .nl. The problem is probalby in the LTS Mapping.

regards

John

Mamta said...

Hi John,

Pls help me with the below senario.

In my RPD I have 3 facts and 4 dimensions and the joins defined are as below

Physical layer - All joins are FK and inner (1:N)

FACTA-------------- DIM_TIME, DIM_CUST,DIM_CAL
FACTB-------------- DIM_TIME, DIM_CUST,DIM_CAL
FACTC-------------- DIM_TIME, DIM_CUST,DIM_CAL, DIM_XYZ

BM layer - All joins are complex, inner

The logical table F1 pulls columns from FACTA, FACTB and FACTC . Complex join exists between all 4 dimensions DIM_TIME, DIM_CUST,DIM_CAL, DIM_XYZ to logical table F1.

Issue: I need to build a report with $ column from FACTA, FACTB and FACTC and need to add filter conditions using columns from DIM_TIME, DIM_CUST,DIM_CAL, DIM_XYZ.

However when ever I add a filter condition from DIM_XYZ the physical query generated is not generating code to access data from FACTA and FACTB. Instead it is subsituting a NULL and just pulls data frm FACTC.

My Questions: What is the best way to build the physical and BMM layer using FACTA, FACTB,FACTC, DIM_TIME, DIM_CUST,DIM_CAL and DIM_XYZ??

I need to resolve this issue ASAP so any help is appreciated. Thanks