OBIEE generates the query differently for each layer of hierarchy.

Question

Hello Everyone!

OBIEE Version:12.2.1.4

My Business Layer is as below :

My dimension D has 2 logical table sources D1,D2 . D1,D2 tables have 3 columns in common and these 3 columns are in 3 levels of  hierarchy.

Hierarchy is as below:

Campus -> Faculty -> subject

Fact table F is joined to dimension D2 and fact table f1 is joined to dimension D1 in physical layer of admin tool.

When I try to build a report based on the fact table f & dimension d

For the first 2 levels of hierarchy  , obiee generates the query as below :

select sum(f.target count), d2. faculty from fact f, dimension d2

on f.campus=d2.campus and f.faculty=d2.faculty and f.subject=d2.subject;

when I drilldown from faculty to subject obiee generates the query as below:

select d1campus,d1.faculty,d1.subject

from

dimension d1/* Dimension Conformed Program */

where  (d1.faculty= 'Education' and d1.CAMPUS = 'K' )

and the fact table column goes blank .Please find the screenshots for reference.

Campuspng.png

faculty.png

subject.png

I’m not sure why it is generating the correct query for the first 2 levels of hierarchy but not the 3rd level of hierarchy.

Can anyone please help me on how to resolve this issue

Thanks in advance

Vineela1 · Answer

Hi Christian,

Sorry for the late response.

1.Regarding the dimensions D1 has granularity of  Program & dimension D2 has granularity of subject.

Hierarchy is as below:

Campus -> Faculty -> subject → oProgram → Program

2.My logical dimension hierarchy is Parent-Child hierarchy .

My faculty level has my faculty desc as key while my Subject level has facultycode-subject code as key, my oprogram has oProgramkey as key & for program level program key as key.

3. My fact table logical table source has content set for Subject for the above hierarchy  & My Dimension D2 has Subject in the content while dimension D1 has program in the content.

Thanks !

[Deleted User] · Answer

Good morning nameless user,

Would you mind updating your user profile so we know who we're talking to? Thanks!

As for your question it's all down to the model: What are D1 and D2 physically? What do they contain with regards to level of granularity?

How is the Business model built? How is the logical dimensional hierarchy built? How are the content levels built and what are their keys?

Does the fact logical table source contain the correct LTS content settings? Do the dim logical table sources contain the correct LTS content settings?