Database Reference
In-Depth Information
Fig. 8.32
Load of the
City
dimension table
In the first case, a lookup is needed for obtaining the
CountryKey
. This will
obtain, for example, the country key for Singapore, which has no states. In the
second case, as explained in Sect.
8.3
, three lookup tasks are needed, where
a couple of values of
State
and
Country
in
TempCities
must be matched with
either
StateName
and
CountryName
,
EnglishStateName
and
CountryName
,or
StateName
and
CountryCode
. Since this process is similar to the one in the
conceptual design, we do not repeat it here. Finally, a union of the four flows
is performed (note that this task is named
Union All 1
since there cannot
exist two tasks with the same name), and the
City
table is loaded.
The task that loads the
Customer
table is shown in Fig.
8.33
, while its
conceptual schema is given in Fig.
8.23
. It starts with a conditional split since
some customers have a null value in
Region
(this attribute in fact corresponds
to states). In this case, a lookup adds a column
State
by matching
City
and
Country
from
Customers
with
City
and
Country
from
TempCities
. Notice that
the value
State
just obtained may be null for countries without states, and
thus another conditional split (called
Conditional Split 1
) is needed. If
State
is null, then a lookup tries to find a
CityKey
by means of matching values of
City
and
Country
. The SQL query of the lookup task, the same as in Sect.
8.3
,
is as follows:
SELECT CityKey, CityName, CountryName
FROM City C JOIN Country T ON C.CountryKey = T.CountryKey
On the other hand, for customers that have a nonnull
Region
,thevaluesof
this column are copied into a new column
State
. Analogously to the loading
of
City
, we must perform five lookup tasks in order to retrieve the city key.
Since this process is analogous to the one in the conceptual design given in
Sect.
8.3
, we do not repeat the details here. Finally, we perform the union of
Search WWH ::
Custom Search