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
17 comments:
John - I think a typo in the select code to build your levels table - shouldn't the constant change at each level?
Pete,
You are right! Changed it!
regards
John
John,
do you have an idea why the level column is sometimes empty when other hierarchical dimensions are also selected ?
Best Regards, Stefan
@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.
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
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
@chris,
thanks for noticing, I chanched it.
regards
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
@Leo,
Try setting the aggrgation to MAX instead of MIN, check your hiearchy levels.
Regards
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
Hi Leo,
Check the logical levels of both your dimensions and fact tables, it's working fine here.
Regards
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
hi john
thanks for your help... could you please help me to create dummy joins.... where i need to do that and how?
regards
@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
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
No, drop me an email at first . lastname at ciber .nl. The problem is probalby in the LTS Mapping.
regards
John
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
Post a Comment