Patterns: Information Aggregation and Data Integration with DB2 Information Integrator

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

"APPLY_EXCESS_TO" CHAR(1) ,

"TRANSACTION_TS" TIMESTAMP NOT NULL )

DATA CAPTURE CHANGES

IN "USERSPACE1" ;

COMMENT ON COLUMN "DB2LOAN "."TRANSACTION"."AMOUNT" IS 'AMOUNT OF

PAYMENT/CHARGE';

COMMENT ON COLUMN "DB2LOAN "."TRANSACTION"."APPLY_EXCESS_TO" IS 'APPLY EXCESS

PAYMENT TO P=PRINCIPLE OR I=INTEREST';

COMMENT ON COLUMN "DB2LOAN "."TRANSACTION"."CHANNEL" IS 'WHERE TRANSACTION

OCCURED E.G. MAIL, BRANCH OFFICE';

COMMENT ON COLUMN "DB2LOAN "."TRANSACTION"."LOAN_ID" IS 'ACCOUNT FOR

TRANSACTION';

COMMENT ON COLUMN "DB2LOAN "."TRANSACTION"."PAYMENT_FORM" IS 'CHECK, CASH,

DEBIT ACCOUNT';

COMMENT ON COLUMN "DB2LOAN "."TRANSACTION"."TRAN_DATE" IS 'TRANSACTION DATE';

COMMENT ON COLUMN "DB2LOAN "."TRANSACTION"."TYPE_OF_TRANS" IS 'PAYMENT,

INTEREST CHARGE, LATE FEE';

-- DDL Statements for primary key on Table "DB2LOAN "."TRANSACTION"

ALTER TABLE "DB2LOAN "."TRANSACTION"

ADD CONSTRAINT "TRANSACTION_PK" PRIMARY KEY

("LOAN_ID",

"TRANSACTION_TS");

We loaded some representative data in these tables, which is not included here.

Note that the data in operational systems is maintained by its business

transactions.

Data warehouse details

The data warehouse contains transaction history from each operational system,

as well as maintains daily and monthly summaries of transactions for some of the

operational systems when appropriate. The data warehouse has a latency of

end-of-business-day, and is therefore updated from the operational systems on a

scheduled basis corresponding to the end-of-business-day.

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

In our DFC Customer Insight solution, the relationship information between the

various customer accounts in the different operational systems is maintained in

the CIF. Therefore, the data warehouse tables corresponding to each operational

system are independent of each other and can be loaded/updated in parallel.

Figure C-9 on page 258 shows the data model of the data warehouse.

Figure C-9 Data warehouse data model

Example C-6 shows the DDL used to create the DB2 UDB for Multiplatforms data

warehouse tables shown in Figure C-9.

Example: C-6 Data warehouse DDL

CREATE TABLE "PATRNSDW"."CRDT_TRANS_HIS" (

"CC_NBR" BIGINT NOT NULL ,

"TRANSACTION_TS" TIMESTAMP NOT NULL ,

"CARD_HOLDER_ID" INTEGER NOT NULL ,

"TYPE" CHAR(5) NOT NULL ,

"VENDOR" CHAR(10) NOT NULL ,

"AMOUNT" DECIMAL(15,3) NOT NULL ,

"REFERENCE" CHAR(20) NOT NULL ,

"CATEGORY" CHAR(15) NOT NULL ,

"CREATED_BY" CHAR(15) NOT NULL )

IN "USERSPACE1" ;

-- DDL Statements for primary key on Table "PATRNSDW"."CRDT_TRANS_HIS"

ALTER TABLE "PATRNSDW"."CRDT_TRANS_HIS"

ADD CONSTRAINT "CC1078958022705" PRIMARY KEY

("CC_NBR",

"CARD_HOLDER_ID",

"TRANSACTION_TS");

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

CREATE TABLE "PATRNSDW"."CHCK_SAV_TRANS_HIS" (

"TRANSACTION_TS" TIMESTAMP NOT NULL ,

"MASTER_ACCOUNT_NUMBER" BIGINT NOT NULL ,

"MEMBER_ACCOUNT_NUMBER" BIGINT NOT NULL ,

"TYPE" CHAR(5) NOT NULL ,

"VENDOR" CHAR(10) NOT NULL ,

BROKER

LOANSCREDIT

CHK/SVG

TRANS_HIS

TRANS_HIS

TRANS_HIS

TRAN_HIS

DAILY_SUM

MNTH_SUM

DAILY_SUM

MNTH_SUM

MNTH_SUM

DAILY_SUM

MNTH_SUM

REWARDS

TRANS_HIS

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

"LOCATION" CHAR(20) NOT NULL ,

"CHECK_NO" BIGINT NOT NULL ,

"AMOUNT" DECIMAL(10,3) NOT NULL ,

"CHANNEL" CHAR(15) NOT NULL )

IN "USERSPACE1" ;

ALTER TABLE "PATRNSDW"."CHCK_SAV_TRANS_HIS"

ADD CONSTRAINT "CC9278958122705" PRIMARY KEY

("TRANSACTION_TS",

"MASTER_ACCOUNT_NUMBER",

"MEMBER_ACCOUNT_NUMBER");

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

CREATE TABLE "PATRNSDW"."LOAN_TRANS_HIS" (

"TRANSACTION_TS" TIMESTAMP NOT NULL ,

"LOAN_ID" INTEGER NOT NULL ,

"TRANSACTION_TYPE" CHAR(20) NOT NULL ,

"AMOUNT" DECIMAL(10,3) NOT NULL ,

"DATE" DATE NOT NULL ,

"CHANNEL" CHAR(10) NOT NULL ,

"FORM" CHAR(10) NOT NULL ,

"APPLY_EXCESS_PAY" CHAR(1) NOT NULL ,

"CREATED_BY" CHAR(15) NOT NULL )

IN "USERSPACE1" ;

ALTER TABLE "PATRNSDW"."LOAN_TRANS_HIS"

ADD CONSTRAINT "CC9678958122795" PRIMARY KEY

("TRANSACTION_TS",

"LOAN_ID");

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

CREATE TABLE "PATRNSDW"."CHKSVG_SUM_DAY" (

"ACCOUNT_NBR" INTEGER NOT NULL ,

"REPORTING_PERIOD DATE NOT NULL,

"MEMBER_NUMBER" INTEGER NOT NULL ,

"MASTER_ACCOUNT_NBR" INTEGER NOT NULL ,

"DEBIT_CARD_NBR" CHAR(16) ,

"ACCOUNT_TYPE" CHAR(10) NOT NULL ,

"ACCOUNT_STATUS" CHAR(1) NOT NULL ,

"ACCOUNT_CREATION_DATE" DATE NOT NULL ,

"PRODUCT_ID" INTEGER NOT NULL ,

"NUMBER_OF_DEBIT_TRANS" INTEGER NOT NULL,

"NUMBER_OF_DEPOSITS SMALLINT NOT NULL,

"LAST_TRANSACTION_TS TIMESTAMP,

"TOTAL_MONEY_IN_DEPOSITS DECIMAL(15,2),

"TOTAL_MONEY_IN_WITHDRAWALS DECIMAL(15,2))

IN "USERSPACE1";

-- DDL Statements for primary key on Table "PATRNSDW"."CHKSVG_SUM_DAY"

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

ALTER TABLE "PATRNSDW"."CHKSVG_SUM_DAY"

ADD PRIMARY KEY

("ACCOUNT_NBR",

"REPORTING_PERIOD");

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

CREATE TABLE "PATRNSDW"."CHKSVG_SUM_MONTH" (

"ACCOUNT_NBR" INTEGER NOT NULL ,

"REPORTING_PERIOD DATE NOT NULL,

"MEMBER_NUMBER" INTEGER NOT NULL ,

"MASTER_ACCOUNT_NBR" INTEGER NOT NULL ,

"DEBIT_CARD_NBR" CHAR(16) ,

"ACCOUNT_TYPE" CHAR(10) NOT NULL ,

"ACCOUNT_STATUS" CHAR(1) NOT NULL ,

"ACCOUNT_CREATION_DATE" DATE NOT NULL ,

"PRODUCT_ID" INTEGER NOT NULL ,

"NUMBER_OF_DEBIT_TRANS" INTEGER NOT NULL,

“NUMBER_OF_DEPOSITS SMALLINT NOT NULL,

"LAST_TRANSACTION_TS TIMESTAMP,

"TOTAL_MONEY_IN_DEPOSITS DECIMAL(15,2),

"TOTAL_MONEY_IN_WITHDRAWALS DECIMAL(15,2))

IN "USERSPACE1";

-- DDL Statements for primary key on Table "PATRNSDW"."CHKSVG_SUM_MONTH"

ALTER TABLE "PATRNSDW"."CHKSVG_SUM_MONTH"

ADD PRIMARY KEY

("ACCOUNT_NBR",

"REPORTING_PERIOD");

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

CREATE TABLE "PATRNSDW"."BRKRG_SUM_DAY" (

"ACCOUNT_ID" INTEGER NOT NULL ,

"REPORTING_PERIOD" DATE NOT NULL ,

"OWNER_ID" INTEGER NOT NULL ,

"LAST_TRANSACTION_TS" TIMESTAMP NOT NULL ,

"BUYING_POWER" DECIMAL(15,3) NOT NULL ,

"CURRENT_ACCOUNT_WORTH" DECIMAL(15,3) NOT NULL ,

"UNITS_SOLD" DECIMAL(15,3) NOT NULL ,

"UNITS_BOUGHT" DECIMAL(15,3) NOT NULL ,

"TOTAL_NUMBER_OF_TRANS" SMALLINT NOT NULL ,

"TOTAL_TRANSACTION_FEE_PAID" DECIMAL(10,1) NOT NULL )

IN "USERSPACE1" ;

-- DDL Statements for primary key on Table "PATRNSDW"."BRKRG_SUM_DAY"

ALTER TABLE "PATRNSDW"."BRKRG_SUM_DAY"

ADD PRIMARY KEY

("ACCOUNT_ID",

"REPORTING_PERIOD");