OBIEE - Full outer Join

Bi Server Architecture With Client

About

To perform a SQL - Full Outer Join, you have two ways :

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

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:

Obiee Logical Join Bmm

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 ?

Documentation / Reference





Discover More
Obiee Fact Cross Join
OBIEE - Densification with the fact-based fragmentation capabilities

You may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. The preservation of the dimensions is is also well known...
Bi Server Architecture With Client
OBIEE - Fact-based vertical partitioning/fragmentation

This technique allow you to : mix two facts table with two different grains (the level based partitioning) perform a densification (to preserve dimension value) : partition vertically your fact...
Bi Server Architecture With Client
OBIEE - How to control the use of the WITH CLAUSE and of STITCH Join ?

The physical sql issued when you have designed a vertical fragmentation depend of several database features parameters: PERF_PREFER_MINIMAL_WITH_USAGE: if checked, the WITH clause is not used PERF_PREFER_INTERNAL_STITCH_JOIN:...
Obiee Ceim
OBIEE - Logical Sql

The logical SQL is the SQL that understand BI Server. It's a full Ansi 92 SQL (including subqueries and derived tables), plus special functions (SQL extensions) like AGO, TODATE, EVALUATE, and others....
Obi Edition
OBIEE - Outer Join

Outer Joins in OBIEE: Outer joins in logical table sources are always included in a query, even if the table source is not used. If possible, create one logical table source without the...
Bi Server Architecture With Client
OBIEE - PERF_PREFER_INTERNAL_STITCH_JOIN database feature

PERF_PREFER_INTERNAL_STITCH_JOIN is a database feature parameter. This parameter controls where a full outer join is performed. If it's checked, the full outer join is performed by BI Server (See stitch...
Obiee Combine With Similar Request
OBIEE 10G - Reporting on Multiple Subject Area (Advanced Logical SQL)

In 11G, you can easily joins two subject areas. This article shows advanced logical SQL statement for the sake of knowledge and was written on a 10G because within an answer, it was possible through the...
Bi Server Architecture With Client
OBIEE 10G/11G - Driving Table

You can specify a Driving table parameter in a complex join. Driving tables are for use in optimizing the manner in which the Oracle BI Server processes cross-database joins when one table is very small...



Share this page:
Follow us:
Task Runner