Use of Outer Joins in Combination with Self Restricting Joins

6 Replies

Problem

BusinessObjects use some algorithm for SQL generation based on objects selected in the query and the universe. There are a number of parameters that control SQL generation. The problem is that in some cases BO generates SQL that is not intuitively expected.

What we would expect is that the self restricting joins are applied to the tables and then the tables are joined.

When SELFJOINS_IN_WHERE=No, the self restricting joins are put in the ON clause of the SQL. The generated SQL ignores the self restricting join on the table FACT.

SELECT DISTINCT
  FACT.A,
  DIM.B
FROM
  DIM  RIGHT OUTER JOIN  FACT
  ON  (FACT.DIM_ID=DIM.DIM_ID  AND  FACT.TYPE=0  AND  DIM.TYPE=0)

When SELFJOINS_IN_WHERE=Yes, the self restricting joins are put in the WHERE clause.  The effect of this is that the outer join turns into inner join in the generated SQL.

SELECT DISTINCT
  FACT.A,
  DIM.B
FROM
  DIM  RIGHT OUTER JOIN  FACT
  ON  (FACT.DIM_ID=DIM.DIM_ID)
WHERE
  ( FACT.TYPE=0  )
  AND  ( DIM.TYPE=0  )

In fact, we expect the following SQL:

SELECT DISTINCT
  FACT.A,
  DIM.B
FROM
  DIM  RIGHT OUTER JOIN  FACT
  ON  (FACT.DIM_ID=DIM.DIM_ID  AND  DIM.TYPE=0)
WHERE
  ( FACT.TYPE=0 )

BusinessObjects cannot place self restricting joins conditionally depending on the join structure. (Probably this will make the algorithm too complex, so this is not a bad thing)

A workaround is to avoid using of self joins using derived tables.

A Solution when SELFJOINS_IN_WHERE=No

Replace FACT with a derived table FACT_D

SELECT DIM_ID, A FROM FACT WHERE FACT.TYPE=0

The generated SQL will be:

SELECT DISTINCT
  FACT_D.A,
  DIM.B
FROM
  DIM RIGHT OUTER JOIN (
  SELECT DIM_ID, A FROM FACT WHERE FACT.TYPE=0
  ) FACT_D
  ON (DIM.TYPE=0  AND  FACT_D.DIM_ID=DIM.DIM_ID)

A Solution when SELFJOINS_IN_WHERE=Yes

Replace DIM with a derived table DIM_D

SELECT DIM_ID, B FROM DIM WHERE DIM.TYPE=0

The generated SQL will be:

SELECT DISTINCT
  FACT.A,
  DIM_D.B
FROM
  (
  SELECT DIM_ID, B FROM DIM WHERE DIM.TYPE=0
  ) DIM_D RIGHT OUTER JOIN FACT
  ON (FACT.DIM_ID=DIM_D.DIM_ID)
WHERE
  ( FACT.TYPE=0 )

6 thoughts on “Use of Outer Joins in Combination with Self Restricting Joins”

  1. Elisabeth

    Wow, your blog has become somewhat of a BO bible to me lately! Whatever challenge I am facing, you always have a very educational way of clearing things up for me. I am very impressed!

    As for this outer join/self join issue, we have so far been hacking it a bit. We have not been using ANSI-92, and in Oracle we have hard coded the join to include and “outer” the self join, like this:
    DIM.DIM_ID(+) = FACT.DIM_ID AND DIM.TYPE(+) = 0
    by selecting the two columns in the join editor, ticking the outer join box, and typing in the last bit “AND DIM.TYPE(+) = 0”.

    The problem now is that we want to go ANSI92 to be able to use the same universe for clients on Oracle and SQL Server, and thus no more hard coding joins. So I guess the way to go will be derived tables for us, right? Because it’s not possible to “outer” a self join any other way?

    And how do I decide if I should have YES or NO for SELFJOINS_IN_WHERE?

    Thanks again for your excellent blog. Now I’m off to read “How to Replace a Table with a Derived Table” 😀

    Like

    Reply ↓
    2. dmytro Post author

      Should YES or NO be used for SELFJOINS_IN_WHERE?

      The default is NO and this seems to be the most logical behavior.
      YES might be reasonable when all joins are INNER.

      Like

      Reply ↓
    3. dmytro Post author

      Regarding DIM.DIM_ID(+) = FACT.DIM_ID AND DIM.TYPE(+) = 0

      Yes, in this situation the correct way is to “embed” the self join into derived table

      Like

      Reply ↓
  2. ganesh

    Great explanation.
    Seems there is an error in the SQL of FACT_D.
    It should be like SELECT DIM_ID, A FROM FACT WHERE FACT.TYPE=0
    instead of SELECT DIM_ID, B FROM DIM WHERE DIM.TYPE=0.

    Correct me if am i wrong. 🙂

    Like

    Reply ↓

Leave a comment