Unit 3.5
Data Warehouses and Data Marts for Business Intelligence
Learning Objectives
By the end of this session, you will be able to:
- ✅ Define a data warehouse and its purpose in supporting Business Intelligence (BI).
- ✅ Describe the four key characteristics of a data warehouse.
- ✅ Differentiate between a data warehouse (OLAP) and a transactional database (OLTP).
- ✅ Define a data mart and explain its relationship to a data warehouse.
The Core Problem: Two Different Workloads
⚡ Day-to-Day Operations
Running the business requires handling thousands of small, fast transactions.
- Examples: Selling an item, withdrawing cash from an ATM, booking a ticket.
- Focus: Speed, accuracy, and real-time data.
- This is called Online Transaction Processing (OLTP).
📊 Big Picture Analysis
Analyzing the business requires asking complex questions over large amounts of historical data.
- Examples: "What are our sales trends over 5 years?", "Which products are most profitable?"
- Focus: Insights, trends, and patterns.
- This is called Online Analytical Processing (OLAP).
The Core Problem: Two Different Workloads (cont.)
Problem: Running complex analysis (OLAP) on a live operational system (OLTP) can drastically slow it down, impacting daily business!
Quick Check: OLTP or OLAP?
For each scenario below, click the correct workload type.
The Solution: The Data Warehouse 🏛️
Data Warehouse: A special type of database designed specifically for data analysis and reporting (OLAP). It serves as a central repository of historical and integrated data from various sources.
Think of it as the company's "corporate memory" or a "single source of truth."
Main Purpose
To separate the analytical workload (OLAP) from the transactional workload (OLTP), enabling powerful Business Intelligence without disrupting operations.
OLTP vs. OLAP: A Head-to-Head Comparison
OLTP (Transactional Database)
- Purpose: Run the business
- Data: Current, real-time
- Queries: Simple, fast, repetitive
(e.g., "Find customer #123") - Users: Front-line workers, clerks
- Example: E-commerce checkout system
OLAP (Data Warehouse)
- Purpose: Analyze the business
- Data: Historical, summarized
- Queries: Complex, multi-dimensional
(e.g., "Compare Q1 sales by region for the last 3 years") - Users: Analysts, managers, executives
- Example: Sales forecasting dashboard
Which System Is Being Described?
Each card describes one characteristic. Click OLTP or OLAP — then flip to check.
🎬 Watch: Heartbeat vs. Brain
The 4 Pillars of a Data Warehouse (Part 1)
1. Subject-Oriented 🎯
Data is organized around the major subjects of the business.
- Subjects: Customer, Product, Sales
- Instead of: Business processes like 'Order Entry' or 'Invoicing'.
- This makes it intuitive for business analysis.
2. Integrated 🔗
Data is combined from multiple, different sources into a single, consistent format.
- Sources: CRM, ERP, flat files, APIs
- Process: Data is cleaned and standardized.
- Example: "USA", "U.S.", "United States" all become "USA".
🔄 ETL: How Data Gets Into the Warehouse
1. Extract
Copy data out of the source systems: POS, ERP, CRM, spreadsheets, web logs.
2. Transform
Clean and standardise: fix errors, remove duplicates, convert formats, calculate totals.
3. Load
Write the cleaned data into the warehouse on a schedule, for example every night.
| Source record | After Transform |
|---|---|
| Store A: date "03/04/2026", amount "1,500" | Date 2026-04-03 · Amount 1500.00 NPR · Store A / B |
| Store B: date "2026-04-03", amount "Rs 1500" |
Transform is where most of the effort goes. Bad data in means bad decisions out.
🎬 Watch: Extract, Transform, Load
The 4 Pillars of a Data Warehouse (Part 2)
3. Time-Variant ⏳
Data represents a historical record, allowing for analysis over time.
- Contains data over a long period (e.g., 5-10 years).
- Each record is associated with a point in time.
- Essential for trend analysis and forecasting.
4. Non-Volatile 🔒
Once data is loaded into the warehouse, it is stable and does not change.
- Data is read-only.
- New data is added periodically (e.g., daily, weekly), but old data is not overwritten.
- Ensures a reliable historical record for analysis.
The 4 Pillars: Match the Pillar to Its Description
Drag each description to the correct pillar, or click a pillar name then click a description to link them.
🧊 Working the Cube: OLAP Operations
Picture a retail sales cube with three dimensions: Product × Store × Month.
| Operation | What it does | Example question |
|---|---|---|
| Slice | Fix one dimension to a single value | All products, all stores, for March only |
| Dice | Pick a sub-cube on several dimensions | Shoes and bags, in 2 stores, for Q1 |
| Drill-down | Go to finer detail | Year → quarter → month → day |
| Roll-up | Summarise to a higher level | Stores → city → country |
| Pivot | Rotate the view | Swap rows (products) and columns (months) |
Managers explore the cube with clicks, with no new code, which is why OLAP suits a warehouse.
Introducing Data Marts 🔍
Data Mart: A smaller, simpler version of a data warehouse that is focused on a single subject or line of business.
It's a subset of the enterprise data warehouse, designed for a specific department's needs.
Sales Data Mart
Contains data on customers, sales, and performance for the sales team.
Finance Data Mart
Contains data on revenue, costs, and profits for the finance department.
Marketing Data Mart
Contains data on campaigns, leads, and customer segments for marketing.
Introducing Data Marts 🔍 (cont.)
Why use a Data Mart? Faster access, simpler to use, and quicker to build for specific user groups.
Which Data Mart Handles This Request? (1/3)
For each business request below, click the department data mart best suited to answer it.
Which Data Mart Handles This Request? (2/3)
Which Data Mart Handles This Request? (3/3)
🏗️ Warehouse First or Mart First?
| Top-down (Bill Inmon) | Bottom-up (Ralph Kimball) | |
|---|---|---|
| Start with | One enterprise warehouse, then dependent marts fed from it | Department marts first, joined later through shared dimensions |
| Strength | One consistent "single source of truth" | Fast, cheaper first results |
| Risk | Slow and costly to build | Separate marts may disagree on the numbers |
| Suits | Large firms, such as a bank needing regulatory reports | A smaller firm, or one urgent department |
An independent data mart is built straight from the sources with no warehouse behind it. It's quick, but it can become a data silo.
Practical Application: A Nepali Telco
Scenario: Analyzing Customer Data at Ncell or Nepal Telecom
OLTP System: The live billing and call record system. It processes millions of transactions (calls, SMS, data usage) per minute. You can't run slow, heavy queries here!
Data Warehouse: Integrates data from call records, billing systems, customer recharge cards, and CRM. It allows analysts to ask complex questions like: "What was the average data consumption per user in Province 3 during the Dashain festival season over the last 5 years?"
Practical Application: A Nepali Telco (cont.)
Scenario: Analyzing Customer Data at Ncell or Nepal Telecom
Marketing Data Mart: The marketing team gets a focused data mart with just customer demographics, recharge history, and service usage. This allows them to quickly analyze the success of a new data pack offer without needing to query the entire, massive data warehouse.
🛒 Real Case: Tesco Clubcard (UK Retail)
- In 1995, Tesco launched the Clubcard loyalty card nationwide. Every swipe linked a shopping basket to a customer.
- The analytics firm dunnhumby loaded this data into a warehouse and grouped shoppers by what they actually bought.
- The result was targeted coupons by post instead of one mass mailing, plus better decisions on store ranges and stock.
- Tesco chairman Lord MacLaurin is widely quoted as saying: "You know more about my customers after three months than I know after 30 years."
The warehouse is subject-oriented (Customer), integrated (tills plus card data), and time-variant (years of baskets).
🎬 Watch: What Tesco's Data Revealed
🏦 Worked Example: A Bank's Data Warehouse
Illustrative: a hypothetical Nepali commercial bank.
Sources (OLTP)
- Core banking: accounts and loans
- Card switch: debit card use
- Mobile banking app logs
- Branch customer forms
Questions the warehouse answers
- Which branches show rising loan arrears over 3 years?
- Which customers have moved from branch visits to the app?
- Which salary-account holders are good candidates for a home loan?
A credit data mart for the loan team and a customer data mart for marketing would each draw from the same warehouse.
🗳️ Quick Poll: Warehouse or Data Mart?
Hands up: W for an enterprise warehouse, M for a data mart.
- A university's library wants a quick dashboard of book loans. → M: one department and fast to build
- A hospital group needs one view of patients across all its hospitals. → W: integrates many sources enterprise-wide
- An airline's revenue team analyses fares on one route network. → M (dependent): a focused subset of the warehouse
Discuss: what goes wrong if every department builds its own independent mart?
Chapter 5: Review Quiz (1/5)
Five questions — one answer per question. Click to select, then press Submit.
Chapter 5: Review Quiz (2/5)
Chapter 5: Review Quiz (3/5)
Chapter 5: Review Quiz (4/5)
Chapter 5: Review Quiz (5/5)
Chapter 5: Key Takeaways
- OLTP vs. OLAP: Operational databases (OLTP) run the business; data warehouses (OLAP) analyze the business.
- The 4 Pillars: Data warehouses are Subject-Oriented, Integrated, Time-Variant, and Non-Volatile.
- Single Source of Truth: A data warehouse provides one consistent, trusted version of data for reliable business intelligence.
- Data Marts: These are smaller, departmental subsets of a data warehouse that provide focused, faster data access for specific user groups.
Exam Practice 📝
Short answer
- What is a data warehouse?
- Distinguish between a data warehouse and a data mart.
- What does ETL stand for? Explain briefly.
Exam Practice 📝 (continued)
Long answer
- Explain the characteristics and purpose of a data warehouse, with an example from banking or retail.
- Compare data warehouses and data marts, and explain when an organization would use each.
Revise the answers in the full note.
Image & Video Credits
Images
- OLAP operations diagram: made for this course
- Tesco Clubcard (2017): Marccoton, CC BY-SA 4.0, via Wikimedia Commons
Videos (YouTube)
- techTFQ, "OLTP vs OLAP Explained in 5 Minutes" (121–177 s)
- IBM Technology, "What is ETL (Extract, Transform, Load)?" (34–97 s)
- University of Sheffield Information School, "Clive Humby OBE | The Tesco Clubcard and Data Privacy" (8–68 s)
Thank You
Any questions?
Next Up: Chapter 6 - Introduction to Big Data