"Bank BI Blues"

We are a large Bank with product lines such as checking and savings accounts, credit cards, home mortgages, home equity, car loans, etc. We also have a central customer database called CUSTOMER. In order to be in compliance with government bank regulators, we must produce regular reports that show we are lending fairly.

The existing BI Solution pulls data directly from the demand deposit, customer and the loan databases into a set of Excel spreadsheets on a server owned by the Compliance Department. The queries are scheduled to run at night, so as to minimize performance impacts on the databases. The department then uses these spreadsheets during the day to prepare their reports.

The BI Solution was working fine for a long time, but now it is 'broken'. Recently, the regulators required us to add three new fields to CUSTOMER to track demographic information about loan applicants (veteran status, religion, public_assistance_status), as well as to keep a record of rejected loan applicants for up to seven years. The queries now sometimes do not finish overnight. When we add HOME_EQUITY we expect it to be even slower. The compliance team must schedule the jobs to run over weekends and holidays or take the systems offline during the day to get their work done. Taking the systems offline will likely lead to revenue loss. It is possible that the reports will be late - which may lead to regulator fines, or other regulator action.

Enterprise Architecture recommends using the SOA Middleware application to pull data from databases (i.e. replace the direct queries). SOA Middleware is faster and more robust than direct queries, has no performance impact on the transaction systems. It is also configured to comply with Bank information security standards. Note: SOA Middleware is NOT a database - it needs to move the data into a permanent data store.

Assets Available

Databases:

CUSTOMER (customerID, race, national_origin, age, sex, marital_status, handicap)

CHECKING (customerID, accountID, currentBalance)

SAVINGS (customerID, accountId, currentBalance)

CREDIT_CARD (customerID, cardNumber, currentBalance, minimumPayment)

MORTGAGE (customerID, loanID, currentBalance, monthlyPayment, assetValue)

CAR (customerID, loadID, currentBalance, monthlyPayment, assetValue)

HOME_EQUITY (customerID, currentBalance, monthlyPayment, assetValue)

Other Artifacts and Tools:

SOA_Middleware

Teradata data warehouse technology - schema and infrastructure

MS Excel (on desktops)

Database query language (e.g. SQL)

Set of queries to pull data from Checking, Savings, Credit_Card, Mortgage and Car.

Other Resources:

Compliance Reporting Staff (Power Users) who wrote the queries, and understand the requirements, and know how to prepare the reports.

Database Architect (with experience designing data marts)

MIddleware Programmers

Enterprise Architects

IT Infrastructure Teams

Propose a new BI Solution for the Bank's Compliance function. Be specific regarding how it will meet the department's requirements?

What is the BI Product that will be produced?

Describe the validation approach?