How to resolve Outer Join generation Issue in the Business Object Migration project (Netezza to PostgreSQL)

[rathish_s4]

 

Issue

After migrating the universe from Netezza to PostgreSQL, the outer joins are generated the opposite way in YB compared to Netezza causing record drop.

Example

Netezza generated query brings 5 records but PostgreSQL generated query brings 1 record.

Analysis

The value ANSI 92 generates an outer join in the FROM clause. Other values generate the outer join in the WHERE clause.

When you modify this setting, you should check join properties to verify that the outer join expression is valid, and that the cardinalities are correct. ANSI92 does not support any manual customization in the join syntax.

Note

The PRM file OUTERJOINS_GENERATION parameter relates to the universe ANSI92 setting in the following way:

If the PRM file OUTERJOINS_GENERATION parameter is set to ANSI92 and the universe ANSI92 setting is set to NO, the PRM parameter overrides the universe setting and outer joins conform to ANSI92 behavior.

If the PRM file OUTERJOINS_GENERATION parameter is set to USUAL, then the universe ANSI92 setting takes precedence, and outer joins conform to ANSI92 depending on whether the universe ANSI92 setting is YES or NO.

Remember

The ANSI92 value makes REVERSE_TABLE_WEIGHT parameter not useful for optimization of SQL generation. Outer joins that conform to ANSI92 behavior leads the order of the tables in the SQL sentence.

Solution1

Configuration steps(prm file)

Step 1: Login to BO server

Step 2: Go to C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\odbc

Step 3: Take the backup of the particular database driver .prm file

Step 4: Add the below parameter in the .prm file and save the file

<Parameter Name="OUTERJOINS_GENERATION">ANSI92</Parameter>

Step 5: Restart the BO server

Step 6: Run/Refresh/schedule the report.

 

Solution 2

Configuration steps (Universe Level)

Step 1: Import the universe from Repository

Step 2: Go to the parameters and click the parameter tab

Step 3: Change the ANSI92 value from No to Yes

Step 4: Save the changes and export the universe to repository

 

 

SAP Portal Reference

https://help.sap.com/viewer/3d4f417fd0764f909c0ef7931e19fe1a/4.2.3/en-US/46746b536e041014910aba7db0e...

Feedback and comments are appreciated Thanks for stopping by, and hope this helps at least a few developers/users out there.,