Patterns: Information Aggregation and Data Integration with DB2 Information Integrator

266 Patterns: Information Aggregation and Data Integration with DB2 Information Integrator

"REWARDID" CHAR(10) ,

"POINTBALANCE" DECIMAL(10,2) ,

"PROCESS_DATE" DATE ,

"CREATED_DATE" DATE ,

"CREATED_BY" CHAR(10) )

IN "USERSPACE1" ;

---------------------------------------------------------------------------

CREATE TABLE "DWPATRNS"."REWARD_JOURNAL" (

"RECORD_TYPE" CHAR(1) ,

"CHANGE_SEQ" SMALLINT ,

"CHANGE_TS" TIMESTAMP ,

"CHANGE_SOURCE" CHAR(1) ,

"INITIATOR" INTEGER ,

"CCNUM" CHAR(16) ,

"LAST_NAME" CHAR(30) ,

"FIRST_NAME" CHAR(30) ,

"PARTNER_ID_NUM" CHAR(12) ,

"REWARDID" CHAR(10) ,

"POINTBALANCE" DECIMAL(10,2) ,

"PROCESS_DATE" DATE ,

"CREATED_DATE" DATE ,

"CREATED_BY" CHAR(10) )

IN "USERSPACE1" ;

The data warehouse is both initially populated as well as incrementally updated

using the Ascential DataStage product. Each of these processes is described

briefly in the following sections.

Initial load of the data warehouse

This is a one-time effort to populate the data warehouse from the operational

systems.

Figure C-10 on page 267 through Figure C-17 on page 274 show

some of the

DataStage screens used to define the processes for performing this initial

population.

Note: As mentioned earlier, we chose to access all the operational systems’

data sources through the federated server using nicknames by Ascential

DataStage for both the one-time initial population of the data warehouse as

well as its recurring incremental updates.

Appendix C. Data models, table/file definitions, et al used in the DFC Customer Insight business solution

Figure C-10 DataStage Designer - Data warehouse tables

Figure C-10 shows the simple model for loading the four data warehouse tables

corresponding to the checkings/savings, credit card, loans and brokerage

operational systems. The rewards system load is not shown here. It shows a data

source (CREDIT, for example) being linked (named Extract_Credit) to a

transformer stage (named Transform_Credit_Data), which is then linked (named

Load_Credit_Data) to the target table (CREDIT_DW) for each system.

268 Patterns: Information Aggregation and Data Integration with DB2 Information Integrator

Figure C-11 DataStage Designer Extract ODBC Stage

Figure C-11 displays the contents of the Columns tab for the Extract_Credit link,

and identifies the data source columns in the Derivation field. The Column

name field lists the user-defined names of the extracted columns. The ODBC

driver is used to perform this extraction and is called the ODBC Stage.

Appendix C. Data models, table/file definitions, et al used in the DFC Customer Insight business solution

Figure C-12 DataStage Designer Transformer Stage

Figure C-12 describes the Transformer Stage, which includes the mapping

between the source columns (Extract_Credit link) and the target columns

(Load_Credit_Data link), and any transformations to be performed. Note the

simple data type transform in the SQL type of the CC_NBR column from CHAR

to BigInt.

270 Patterns: Information Aggregation and Data Integration with DB2 Information Integrator

Figure C-13 DataStage Designer Load_Credit_Data ODBC Stage

Figure C-13 shows the options for loading the target table from the input link

Load_Credit_Data. Ascential DataStage provides a number of options on how

the target is to be updated as shown under the Update action field. For the initial

load, the Insert rows without clearing was appropriate in our case.