Data Warehouse; OLAP vs. OLTP

1. Definition of a data warehouse

A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision-making process.

• Subject-oriented: organised around major subjects such as customer, product, sales rather than around applications or transactions.
• Integrated: data from many heterogeneous sources (files, relational DBs, external feeds) are merged under a single schema with consistent naming, units and codes.
• Time-variant: every attribute records the time period; the data covers a long historical period (years), unlike operational data which holds only the current value.
• Non-volatile: once loaded, data are not updated or deleted - they are read-only, so historical data remain stable for analysis.

It is built by ETL (extract, transform, load) from operational systems and organised as a data cube (fact and dimension tables) to support analytical queries.

2. OLAP vs. OLTP

Feature	OLTP (On-Line Transaction Processing)	OLAP (On-Line Analytical Processing)
Purpose	Run day-to-day transactions (insert, update, delete)	Analysis, reporting and decision support
Users	Clerks, operators, customers	Managers, analysts, executives
Design	Application-oriented, ER / 3NF normalised schema	Subject-oriented, star / snowflake schema
Operations	Simple, short read-write queries	Complex queries: roll-up, drill-down, slice, dice, pivot
Data	Current, detailed, small volume per query	Historical, aggregated, very large volume
Response time	Very fast (milliseconds), few records touched	Seconds to minutes, large portions of data scanned
Query volume	Many concurrent short transactions	Fewer, long-running analytical queries
Indexing	Heavy indexing for fast writes	Bitmap / join indexes for fast aggregation
Failure impact	Affects business operations immediately	Affects reporting only

In short: OLTP supports the operations of the organisation (current, fast, transactional), while OLAP supports the analysis of the organisation (historical, aggregated, multidimensional) over a data warehouse.