For Actionable Information
Logical Data Model for Retail Banking
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.
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
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.
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
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
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
For Actionable Information 4.1 Star Schema
For Actionable Information
For Actionable Information
For Actionable Information
Corporate Services
For Actionable Information
Expenses
For Actionable Information
Asset Liability Management
For Actionable Information
For Actionable Information
For Actionable Information 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 Nu m be r 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
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
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
For Actionable Information
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 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
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
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 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 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
For Actionable Information Paid amount 4.4 Bus Matrix Subjects Dimensions C u s tom e r P r of il e D e p os it s L oa n s I n te re s t I n c o m e C or p or a te S e rv ic e s T r e a s u r y E x p e n s e s P r of it a b il it y A L M H R C r e d it C a r d A T M Time √ √ √ √ √ √ √ √ √ √ √ √ Customer √ √ √ √ √ √ √ Location √ √ √ √ √ √ √ √ √ √ √ Service √ √ √ Loans √ √ Deposits √ Investment √ Expenses √ Corporate Services √ Asset Liability Mgt. √ Employee √ Credit Card √
For Actionable Information
