Random Thoughts

Views on life

OBIEE: The outer join challenge solved

Posted by Ananth Sridharan on July 16, 2010

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 potential 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
  – Write ETL logic to bring all the suppliers over into this lookup table with a default value that can be overwritten by the user
  – 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

What’s next:
The “materialize” (Oracle) database hint

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: