OBIEE: The outer join challenge solved

Written by Hemanta Banerjee | Jul 16, 2010 10:16:00 AM

The case:
a. I am looking at spend by supplier, and as a user, I would like to have the flexibility of grouping the suppliers
b. I have a thousand over suppliers, but I am interested in grouping only a hundred or so
c. This is a very dynamic grouping that could potentially change 2-3 times a week.
The approach:
a. Define a lookup table that will have a list of suppliers, and the group to which the supplier belongs to. Lets assume type 1 to keep it simple
b. Either
A - Write ETL logic to bring all the suppliers over into this lookup table with a default value that can be overwritten by the user
B - Use a left outer join (keep all in fact) on the lookup table “this will require the user to maintain the table, and no ETL logic

The problem:
When we use the left outer join, there are 2 options in OBIEE that come to mind. 1, define an LTS to bring the 2 tables together with a left outer join, and 2, define a left outer between the logical dim and fact tables.
I am not a fan of the 1st as that would use the left outer join always (whether I use the grouping or not). And the 2nd approach works except when you use a NOT filter criteria on the grouping. Consider this:
I want to get the spend by supplier group where the supplier group does not equal SG_TECH. When I fetch the results in OBIEE, all the unmapped suppliers will be missed out.

The solution:
Simple as always. When you map the group field into the logical layer, use the ifnull (UNMAPPED) function to set a default value. This way all the suppliers that are not mapped will not be treated as null values but as UNMAPPED, and therefore will not be excluded from the report

Whats next:
The materialize (Oracle) database hint

View full post