Thursday, February 3, 2011

Interview Questions on Joins in OBIEE

What is a complex join in OBIEE?

A Complex Join in used in the Business Model & Mapping Layer (BMM layer) in the repository. Logical tables in a BMM layer can have multiple logical tables sources (LTS). Complex join actually is an intelligent join between LTSs of two logical tables in BMM Layer. When two columns are selected for a query in Answers/Dashboard, BI Server reads the complex join between two logical tables and then intelligently & dynamically selects the LTSs to join. This means that the BI server is able to select the most efficient join in the physical layer and translate query into a physical SQL.

What happens when a Foreign Key Join is defined in BMM Layer?

If a physical join (foreign key join) is used in the BMM layer, then BI Server will always select this physical join to create the SQL even when it is not the most efficient one.

When is a complex join used in Physical Layer of OBIEE?

Complex join is used when we want to use join expressions e.g. we want to use less than or greater than operators. A complex join is used while using slowly changing dimension, the expression is then used to define date ranges.


  1. Can you tell something brief about Circular Join and how to solve it with logical/physical alias?

  2. Hi Jyoti,

    Check out this link:


    1. Hi deepak,

      I am suresh,i am searching for obiee10g 3yr experience plz give me ur contact mail id is

  3. What's the difference between Logical Tables and Physical Tables?
    Thanks in advance.

  4. can we use logical joins in physical layer and physical join in logical layer?