TARAN PHILLIPS

HORIZON 360 BI • Core Banking KPI Model

ERD-Style Banking Data Diagram

A polished, executive-friendly view of the core banking entities behind Net Interest Margin, Deposit Growth, Loan-to-Deposit Ratio, and Fee Income Trend.

Dimension Table
Fact Table
Relationship Flow
Dimension
DIM_CUSTOMER
customer_id (PK)
cif_number
customer_name
segment
status
Dimension
DIM_BRANCH
branch_id (PK)
branch_name
region
market
Dimension
DIM_PRODUCT
product_id (PK)
product_code
product_name
product_group
Dimension
DIM_DATE
date_key (PK)
year
quarter
month
month_end_flag
Core Entity
DIM_ACCOUNT
account_id (PK)
account_number
customer_id (FK)
branch_id (FK)
product_id (FK)
account_type
open_date
close_date
status
Fact Table
FACT_ACCOUNT_DAILY
account_id (FK)
date_key (FK)
ending_balance
average_balance
interest_income
interest_expense
fee_income
Fact Table
FACT_ACCOUNT_MONTHLY
account_id (FK)
date_key (FK)
ending_balance
average_balance
interest_income
interest_expense
fee_income
Fact Table
FACT_TRANSACTION
transaction_id (PK)
account_id (FK)
date_key (FK)
transaction_code
transaction_amount
fee_type
fee_amount
Fact Table
FACT_GL_MONTHLY
gl_account_id (FK)
date_key (FK)
branch_id (FK)
balance
amount
Dimension
DIM_GL_ACCOUNT
gl_account_id (PK)
gl_number
gl_name
gl_category
financial_category
BI Usage
Conformed Dimensions
Shared keys support
branch, product, customer,
date, and GL rollups across
Finance, Treasury, and Retail.
Dimension
DIM_BRANCH
branch_id (PK)
branch_name
region
market
KPI Output
Net Interest Margin
Interest Income − Interest Expense
÷ Average Earning Assets
KPI Output
Deposit Growth
Current Deposit Balance
vs Prior Period Balance
KPI Output
Loan-to-Deposit Ratio
Total Loans
÷ Total Deposits
KPI Output
Fee Income Trend
Monthly Fee Revenue Trend
by Product, Branch, or Channel
Modeling Intent
This layout reflects a practical account-centric banking star schema where dimension tables describe the business context and fact tables carry balances, income, fees, and GL values.
Best Executive Use
Use monthly snapshot facts for board and executive KPIs, and use transaction and GL facts for drill-through, reconciliation, and finance alignment.
Interview Talk Track
“I’d standardize KPI logic in the BI semantic layer so Treasury, Finance, and Retail all pull NIM, deposit growth, LDR, and fee income from one governed model.”