About Physical Joins

All valid physical joins need to be configured in the Physical layer of the Administration Tool.

NOTE:  You do not create joins for multidimensional data sources.

When you import keys in a physical schema, the primary key-foreign key joins are automatically defined. Any other joins within each database or between databases have to be explicitly defined to express relationships between tables in the physical layer.

NOTE:  Imported key and foreign key joins do not have to be used in metadata. Joins that are defined to enforce referential integrity constraints can result in incorrect joins being specified in queries. For example, joins between a multipurpose lookup table and several other tables can result in unnecessary or invalid circular joins in the SQL issued by the Oracle BI Server.

Multi-Database Joins

A multi-database join is defined as a table under one metadata database object that joins to a table under a different metadata database object. You need to specify multi-database joins to combine the data from different databases. Edit the Physical Table Diagram window to specify multi-database joins. The joins can be between tables in any databases, regardless of the database type, and are performed within the Oracle BI Server. 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. For more information about the Physical Table Diagram, refer to Defining Physical Joins in the Physical Diagram.

Fragmented Data

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. Figure 9 shows the physical joins with a fragmented sales table and a fragmented customer table where they are fragmented the same way (A through M and N through Z).

Figure 9. Fragmented Tables Example

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, as shown in Figure 10.

Figure 10. Joins for Fragmented Tables Example

TIP:   Avoid adding join conditions where they are not necessary (for example, between Sales A to M and Customer N to Z in Figure 9). Extra join conditions can cause performance degradations.

Primary Key and Foreign Key Relationships

A primary key and foreign key relationship defines a one-to-many relationship between two tables. A foreign key is a column or a set of columns in one table that references the primary key columns in another table. The primary key is defined as a column or set of columns where each value is unique and identifies a single row of the table. You can specify primary key and foreign keys in the Physical Table Diagram or by using the Keys tab and Foreign Keys tab of the Physical Table dialog box. Also refer to Defining Physical Joins in the Physical Diagram and Creating and Administering Columns and Keys in a Physical Table.

Complex Joins

In the physical layer of the repository, complex joins are joins over nonforeign key and primary key columns. When you create a complex join in the physical layer, you can specify expressions and the specific columns on which to create the join. When you create a complex join in the business model layer, you do not specify expressions.