OBIEE Understanding Outerjoins Part 1

First of all in an "ideal" DWH you wouldn't have a need for outerjoins, but sometimes you need to make data visible which you don't have.

Outerjoins in OBIEE can be a bit tricky and don't always give you the result you would expect. In this article I will get trough the basics, in a next trough some pittfalls....

Creating an outerjoin

In OBIEE outerjoins are created in the Business Model Layer:

Open the properties screen of the logical join:

 

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

Left outer join

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

select T26.D_YEAR as c1,
     sum(T31.F_FACT_VAL) as c2
from
          DIM_YEAR_MONTH_DAY T26 left outer join
          F_FACTS T31 On T26.D_YEAR_MONTH_DAY = T31.D_DATE
group by T26.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 {DIM_YEAR_MONTH_DAY}. If we look in the log we can see the following physical query:

select T26.D_YEAR as c1,
     sum(T31.F_FACT_VAL) as c2
from
          F_FACTS T31 left outer join
          DIM_YEAR_MONTH_DAY T26 On T26.D_YEAR_MONTH_DAY = T31.D_DATE
group by T26.D_YEAR
order by c1, c2

If you have a closer look at the log you will see that OBIEE has translated the join to a "left outer join"by switching the tables.

Full Outer Join

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

select T26.D_YEAR as c1,
     sum(T31.F_FACT_VAL) as c2
from
          DIM_YEAR_MONTH_DAY T26 full outer join
          F_FACTS T31 On T26.D_YEAR_MONTH_DAY = T31.D_DATE
group by T26.D_YEAR
order by c1, c2

..

Till Next Time

This article is orignally written for the ciber knowledge blog: http://knowledge.ciber.nl/weblog/?p=152