Extra Pages

▼

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

at

17 comments:

  1. Pete ScottMarch 6, 2009 at 5:56 PM

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

    ReplyDelete
  2. John MinkjanMarch 6, 2009 at 6:05 PM

    Pete,

    You are right! Changed it!

    regards

    John

    ReplyDelete
  3. AnonymousMay 19, 2009 at 2:14 AM

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

    Best Regards, Stefan

    ReplyDelete
  4. John MinkjanMay 19, 2009 at 1:31 PM

    @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.

    ReplyDelete
  5. AnonymousMay 20, 2009 at 10:06 AM

    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

    ReplyDelete
  6. Chris HanleyDecember 21, 2009 at 3:47 PM

    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

    ReplyDelete
  7. John MinkjanJanuary 3, 2010 at 8:08 PM

    @chris,

    thanks for noticing, I chanched it.

    regards

    ReplyDelete
  8. leoJune 28, 2010 at 4:01 PM

    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

    ReplyDelete
  9. John MinkjanJune 28, 2010 at 4:27 PM

    @Leo,

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

    Regards

    ReplyDelete
  10. leoJune 28, 2010 at 5:12 PM

    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

    ReplyDelete
  11. John MinkjanJune 28, 2010 at 5:48 PM

    Hi Leo,

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

    Regards

    ReplyDelete
  12. John MinkjanJune 28, 2010 at 6:59 PM

    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

    ReplyDelete
  13. leoJune 29, 2010 at 10:22 AM

    hi john

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

    regards

    ReplyDelete
  14. John MinkjanJune 29, 2010 at 10:27 AM

    @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

    ReplyDelete
  15. leoJune 29, 2010 at 10:40 AM

    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

    ReplyDelete
  16. John MinkjanJune 29, 2010 at 10:42 AM

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

    regards

    John

    ReplyDelete
  17. MamtaSeptember 3, 2010 at 8:57 AM

    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

    ReplyDelete

Note: Only a member of this blog may post a comment.