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 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 is awarded to SIPL for this 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 Star Schema.................................................................................................................8
4.2 Dimensions ................................................................................................................ 16
4.3 Measures ................................................................................................................... 20
4.4 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 countries. It provides financial
services, including issuing money in the form 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 between what level of interest it pays for deposits and
other sources of funds, and what level of interest 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 depends 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 related 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 State
Relationship Number
City
Household Name
ZIP Code
Customer Name
Customer Type
Address
Household
City Name
State CR
Nu
ZIP m
Contact Details be
r
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 State
City name City
ZIP Code ZIP Code
Region Region
Zone Zone
Branch Type Branch
16/25
For Actionable Information
Branch Name Type
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
% Return on Investments
Period of Investment
% 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
Customer
HR
Credit Card
Deposits
Loans
Services
Expenses
Profitability
Profile
Interest
Corporate
Treasury
Income
ALM
ATM
Subjects
Dimensions
Time √ √ √ √ √ √ √ √ √ √ √ √
Customer √ √ √ √ √ √ √
Location √ √ √ √ √ √ √ √ √ √ √
Service √ √ √
Loans √ √
Deposits √
Investment √
Expenses √
Corporate √
Services
Asset √
Liability
Mgt.
Employee √
Credit Card √
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
