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");

Get Patterns: Information Aggregation and Data Integration with DB2 Information Integrator now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.