Foreign keys and Primary keys

Fact tables and Dimension tables

Some tables contain the actual data or the facts that we would like to analyze such as Amount, Quantity etc. These tables are often referred to as Fact tables.

Each post in the fact tables also contains information about e.g. which item has been sold, but the item information in the fact table may be kept short, often limited to the item number only.

Further information about an item number such as name and description may then be found in another table, e.g. the Item table. Such a table is then called a Dimension table.

We need to tell the system that these two tables are related by creating a relationship between them by linking the Item number keys in the fact table to the Item number keys in the dimension table.

The keys in the fact tables are called Foreign keys while the keys in the dimension tables are called Primary keys.

You may also link two dimension tables together in this way in which case the key you are linking from is called the Foreign key.

In some systems you may experience that Primary keys have already been created in the Stage database, but if they are not, they will need to be created as part of setting up the relationship between two tables.

Creating relationships between tables, and knowing which fields are to be regarded as foreign keys and primary keys of course requires quite some knowledge about the source data at hand.

To help you to create the correct relationships at this point, please use this little guide: