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

Fig. 8.26 Overall view of the ETL process in Integration Services
Fig. 8.27 Load of the Category ( a )andthe Time ( b ) dimension tables
As explained in Sect. 8.3 , in some tables, the keys of the operational
database are reused as surrogate keys in the data warehouse, while in other
tables a surrogate key must be generated in the data warehouse. Therefore,
the mapping of columns in the OLE DB Destination tasks should be done
in one of the ways shown in Fig. 8.28 . For example, for the table Category
(Fig. 8.28 a), we reuse the key in the operational database ( CategoryID )as
key in the data warehouse ( CategoryKey ). On the other hand, for the table
Customer (Fig. 8.28 b), the CustomerID key in the operational database is
kept in the CustomerID column in the data warehouse, and a new value for
CustomerKey is generated during the insertion in the data warehouse.
Figure 8.29 shows the data flow used for loading the table TempCities from
the text file Cities.txt . A data conversion transformation is needed to convert
the default types obtained from the text file into the database types.
Figure 8.30 a shows the data flow that loads the hierarchy composed of
the Continent , Country ,and State tables. This is the Integration Services
equivalent to the conceptual control flow defined in Fig. 8.21 a. A Sequence