OACS Report based on Cross database tables with Left Outer join Requirement

GauravSingh

We have a requirement where two tables which needs from different databases needs to join as left outer join.

For ex.

Primary Database - DB1
Secondary database - DB2

Primary database Table - TB1
Secondary Database Table - TB2

Our Report SQL should like below.

select TB1.*, TB2.*
FROM DB1.TAB1 left Outer Join DB2.TB2
WHERE DB1.TB1.COL1 = DB2.TB2.COL1 and DB1.TB1.COL2 = DB2.TB2.COL2 AND DB1.TB1.COL1 = <Presentation Variable>

We have created two separate databases in physical layer and both tables are under the respective connection pools. We have joined both the tables
basis on two columns COL1 and COL2. In Single Business Model, both the tables are dragged and joined as left outer join.

Connection Pools under separate databases refer the Console-Data Connection Names using "Use Data Connection" checkbox.

Issue —> When we are running the report using both the tables, OACS is not applying left outer join instead Inner Join is being applied.

Is there any way we can achieve the left outer join between two tables pulling the data from different databases.

GauravSingh

Can someone please suggest on this?

Gianni Ceresa

Did you check the logs?

Without building a same use cases, difficult to say if OAC can or can't do an outer join when done by the BI Server. But the logs should tell you everything is going on with your query and how it is being handled. It also allow you to make sure you aren't killing your outer join, turning it into the same result of an inner join, because of some filter or things like that.

GauravSingh

Thanks @Gianni Ceresa for responding.

I verified within my abilities -

Next to "Logical Request (before navigation)" section - The logical query seed the plan cache contains the below string.

InnerJoin (Eager) <<34169891>> On D901.c2 = D902.c2 and D901.c5 = D902.c3; actual join vectors: [ 0 1 ] = [ 0 1 ]

which clearly indicate the inner join. Is it fine if i can schedule a quick call to discuss.

GauravSingh

Any update further please?

GauravSingh

Basically When i apply filter (analytics) on left out joined table, OACS BI server makes the inner join and if i do not apply filter on left outer joined table - it makes the outer join as mentioned in BMM layer.

For ex. TB2 is the left outer joined table

select TB1.* , TB2.* FROM DB1.TAB1 left Outer Join DB2.TB2

Chere-Oracle

Hi GauravSignh,

Presuming your BMM has one logical table for DB1.TAB1 columns ("factTAB1") and a second logical table for DB2.TB2 columns ("dimTB2"), with a left outer join between the two logical tables (with factTAB1 on the left) . . .

Check the logical table source for dimTB2. Does this table source include DB1.TAB1, too? If so, check the join type used within the table source. It should also be left outer (with DB1.TAB1 on the left).

If both tables are included in the table source for dimTB2, and if you are still having the problem after changing the join type within the table source, consider modifying the logical tables so that DB1.TAB1 can be removed from dimTB2's table source.

GauravSingh

There is no common in dimTB2 and DB1.TAB1.

Both tables are separate in BMM and logical left outer joined.