Outer Joins in Multi Source Universes and ANSI92 Parameter

[JYOUSAF]

I am writing this thread to ask some questions about outer joins created within multi source universes (based on JCO connections) on BW cubes.  

1. Will Outer join within a multi-source universe work even if the universe parameter ANSI92 is set to ‘No’ or the ‘ANSI92’ is not even in the parameter list.
2. I have seen some behavior regarding outer joins in MS universes where an outer join behaves as an inner join.  Is it because of the prompts set in the webi report inserting a where clause in the SQL generated at run time or is there another explanation.  I have seen an oos note (1834782)that talks about it.
3. Lastly, is there a consensus as to which join is more efficient, outer join or inner join?

[amitrathi239 · Accepted solution]

Hi,

Without setting the ANSI92 to Yes you can not set the full outer join between two tables.if you see the Data Foundation->parameters>ANSI92 is available with default parameter No for BW DSO/Cube.

Setting joins are more depend on the requirement. efficient join is inner join when we are talking sap bw cube/dso.

but if you set the full outer or left/right join between two I -tables where records in millions might be some time huge volume reports not run.

Amit

[JYOUSAF · Accepted solution]

Thank you Kumar for replying.

In my universe, there are options available to set left or right outer joins.  This is even when the ANSI92 parameter is not introduced in the universe parameter list. These options are in the form of check boxes under the table(s) on the pop up window where you set a join.   It lets me check on either outer join box (left or right) but the outer join doesn’t seem to work, that is, it generates a result set which is essentially a result of an inner join.

How about efficiency of an outer join between an I table and a text table with I-table having a few million records.

I under decision to set a join depends on the requirement but is there a way to avoid an outer join and still get all records (matched and mis matched)?

Thank you.   

[amitrathi239 · Accepted solution]

Hi,

Letf/Right joins you can set the without setting the ANSI92 to Yes.ANSI92 "Yes" is required when you have to set full outerjoin. Check if you can select the  both tables outerjoin checkbox without setting up ANSI92 to Yes.

There is no need to go with outer join when you are joining I and T tables.You will get the correct results with inner joins only.

When you are building universe on top of Cube/DSO all records will come in the I-Table.

Amit