3.4.7 Implied Foreign Keys

Implied foreign keys are dependencies that exist between tables but are not defined in the database. In a data warehouse environment, it is a common practice not to create foreign keys. However, it becomes necessary to show these dependencies for presentation or reporting purposes.

You can display implied foreign keys for objects in a star schema by defining them or by discovering them in the data dictionary. It is possible to have more than one source for implied foreign keys.

Define Implied Foreign Keys

You can defined implied foreign keys in two ways:

• Using the Implied Foreign Keys dialog

• By dragging the arrow to the referenced object in the diagram

Using the Implied Foreign Keys Dialog

You can define implied foreign keys using the Implied Foreign Keys dialog in Data Modeler.

1. In a Data Modeler diagram, right-click an object (table or view) and select Implied Foreign Keys.

   Figure 3-18 Select Implied Foreign Keys for an Object

   
   Description of the illustration select_implied_foreign_keys.png

   The Implied Foreign Keys dialog is displayed.

2. In the Implied Foreign Keys dialog, click + to add an entry in the grid.

3. Select the entry in the grid to enable and enter values in the following fields:

   • Referenced Object: Object in the diagram that has a dependency to the source object.

   • Local Column: Name of the column in the source object.

   • Referenced Column: Name of the column in the targeted object.

   • Discovery Sources: Automatically prefilled, displays whether the implied foreign keys have been defined or were discovered in the data dictionary.

4. Click OK. The implied foreign key dependency is displayed with a dotted line on the diagram.

   Figure 3-19 Dotted Line Between Two Objects

   

By Dragging the Arrow to the Referenced Object in the Diagram

You can also define an implied foreign key in the following way:

1. Select the source object on the diagram.

2. Click and drag the small curved arrow with a blue indicator to the referenced object. The dependency will be displayed with a dotted line on the diagram.

   Figure 3-20 Drag Blue Indicator

   
   Description of "Figure 3-20 Drag Blue Indicator"

3. Right-click the source object and enter the column names in the Implied Foreign Keys dialog.

Discover Implied Foreign Keys in the Data Dictionary

A star schema can be discovered by searching for several types of definitions in the data dictionary.

In the Navigator tab, right-click an object (table or view) and select Add Object as Star Schema to Diagram. The object must be a fact table. The data dictionary is then searched for joins and dependencies related to the object, such as:

• Foreign keys defined for the selected table to other tables. If implied foreign keys are later discovered for the same columns, they will not be displayed on the diagram.

• Joins used in the definitions of bitmap join indexes, materialized views with aggregates, and attribute clustering.

• Dependencies based on dimension definitions and column name matching in fact table.

• Fact and dimension definitions for Analytic views and OLAP cube and dimensions.