About
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
To know what sort of join is a full outer join, follow this link : SQL - Full Outer Join.
The full outer join is often a sign that you are in presence of a preservation of the dimensions: OBIEE - Densification / Dimensions Preservation Possibilities
Articles Related
In the repository
With a vertical fragmentation
To have example on how to design a fact vertical partitioning capabilities to perform a full outer join : OBIEE - Fact-based vertical partitioning/fragmentation
In the logical join
In the logical join of the business model layer:
With the Obiee Logical Sql (In Answer for instance)
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 10G - 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
Support
Issues with Full Outer Join and WITH Clause on Oracle Database 10g
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 :
- PERF_PREFER_MINIMAL_WITH_USAGE
- 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 ?