Databases Reference
In-Depth Information
Working with link tables
When we include two or more fact tables in a single QlikView document, it's
very likely they all are somehow related and will, therefore, have some common
dimension fields among them. However, as we've outlined before, in a QlikView
data model, two tables should not be associated through two or more fields
because it would generate a synthetic key.
So how do we incorporate two or more fact tables into one data model and treat
them as two separate logical tables while, at the same time, avoiding the synthetic-
key issue? At first sight, it can seem like both options are mutually exclusive, but
there is a workaround which is to create a
Link Table
.
As its name implies, a link table essentially "links" two or more fact tables by taking
all common fields out of the original tables and placing them into a new one (the link
table).The new link table contains all possible combination of values for that set of
fields and, through a unique key, is associated to the original tables.
A link table example
Take, for example, the following scenario:
• We are required to design a data model for analyzing Call Center
Performance data, and have two fact tables:
Operations
and
Payroll
. Based
on these tables, we need to be able to present cross-functional information in
a QlikView dashboard.
• The
Operations
table has the following fields:
Call ID
,
Timestamp
,
Employee ID
,
Supervisor ID
,
Department ID
,
Call Type ID
,
Customer
ID
,
Call Duration
, and
Total Hold Time
.
• The
Payroll
table has the following fields:
Payroll ID
,
Employee ID
,
Department ID
,
Position ID
,
Amount
.
• We also have the corresponding dimension tables to provide a description to
the fields
Call Type ID
,
Employee ID
,
Department ID
, and
Position ID
.
