For Actionable Information
Logical Data Model for Retail Banking September 6, 2007
1/25
For Actionable Information
CONFIDENTIALITY STATEMENT
The material contained in this document represents proprietary and confidential information pertaining to SIPL. By accepting this response, Client Client hereby agrees that the information in this response shall not be disclosed outside of Client and shall not be duplicated, used or disclosed for any purpose other than to evaluate this response. If, however, a contract contract is awarded to SIPL for this response response as a result of, or in conjunction with, the submission of this information, Client will have the right to duplicate, use or disclose the material contained herein to the extent provided for in the resulting contract.
2/25
For Actionable Information
TABLE OF CONTENTS
1
Executive Summary .................................................... ...................................................... .....4
2
Business Architecture.................................................. ...................................................... .....5
3
BI-DW System Architecture ....................................................................................................6
4
Data Architecture ................................................... ..................................................... .......... 7 4.1 4.2 4.3 4.4
Star Schema...................................................... ...................................................... .....8 Dimensions ................................................................................................................ 16 Measures ...................................................................................................................20 Bus Matrix ..................................................... ..................................................... ........ 24
3/25
For Actionable Information 1
Executive Summary
C-BIA hereby presents Logical Data Model (LDM) for Retail Bank. A brief overview of Bank Business and need for Business Intelligence and Data Warehouse are identified in the Business Architecture. BI-DW System Architecture lists the features of LDM and the components of data architecture namely
Star Schemas
Dimensions
Measures
Bus Matrix
Data Architecture contains the star schema for each subject. Dimensions & Facts in the Star Schema are detailed out with the respective attributes and measures. A Bus matrix identifying the common dimensions to be conformed across subjects is also detailed.
4/25
For Actionable Information
Project
Retail Bank
Assembly
Data Warehouse
Component
Logical Data Model (LDM) Design
2
Business Architecture
Overview of Business
A retail bank is a commercial institution with several branches across countrie s. It provides financial services, including issuing money in the for m of coins, banknotes or debit cards, receiving deposits of money, lending money and processing transactions. A retail bank accepts deposits from customers and in turn makes loans based on those deposits. Some banks (called Banks of issue) issue banknotes as legal tender. Many retail banks offer ancillary financial services to make additional profit; for example: selling insurance products, investment products or stock broking. A retail bank generates a profit from the differential betwe en what level of interest it pays for deposits and other sources of funds, and what level of inte rest it charges in its lending activities. This difference is referred to as the spread between the cost of funds and the loan interest rate. Services typically offered by banks
Although the basic type of services offered by a retail bank depe nds upon the type of bank and the country, services provided usually include:
Taking deposits from their customers and issuing current and savings accounts to individuals and businesses
Extending loans to individuals and businesses
Cashing cheques
Facilitating money transactions such as wire transfers and cashiers checks
Issuing credit cards, ATM cards, and debit cards
Storing valuables, particularly in a safe deposit box
Cashing and distributing bank rolls
Consumer & commercial financial advisory services
Need for Business Intelligence and Data Warehouse
Summary information to assess performance v/s target and benchmark
Discover low performing and high performing areas
Discover causes of low / high performance
Bringing data together for a single point of truth.
Make better decisions with timely reports and increased collaboration.
Real-time analysis of information on integrated systems.
Get instantaneous answers to those "what if" questions
Deploy analytical techniques – correlation, pareto analysis, trend analysis, distribution analysis
5/25
For Actionable Information
Project
Retail Bank
Assembly
Data Warehouse
Component
Logical Data Model (LDM) Design
3
BI-DW System Architecture
Features of logical data model include: o
All entities and relationships among them
o
All attributes for each entity
o
The primary key for each entity
o
Foreign keys (keys identifying the relationship between different entities)
Data architecture o
Star/ Snowflake Schemas
o
Dimensions
o
Measures
o
Bus Matrix
6/25
For Actionable Information
Project
Retail Bank
Assembly
Data Warehouse
Component
Logical Data Model (LDM) Design
4
Data Architecture
Data Architecture for the following subjects re lated to the operations of a Bank are defined: o
Customer Profile
o
Deposits
o
Loan Accounts
o
Interest Income
o
Corporate Services
o
Treasury
o
Expenses
o
Profitability
o
Asset Liability Management
o
Human Resource
o
Credit Card
o
ATM
7/25
For Actionable Information 4.1
Star Schema
Customer Profile
8/25
For Actionable Information
Deposits
9/25
For Actionable Information
Loan Accounts
10/25
For Actionable Information
Corporate Services
Treasury
11/25
For Actionable Information
Expenses
Profitability
12/25
For Actionable Information
Asset Liability Management
Human Resources
13/25
For Actionable Information
Credit Card
14/25
For Actionable Information ATM
15/25
For Actionable Information 4.2
Dimensions
Customer Primary Key: Customer_Key Dimension
Attribute
Hierarchy
Customer
Customer Type
Country
Customer Relationship Number
State City
Household Name
ZIP Code
Customer Name
Customer Type
Address
Household Name
City State
CR Nu m be r
ZIP Contact Details Age Group Month Since Account Opened Month Since Account Closed Annual Income Marital Status Gender Education Occupation Credit Rating
Location Primary Key: Location_Key Dimension
Attribute
Hierarchy
Location
Country
Country
State City name ZIP Code Region Zone Branch Type
State City ZIP Code Region Zone Branch
16/25
For Actionable Information Type
Branch Name
Service Primary Key: Service_Key Dimension
Attribute
Hierarchy
Service
Type
Type
Category
Category
Subcategory
Subcategory
Description
Description
Accounts attributes… Loan Attributes… Deposit attributes… …many service specific attributes
Time Primary Key: Time_Key Dimension
Attribute
Hierarchy
Time
Fiscal Year
Fiscal Year
Year
Year
Quarter
Quarter
Month
Month
Week of month
Week of month
Day
Day
Hour
Hour
Deposit Primary Key: Deposit_Key Dimension
Attribute
Hierarchy
Deposits
Deposit Type
Deposit Type
Deposit Name
Deposit Name
Maturity Period Interest Rate Renewal Type Penalty
17/25
For Actionable Information
Loans Primary Key: Loans_Key Dimension
Attribute
Hierarchy
Loan
Loan Type
Loan Type
Loan Name
Loan Name
Loan Term Interest Rate Pre-Payment Penalty Repayment Periodicity Total Duration
Corporate Service Primary Key: CorpService_Key Dimension
Attribute
Hierarchy
Corporate Service
Service Type
Service Type
Service Name
Service Name
Service Period Other Service Specific attributes…
Treasury Primary Key: Investment_Key Dimension
Attribute
Hierarchy
Investment
Investment Type
Investment Type
Investment Name
Investment Name
Investment Period Interest Rate
18/25
For Actionable Information
Expenses Primary Key: Expense_Key Dimension
Attribute
Hierarchy
Expense
Expense Head
Expense Head
Expense Type
Expense Type
Expense Name
Expense Name
Asset Liability management Primary Key: Asset_Liability_Key Dimension
Attribute
Hierarchy
ALM
Asset Liability type
Asset Liability Type
Asset Liability name
Asset Liability Name
Asset Liability indicator ALM specific attributes…
Credit Card Primary Key: creditcard_Key Dimension
Attribute
Hierarchy
Credit Card
Credit Card Type
Credit Card Type
Credit Card No.
Credit Card No.
Issue Date Expiry Date Billing Cycle Loyalty Points Interest Rate Credit Withdrawal Limit Cash Withdrawal Limit
19/25
For Actionable Information 4.3
Measures
Customer Profile Primary Key: custprofilefact_key Foreign Keys: Customer_key, Location_key, Time_key, service_key, status_key, acno_key Measure
Calculated Measures
Customer Count
Customer Turnover
Closing Balance
Average Revenue per customer
Interest Payable
No. of new customers acquired
No. of withdrawals
Avg. No. of Transactions per customer
No. of deposits
No. of Inactive Customers
No. of Transactions
% Loan to Deposits
Total Deposit amount
Customer profitability
Total Withdrawal amount Total loan amount
Deposits Primary Key: depositfact_key Foreign Keys: Customer_key, Location_key, Time_key, deposit_key, status_key, acno_key Measure
Calculated Measures
No. of Deposits
No. of Accounts
Interest Rate
No. of Active Accounts
Deposit Amount – opening balance
Avg. period of deposits
Deposit Amount – closing balance
No. of Fixed Deposits
Transaction cost
Total Deposit
Transaction charges levied
Fixed deposits % to total deposits Monthly average deposit Avg. deposit per branch
20/25
For Actionable Information
Loans & Interest Amount Primary Key: loanfact_key Foreign Keys: Customer_key, Location_key, Time_key, loantype_key, status_key, acno_key Measure
Calculated Measures
Loan Amount
No. of Loan Accounts
Interest Amount
Average loan amount per branch (Rs. Lakhs)
Recovered Amount
Current Recovery Rate per Branch %
Loan Amount Disbursed
No. of Non-Performing Assets
No. of loans Disbursed
No. of Installments Due
Interest Accrued
% Non-Performing Assets
Total Repayment
No. of Overdue Installments
Overdue Amount Current Balance No. of Paid Installments
Corporate Services Primary Key: CorpServicefact_key Foreign Keys: Customer_key, Location_key, Time_key, servicetype_key Measure
Calculated Measures
Service Charge
Avg yield per service
No. of Transactions
Avg. revenue per Corporate customer
Transaction Value
No. of New customers
Total Debits
No. of transactions per customer
Total Credits
No. of transactions per service type
Overdraft Limit
% Overdraft limit utilised
Overdraft utilised
Treasury Primary Key: Treasuryfact_key Foreign Keys: Time_key, Investment_key Measure
Calculated Measures
Investment Amount
Amount invested in market instruments (Rs. In Lakhs)
Income earned Period of Investment
% Return on Investments % Foreign currency earnings
21/25
For Actionable Information Foreign Currency earnings
Expense Primary Key: Expensefact_key Foreign Keys: Account_key, Location_key, Time_key, Expense_key Measure
Calculated Measures
Variable Overheads
Total Overheads (Rs. In Lakhs)
Fixed Overheads
% Fixed Overheads
Expense Amount
Asset Liability Management Primary Key: ALM _key Foreign Keys: Location_key, Time_key, Measure
Calculated Measures
Credit Amount
Credit ratio
Investment Amount
Market risk
Loan Amount
Liquidity risk
Total Capital
Interest rate risk
Total Assets
Risk-adjusted-return-on-capital (RAROC)
Total Liability
Funds Transfer pricing
Credit risk
Assets % of liabilities
Maturity gap Duration gap
Profitability Primary Key: profitabilityfact_key Foreign Keys: Account_key, Location_key, Time_key, Profitability_key Measure
Calculated Measures
No. of Customers
Total Expenses
No. of Accounts
Total Income
Total Assets
Return on Equity %
Total Equity
Investment % of Assets
Business Income
Expense % of Income
Investment Income
Avg. expense per customer or /account
Interest given on deposits
Gross Profit
22/25
For Actionable Information Fixed Expenses Var. Expenses
HR Primary Key: HRfact_key Foreign Keys: Service _key, Location_key, Time_key, Employee_Key Measure
Calculated Measures
No of Customers
% Transaction to total transactions in branch
No of transactions
% Transaction to total customers in branch
Total value of transactions
Revenue % to Total Revenue
Cost to Company (CTC)
Revenue % to Employee Cost
ATM Primary Key: ATMfact_key Foreign Keys: Customer_key, Location_key, Time_key Measure
Calculated Measures
ATM Withdrawals
Avg. transaction value
ATM Deposits
% deposits to withdrawals
Transaction Charge
Avg. no. of transactions
No. of ATM Withdrawals No. of ATM Deposits
Credit Card Primary Key: creditcardfact_key Foreign Keys: Customer_key, Location_key, Time_key, creditcard_Key Measure
Calculated Measures
Credit Card Withdrawals
Avg. transaction value
Credit Card transaction value
% deposits to withdrawals
No. of cash Withdrawals
Avg. no. of transactions
No. of transactions
Avg. credit value
Interest Amount
% overdue to total amount
Finance Charge
No. of defaulters
Total Due Amount
Non Performing Assets (NPA)
Minimum Due Amount
23/25
For Actionable Information Paid amount
4.4
Bus Matrix
Subjects
r e e l i f m o o r t P s u C
s t i s o p e D
s n a o L
t e s e m r o e c t n n I I
e s t e a c r i o v p r r e S o C
y r u s a e r T
s e s n e p x E
Dimensions Time
√
√
√
√
√
Customer
√
√
√
√
√
Location
√
√
√
√
√
Service
√ √
Employee Credit Card
√
√
√
√
√
d r a C t i d e r C
M T A
√
√
√
√
√
√
√
√ √ √
Expenses
Asset Liability Mgt.
√
√
R H
√
Investment
Corporate Services
√
M L A
√
Loans Deposits
√
y t i l i b a t i f o r P
√
√
√ √
24/25
For Actionable Information
Syscon Infotech Pvt. Ltd.
www.sysconinfotech.com © September 2007 SIPL. All other trademarks and logos appearing in this document are the property of their respective owners.
25/25
