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

Fig. 8.22 Load of the City dimension table
Finally, a union is performed with the results of the four flows, and the table
is populated with an insert data task. Note that in the City table, if a state
was not found in the initial lookup ( Input1 in Fig. 8.22 ), the attribute State
will be null; on the other hand, if a state was found, it means that the city will
have an associated state; therefore, the Country attribute will be null ( Inputs2 ,
Input3 ,and Input4 in the figure). Records for which the state and/or country
are not found are stored into a BadCities.txt file.
Figure 8.23 shows the conceptual ETL process for loading the Customer
table in the data warehouse. The input table Customers is read from the
operational database using an input data task. Recall that the Region
attribute in this table corresponds actually to a state name or a state code.
Since this attribute is optional, the first exclusive gateway checks whether this
attribute is null or not. If Region is null, a lookup checks if the corresponding
(City, Country) pair matches a pair in TempCities and retrieves the State
attribute from the latter, creating a new column. Since the value State just
obtained may be null for countries without states, another exclusive gateway
tests whether State is null, in which case a lookup obtains the CityKey by