Working with Physical Foreign Keys and Joins
You can create physical foreign keys and complex joins using either the Physical Diagram, or the Joins Manager.
However, you do not create joins for multidimensional data sources.
This section contains the following topics:
About Physical Joins
When you import keys in a physical schema, the primary key-foreign key joins are automatically defined.
Any other joins within each data source or between data sources have to be explicitly defined to express relationships between tables in the Physical layer.
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 queries issued by the Oracle BI Server.
This section contains the following topics:
About 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.
Note:
There are two cases where multiple foreign key columns in a table point to the same table:
-
When the primary key of the foreign table is concatenated, meaning that it consists of a set of columns. This is a single join between two tables that happens to use multiple columns.
-
When you have created an alias to the foreign table, because the foreign table needs to serve in different roles. In this case, each foreign key joins to a primary key in one role-playing alias or the other. See About Physical Alias Tables for more information.
You can specify primary key and foreign keys in the Physical Diagram, or by using the Keys and Foreign Keys tabs of the Physical Table dialog. Also refer to Defining Physical Joins with the Physical Diagram and Creating and Managing Columns and Keys for Relational and Cube Tables for more information.
About Complex Joins
In the Physical layer of the repository, complex joins are joins over nonforeign key and primary key columns. In other words, physical complex joins are joins that use an expression rather than key column relationships.
When you create a complex join in the Physical layer, you specify the expression for the join.
For most data sources, foreign key joins are preferred for performance reasons. Complex joins are usually not as performant because they do not use key column relationships to form the join. The exception is ADF data sources, which use physical complex joins exclusively to denote ViewLink instances that connect pairs of View Objects in the ADF model.
About 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.
Use the Physical Diagram to specify multi‐database joins. See Defining Physical Joins with the Physical Diagram for more information.
Multi-database joins can be created between tables in most types of databases and are performed within the Oracle BI Server. Note that you cannot create multi-database joins to tables in Oracle OLAP data sources.
While the Oracle BI Server has several strategies for optimizing the performance of multi-database joins, these joins are significantly slower than joins between tables within the same database. For this reason, avoid them whenever possible.
About Fragmented Data
Fragmented data is data from a single domain that is split between multiple tables.
For example, a data source 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 to which it relates. The figure 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).
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, in addition to the joins created in the fragmented table, you need to define a one-to-many join from Customer A to F and from Customer G to Z to Sales A to M.
Note:
Avoid adding join conditions where they are not necessary, for example, between Sales A to M and Customer N to Z. Extra join conditions can cause performance degradations.
Defining Physical Joins with the Physical Diagram
You can define foreign keys and complex joins between tables, whether or not the tables are in the same data source.
When you use the Physical Diagram to create joins, the Administration Tool determines what type of join to create based on the selected object types and the join expression.
If you do not want the Administration Tool to automatically determine what type of join to create, use the Joins manager to explicitly create the join. See Defining Physical Joins with the Joins Manager for more information.
To define a physical foreign key join or a complex join with the Physical Diagram:
- Click the New Join button on the Administration Tool toolbar:
- Click OK to apply the selections.
In the Physical Diagram, the join is represented by a line between the two selected tables, with an arrow at the "one" end of the join. The image shows a join in the Physical Diagram.
