Random Thoughts

Views on life

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.

Advertisements

One Response to “Outer Joins in Universe (BusinessObjects)”

  1. Fazalhusein Z Patvi said

    Thanks, it was a concise article shwoing the steps in an easy to understand manner.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: