In a previous article I showed the basics of working with OBIEE Outerjoins: (http://knowledge.ciber.nl/weblog/?p=152).

This works fine until you add a filter :

From the log:

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
having 440000 < sum(T31.F_FACT_VAL)
order by c1

This "kills" the outerjoin…. I hear think why not add "OR IS NULL". Let’s do that:

From the log:

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
having 440000 < sum(T31.F_FACT_VAL) or sum(T31.F_FACT_VAL) is null
order by c1

We still  are missing a couple off years.

Let try an in view filter: change F_FACT_VAL to:

CASE WHEN SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR )> 440000 then SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR ) else NULL end

This give us all the years back:

From the log:

SET VARIABLE QUERY_SRC_CD=’Report’;SELECT DIM_YEAR_MONTH_DAY.D_YEAR saw_0, CASE WHEN SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR )> 440000 then SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR ) else NULL end saw_1 FROM BM_OUTER_JOIN ORDER BY saw_0

select T26.D_YEAR as c1,
     sum(T31.F_FACT_VAL) as c4
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

As you can see OBIEE did the case when logic internally.

Till Next Time

John Minkjan is a senior BI-consultant and OBIEE Product Expert at Ciber in the Netherlands, the text of this article is also published on his personal blog http://obiee101.blogspot.com/