Databases Reference
In-Depth Information
The Carrier Decode table
Let's start by taking a closer look at the
Carrier Decode
table and its contents. If we
were to open the table in Excel this is what we would see:
As you can see, the table extract shown in the screenshot contains the data
corresponding to one particular carrier:
Tradewinds Airlines
. The first seven
columns of the table are not relevant for us right now, so let's focus on the remaining
four. We have a
Carrier Group ID
column which tells us if the carrier is catalogued
as a Major, Large, Medium Carrier, and so on. We also have a
Region Code
column
to indicate if the record corresponds to the domestic or international entity of the
carrier (one carrier can perform both types of flights). And last but not least, we have
a
Start Date
column and an
End Date
column, which will be the main fields we
will use to deal with the slowly changing nature of this particular dimension. Those
values indicate in which time frame the particular record is valid.
For example, the first two records shown earlier have a validity period from January
98 through December 99, in which Tradewinds Airlines was catalogued as a Medium
Regional Carrier (
Group ID
=
4
). Then, from January 2000 all the way through
December 2010, the carrier was playing as a Large Regional Carrier (
Group ID
=
1
).
Afterwards, and until December 2011, it rolled back to the Medium Regional
Carrier category but ascended back up as a Large Regional Carrier again for an
undefined time.
When reporting Tradewinds Airlines' operations, we must take into account the
carrier's classification in place (
Carrier Group ID
field) depending on the time
period(s) being analyzed. Dealing with this is not trivial, so let's get going and create
some
IntervalMatch
magic.
If we look at the original Flight Data table, we can see that we already have a
%Carrier Group ID
field in it, which is the same to that shown in the
Carrier
Decode
table. However, to demonstrate how the
IntervalMatch
function can be
useful, let's assume the field is not already in the fact table and that we must obtain
it from the
Carrier Decode
table.
