OBIEE and Outer Joins

December 16, 2008 Oracle BI Suite EE

One of the developers at a client’s site came to ask me a simple question today: How do you set up an Outer Join in OBIEE? I was fairly certain that I had done so in the past, and when I looked at his physical model I saw, as the developer already pointed out, that the possibility to set the physical join to ‘Outer’ is greyed out; suggesting that it should be possible, but for some reasons it’s not possible right now …

But when you think about it, it really doesn’t make much sense to set the outer join on the physical model: the same tables can be added multiple times in to a business layer, and how the tables are treated there can differ between subjects. My keen developer had already set the join options to Outer in the business layer, but was unable to get the expected result. The reason for that turned out to be completely different: Caching! The BI server was not trying to execute the query as the results were already in the cache, and even though the join had been changed the cache wasn’t about to expire.

Another thing I learned while quickly looking at this problem was this: The BI server generates a mix of ‘regular’ and ANSII join syntax. I didn’t even know that was possible! Here is an example code:

So, a mix between the two types of syntax is supported in the Oracle database. My DB version here is Oracle