Database Reference
In-Depth Information
Returning back to the first exclusive gateway, if the
Region
attribute is not
null, we add a new column
State
initialized with the values of column
Region
and we make the union of these records with ones having a value of
State
different from null obtained in the first lookup.
Then, in a similar way as the task that loads the
City
table, five lookup
tasks are needed, where each one tries to match a couple of values of
State
and
Country
to values in the lookup table built as a join between the
City
,
State
,and
Country
tables as follows:
SELECT C.CityKey, C.CityName, S.StateName, S.EnglishStateName,
S.StateCode, T.CountryName, T.CountryCode
FROM City C JOIN State S ON C.StateKey = S.StateKey
JOIN Country T ON S.CountryKey = T.CountryKey
Two additional cases are needed with respect to the
City Load
task:
The fourth lookup process records where
State
and
Country
correspond,
respectively, to
StateCode
and
CountryName
. An example is state
BC
and
country
Canada
.
The fifth lookup process records where
State
and
Country
correspond,
respectively, to
StateCode
and
CountryCode
. An example is state
AK
and
country
USA
.
Finally, we perform the union of all flows, add the column
CustomerKey
for
the surrogate key initialized to null, and write to the target table by means
of an insert data task. We omit the description of the ETL process that loads
the
Supplier
table since it is similar to the one that loads the
Customer
table
just described.
Fig. 8.24
Load of the
Territories
bridge table
Figure
8.24
depicts the process for loading the
Territories
bridge table. The
input table is the following SQL query:
SELECT E.*, TerritoryDescription
Search WWH ::
Custom Search