OBIEE and Outer Joins

December 16th, 2008 by

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 10.2.0.3

Comments

  1. Gilles Says:

    that query looks weird! The caching problem is recognizable. Makes you doubtful about everything and then it turns out to be nothing/!

  2. Gints Plivna Says:

    It is possible to mix tables in from clause and tables with explicit join conditions although I wouldn’t suggest to do so, it really smells like story how to write unmaintainable code – http://www.web-hits.org/txt/codingunmaintainable.html

    However it IS NOT possible to mix old outer join syntax (+) with ANSI explicit join syntax.

  3. Borkur Steingrimsson Says:

    @Gilles I agree, that just looks plane weird. I find it quite hard just to read the query. My point was that it never even crossed my mind to mix the two, so I was very surprised to see a tool mixing the two. About the caching: yes, it’s a known thing indeed. As a developer it is one of the very first things to look at. But in today’s case, the issue arose from not being able to get the outer join to work in the first go, and then the caching hid the solution of getting it right. My developer was quite right on his track but the caching got the better of him in the end :) (Sorry Peter ;) )

    @Gints thanks for that clarification. Again we live and learn. It didn’t cross my mind to test for that (given, I had very short attention span when this problem hit my desk). I can’t imagine why this mix of syntax would be supported, it just begs for unmaintainable code, doesn’t it?

  4. Kim Kraemer Says:

    So, what if there is an unwanted outer join being created? I have not explicitedly defined one, yet one is appearing

    select T5765.LEVEL_12_ORG_NAM as c1,
    T5958.FULL_NAM as c2,
    sum(T6298.US_RULE_EMPLOYEE_CNT) as c3,
    T5765.LEVEL_12_ORG_NUM as c4
    from
    EMPLOYEE_TRANSACTION_DIM T5958 /* Dim_EMPLOYEE_TRANSACTION_DIM */ ,
    HR_REPORTING_ORG_DIM T5765 /* Dim_HR_REPORTING_ORG_DIM */ ,
    EMPLOYEE_COUNT_FAC T6298 /* Fact_YEAR_EMPLOYEE_COUNT_FAC */ left outer join EMPLOYEE_HR_BENEFIT_REL T8393 /* Fact_YEAR_EMPLOYEE_COUNT_FAC (Employee Benefit Relationship) */ On T6298.EMPLOYEE_IDN = T8393.EMPLOYEE_IDN,
    MONTH_DIM T6350 /* Fact_YEAR_EMPLOYEE_COUNT (Year Info) */
    where ( T5765.HR_REPORTING_ORG_IDN = T6298.HR_REPORTING_ORG_IDN and T5958.EMPLOYEE_TRANSACTION_IDN = T6298.EMPLOYEE_TRANSACTION_IDN and T5765.LEVEL_12_ORG_NAM = ‘Corp-IT Architecture-185-44′ and T6298.CALENDAR_MONTH_IDN = T6350.CALENDAR_MONTH_IDN and (T6350.CALENDAR_MONTH_COD in (200801.0) or T6350.MONTH_OF_YEAR_NUM in (12)) )
    group by T5765.LEVEL_12_ORG_NAM, T5765.LEVEL_12_ORG_NUM, T5958.FULL_NAM

  5. Mohan S Says:

    Hi Borkur Steingrimsson,

    This one is a good one ,,,

    but i have doubt ,abt how to create this condition in repository ,

    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 and t67954.col1=’TEST’
    WHERE (t63443.ID = t67650.ID AND
    t67650.ID = t67720.ID AND
    t67650.ID = t67715.ID AND
    t67650.ID = t67974.ID)

  6. Mohan S Says:

    Sorry, i coudnt highlight the change in sql , here the part LEFT OUTER JOIN table6 t67650 ON t67650.ID = t67954.ID and t67954.col1=’TEST’

  7. John Tesson Says:

    Are you using 10.1.3.4 OBIEE?
    I’m using 10.1.3.2 and know that the left outer join doesn’t work correctly unless you are only dealing with one “dimension” table and a fact table. As soon as you include dimensions that are inner joined to the fact table you’ll find that you no longer get rows where there is no match.

  8. rhill Says:

    John Tesson, do you have a workaound to the left outer join problem? that it is not applied when there are multiple dimensions joined to the fact. only one of the joins is specified as left outer. the goal is to create a table and graph including all dates within a range, with counts of fact records associated with each date, including the zero-count days. the problem right now is that days with zero counts are not displayed. this makes tables hard to read and graphs incorrect.

  9. Derek Says:

    Seems whenever you apply a filter to any “Outer Join” query it won’t work. It always gets put into the where clause. You can obviously make filters work with outer joins but obiee should be smart enough to know not to put them in the where clause. this was a serious oversight on their part.

  10. Srikanth Boddupalli Says:

    I’m still facing the issue “whenever you apply a filter to any “Outer Join” query it won’t work.” in OBIEE 11.1.1.6.2. Any workarounds please …. to move the filter from where clause to outer join syntax only.

    Thanks,
    Srikanth

  11. Sree Sanga Says:

    I am also using 11.1.1.6.2 but i didn’t faced teh same.

    Since Siebel analytics is designed to work aroudn dimensional models, here are some guidelines while working with outer joins.

    1. No outer joins in physical layer (OBIEE doesn’t allow this.)
    2. Try to all related tables into one dimension and setup outer joins at LTS level. FOr ex, organize all address related fields into single dimension(BMM), and setup the joins between address table country table as left outer at LTS.
    3. Setup outer joins between fact and dimension as logical join .

    In the worst case, try to push any outer joins into database by creating equivalant view.

    Hope this helps.

    Regards,
    Sree

Website Design & Build: tymedia.co.uk