Business Intelligence Tutorial
In this lesson, you will define primary and foreign keys on target tables
to use later in a join. To do this lesson, you must have defined the
dimension tables and fact table in Defining the rest of the tables for the star schema (optional).
In each target table, you will select a column that can be used to uniquely
identify rows in that table. This will be the table's primary
key. The column that you select as a primary key must have the
following qualities:
- It must always have a value. The column for a primary key cannot
contain null values.
- It must have unique values. Each value in the column must be
different for each row in the table.
- Its values must be stable. A value should never change to another
value.
For example, the CITY_ID column in the LOOKUP_MARKET table (created in Defining data transformation and movement) is a good candidate for designation as a primary
key. Because each city needs an identifier, no two cities can have the
same identifier, and identifiers are unlikely to change.
Defining a primary key for a table is highly recommended because uniquely
identifying each row speeds up row access.
You use foreign keys to define relationships between tables. In a
star schema, a foreign key defines the relationship between the fact table and
its associated dimension tables. The primary key of the dimension table
has a corresponding foreign key in the fact table. The foreign key
requires that all the values of a given column in the fact table also exist in
the dimension table. For example, the CITY_ID column of the FACT_TABLE
might have a foreign key defined on the CITY_ID column of the LOOKUP_MARKET
dimension table. This means that a row cannot exist in the FACT_TABLE
unless the CITY_ID exists in the LOOKUP_MARKET table.
In this lesson, you will define primary keys on the four target tables that
you created in Defining data transformation and movement: LOOKUP_MARKET, LOOKUP_TIME, LOOKUP_PRODUCT, and
LOOKUP_SCENARIO. You will define corresponding foreign keys in the
FACT_TABLE target table.
In this exercise, you will define a primary key for the LOOKUP_MARKET,
LOOKUP_TIME, LOOKUP_PRODUCT, and LOOKUP_SCENARIO target tables.
To define the primary keys:
- Go to the Control Center window.
- Click View -> Refresh.
- Find the LOOKUP_MARKET table in the list of tables for the TUTWHS
database. Right-click the table and click Alter.
The Alter Table notebook opens.
- Click the Primary Key tab. The Primary Key page
opens.
- In the Available columns list, select CITY_ID as the
primary key.
- Click > to move CITY_ID to the Primary key
columns list.
- Leave the Constraint name field empty, so that DB2 Universal
Database will generate a constraint name for you. A primary key can be
considered a constraint, because all values in the selected column must be
unique.

- Click OK to save your definition.
Follow the same steps to define primary keys for the other target
tables. Define:
- TIME_ID as the primary key for the LOOKUP_TIME table.
- PRODUCT_KEY as the primary key for the LOOKUP_PRODUCT table.
- SCENARIO_ID as the primary key for the LOOKUP_SCENARIO table.
You need to define foreign keys for the relationships between the
FACT_TABLE and the other target tables.
In this exercise, you will define a foreign key in the FACT_TABLE
(dependent table) based on the primary key of the LOOKUP_MARKET table (parent
table).
To define the foreign keys:
- Find the FACT_TABLE table in the list of tables for the TUTWHS
database. Right-click the table and click Alter.
The Alter Table notebook opens.
- Click the Foreign Keys tab. The Foreign Key page
opens.
- Click Add. The Add Foreign Key window
opens.
- In the Table schema field, select IWH.
- In the Table name field, specify LOOKUP_MARKET, which is the
parent table. The Primary key field displays the primary key
for LOOKUP_MARKET. The steps that generate the parent table must be in
test or production mode in order for the primary key to be available. A
primary key must be defined for the parent table before a foreign key can be
defined for the FACT_TABLE.
- In the Available columns list, select CITY_ID as the column
that you want defined as the foreign key.
- Click > to move CITY_ID to the Foreign key
list.

- Click OK to save your definitions.
Define foreign keys for the FACT_TABLE to the other target tables by
repeating steps 3 to 8. Define:
- TIME_ID as a foreign key with the LOOKUP_TIME table as the parent.
- PRODUCT_KEY as a foreign key with the LOOKUP_PRODUCT table as the
parent.
- SCENARIO_ID as a foreign key with the LOOKUP_SCENARIO table as the
parent.

- Click OK to save the information in the Alter Table
window.
In this section, you will define foreign keys in the Data Warehouse
Center.
To define foreign keys into your data warehouse:
- In the Data Warehouse Center window, expand the Warehouse
Targets tree.
- Expand the Tutorial Targets tree until you see
Tables folder.
- Right-click on the FACT_TABLE table.
- Click Properties.
The table Properties notebook opens.
- Click the Warehouse Foreign Keys tab.
- Right-click on the Constraint name of the first foreign key in
the view and click Remove. Do this for each foreign key in
the view. These foreign key definitions were carried over when the Fact
Table Join step was copied. They refer to tables in the TBC Sample
Targets and need to be deleted and replaced with new foreign keys that refer
to tables in the Tutorial Targets.
- Right-click on the white space and click
Define. The Define Warehouse Foreign Key window
opens.
- Select IWH in the Object schema list.
- Select LOOKUP_MARKET in the Object name list.
The primary key for LOOKUP_MARKET appears in the Warehouse primary
key field.
- Select CITY_ID in the Available columns
field.
- Click > to move CITY_ID into the Warehouse foreign key
columns field.
- In the Constraint name field, type:
"Whse Market FK"
The constraint names must be different for each foreign key. The
constraints for the remaining three LOOKUP tables are shown in the table
below.
- Click OK to save your definition and close
the Define Warehouse Foreign Key window.
- Repeat steps 7 to 13 to add foreign keys for the remaining three
LOOKUP tables.
Table
| Constraint name
|
LOOKUP_TIME
| "Whse Time FK"
|
LOOKUP_PRODUCT
| "Whse Product FK"
|
LOOKUP_SCENARIO
| "Whse Scenario FK"
|
- Click OK to save your changes and close the table Properties
window.
In this lesson, you defined primary keys for the following target
tables:
- LOOKUP_MARKET
- LOOKUP_TIME
- LOOKUP_PRODUCT
- LOOKUP_SCENARIO
You defined foreign keys for the FACT_TABLE that correspond to the primary
keys. In Creating a star schema from within the Data Warehouse Center, you will use these keys to join the tables in the star
schema.
[ Top of Page | Previous Page | Next Page | Table of Contents ]