OBIEE and Outer Joins

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:

SELECT DISTINCT t63443.code AS c1,
                t67974.area AS c2,
                t67974.status AS c3,
                t67715.code AS c4,
                t67720.code AS c5,
                t67720.status AS c6,
                t67954.cost AS c7
           FROM table1 t67715,
                table2 t63443,
                table3 t67720,
                table4 t67974,
                table5 t67954
           LEFT OUTER JOIN table6 t67650 ON t67650.ID = t67954.ID
          WHERE (t63443.ID = t67650.ID AND
                 t67650.ID = t67720.ID AND
                 t67650.ID = t67715.ID AND
                 t67650.ID = t67974.ID)

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

i