1logical data model - bank
TRANSCRIPT
For Actionable Information
1/25
Logical Data Model for Retail Banking
September 6, 2007
For Actionable Information
2/25
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.
For Actionable Information
3/25
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
For Actionable Information
4/25
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.
For Actionable Information
5/25
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
For Actionable Information
6/25
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
For Actionable Information
7/25
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
For Actionable Information
8/25
4.1 Star Schema
Customer Profile
For Actionable Information
9/25
Deposits
For Actionable Information
10/25
Loan Accounts
For Actionable Information
11/25
Corporate Services
Treasury
For Actionable Information
12/25
Expenses
Profitability
For Actionable Information
13/25
Asset Liability Management
Human Resources
For Actionable Information
14/25
Credit Card
For Actionable Information
15/25
ATM
For Actionable Information
16/25
4.2 Dimensions
Customer
Primary Key: Customer_Key
Dimension Attribute Hierarchy
Customer Customer Type
Customer Relationship Number
Household Name
Customer Name
Address
City
State
ZIP
Contact Details
Age Group
Month Since Account Opened
Month Since Account Closed
Annual Income
Marital Status
Gender
Education
Occupation
Credit Rating
Country
State
City
ZIP Code
Customer Type
Household Name
CR Number
Location
Primary Key: Location_Key
Dimension Attribute Hierarchy
Location Country
State
City name
ZIP Code
Region
Zone
Branch Type
Country
State
City
ZIP Code
Region
Zone
Branch
For Actionable Information
17/25
Branch Name Type
Service
Primary Key: Service_Key
Dimension Attribute Hierarchy
Service Type
Category
Subcategory
Description
Accounts attributes…
Loan Attributes…
Deposit attributes…
…many service specific attributes
Type
Category
Subcategory
Description
Time
Primary Key: Time_Key
Dimension Attribute Hierarchy
Time Fiscal Year
Year
Quarter
Month
Week of month
Day
Hour
Fiscal Year
Year
Quarter
Month
Week of month
Day
Hour
Deposit
Primary Key: Deposit_Key
Dimension Attribute Hierarchy
Deposits Deposit Type
Deposit Name
Maturity Period
Interest Rate
Renewal Type
Penalty
Deposit Type
Deposit Name
For Actionable Information
18/25
Loans
Primary Key: Loans_Key
Dimension Attribute Hierarchy
Loan Loan Type
Loan Name
Loan Term
Interest Rate
Pre-Payment Penalty
Repayment Periodicity
Total Duration
Loan Type
Loan Name
Corporate Service
Primary Key: CorpService_Key
Dimension Attribute Hierarchy
Corporate Service Service Type
Service Name
Service Period
Other Service Specific attributes…
Service Type
Service Name
Treasury
Primary Key: Investment_Key
Dimension Attribute Hierarchy
Investment Investment Type
Investment Name
Investment Period
Interest Rate
Investment Type
Investment Name
For Actionable Information
19/25
Expenses
Primary Key: Expense_Key
Dimension Attribute Hierarchy
Expense Expense Head
Expense Type
Expense Name
Expense Head
Expense Type
Expense Name
Asset Liability management
Primary Key: Asset_Liability_Key
Dimension Attribute Hierarchy
ALM Asset Liability type
Asset Liability name
Asset Liability indicator
ALM specific attributes…
Asset Liability Type
Asset Liability Name
Credit Card
Primary Key: creditcard_Key
Dimension Attribute Hierarchy
Credit Card Credit Card Type
Credit Card No.
Issue Date
Expiry Date
Billing Cycle
Loyalty Points
Interest Rate
Credit Withdrawal Limit
Cash Withdrawal Limit
Credit Card Type
Credit Card No.
For Actionable Information
20/25
4.3 Measures
Customer Profile
Primary Key: custprofilefact_key
Foreign Keys: Customer_key, Location_key, Time_key, service_key, status_key, acno_key
Measure
Customer Count
Closing Balance
Interest Payable
No. of withdrawals
No. of deposits
No. of Transactions
Total Deposit amount
Total Withdrawal amount
Total loan amount
Calculated Measures
Customer Turnover
Average Revenue per customer
No. of new customers acquired
Avg. No. of Transactions per customer
No. of Inactive Customers
% Loan to Deposits
Customer profitability
Deposits
Primary Key: depositfact_key
Foreign Keys: Customer_key, Location_key, Time_key, deposit_key, status_key, acno_key
Measure
No. of Deposits
Interest Rate
Deposit Amount – opening balance
Deposit Amount – closing balance
Transaction cost
Transaction charges levied
Calculated Measures
No. of Accounts
No. of Active Accounts
Avg. period of deposits
No. of Fixed Deposits
Total Deposit
Fixed deposits % to total deposits
Monthly average deposit
Avg. deposit per branch
For Actionable Information
21/25
Loans & Interest Amount
Primary Key: loanfact_key
Foreign Keys: Customer_key, Location_key, Time_key, loantype_key, status_key, acno_key
Measure
Loan Amount
Interest Amount
Recovered Amount
Loan Amount Disbursed
No. of loans Disbursed
Interest Accrued
Total Repayment
Overdue Amount
Current Balance
No. of Paid Installments
Calculated Measures
No. of Loan Accounts
Average loan amount per branch (Rs. Lakhs)
Current Recovery Rate per Branch %
No. of Non-Performing Assets
No. of Installments Due
% Non-Performing Assets
No. of Overdue Installments
Corporate Services
Primary Key: CorpServicefact_key
Foreign Keys: Customer_key, Location_key, Time_key, servicetype_key
Measure
Service Charge
No. of Transactions
Transaction Value
Total Debits
Total Credits
Overdraft Limit
Overdraft utilised
Calculated Measures
Avg yield per service
Avg. revenue per Corporate customer
No. of New customers
No. of transactions per customer
No. of transactions per service type
% Overdraft limit utilised
Treasury
Primary Key: Treasuryfact_key
Foreign Keys: Time_key, Investment_key
Measure
Investment Amount
Income earned
Period of Investment
Calculated Measures
Amount invested in market instruments (Rs. In Lakhs)
% Return on Investments
% Foreign currency earnings
For Actionable Information
22/25
Foreign Currency earnings
Expense
Primary Key: Expensefact_key
Foreign Keys: Account_key, Location_key, Time_key, Expense_key
Measure
Variable Overheads
Fixed Overheads
Expense Amount
Calculated Measures
Total Overheads (Rs. In Lakhs)
% Fixed Overheads
Asset Liability Management
Primary Key: ALM_key
Foreign Keys: Location_key, Time_key,
Measure
Credit Amount
Investment Amount
Loan Amount
Total Capital
Total Assets
Total Liability
Credit risk
Maturity gap
Duration gap
Calculated Measures
Credit ratio
Market risk
Liquidity risk
Interest rate risk
Risk-adjusted-return-on-capital (RAROC)
Funds Transfer pricing
Assets % of liabilities
Profitability
Primary Key: profitabilityfact_key
Foreign Keys: Account_key, Location_key, Time_key, Profitability_key
Measure
No. of Customers
No. of Accounts
Total Assets
Total Equity
Business Income
Investment Income
Interest given on deposits
Calculated Measures
Total Expenses
Total Income
Return on Equity %
Investment % of Assets
Expense % of Income
Avg. expense per customer or /account
Gross Profit
For Actionable Information
23/25
Fixed Expenses
Var. Expenses
HR
Primary Key: HRfact_key
Foreign Keys: Service_key, Location_key, Time_key, Employee_Key
Measure
No of Customers
No of transactions
Total value of transactions
Cost to Company (CTC)
Calculated Measures
% Transaction to total transactions in branch
% Transaction to total customers in branch
Revenue % to Total Revenue
Revenue % to Employee Cost
ATM
Primary Key: ATMfact_key
Foreign Keys: Customer_key, Location_key, Time_key
Measure
ATM Withdrawals
ATM Deposits
Transaction Charge
No. of ATM Withdrawals
No. of ATM Deposits
Calculated Measures
Avg. transaction value
% deposits to withdrawals
Avg. no. of transactions
Credit Card
Primary Key: creditcardfact_key
Foreign Keys: Customer_key, Location_key, Time_key, creditcard_Key
Measure
Credit Card Withdrawals
Credit Card transaction value
No. of cash Withdrawals
No. of transactions
Interest Amount
Finance Charge
Total Due Amount
Minimum Due Amount
Calculated Measures
Avg. transaction value
% deposits to withdrawals
Avg. no. of transactions
Avg. credit value
% overdue to total amount
No. of defaulters
Non Performing Assets (NPA)
For Actionable Information
24/25
Paid amount
4.4 Bus Matrix
Subjects
Dimensions
Customer
Profile
Deposits
Loans
Interest
Income
Corporate
Services
Treasury
Expenses
Profitability
ALM
HR
Credit Card
ATM
Time √ √ √ √ √ √ √ √ √ √ √ √
Customer √ √ √ √ √ √ √
Location √ √ √ √ √ √ √ √ √ √ √
Service √ √ √
Loans √ √
Deposits √
Investment √
Expenses √
Corporate Services
√
Asset Liability Mgt.
√
Employee √
Credit Card √
For Actionable Information
25/25
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.