Databases Reference
In-Depth Information
Drop Field [%TEMP Key Field];
Drop Fields Year,
Period,
[Month (#)],
Month,
[%Airline ID],
[%Unique Carrier Code],
[%Carrier Group ID],
[Unique Carrier],
[Carrier Code],
[Carrier Name] From [Employment Statistics];
Drop Fields Year,
Period,
[Month (#)],
Month,
Quarter,
[%Airline ID],
[%Unique Carrier Code],
[%Carrier Group ID],
[Unique Carrier],
[Carrier Code],
[Carrier Name] From [Main Data];
With the preceding script we are doing the following:
a.
Assign a name to the table:
Link Table
.
b.
Create a list of distinct combinations of all shared fields from the
previously loaded
Main Data
table, including the new
%Key Field
,
by performing a
Resident Load
(more on Resident Loads in
Chapter
12
,
Advanced Data Transformation
).
c.
Create a duplicate of the
%Key Field
attribute and name it
%TEMP
Key Field
.
d.
Concatenate a new list of distinct combinations of all shared fields
from the previously created
Employment Statistics
table,
including the new
%Key Field
attribute and adding a calculated field
Quarter
. From this new list we exclude all combinations that already
exist on the first list earlier, using a
Where
clause.
e.
Remove the
%TEMP Key Field
field from the data model, as it was
only to be used in the
Where
clause.
f.
Remove the shared fields from each fact table, except the
%Key Field
attribute, as they will now be stored in the link table.
