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.
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
We use complex join in physical layer mostly in below scenarios.
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.
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.
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.
4) When the operator is other than the equal " = " operator.
That's all about joins in OBIEE.
Please share your feedback and keep me posted.
have a good day!!
have a good day!!
Thanks
Very good post....
ReplyDeleteCould You explain one senario about compels join use in physical layer with one senario
ReplyDeleteWe 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.
Thanks for summarising all the important details on OBIEE joins
ReplyDelete