Chapter 2. Financial services business scenario 435
Baseline Analysis to determine whether any significant changes have
occurred to the structure and content of the source from the time the data
profiling effort commenced to just prior to the execution of the plan to migrate
the data sources to the target.
2.5.3 IBM WebSphere AuditStage features used
We will be using IBM WebSphere AuditStage’s Data Filters functionality to
perform business rule compliance for data elements within the same table and
across multiple tables in the source.
2.5.4 North American Bank analysis
As mentioned earlier, North American Bank’s core services are to be migrated to
those of the Northern California Bank.
Using metadata information available in dictionaries, documentation and the
relational catalogs, the keys, code fields, indicator fields, and referential integrity
relationships (both implicit and explicit) to be profiled were identified as shown in
Table 2- 3.
We include only selected portions of the generated reports (for some of the fields
shown in Table 2-3) of the data profiling effort in this section. You can download
the complete reports from the IBM Redbooks Web site:
Business rules involving data elements in one or more tables are shown in
Table 2-4. These are verified using IBM WebSphere AuditStage.
Note: For convenience, we chose to include all the columns in all the tables in
our data profiling effort. In the real world, however, to avoid report and
information overload, you would apply the 80/20 rule and focus on only the
critical master data as described in 1.2.1, “Data assessment approach” on
Note: We describe the process of generating reports in 1.14, “Reports” on
page 394 and do not repeat that information here. We show only the relevant
portions of reports here.
436 IBM WebSphere Information Analyzer and Data Quality Assessment
Table 2-3 Main keys, codes, indicators in North American Bank core services
Table 2-4 Business rules in North American Bank core services
Column Analysis reports
These reports provide information about column values for completeness,
validity, structure, and format as described in 1.7, “Column analysis” on
page 109. These reports are used to review the columns for domain, structure
and format integrity.
Figure 2-6 on page 437 shows the Column Inferences “Type & Length Properties
(Defined/Inferred/Chosen)” report for the CUSTOMER table. It identifies the data
type, length, precision and scale of each column as defined in the metadata and
inferred from the data content. Any selected status of each of these categories is
also shown here. This information (along with those relating to other columns in
Table Key column(s) Code column Indicator column RI relationships
CUSTOMER CUSTOMER_ID TITLE
LEVEL_CD to LEVEL_REF table
CONTACT_INFO CUSTOMER_ID, ACCOUNT_ID HOME_ZIP
CUSTOMER_ID to CUSTOMER table
ACCOUNT ACCOUNT_ID ACTIVE_IND
ACCOUNT_ID to ACCOUNT table
LOAN ACCOUNT_ID, LOAN_ID AUTOMAT_DEBIT_IND ACCOUNT_ID to ACCOUNT table
LOAN_TRANSACTION ACCOUNT_ID, LOAN_ID,
TRANS_TYPE_ID AUTOMAT_DEBIT_IND ACCOUNT_ID to ACCOUNT table
TRANS_TYPE_CD ACCOUNT_ID to ACCOUNT table
BRANCH BRANCH_ID WORK_ZIP
CUST_ACC CUSTOMER_ID, ACCOUNT_ID CUSTOMER_ID to CUSTOMER
ACCOUNT_ID to ACCOUNT table
TRANSACTION_TYPE_REF TRANS_TYPE_CD TRANS_TYPE_CD
Serial number Description
1 An account (in the ACCOUNT table) can only have a transaction if
the ACTIVE_IND column is set to “Y”
2 The OVERDRAFT column value cannot exceed the
OVERDRAFT_LIMIT column value in the ACCOUNT table
3 When the OVERDRAFT column has a value, the
OVERDRAFT_RATE and OVERDRAFT_FEE columns in the
ACCOUNT table must be filled
Chapter 2. Financial services business scenario 437
other tables) is recorded in Table 2-13 on page 475. Our focus is on keys, codes,
and indicators as highlighted.
Figure 2-7 on page 438 shows the Column Frequency “Frequency By
Frequency” report for the GENDER_IND column in the CUSTOMER table. It
shows some unexpected values such as spaces, in addition to the valid values
M, F, U, and NULL.
Figure 2-8 on page 439 shows the Column Domain Values “Completeness and
Validity Summary” report for the CUSTOMER table. It shows 41 occurrences of
incomplete (NULL) values, and 41 occurrences of invalid values in the
Document all the valid and invalid values for a column in a table as shown in
Table 2-5 on page 439.
Figure 2-6 Column Inferences Type & Length Properties (Defined/Inferred/Chosen) report for the
438 IBM WebSphere Information Analyzer and Data Quality Assessment
Figure 2-7 Column Frequency “Frequency By Frequency” report for the GENDER_IND column in the