Logical and Physical Joins in OBIEE

In simple terms I would say most of the times Logical join is created in BMM layer and Physical join is created in physical layer,but there might be some scenarios where we need to create complex join in physical layer and vice versa, these will be discussed later.

What is Logical Join

Below diagram is window of complex join . In here ,you can see that we can't write any expression in expression pane.however ,we can change the type of join and can define the driving table and set the cardinality which means logical join helps BI server to determines only the relationship between the table , how these table are connected it doesn't tell BI server that what physical columns are joining it just tell that what type of join is between tables.

Physical Joins

Whereas,Physical join helps BI server to understand that how to join two tables by specifying physical columns.Below diagram is  window of physical join.In here,as you can see in expression pane we can define that how these two table should be join to each other

In short, Logical join helps to define the relationship between two tables and what type of join is b/w two tables. Whereas, Physical join helps to understand that how two tables are joined.

We use complex join in physical layer mostly in below scenarios.

1 ) When we have to join key column of one table to non key column of other table.
2 ) When the operator is other than equal to operator. 

Here comes a interview question

we already have joins in Physical layer then why we need to create logical joins in BMM layer or question comes in this way that why we need to create logical joins :

The answer is whenever a user runs a report logical query are generated based on the logical joins in BMM layer as OBIEE server understands logical query only.

Other reasons for creating logical joins are

1) Type of join can be specify through logical join only.  

2) Cardinality can set only in logical join.

3) Driving table only in logical join.
4) When the operator is other than the equal " = " operator.

Let's look at complex join in physical layer. 

Although it doesn't happen frequently, it is sometimes needed. Let's say we have 2 tables, promotion fact and contract date dimension. I want to join these 2 tables in such way so that only the dates that are still in contract should return. Therefore, I can't just use a simple join on the date columns from both tables, conditions need to be applied.. In this case, let's use complex join in physical layer:

In the below diagram, I enter 'PTS_DATES.COMPANYDATEID >= PTS_STAR_FACTS.CONTRACTSTARTDATEID AND PTS_DATES.COMPANYDATEID <= PTS_STAR_FACTS.CONTRACTENDDATEID' to satisfy the joining condition. At the front end, when you run a report using these tables, this expression will be included in the where clause of the SQL Statement:

Having physical join in BMM layer is also acceptable, however it is very rare to see that happen. The purpose of having physical join in BMM layer is to override the physical join in physical layer. It allows users to define more complex joining logic there than they could use physical join in physical layer, in other words, it works similar to complex join in physical layer.