Resolving Outer joins in OBIEE Business model layer

Technorati Tags: OBIEE,Outer join

This article explains the use of different joins available in the business model layer for joining two entities. Outer joins in OBIEE are created using the properties screen of the logical join in the Business Model Layer. By default an inner join is created when you join two objects. You can override this based on the requirement as explained below.

Creating an Outer join -

In the type dropdown box you can choose between "Inner","Left Outer", "Right Outer" and "Full outer".

Left outer join

A "Left Outer" in OBIEE shows all data form the left table (by default the left driving table) with any matches to the right table {FACTS}. If we look in the log we can see the following physical query:

select T1.D_YEAR as c1,
sum(T3.F_FACT_VAL) as c2
from
LEFT DIM_TABLE T1 left outer join
F_FACTS T3 On T1.D_YEAR_MONTH_DAY = T3.D_DATE
group by T1.D_YEAR
order by c1, c2

Right Outer Join

A "Right Outer" is interpreted by OBIEE as show me all data form the right table {F_FACTS} and any matches with the left table. If we look in the log we can see the following physical query:

select T1.D_YEAR as c1,
sum(T3.F_FACT_VAL) as c2
from
F_FACTS T3 left outer join LEFT DIM_TABLE T1
On T1.D_YEAR_MONTH_DAY = T3.D_DATE
group by T1.D_YEAR
order by c1, c2

OBIEE translates the join to a "left outer join"by switching the order of tables.

Full Outer Join

A "Full Outer Join" in OBIEE shows all data from the left table {DIM_TABLE} and all the data from the the right table {F_FACTS} including ones without any match. The log from the physical query will look like:

select T1.D_YEAR as c1,
sum(T3.F_FACT_VAL) as c2
from
LEFT DIM_TABLE T1 full outer join
F_FACTS T3 On T1.D_YEAR_MONTH_DAY = T3.D_DATE
group by T1.D_YEAR
order by c1, c2

..