Data Warehousing Components
A Data Warehouse (DW) is a subject-oriented, integrated, time-variant and non-volatile collection of data that supports management's decision-making (Inmon's definition).
Inmon's four characteristics and the component diagram are asked almost every year (7-10 marks). Learn the four words with one example each.
OLTP runs the day-to-day business with fast, small transactions. A data warehouse analyses the business with large, historical queries.
1. Four Key Characteristics
| Property | Meaning | Example |
|---|---|---|
| Subject-oriented | Organized around business subjects, not applications | Customer, Sales, Product (not the billing software) |
| Integrated | One unified format from many sources | M/F, Male/Female, 0/1 all become Male/Female |
| Time-variant | Keeps history (5-10 years), every row time-stamped | Sales 2020 vs 2021 vs 2022 |
| Non-volatile | Read-only after load: no update or delete, only refresh | A loaded sales record is never changed |
The warehouse only grows by appending new batches, old data is never modified:
where is the new batch loaded at time .
2. Major Components
3. Warehouse vs Operational DBMS
| Feature | Operational DBMS | Data warehouse |
|---|---|---|
| Purpose | Run the business | Analyse the business |
| Data | Current, detailed | Historical, summarized |
| Updates | Continuous insert, update, delete | Periodic batch refresh |
| Queries | Simple, predefined | Complex, ad hoc |
| Users | Thousands (clerks) | Few (managers, analysts) |
| Size | GB | TB to PB |
- Draw and explain the data warehouse component diagram (7-10 marks, very frequent).
- Explain Inmon's four characteristics with examples.
- Why is a staging area needed? (2-3 marks)
- Differentiate operational database and data warehouse (5 marks).
Frequently Asked Questions
QWhat is a data warehouse?
A subject-oriented, integrated, time-variant and non-volatile collection of data used to support management decisions (Inmon).
QExplain "subject-oriented" and "non-volatile".
Subject-oriented means the data is organized around subjects such as Customer or Sales instead of applications. Non-volatile means the data is not changed or deleted after loading; it is only refreshed by appending new data.
QWhat is a data mart?
A smaller, department-specific subset of the warehouse (for example Sales or Finance) that serves one group of users.
QWhat is the role of the metadata repository?
It stores data about data: source mappings, transformation rules, refresh schedules and ownership. ETL and query tools use it to work correctly.
QWhat is ETL?
Extract, Transform, Load: pulling data from sources, cleaning and converting it, and loading it into the warehouse.
QWhat is the difference between OLTP and a data warehouse?
OLTP handles many short, current, detailed transactions. A warehouse handles fewer, long, analytical queries over historical, summarized data.