OBIEE - Join

This article talk about the SQL joins in OBIEE.

You have two ways to define a join in OBIEE:

• in the repository when you model : OBIEE - Joins definitions in the repository (complex, foreign, logical table source)
• in a logical sql statement

OBIEE can perform internal join in its memory. This in-memory joins are called stitch join.

A multi-database join (or cross-database) is defined as a table under one metadata database object that joins to a table under a different database object in the physical layer.

You need to specify multi-database joins to combine the data from different databases.

The joins can be between tables in any databases, regardless of the database type, and are performed within the Oracle BI Server (known as stitch join)

While the Oracle BI Server has several strategies for optimizing the performance of multi-database joins, multi-database joins will be significantly slower than joins between tables within the same database. It is recommended to avoid them whenever possible.

Fragmented data is data from a single domain that is split between multiple tables. For example, a database might store sales data for customers with last names beginning with the letter A through M in one table and last names from N through Z in another table.

With fragmented tables, you need to define all of the join conditions between each fragment and all the tables it relates to.

In some cases, you might have a fragmented fact table and a fragmented dimension table, but the fragments might be across different values. In this case, you need to define all of the valid joins.