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.
