Database Reference
In-Depth Information
a
b
Fig. 8.17
(
a
) Beginning of the file
Cities.txt
.(
b
) Associated table
TempCities
The control task that loads the tables composing the hierarchy
State
→
Country
Continent
is depicted in Fig.
8.21
a. As can be seen, this requires
a sequence of data tasks. Figure
8.21
b shows the data task that loads the
Continent
table. It reads the data from the XML file using the following
XPath expression:
→
<
Continents
>
/
<
Continent
>
/
<
ContinentName
>
Then, a new column is added to the flow in order to be able to generate the
surrogate key for the table in the data warehouse.
Figure
8.21
c shows the task that loads the
Country
table. It reads the data
from the XML file using the following XPath expressions:
<
Continents
>
/
<
Continent
>
/
<
Country
>
/*
<
Continents
>
/
<
Continent
>
/
<
ContinentName
>
In this case, we need to read from the XML file not only the attributes
of
Country
but also the
ContinentName
to which a country belongs. For
example, when reading the
Country
element corresponding to
Austria
,we
must also obtain the corresponding value of the element
ContinentName
,that
is,
Europe
. Thus, the flow is now composed of the attributes
CountryName
,
CountryCode
,
CountryCapital
,
Population
,
Subdivision
,
State
,and
Continent-
Name
(see Fig.
8.16
b). The
ContinentName
valueisthenusedinalookup
task for obtaining the corresponding
ContinentKey
from the data warehouse.
Finally, the data in the flow is loaded into the warehouse. We do not show
the task that loads the
State
table since it is similar to the one that loads the
Country
table just described.
The process that loads the
City
table is depicted in Fig.
8.22
. The first task
is an input data task over the table
TempCities
. Note that the final goal is
to populate a table with a state key and a country key, one of which is null
depending on the political division of the country, that is, on whether the
country is divided into states or not. Thus, the first exclusive gateway tests
whether
State
is null or not (recall that this is the optional attribute). In the
first case, a lookup obtains the
CountryKey
. In the second case, we must match
(State,Country)
pairs in
TempCities
to values in the
State
and
Country
tables.
However, as we have explained, states and countries can come in many forms;
Search WWH ::
Custom Search