Sunday 22 January 2017

Complex join in OBIEE - In Brief

Any join other than primary-foreign relationship, it is complex join. Generally we use physical join in physical layer of the repository and complex join in BMM layer. We can have complex join in both the physical and BMM layer but with some changes.

If we apply complex join in physical layer, we can set a formula for joining but cannot change the cardinality of it i.e. we can put any complex formula in joining expression but will not be able to tell server which type of join it should use while making a query.

Whereas if we set it in BMM layer, we can change the cardinality but will not be able to change the expression for the joining i.e. we can change the type of join from inner to outer or vice-versa but will not be able to tell BI server that which physical columns should get used while making a query.

The best practice is not to use complex join anywhere in your data model but if you have to use it for any requirement, use the physical join in physical layer and complex join in BMM layer.

No comments:

Post a Comment