Chapter 06

Data Warehouses & OLAP

The second pillar of the course. What a data warehouse is (subject-oriented, integrated, time-variant, non-volatile), how OLAP differs from OLTP, the multidimensional cube and its hierarchies, the OLAP operations (roll-up, drill-down, slice-and-dice, pivot), and the WITH CUBE / WITH ROLLUP SQL that every Part II warehouse query is built from.

Reading: ~35 min Interactive: 1 widgets Source: Polimi TIS 2025/26 — Intro to Data Warehouses (deck 07)

01 · Setup

OLTP vs OLAP & the data-warehouse architecture

A data warehouse is a “single, complete and consistent store of data, obtained from a variety of sources and made available to end users … in a business context” (Devlin). It exists because decisions need data integrated across the enterprise, historical, summarised, and open to what-if analysis.

Inmon’s operational definition gives the four properties the exam expects verbatim:

Subject-oriented

Organised around the business subjects of analysis (sales, policies), not around applications.

Integrated

Assembled and reconciled from many sources into one consistent schema.

Time-variant

Keeps history — ETL runs periodically (daily/weekly/monthly), building a time series.

Non-volatile

Loaded and read, not continuously updated in place — a stable base for analysis.

It is materialized integration: an ETL (Extract-Transform-Load) process fills the warehouse periodically. The architecture flows data sources → ETL / staging area (+ metadata) → enterprise data warehouse → data marts → OLAP front-ends (analysis, visualization, data mining). Data quality (Chapter 5) is done in that staging area. A warehouse is normally stored in a relational DBMS, but it is a specialised database running a very different workload from a transactional one:

Aspect	OLTP (transactional)	OLAP (warehouse)
Workload	Mostly updates, many small transactions	Mostly reads, long complex queries
Data	Current snapshot, raw	History, summarised & reconciled
Access	Index/hash on primary key	Lots of scans
Size	GB – PB	PB – EB
Users	Thousands (generic employees)	Hundreds (management)

key

A warehouse is a paradigm, not a product

Big-data infrastructures (cloud stores, lakes) do not replace the warehouse: the DW is a model — it can be stored in a cloud repository, so the two are complementary. (Data lakes return in the final chapter.) Warehouses also come in flavours — offline vs online (trigger-updated), and single-source vs integrated.

02 · Model

The multidimensional cube & hierarchies

OLAP studies data from different viewpoints (dimensions) at different granularities (hierarchies). The natural metaphor is the data cube: a Sales cube whose axes are Products, Markets and Time Periods, with each cell holding a metric value (a measure). Three vocabulary words carry the whole model — and get a full chapter of their own next:

Fact

A concept relevant to the decision process — usually a set of events (e.g. sales, phone calls, policies).

Measure

A numeric property of a fact (number of sales, premium value, call duration).

Dimension

A fact property over a finite domain — an analysis coordinate (which product? which market? which period?).

Most dimensions are hierarchical, and OLAP moves up and down those hierarchies:

Time — day → month → trimester → year
Product — product → brand → type → category (Land Rover → cars → vehicles)
Market — store → city → region-area → region

tip

One cube, four managers

The power of the cube is that different roles read the same Sales cube differently: the area manager fixes their markets and scans all products/periods; the product manager fixes some products across all markets/periods; the financial manager fixes a period across all markets/products; the strategic manager narrows a product category, a region and a medium time span. Each is a slice or projection of one cube — which is exactly what the OLAP operations formalise.

03 · Exam-hot

OLAP operations

OLAP operations — asked in 2016-06-27

“Describe the OLAP operations” is a Part I question (2016-06-27). Graders want the operations named and defined, ideally with the roll-up ↔ drill-down duality and a small worked move. The operations also drive the design: the queries a warehouse must answer dictate which dimensions and hierarchies you build (Chapters 7–8).

Roll-up

Aggregate to a higher hierarchy level — e.g. last year’s sales volume per product category and per region.

Drill-down

The inverse — de-aggregate to a lower level — e.g. for one category and region, show daily sales.

Slice & dice

Apply selections and projections that reduce the cube’s dimensionality (fix one axis, cut a sub-cube).

Pivoting

Choose two dimensions to re-aggregate on — reorient the cube.

Beyond these there is ranking (sort by a criterion) and the traditional relational operations (select, project, join, derived attributes).

Worked moves Roll-up and drill-down on a sales table

Start — coarse

(month, product) → quantity: February/pasta 45 000, March/pasta 50 000, April/pasta 51 000.

Drill-down — add the zone dimension

Split each row by zone: (month, zone, product) — February/north/pasta 15 000, February/east/pasta 17 000, February/center/pasta 13 000, … More detail, more rows.

Roll-up — eliminate the month dimension

Sum over months back up to (zone, product) — north/pasta 51 000, east/pasta 52 000, center/pasta 43 000. Less detail, fewer rows.

Those three steps are the buttons along the top of the cube below, running on exactly these numbers. Walk them in order and watch the grand total refuse to move while the granularity changes underneath it — that invariance is what makes roll-up and drill-down safe:

Hands-on

The cube — roll-up, drill-down, slice & pivot

One fact table, (month, zone, product) → quantity. Put each dimension at detail, roll it up to ALL, or slice it to a single value — then choose which two survivors form the axes. Every number below is a SUM over whatever you left in play.

rows Month · columns Product

Granularity: month = detail · zone = ALL · product = detail

Month	pasta	rice	total
February	45,000	24,000	69,000
March	50,000	25,000	75,000
April	51,000	27,000	78,000
total	146,000	76,000	222,000

Cells shown

Events folded

Grand total

222,000

A primary event is one row of the fact table; every cell above is a secondary event aggregating some of them. Roll-up and drill-down move between the two without ever changing the grand total — that invariance is the point.

Try thisWalk the three lecture buttons in order and watch the grand total for pasta stay at 146 000 while the cell count goes 3 → 9 → 3. Then hit Pivot ⇄: the numbers are identical, only transposed — which is exactly why pivoting is the one operation that costs nothing.

TakeawayRoll-up and drill-down are inverses along a hierarchy and preserve the measure; slice and dice cut the cube down without aggregating; pivot only re-lays-out what is already there. The queries a warehouse must answer decide which hierarchies you build in ch. 7 — this is why the cube comes first.

2016-06-27-q12016Q01OLAP operationseasy5 pts

Define the typical operations necessary in the multidimensional data model that is at the basis of data warehouses.

04 · SQL

The data-cube SQL operator

On a relational warehouse (ROLAP, by far the most adopted, versus MOLAP’s physical cube), OLAP aggregation is written in SQL. Two operators — WITH CUBE and WITH ROLLUP — do the heavy lifting, and they appear in every Part II warehouse query, so this is the most exam-critical section of the chapter.

The data cube operator expresses all possible aggregations of a table, introducing the polymorphic value ALL (some systems use NULL) to mark a fully-aggregated column.

SELECT   Model, Year, Color, SUM(Sales)
FROM     Sales
WHERE    Model IN ('Fiat','Ford') AND Color = 'Red' AND Year BETWEEN 1994 AND 1995
GROUP BY (Model, Year, Color)
WITH CUBE

With three grouping columns, WITH CUBE returns all $2^3 = 8$ group-by combinations. From three base facts (fiat/1994/red 50, fiat/1995/red 85, ford/1994/red 80) it produces every sub-total up to the grand total:

Worked example What WITH CUBE expands to

model	year	color	sum(sales)
fiat	1994	red	50
fiat	1995	red	85
fiat	ALL	red	135
ford	1994	red	80
ford	ALL	red	80
ALL	1994	red	130
ALL	1995	red	85
ALL	ALL	red	215
fiat	ALL	ALL	135
ford	ALL	ALL	80
ALL	ALL	ALL	215

(…plus the remaining ALL-combinations; every column independently either keeps its value or collapses to ALL.)

CUBE vs ROLLUP — the distinction graders test in every DW exercise

WITH CUBE evaluates the aggregate for all possible combinations of the group-by columns. WITH ROLLUP evaluates it only along the order of the columns — the hierarchical prefixes (a,b,c) → (a,b) → (a) → (). So: a query asking for “by date, by month, by year, and each single level” of one hierarchy wants ROLLUP; a query asking for “all one- and two-attribute combinations” of independent attributes wants CUBE. Picking the wrong one is the single most common way to drop marks on a Part II query (you will see this trap flagged in the DW-design exercises next).

Load-bearing ideas

A data warehouse is subject-oriented, integrated, time-variant, non-volatile (Inmon) — a materialized store filled by ETL, read by OLAP, complementary to (not replaced by) big-data infrastructure.
OLTP vs OLAP: transactional (updates, current, PK access, many users) vs analytical (reads, history, scans, few management users).
The cube: facts with measures, analysed along dimensions organised into hierarchies; ROLAP implements it relationally.
OLAP operations: roll-up ↔ drill-down (up/down a hierarchy), slice-and-dice (reduce dimensionality), pivot (reorient), ranking.
SQL: WITH CUBE = all column combinations; WITH ROLLUP = hierarchical prefixes only — the choice is graded in every Part II query.
Exam radar: name/define the OLAP operations; and — most important for Part II — know exactly when to write WITH CUBE vs WITH ROLLUP.