OLAP operations: roll-up, drill-down, slice and dice

Analysts look at the same data from different angles. Picture sales as a cube with three dimensions (time, product and geography) and one measure in every cell: revenue. Each dimension has levels:

Time: year → quarter → month → day
Product: category → brand → product
Geography: country → state → city

The classic OLAP operations move around that cube.

Roll-up

Summarise to a higher level: day → week, city → state.

Day	Revenue
Monday	₹10,000
Tuesday	₹12,000
Wednesday	₹8,000
Thursday	₹15,000
Friday	₹20,000

Rolled up to the week: ₹10,000 + ₹12,000 + ₹8,000 + ₹15,000 + ₹20,000 = ₹65,000.

SQL can produce several levels at once with ROLLUP, which adds subtotal rows for each level plus a grand total:

sql

SELECT sales_year, sales_month, SUM(revenue) AS revenue

FROM sales

GROUP BY ROLLUP (sales_year, sales_month);

Drill-down

The opposite: go from a summary to finer detail, year → quarter → month, or country → state → city. If sales in India fell this year, a manager drills down to states, then cities, to find where.

Slice

Fix one dimension to a single value, here only 2026:

sql

SELECT category, region, SUM(revenue) AS total_revenue

FROM sales

WHERE sales_year = 2026

GROUP BY category, region;

Dice

Pick a sub-cube across several dimensions:

sql

SELECT sales_year, category, region, SUM(revenue) AS total_revenue

FROM sales

WHERE sales_year IN (2025, 2026)

  AND category IN ('Electronics', 'Furniture')

  AND region IN ('Delhi', 'Mumbai')

GROUP BY sales_year, category, region;

Pivot

Rotate the view: swap what's in rows and what's in columns. The data doesn't change; only the layout does.

Delhi	₹10 lakh	₹13 lakh
Mumbai	₹12 lakh	₹15 lakh

Year	Delhi	Mumbai
2025	₹10 lakh	₹12 lakh
2026	₹13 lakh	₹15 lakh

Operation	What it does	Example
Roll-up	Aggregates to a higher level	Daily sales → monthly sales
Drill-down	Shows a finer level	Annual sales → quarterly sales
Slice	Fixes one dimension	Sales for 2026 only
Dice	Filters several dimensions	Two years, two categories, two cities
Pivot	Rotates rows and columns	Years as columns → years as rows
Drill-through	Opens the records behind a total	The orders behind Delhi's ₹13 lakh

	MOLAP (multidimensional)	ROLAP (relational)	HOLAP (hybrid)
Stores data as	Prebuilt cubes	Ordinary fact and dimension tables	Tables for detail, cubes for chosen summaries
Aggregates	Mostly precomputed	Computed by SQL at query time, or materialized	Some of each
Strength	Very fast for predictable questions	Flexible questions over detailed data	Speed on common views, detail when you drill
Weakness	Cubes grow fast and need refreshing	Big queries can be expensive	More moving parts