Online Analytical Processing (OLAP)

Online Analytical Processing (OLAP)

1. Definition

OLAP is the technology that lets analysts view warehouse data multidimensionally and interactively — consolidating, drilling and pivoting on large volumes of summarized data for decision support.

FASMI test (Pendse & Creeth) for an OLAP system:

Letter	Requirement
Fast	Most queries answered in a few seconds, never minutes
Analysis	Handles business logic and statistics, not just data retrieval
Shared	Multi-user, concurrency control, row-level security
Multidimensional	Conceptual view with dimensions, hierarchies, measures
Information	Scales to all needed data and derived information

2. OLAP vs OLTP

Aspect	OLTP	OLAP
Purpose	Run the business	Analyse the business
Data	Current, detailed	Historical, summarised
Operations	INSERT / UPDATE / DELETE	Mostly SELECT with aggregation
Queries	Short, simple, many	Long, complex, few
Users	Clerks, thousands	Managers/analysts, dozens
Schema	Normalised (3NF)	Star / snowflake / cube
Response	Milliseconds	Seconds to minutes
Size	GB	TB-PB
Metrics	Throughput, availability	Query response, cube build time

3. Codd's Rules for OLAP (abridged — 12 rules)

1. Multidimensional conceptual view — data seen as a cube of dimensions.
2. Transparency — OLAP sits behind an open architecture, invisible to the user.
3. Accessibility — only the server logic exposes data; single uniform access to heterogeneous sources.
4. Consistent reporting performance — response time does not degrade as dimensions increase.
5. Client-server architecture — server is multidimensional and logically central.
6. Generic dimensionality — every dimension is symmetric in structure and operations.
7. Dynamic sparse matrix handling — optimised storage of sparse cubes.
8. Multi-user support — concurrent query, update, security, integrity.
9. Unrestricted cross-dimensional operations — any function across any dimensions.
10. Intuitive data manipulation — drill-down, roll-up, slice, dice, pivot done by direct action.
11. Flexible reporting — dimensions and measures arranged in any way desired.
12. Unlimited dimensions and aggregation levels — no artificial limit.

4. OLAP Server Types

ROLAP (Relational OLAP)

• Data and aggregates stay in relational tables (star/snowflake); SQL is generated at query time.
• Best scalability, huge volumes, no cube build window; slower for complex calculations.

MOLAP (Multidimensional OLAP)

• Pre-computed multidimensional arrays (cubes, MDDB) built by batch processing.
• Fastest slicing/dicing; limited size, sparse data problem, cube refresh window.

HOLAP (Hybrid OLAP)

• Detail rows stay relational, aggregates stored in a cube → balances speed and scale.

Criterion	ROLAP	MOLAP	HOLAP
Storage	Relational tables	Array cube	Both
Query speed	Medium	Fastest	Fast
Data volume limit	Very high	Limited by cube size	High
Cube build needed	No	Yes	Partial
Sparse-handling	Excellent	Needs compression (bitmaps)	Good
Example	Oracle OLAP option, MSAS ROLAP mode	Essbase (BSO), MSAS MOLAP	MSAS HOLAP, SAP BW

Cube size versus sparsity:

5. Data Structures Used

• Multidimensional array (MDDB) — dense cuboids, direct index arithmetic $i = ((d_1 \cdot |D_2| + d_2)\cdot|D_3| + d_3)...$
• Bitmap index — one bit-vector per dimension value, fast AND/OR for slice/dice.
• Join index — pre-joined tuple-id pairs between dimension and fact tables; avoids repeated joins.
• Materialised views / summary tables — pre-computed aggregates, picked by query rewrite.

6. Applications of OLAP

• Sales, marketing and profitability analysis; budgeting and forecasting; financial consolidation
• Customer and product profitability; inventory and supply-chain analysis
• Balanced scorecard / KPI dashboards; fraud and risk monitoring

7. Limitations

• Cubes must be rebuilt as source data changes (data latency).
• Sparse cubes waste storage; many dimensions cause explosion ($2^{d}$ cuboids).
• Not designed for row-level detail retrieval (needs drill-through to the warehouse).

8. Exam Points

• Define OLAP, explain FASMI test — 5 marks.
• OLTP vs OLAP comparison table — 5 marks (very frequent).
• ROLAP vs MOLAP vs HOLAP — 5 marks.
• State Codd's rules for OLAP (any 6-8) — 5-7 marks.