Full Outer Join between different result sets

User_R1DW5

Hi All,

I am trying to create a report with three measures from three different fact tables and one column from Dimension table which is joined to all the three fact table. There are some filters as well but all the dimensions are conformed to all three fact table.

Now consider we have Dim 1, Fact 1, Fact 2 and Fact 3 in our report as columns. Some values in Dim 1 do not have data in Fact 3 but have data in Fact 1 and Fact 2. For example, I am creating a report to show Budget, Actuals and Accruals for a Department. So Department A does not have any Accruals but have Budget and Actual data. While running this report, OBIEE only show the values where I have data in Fact 3 against Dim 1. If I remove the column from Fact 3, it will show the data in Fact 1 and Fact 2 column for Dept A. So ideally, my report should show the data as below.

Dim 1	Fact 1	Fact 2	Fact 3
A	2623	2541
B	3522	3200
C	3695	2200	1200
D	3658

There is no issue with modelling as OBIEE is generating 3 separate physical SQLs for all three metrics. In this case, OBIEE should do a full outer join in the logical level and show the data but it is not happening. So is it expected behavior from OBIEE? Is there any way to achieve the above?

Thanks,
Suhel

Joel

How has this been modelled in the BMM layer?  This would help if you could possibly provide a screenshot of the model and joins for the 3 facts and the dimension.

User_R1DW5

It is very simple model. Though in physical, I am doing snow-flaking using different alias of the dimension. I have attached the screenshot of Physical and BMM for your reference. In the physical layer, the dimension (XW_GL_SEGMENT_DH) is connected to fact through W_GL_ACCOUNT_D. There is another way to the fact through the W_GL_SEGMENT_D but that is not connected to my custom fact table. I have set the content of the fact at proper level. The queries are generated as expected by OBIEE but it is not merging the result sets using outer join as desired

.

User_R1DW5

Can someone please help me on this issue? I am not sure if it is the way OBIEE will behave and try to do inner join rather than doing the Outer join.

Thanks,

Suhel

[Deleted User]

Separate result sets in multi-star queries are always joined together using full outer stitch joins. Look at the Lvl 5 log - that's where your investigation has to start.