OLTP vs OLAP with Dimensions, Facts, and Data Modeling

 

🔍 OLTP vs OLAP with Dimensions, Facts, and Data Modeling

✅ 1. OLTP (Online Transaction Processing)

Use Case: Real-time Banking Transactions

• A user transfers $1,000 from checking to savings.

Example Table: BankTransactions

TransactionID	FromAccount	ToAccount	Amount	Timestamp	Type
1001	12345678	87654321	1000	2025-06-17 10:00:12	Transfer

Characteristics:

• Fast INSERT/UPDATE/DELETE

• Row-level operations

• Normalized structure

SQL Example:

INSERT INTO BankTransactions (FromAccount, ToAccount, Amount, Timestamp, Type)
VALUES ('12345678', '87654321', 1000.00, CURRENT_TIMESTAMP, 'Transfer');

---

✅ 2. OLAP (Online Analytical Processing)

Use Case: Monthly Transfer Volume Report

Involved Tables:

• Fact: FactTransfers

• Dimensions: DimCustomer, DimRegion, DimDate

SQL Example:

SELECT
  d.RegionName,
  c.Segment,
  dt.Month,
  SUM(f.TransferAmount) AS TotalTransferVolume
FROM FactTransfers f
JOIN DimCustomer c ON f.CustomerKey = c.CustomerKey
JOIN DimRegion d ON c.RegionKey = d.RegionKey
JOIN DimDate dt ON f.DateKey = dt.DateKey
WHERE dt.Year = 2025
GROUP BY d.RegionName, c.Segment, dt.Month;

Characteristics:

• Analytical read-heavy queries

• Denormalized data

---

✅ 3. Dimensions = Master Data

Use Case: Filtering Transfers by Customer Details

Example Table: DimCustomer

CustomerKey	Name	DOB	Segment	RegionKey
101	John Doe	1985-03-12	Premium	1

Role:

• Describes "Who", "What", "Where"

• Joins with Fact tables to analyze data context

---

✅ 4. Facts = Measurable Events

Use Case: Summing Total Transfer Amounts

Example Table: FactTransfers

TransferID	CustomerKey	DateKey	TransferAmount	Channel
T1001	101	20250415	1000.00	Online

Role:

• Quantifiable, numeric data

• Links to dimensions

---

✅ 5. Data Modeling (Star Schema)

Use Case: Data Warehouse Model for Transfers

Fact Table: FactTransfers

• TransferID (PK), CustomerKey (FK), DateKey (FK), RegionKey (FK), TransferAmount

Dimension Tables:

• DimCustomer, DimDate, DimRegion

Query Examples:

• Monthly transfer volume

• Transfers by segment and channel

---

❗ Clarification: Fact Tables

• Not always aggregated — they may be atomic (OLTP) or summarized (OLAP)

• Grain defines the level of detail

Fact Table Types:

Type	Description	Example
Transactional	One row per event	Each transfer
Snapshot	Point-in-time view	Month-end balance
Aggregated	Pre-summarized data	Daily product revenue

---

🏦 Banking Analytics Data Model

🔷 Dimension Tables

Table	Description
DimCustomer	Master data for customers
DimDate	Calendar date breakdown
DimProduct	Financial products like accounts/cards
DimRegion	Country/state/city of customer

📊 Fact Tables

Table	Description	Grain
FactTransactions	Transaction-level data (atomic)	One row per transaction
FactAccountSummary	Monthly snapshot of balances	One row per account per month
FactProductSales	Daily product sales summary	One row per product per day

🔁 Star Schema

        DimCustomer         DimProduct          DimRegion         DimDate
             |                  |                   |                 |
             |                  |                   |                 |
             +---------+--------+---------+---------+--------+--------+
                                 |         |                  |
                                 v         v                  v
                          FactTransactions         FactProductSales
                                |                          |
                                +--------------------------+
                                            |
                                  FactAccountSummary

---

🧾 Query Examples

1. Total Transactions by Segment & Region

SELECT
  c.Segment,
  r.Country,
  COUNT(t.TransactionID) AS NumTransactions,
  SUM(t.Amount) AS TotalVolume
FROM FactTransactions t
JOIN DimCustomer c ON t.CustomerKey = c.CustomerKey
JOIN DimRegion r ON c.RegionKey = r.RegionKey
GROUP BY c.Segment, r.Country;

2. Monthly Account Balance Trend

SELECT
  d.Year, d.Month,
  c.Name,
  p.ProductType,
  SUM(a.Balance) AS TotalBalance
FROM FactAccountSummary a
JOIN DimCustomer c ON a.CustomerKey = c.CustomerKey
JOIN DimProduct p ON a.ProductKey = p.ProductKey
JOIN DimDate d ON a.MonthEndDateKey = d.DateKey
GROUP BY d.Year, d.Month, c.Name, p.ProductType;

3. Product Sales Performance

SELECT
  p.ProductType,
  d.Month,
  SUM(s.NumSold) AS TotalUnitsSold,
  SUM(s.TotalRevenue) AS Revenue
FROM FactProductSales s
JOIN DimProduct p ON s.ProductKey = p.ProductKey
JOIN DimDate d ON s.DateKey = d.DateKey
GROUP BY p.ProductType, d.Month;

---

✅ Summary Table

Concept	Use Case Description	Example Table	Purpose
OLTP	Real-time banking transactions	BankTransactions	Fast inserts/updates
OLAP	Analytical monthly/quarterly reports	FactTransfers + Dimensions	Complex analytical queries
Dimension	Master data: Customer, Product, Region	DimCustomer, DimRegion	Adds context to facts
Fact	Transaction or metric-based events	FactTransfers	Quantitative analysis
Data Model	Star Schema linking dimensions to facts	Star schema structure	Logical structure for BI/reporting