Unit 1: Data warehousing fundamentals
Data Warehousing and Data Mining notes · PTU syllabus (PGCA1941)
On this page
Unit summary
A data warehouse brings together data from many operational systems so managers can analyse history and trends. This unit covers the components and architecture of a data warehouse, its mapping to multiprocessor architectures, operational vs informational data stores, and OLAP vs OLTP with OLAP operations.
After this unit you can
- Explain the need for and components of a data warehouse
- Map a warehouse to parallel (multiprocessor) architectures
- Distinguish operational and informational data stores
- Compare OLAP and OLTP and apply OLAP operations
PTU syllabus topics
- Data warehouse components and architecture
- mapping to multiprocessor architecture
- need for data warehousing
- operational vs informational data stores
- OLAP vs OLTP and their differences
- OLAP operations
Purpose
Run daily operations
Analyse for decisions
Data
Current, detailed
Historical, summarised
Operations
Insert, update, delete
Read, aggregate, slice
Schema
Normalised
Star or snowflake
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
Components of a data warehouse
- 1
Source systems
ERP, CRM, files, web logs
- 2
Staging area and ETL
Extract, clean, transform, load
- 3
Warehouse database
Integrated, subject-oriented, historical store
- 4
Metadata repository
Technical and business metadata
- 5
Data marts
Department-specific subsets
- 6
Access tools
Query, reporting, OLAP, data mining, dashboards
Topic 3
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
Topic 4
Mapping the warehouse to a multiprocessor architecture
- Large warehouses need parallelism to load and query terabytes quickly.
Shared memory (SMP)
All CPUs share memory and disks
Simple; limited scalability
Shared disk (cluster)
Nodes have own memory, share disks
Good availability (Oracle RAC)
Shared nothing (MPP)
Each node owns its CPU, memory and disk
Best scalability (Teradata, Redshift, Synapse)
- Inter-query
- Different queries on different processors
- Intra-query
- One query split into parallel operations
- Horizontal (partitioned)
- Data partitioned across nodes, same operation on each
- Vertical (pipelined)
- Output of one operation flows to the next in parallel
- Data partitioning: round-robin, hash, or range (e.g., by month) so each node scans only its share.
Topic 5
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.
Topic 6
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
Topic 7
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).
Key terms
- Data warehouse
- Subject-oriented, integrated, time-variant, non-volatile collection of data for decisions
- MPP
- Massively parallel, shared-nothing architecture
- ODS
- Operational data store holding current integrated data
- OLAP
- Online analytical processing
- Data mart
- Departmental subset of a warehouse
Quick revision
- Need for warehousing; components from sources to access tools.
- SMP, shared disk, shared nothing; inter-, intra-query, horizontal, vertical parallelism.
- Operational vs informational stores.
- OLAP vs OLTP; roll-up, drill-down, slice, dice, pivot.
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 do organisations need a data warehouse?
- Q2.List the components of a data warehouse.
- Q3.What is shared-nothing architecture?
- Q4.Distinguish operational and informational data.
- Q5.Give two differences between OLAP and OLTP.
- Q6.What is the slice operation?
Long-answer questions
- Q1.Explain data warehouse components and architecture.
- Q2.Explain how a data warehouse is mapped to multiprocessor architectures.
- Q3.Compare OLAP and OLTP and explain OLAP operations with an example.
Stuck on this unit?
Message SBS on WhatsApp for help with Data Warehousing and Data Mining, or to ask about studying M.Sc IT at Synetic.