To perform a SQL - Full Outer Join, you have two ways :
- in the repository by using the fact vertical partitioning capabilities
- or with the Obiee logical sql
The full outer join is often a sign that you are in presence of a preservation of the dimensions: OBIEE - Densification / Dimensions Preservation Possibilities
To have example on how to design a fact vertical partitioning capabilities to perform a full outer join : OBIEE - Fact-based vertical partitioning/fragmentation
To perform a FULL OUTER JOIN, you can simply manage it with a logical sql that you can set for instance in the advanced tab of an answer. And for a step by step guide, it's here: OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)
Example of full outer join with the Obiee logical sql :
SELECT A.saw_0, B.saw_0 FROM (SELECT Calendar."Calendar Year" saw_0 FROM SH WHERE Calendar."Calendar Year" IN (2000, 2001) ) A FULL OUTER JOIN (SELECT Calendar."Calendar Year" saw_0 FROM SH WHERE Calendar."Calendar Year" IN (1999, 2000, 2001) ) B ON A.saw_0 = B.saw_0
If you use a version of Oracle Database 10g Release 2 prior to 10.2.0.3, then you might encounter an issue with a Full Outer Join that causes severe database performance issues and appears to hang the database when the Oracle BI Server sends to the database SQL queries that include WITH clauses.
To work around this issue when using Oracle Database 10g Release 1 or 2, upgrade to Oracle Database 10g Release 2 Patch Set 2 (for 10.2.0.3). For performance reasons, this is the preferred workaround.
To work around this issue without installing Patch Set 2, modify the Database Features table using the Administration Tool. Set to true :
- and PERF_PREFER_INTERNAL_STITCH_JOIN
This configuration prevents the Oracle BI Server from sending SQL constructs that expose the issue in an unpatched Oracle Database 10g and you can see the behaviour of this features in this article: OBIEE - How to control the use of the WITH CLAUSE and of STITCH Join ?