I have created a logical model where I am using conformed dimensions for reporting on multiple facts withing that Business Model.
So lets say I have two dimensions Dim - Work Order and Dim - Company and I have two facts Fact - GL and Fact - SLA
Fact GL is joined to Dim - Work Order and Dim - Company also does the Fact SLA in both physical and logical.
I have created two Presentation subject areas and called it GL Analytics and SLA Analytics and put my respective GL and SLA facts and thier dims.
When I try to pull the columns only from by dimensions Work Order Number from Dim- Work Order and Company Name from Dim - Comapany, even though these are joined to Fact -SLA table OBIEE is issuing qeury to Fact - GL instead. So I can sense a circular join problem here. I can see only results from one Company Name but not Work order number in the analysis.
However in the BI Apps OOTB data model. I consider AP - Posted Transactions , AR - Posted Transactions with Dim - GL Account(Segment 1) and Dim - GL Company ( semgment 2) they to have a similar join. However when I query only dims Account number and Company name I get results for both and the query send joins to correct table W_AP_XACT_F and joins these dim tables.
What am I doing wrong here.
could someone please help me out.
Thanks in advance.
In the properties of your presentation subject area, you could specify an implicit fact column: For each subject area in the Presentation layer, you can set an implicit fact column. The implicit fact column is added to a query when it contains columns from two or more dimension tables and no measures. The column is not visible in the results. It is used to specify a default join path between dimension tables when there are several possible alternatives or contexts.
Hope it helps,