Complex Join and Physical/Foreign Key Join

Physical/Foreign Key Join

A Physical/Foreign Key  repository join is used only to define a Primary Key-Foreign Key Relationships in the Physical Layer. When you create  Primary Key-Foreign Key Relationships  in the physical diagram, you have to point first the dimension and then the fact table.

Tip : While importing tables from physical schema into RPD, if we select KEY and FOREIGN KEY along with the table, then the primary key-foreign key joins are automatically defined. But this is not a good practice. We should not import keys and foreign keys as it takes long time if we have more data in tables and more tables in schema.

Complex Join :

In the business model, OBI recommends using complex logical joins instead of foreign key logical joins.

When Complex joins are used in the business layer then they are really acting as placeholders. 

They allow the OBI Server to decide on which are the best joins define in the Physical Layer to satisfy the request.

In a Dimension, you may have multiple Logical Table sources that all join to one fact table. The OBI Server will then decide the best joins to use to meet the requirements.