Databases Reference
In-Depth Information
We decided the unique field was composed of both fields because if we had used
only one of them, the key would not be unique. That is:
• If we had used the field
Year
as our key, one record in the fact table would
be associated to 12 records in the
Calendar
table, since there are 12 months
corresponding to one year.
• Likewise, if we had used the field
Month
as our key, one record in the fact
table would be associated to as many records as the number of years exist in
the
Calendar
table.
A relation between a fact table and a dimension table should always be at the
same granularity.
Creating a composite key
While we are at it, let's see how we are going to create the composite key needed in
our simulated scenario to solve the synthetic key issue.
First, you should be familiar with the values that exist in each of the fields at play.
Let's assume the following:
• The
Month
field has the following values:
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
• The
Year
field has the following values:
2010, 2011, 2012
Complex keys can be created from the
Load
script. The following script will create
the corrected data model by loading both tables, creating the complex key in both
tables, and removing the conflicting fields from the
Main Data
table while keeping
them on the
Calendar
table:
[Main Data]:
Load
Year & '|' & Month as YearMonth,
Total
From FactTable.qvd (qvd);
Calendar:
Load
Year & '|' & Month as YearMonth,
Month,
Year,
Quarter,
[Month Name]
From Calendar.qvd (qvd);
