Random Thoughts

Views on life

Posts Tagged ‘Outer Join’

Outer Joins in Universe (BusinessObjects)

Posted by Hemanta Banerjee on November 15, 2010


Lets take a simple scenario. I want to generate a report that shows the sales by showroom, and the report should display all the showrooms in the report. For the showroom with no sales it should display the region with a NULL value for sales.

image

The usual join (also called as inner join) will not work in this scenario. We need what is called as an outer join. If you want to know more about outer joins you can checkout the Wikipedia link here.

To enable outer join you need to first set the ANSI92 parameter to Yes. This will change the query from the simple join to an an inner join syntax with from clause as shown below.

image

You can also enable the FILTER_IN_FROM parameter. This pushes the where clause of the join inside the from which reduces the number of records in the join condition and will greatly improve performance.

image

Now we can setup our outer join. As shown below we can setup the right outer join betwen the fact table and the showroom dimension table.

image

The effect of this is that all showrooms irrespective of whether they had a sale or not will be returned by the query.

image

So in conclusion while it is easy to setup outer joins in the universe, one should be very careful when using outer joins as it can result in a cartesian product of all rows especially when using full outer join.

Posted in BusinessObjects, Universe | Tagged: , , , | 1 Comment »

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

Posted in OBIEE | Tagged: , | Leave a Comment »

 
%d bloggers like this: