1 Reply Latest reply on Feb 18, 2015 7:32 PM by Gianni Ceresa

    OBIEE 11g: Left Outer Join

    Sudipta Chowdhury
    Sudipta Chowdhury Feb 13, 2015 11:15 AM

      Hi Guys,

       

      I have small query regarding the setting up the left outer join in RPD.

       

      Suppose, I have a fact table (F1) and a dimension table (D1), I will have to define left outer join between F1 and D1. like:

       

      select * from F1

      left outer join D1 on

      F1.Emp_ID=D1.Emp_ID

       

      I could see three places where I can define the same.

       

           1. I can add the Fact table into my Dimension Table LTS and set left out join.

           2. I can add the Dimension table into my Fact Table LTS and set left out join.

           3. While defining the Business join in BMM layer, there also same can be defined.

       

      I would like to know, what is the difference between these three ways, any advantage or disadvantage if I am using any of it.

        • 1. Re: OBIEE 11g: Left Outer Join
          Gianni Ceresa
          Gianni Ceresa Feb 18, 2015 7:32 PM (in response to Sudipta Chowdhury)

          Honestly for me it sounds "obvious" that the "keep it stupid simple" approach is the one to use.

          All the 3 have different meanings, provide different answers for different requirements.

          For a "standard" outer join you definitely must start by the one in the Business model diagram, when you draw the join between Fact and Dim, that's a standard simple outer join between a fact a dimension.

          This is also the easiest to maintain.

           

          If you start playing with LTS I would say you must a real need and model built to work in that way.

          Just for the example: adding you dim in the fact LTS => your dimension is into the Fact logical table, you don't have a dimension anymore. Without a dimension OBIEE will not validate your model.

          With the opposite you are bringing fact columns into the dimension, if you don't add at least a column of the fact table source into the logical table of the dimension you have a warning.

          Both these solutions are not clean, generate warning during the consistency check and are a perfect source of huge issues in the future when you are going to expand your model.

           

          PS: do we agree you must not have a single warning in your RPD, right? (not that it doesn't work with warnings, but doings things right now it's the best way to avoid problems tomorrow)