Extraction, Transformation, and Loading - Data Warehouse Systems: Design and Implementation - page 310

Container is used for the three data flows that load the tables of the hierarchy.
Since Continent is the highest level in the hierarchy, we first need to produce
a key for it, so it can be later referenced from the Country level. The data
flow for loading the table Continent is given in Fig. 8.30 b. With respect to the
conceptual model given in Fig. 8.21 b, a data conversion is needed to convert
the data types from the XML file into the data types of the database. This is
shown in Fig. 8.31 ,wherethe ContinentName , read from the XML file, is by
default of length 255, and it is converted into a string of length 20. Finally,
the Continent table is loaded, and a ContinentKey is automatically generated.
Fig. 8.31 Conversion of the data types that are input from the XML file
The data flow that loads the table Country is given in Fig. 8.30 c. With
respect to the conceptual model given in Fig. 8.21 b, a merge join transforma-
tion is needed to obtain for a given Country the corresponding ContinentName .
A data conversion transformation is needed to convert the data types from the
XML file into the data types of the database. Then, a lookup transformation
is needed to obtain, from the database, the ContinentKey corresponding to the
ContinentName . This attribute is also added to the flow. Finally, the Country
table is loaded analogously to the Continent table above. Notice that the data
flow that loads the table State is similar; therefore, we omit it.
Figure 8.32 shows the data flow for loading the City table. The conceptual
model for the data flow has been presented in Fig. 8.22 . The data flow
needs to associate to each city in the TempCities table either a StateKey
or a CountryKey , depending on whether or not the corresponding country is
divided in states. For this, the conditional split tests if the State is null or not.