Unit 1: Data warehouse fundamentals
Data Warehousing & Mining notes · PTU syllabus (BSIT504/BSBC501)
On this page
- Unit summary
- The need for data warehousing
- Operational vs informational data stores
- Data warehouse characteristics and OLAP operations
- Characteristics of a data warehouse
- Role and structure of a data warehouse
- Cost of data warehousing
- OLAP vs OLTP
- OLAP operations with an example
- Key terms
- Quick revision
- Important questions
Unit summary
Operational databases run the business; a data warehouse helps understand it. This unit covers the need for data warehousing, operational versus informational data stores, the characteristics, role and structure of a data warehouse, its cost, OLAP versus OLTP, and OLAP operations.
After this unit you can
- Explain why organisations need data warehouses
- Distinguish operational and informational data stores
- Describe the characteristics, role, structure and cost of a data warehouse
- Compare OLAP and OLTP and perform OLAP operations
PTU syllabus topics
- Need for data warehousing
- operational vs informational data stores
- data warehouse characteristics/role/structure
- cost of warehousing
- OLAP vs OLTP
- OLAP operations
Topic 1
The need for data warehousing
- Managers need integrated, historical, summarised data to analyse trends — but operational systems are separate, keep only current data, use different formats and slow down if heavy analytical queries run on them.
- Integrated view
- Combine sales, finance, HR and CRM data
- Historical analysis
- Five or ten years of trends
- Fast analytical queries
- Without disturbing transaction systems
- Data quality
- Cleaned, consistent data
- Competitive advantage
- Better, faster decisions
Example
A retail chain with 200 stores wants to compare festive-season sales by region and category over five years; store billing databases keep only three months of data, in different formats.
Topic 2
Operational vs informational data stores
Purpose
Run day-to-day operations
Support analysis and decisions
Data
Current, detailed, changing
Historical, summarised and detailed, stable
Access
Many short reads and writes
Few, large read-only queries
Users
Clerks, customers
Managers, analysts
Design
Normalised for updates
Denormalised (star schema) for queries
- An operational data store (ODS) may also integrate current data from several systems for near-real-time operational reporting.
Example
Two stores, two jobs: a bank's core system records each withdrawal instantly (operational). The warehouse holds five years of summarised data so managers can study branch-wise trends without slowing down the live system (informational).
Exam tip
Compare them on purpose, data age, updates, users and query type.
Topic 3
Data warehouse characteristics and OLAP operations
OLAP operations let users analyse a data cube from different angles: roll-up, drill-down, slice, dice and pivot. A data warehouse (Inmon) is a subject-oriented, integrated, time-variant and non-volatile collection of data supporting management decisions.
Roll-up
Summarise: city → state
Drill-down
More detail: year → month
Slice
Fix one dimension: sales in 2026
Dice
Select a sub-cube
Pivot
Rotate the view
Example
OLAP on sales data: roll up from city to state totals; drill down from year to quarters; slice for Q1 only; dice for Q1–Q2 and two cities; pivot to swap rows and columns.
Exam tip
Define each OLAP operation with one example.
Topic 4
Characteristics of a data warehouse
Subject-oriented
Organised around subjects — customer, product, sales — not applications
Integrated
Consistent names, codes and units across sources
Time-variant
Every record linked to a time period; long history
Non-volatile
Loaded and read; not updated in place
Example
The four characteristics in a retail warehouse: organised around customers and products (subject-oriented); sales, inventory and loyalty data use one customer ID (integrated); five years of monthly snapshots (time-variant); loaded data is not edited by daily transactions (non-volatile).
Exam tip
Learn Inmon's definition word for word.
Topic 5
Role and structure of a data warehouse
- Highly summarised data
Yearly totals for top management
- Lightly summarised data
Monthly sales by region
- Current detailed data
Every sale of the recent period
- Older detailed data
Archived on cheaper storage
- Metadata
Data about data — sources, transformations, definitions
- Role: single source of truth for reporting, dashboards, OLAP, data mining and planning; supports CRM, budgeting, fraud detection and performance management.
- Data mart: a subset of the warehouse for one department (sales mart, finance mart) — dependent (fed from the warehouse) or independent.
Example
Levels of data: top management sees yearly totals (highly summarised); regional managers see monthly sales by region (lightly summarised); analysts can drill into individual transactions (current detail); older detail is archived.
Exam tip
The structure runs from current detail to lightly and highly summarised data, plus metadata.
Topic 6
Cost of data warehousing
- Hardware and storage
- Servers, disks or cloud capacity
- Software
- DBMS, ETL, OLAP and BI tools, licences
- People
- Architects, ETL developers, analysts, administrators
- Data preparation
- Cleaning and integrating sources — often the largest effort
- Operations
- Loading, backup, tuning, support
- Hidden costs
- Training, change management, data governance
- Cloud data warehouses (Snowflake, BigQuery, Redshift, Azure Synapse) turn large upfront costs into pay-as-you-use charges.
Example
Hidden costs: a company budgets for servers and software, but data cleaning and ETL development take more time and money than expected, along with training users and ongoing maintenance.
Exam tip
ETL and data quality work is often the largest cost.
Topic 7
OLAP vs OLTP
Full form
Online transaction processing
Online analytical processing
Function
Day-to-day transactions
Analysis and decision support
Data
Current, detailed
Historical, consolidated
Queries
Simple, many, short; insert and update
Complex, few, long; read mostly
Database design
Normalised ER model
Star or snowflake schema; data cube
Size
Gigabytes
Terabytes and more
Example
ATM withdrawal, railway booking
Quarterly sales by region and product
Example
Two queries: 'Record a sale of ₹500 to customer 101' is OLTP — short, frequent, current data. 'Compare monthly sales by region over three years' is OLAP — complex, read-only, historical data.
Exam tip
OLTP is optimised for fast updates; OLAP for fast complex reads.
Topic 8
OLAP operations with an example
- Cube: sales by Time (quarter), Location (city) and Product (category).
- 1Roll-up
City → state: total sales of Punjab
- 2Drill-down
Quarter → month: sales of each month of Q3
- 3Slice
Time = Q4: a two-dimensional view
- 4Dice
Q3–Q4, Ludhiana and Delhi, mobiles and laptops: a sub-cube
- 5Pivot
Swap rows and columns to view products by city
- OLAP types: MOLAP (multidimensional arrays; fast), ROLAP (relational tables; scalable), HOLAP (hybrid).
Example
One cube, five operations: from sales by quarter, city and category, roll up cities to states; drill down Q1 into months; slice for 'Electronics'; dice for Q1–Q2, Ludhiana and Delhi; pivot to show categories as rows and quarters as columns.
Exam tip
Practise drawing a small cube and naming each operation.
Key terms
- Data warehouse
- Subject-oriented, integrated, time-variant, non-volatile collection of data
- Data mart
- Departmental subset of a data warehouse
- Metadata
- Data describing the warehouse data
- OLAP
- Analytical processing of multidimensional data
- Slice
- Selecting one value of a dimension from a cube
Quick revision
- Need for warehousing; operational vs informational data.
- Four characteristics (Inmon).
- Structure: summary levels, detail, metadata; data marts.
- Cost components; cloud warehouses.
- OLTP vs OLAP; roll-up, drill-down, slice, dice, pivot; MOLAP, ROLAP, HOLAP.
Important exam questions
Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).
Short-answer questions
- Q1.Why can't operational databases serve analysis well?
- Q2.State the four characteristics of a data warehouse.
- Q3.What is a data mart?
- Q4.What is metadata?
- Q5.Distinguish OLTP and OLAP.
- Q6.Distinguish slice and dice.
Long-answer questions
- Q1.Explain the need for data warehousing and compare operational and informational data stores.
- Q2.Explain the characteristics, role and structure of a data warehouse.
- Q3.Compare OLTP and OLAP.
- Q4.Explain OLAP operations with an example.
Stuck on this unit?
Message SBS on WhatsApp for help with Data Warehousing & Mining, or to ask about studying B.Sc IT at Synetic.
