Full Outer Join between different result sets

Question

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 1Fact 1Fact 2Fact 3A26232541B35223200C369522001200D3658

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

[Deleted User] · Answer

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.

User_R1DW5 · Answer

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

User_R1DW5 · Answer

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

.