Create References

When you design your target database, you will need to create references (or joins) between tables.

For example:

A link between a fact table column and the classification table that contains the descriptions of the values in that column.

For example, in the sample Retail Banking database there is a link between the Gender column in the Customer fact table (which contains values like M and F) and the C_Gender classification table (which contains the descriptions of those values, such as "Male" and "Female").

A link between classification tables.

Linked classification tables are used where the data contains hierarchical classifications.

For example, in the sample Retail Banking database:

There is a column in the Customer fact table called Area, which contains the customer's post code.
The fact table column Area is linked by a reference to a classification table that also contains post codes (C_Geography_0).
The classification table C_Geography_0 is then linked to a classification table containing suburbs (C_Geography_1).
The classification table C_Geography_1 is then linked to a classification table containing cities (C_Geography_2).
The classification table C_Geography_2 is then linked to a classification table containing states (C_State).

SuperCHANNEL automatically builds the hierarchy based on these links when it builds the target database.

Hierarchy in SuperCHANNEL:

Hierarchy in SuperWEB2:

A link between fact tables. These links are required if there are multiple fact tables in your source database.

For example, in the sample Retail Banking database there are two fact tables. A reference is created between the foreign key of the Account table and the primary key of the Customer fact table.

Create a Reference between a Fact Table Column and its Classification Table

To create a reference:

Right-click the fact table column and select Create Reference.
The mouse pointer changes to indicate that you are in the process of creating a reference:
You can continue making other changes during the Create Reference process (for example you can change the data type of a column). The mouse pointer will revert to its default state when positioned over other panes, but will change back to the Create Reference pointer when positioned over the Target View.
If you need to cancel the Create Reference process, press Esc. The mouse pointer will revert to its default state.
Scroll to the classification table where you want to create the reference.
Expand the classification table, and double-click the column you want to link to.
This must be:
- The column containing the codes for this classification table.
- The primary key of the classification table (if it is not currently set to the primary key, you can set this in the Target Attributes ).
- A column with the same data type as the linked column.
SuperCHANNEL creates the reference: