Work 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 don't 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.
You must explicitly define any other joins in each data source or between data sources to express relationships between tables in the Physical layer.
You don't have to use imported key and foreign key joins in metadata. Joins that are defined to enforce referential integrity constraints can result in specifying incorrect joins 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.
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've created an alias to the foreign table, because the foreign table needs to serve in different roles. Each foreign key joins to a primary key in a role-playing alias; see About Physical Alias Tables.
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, see Define Physical Joins with the Physical Diagram and Create and Manage Columns and Keys for Relational and Cube Tables.
About Complex Joins
In the Physical layer of the repository, complex joins are joins over non-foreign 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 usually don't perform as well because they don't 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 must specify multi‐database joins to combine the data from different databases. Use the Physical Diagram to specify multi‐database joins, see Define Physical Joins with the Physical Diagram.
You can create multi-database joins between tables in most types of databases. You can't 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. As a result of the negative performance impact, you should avoid using multi-database joins whenever possible.
About Fragmented Data
Fragmented data is data from a single domain that's 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 related tables. The figure shows the physical joins with a fragmented sales table and a fragmented customer table where the data are fragmented the same way (A through M and N through Z).
You could have a fragmented fact table and a fragmented dimension table with fragments across different values. You create the joins to the fragmented table and define a one-to-many join, as shown in the Customer A to F and from Customer G to Z to Sales A to M example.
Note:
Avoid adding join conditions where they aren't necessary, for example, between Sales A to M and Customer N to Z. Extra join conditions can cause performance degradations.
Define 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.
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.
The physical foreign key joins are the default join type. The object type might change to a complex join after you define the join and click OK.
If you don't want the Administration Tool to automatically determine what type of join to create, use the Joins manager to explicitly create the join. See Define Physical Joins with the Joins Manager.
See Use Hints in SQL Statements and Specify a Driving Table. The driving table option isn't available for selection because the Oracle BI Server implements driving tables only in the Business Model and Mapping layer.