Joins in Oracle BI EE

There a lot of questions on the Oracle Forums, recently also, about join in Oracle BI EE. It seems like there are a lot of misconceptions about the use of joins in Oracle BI EE. 

There are two types of joins;

  • Foreign Key Join
  • Complex Join

These joins could be applied to two different in layers;

  • Physical Layer
  • Logical Layer (Business Model)

 –> When would you use which join?

You can use both joins in both layers. There are some basic rules when it comes to using joins in Oracle BI EE.

In the Physical Layer, you should use Foreign Key joins, while in the Logical layer it’s common to use Complex Joins.

–> Why should you use these joins ?

Physical Layer – Foreign Key Join

The Oracle BI Server uses the Foreign Key Joins to construct the where clause when selecting form multiple tables.

There are two types of keys in the Physical Layer:

  • Primary Key – Unique identifier of a single record in a table
  • Foreign Key – Reference to the Primary Key of another table

So basically the Foreign Key Join is a ‘Primary Key / Foreign Key’- relationship between two tables.

 There are situations where you could use Complex Joins in the Physical Layer. Jeff McQuigg  has written a good blogpost about this subject.

Basically it comes down to the following three situations:

  • Range Joins
  • Data Type Conversion Joins
  • Cartesian Joins

Logical Layer – Complex Join

You use Complex Joins in the Logical Layer only to tell the Oracle BI Server that there is a link between the two tables. The Oracle BI Server should go to the Physical layer to see the actual link between the two tables.

A logical tabel consists of one or more Logical Table Sources. Multiple Logical Table Sources  lead to multiple join paths. Using a Foreign Key join in a situation where multiple join paths exists, restricts the Oracle BI Server only to use the specified join. Therefore it is common practice to use a Complex Join in the Logical Layer.

I hear you think; Why is the Foreign Key Join supported in the first place? It seems to only be there for backwards compatibility

In the Logical Layer you define Primary Keys as well. The purpose of a Primary Key in the Logical Layer is….

  • …to identify the Unique identifier of a single record in a Logical Table
  • … to identify the lowes level of detail of a Logical Table

Change a Physical table to an Alias of a different Physical table

Sometimes you have to make some changes in your physical model, because of changes in the underlying datamodel. This could be a lot of work. In this case UDML could come in handy. I am sure Andreas Nobbmann will agree.

Picture the following;

You have created a physical table with relationships.

When you want to change the actual source of your  physical table you realize that you should have used an alias instead of the actual physical table.

What to do?

With the following scripting you can change the physical table to an alias of a different physical table. This could also be used to change the source of an already existing alias or multiple aliases, or add columns etc.

For an example and reference of how to use udml to change a repository variable script-wise, please check Venkat’s ‘old’ blog.

Step 1.

Produce a UDML script of your source repository:

C:\oracle\bise1\bi\server\Bin>nqudmlgen -U Administrator -P Administrator -R C:\oracle\bise1\bi\server\Repository\OBIBB.rpd -O C:\temp\udml\source.udml

Look for the definition of your physical table in source.udml. The definition runs from “DECLARE TABLE” until “PRIVILEGE ( READ);”

Step 2.

Copy the statement to a seperate text-file:

Step 3.

Modify the statement and add new source to physical table:


In essence the physical table “DIM_TABLE_CONF_1” will become an alias which references a different physical table “DIM_TABLE_CONF_3”.

Save as update.udml

Step 4.

Execute update:

nqudmlexec -U Administrator -P Administrator -I C:\temp\udml\update_source.udml -B C:\oracle\bise1\bi\server\Repository\OBIBB.rpd -O C:\oracle\bise1\bi\server\Repository\updated_OBIBB.rpd

Step 5.

Check the result in the updated repository, in this case; updated_OBIBB.rpd

Bookmark and Share