Outer join definition in multisource universe

[Former Member]

Hi all,

I am using Information Design Tool Version: 14.0.4.v20120611-0738.

I have to create a report using a oracle table (outer join) and a SAP BW multiprovider.

I have created a multisource universe: using a relational connection to the oracle table (ZRC_ORACLE_ND1) and a relational connection to the
SAP BW multiprovider (ztest_gv01). I have created a data foundation (zdf_ztest_gv01). The BW multiprovider contains objects with time dependant attributes. I think that is the reason that there is automatically added a Reference_Date in the data foundation. I have defined the right outer join on the oracle table.

I have created a business layer (zbl_ztest_gv01). I have created test queries on the business layer:

1) zq_ztest_gv01_query01 (only dimension of the oracle table): returns all the values of the oracle table (2714 rows).

2)  zq_ztest_gv01_query02 (combination of a dimension on the oracle table and a key fig of the BW multiprovider - with outer join configuration

on the oracle table): returns only 677 rows, so the outer join is not working. This is probably caused by the Reference_Date condition which

automatically added by the sql generator. How can I remove this reference_date condition in the sql ? This condition is not relevant for this report.

"SELECT Table__1."FUND_CENTER", SUM(Table__2."ZFME0101")

FROM "ztest_gv01"."PUBLIC"."IZPU_M02" Table__2

RIGHT OUTER JOIN "ZRC_ORACLE_ND1"."BOECMS"."ZFC_VALID" Table__1 ON (Table__1."FUND_CENTER"=Table__2."0FUNDS_CTR")

WHERE Table__2."REFERENCE_DATE" = {d '2013-05-02'}

GROUP BY 1"

So my question is: how can I make the outer join on the oracle table work fine ? I think it should work when I can remove the Reference_Date condition from the query. But when I try to change the sql manually, the statement is not valid. Anybody  an idea ?

Kind regards,

Guy Verschueren

[Former Member]

I'm having a similar issue.  did you find a solution?

I created a "from" and "to" prompt and tried to feed that into a filter at the business layer...like this:

ref_date between (from) and (to)

but it doesn't work...I'm dealing with HR and they want to see previous salary.    By default this just pulls top of stack unless you make the prompt open to users...but then they are just guessing at dates to enter...    

[Former Member]

trying this note...

1834782 - Outer Join behaves as inner join in multisource universe based on SAP JCo connection