Case Study: Customer Loan Data Warehouse Project
Step 11: Sequence the Physical DI Models
Once the data integration models have been converted into physical functional modules and are ready for final instantiation into source code, then all the data integration models should be reviewed for job sequencing and scheduling, as depicted in Figure 4.32.
Model Name: CL Data Integration Model Project: Customer Loan
Life Cycle Type: Physical
DI Architecture Layer: Common Component: Transformations
I. Source System Code Matching
Assign “001” to Source System Code if Customer Hub, “002” if Commercial Loan, “003” if Retail Loan
CUST.dat Subject Area File
LOAN.dat Subject Area File CUST.dat
Subject Area File
LOAN.dat Subject Area File
Figure 4.30 Physical transformation common components data integration model
ptg More details on the tasks and steps to making these data integration processes “production
ready” are reviewed in Chapter 7, “Data Integration Logical Design.”
Summary
This case study presents all the tasks and activities and techniques needed to build a scalable application and a foundation for a component-based data integration environment.
Although the case study is not at a large scale, for example of integrating 30 systems into an enterprise data warehouse, it does demonstrate what is needed to represent that level of integra-tion using a graphical diagramming approach.
It used three sources to demonstrate how to consolidate data into a single target using the subject area approach; it demonstrated how to apply the target-based design technique in moving data quality business rules to the sources and transformation business rules to the targeted sub-ject areas.
The next part of the book focuses on all the phases, tasks, activities, and deliverables in the data integration Systems Development Life Cycle.
Step 11: Sequence the Physical DI Models 95
Model Name: CL Data Integration Model Project: Customer Loan Life Cycle Type: Physical DI Architecture Layer: Common Component: Transformations
I. Source System Code Matching Assign “001” to Source System Code if Customer Hub, “002” if Commercial Loan, “003” if Retail Loan
CUST.dat Subject Area File
LOAN.dat Subject Area File CUST.dat
Subject Area File
LOAN.dat Subject Area File
Model Name: CL Data Integration Model Project: Customer Loan Life Cycle Type: Physical DI Architecture Layer: Common Component: Data Quality
Bad Transactions 0101 3443434 Missing Fields 0304 535355 Referential Integrity 0101 3443434 Missing Fields 0304 535355 Referential Integrity 1. Gender Check
Must be “Male,” “Female,”
or “Unknown”
Format Clean File
Format Reject File
Format Reject Report Error Handling
CUST.dat Subject Area File
LOAN.dat Subject Area File CUST.dat
Subject Area File
LOAN.dat Subject Area File
Load Loan Subject Area 1. Load Loans Table 2. Load Products Table Model Name: Loan Subject Area Load Data Integration Model Project: Customer Loan Life Cycle Type: Physical DI Architecture Layer: Load
Loans Table
Products Table LOAN.dat
Subject Area File
II. Transform Loan1. Conform Commercial Loan to the Loan Subject Area 2. Conform Retail LoanCustomer to the Customer Subject Area
Load Loan Subject Area 1. Load Loans Table 2. Load Products Table Model Name: Loan Subject Area Load Data Integration Model Project: Customer Loan Life Cycle Type: Physical DI Architecture Layer: Load
LoansTable
ProductsTable LOAN.dat
Subject Area File
II. Transform Loan 1. Conform Commercial Loanto the Loan Subject Area 2. Conform Retail Loan
Customer to the Customer Subject Area
Load Loan Subject Area 1. Load Loans Table 2. Load Products Table Model Name: Loan Subject Area Load Data Integration Model Project: Customer Loan Life Cycle Type: Physical DI Architecture Layer: Load
Loans Table
Products Table LOAN.dat
Subject Area File
II. Transform Loan1. Conform Commercial Loan to the Loan Subject Area 2. Conform Retail LoanCustomer to the Customer Subject Area Extract Header
& Detail from the Customer Hub
Verify the Header and Detail Extractwith the Control File Format into theCUST.dat
Subject AreaFile CustomerHub
Application Header Detail Model Name: Customer Physical Source System Extract Data Integration Model Project: Customer Loan Life Cycle Type: Physical DI Architecture Layer: Extract
Error 0101 3443434 Missing Fields 0304 535355 Referential Integrity 0101 3443434 Missing Fields 0304 535355 Referential Integrity Reject Report Reject File CUST.dat Subject Area File
Commercial 010 and COM 200 from the Commercial Loan System
Verify the COM010 and COM 200 Extractswith the Control File
Format COM010 into the CUST.dat Subject AreaFile
Format COM200 into the LOAN.dat Subject Area File Model Name: Commercial Loan Physical Source System Extract Data Integration Model Project: Customer Loan Life Cycle Type: Physical DI Architecture Layer: Extract
Error Handling Technical DQ Checks 1.Check Customers
Technical DQ Checks 3.Check Com Loans 4. Check Com Products
Bad Transactions 0101 3443434 Missing Fields 0304 535355 Referential Integrit 0101 3443434 Missing Fields 0304 535355 Referential Integrit Reject Report Reject File CUST.dat Subject Area File
Loan.dat Subject Area File
Extract RETL 010 and RETL020 from theRetail Loan System
Verify the RETL 010 and RETL 020 Extracts with the Control File
Format RETL 010 into theCUST.dat Subject AreaFile
Format RETL 020 into the LOAN.dat Subject AreaFile Model Name: Commercial Loan Physical Source System Extract Data Integration Model Project: Customer Loan Life Cycle Type: Physical DI Architecture Layer: Extract
Error Handling Technical DQ Checks 1.Check Customers
Technical DQ Checks 3.Check Com Loans 4. Check Com Products
Bad Transactions 0101 3443434 Missing Fields 0304 535355 Referential Integrit 0101 3443434 Missing Fields 0304 535355 Referential Integrit Reject Report Reject File CUST.dat Subject Area File
Loan.dat Subject Area File RETL 010
Figure 4.32 The physical data integration model job flow
ptg
ptg