OLAP cube
An OLAP cube is a multidimensional data structure that pre-aggregates measures across combinations of dimensions, letting users slice, dice, drill down, and roll up data quickly without recomputing aggregations each time.
Overview
An OLAP cube organizes data along multiple dimensions (time, product, region, customer) with measures (revenue, quantity) at their intersections — conceptually a multidimensional array, though most implementations don't literally store an N-dimensional array. Classic OLAP cube operations include:
- Slice: fix one dimension to a single value (e.g., only 2026 data).
- Dice: filter to a sub-cube across multiple dimensions at once.
- Drill down / roll up: move between levels of a dimension's hierarchy (year → quarter → month, or region → country → city).
- Pivot: rotate the cube to view it from a different dimensional axis.
Traditional OLAP engines (MOLAP) pre-compute and store aggregations at every level of every dimension hierarchy so these operations return instantly. ROLAP engines skip pre-computation and query a relational star schema directly, computing aggregations on demand. HOLAP mixes the two.
The relational equivalent
A star schema queried with GROUP BY is the relational (ROLAP) way to get the same result a cube provides, computed on demand rather than pre-materialized:
Copy code
-- Roll up
SELECT region, SUM(revenue) FROM fact_sales JOIN dim_date USING (date_key)
WHERE year = 2026 GROUP BY ALL;
-- Drill down
SELECT region, quarter, SUM(revenue) FROM fact_sales JOIN dim_date USING (date_key)
WHERE year = 2026 GROUP BY ALL;
-- Slice + dice
SELECT product_category, SUM(revenue) FROM fact_sales
JOIN dim_date USING (date_key) JOIN dim_product USING (product_key)
WHERE year = 2026 AND region = 'EMEA' GROUP BY ALL;
ROLLUP and GROUPING SETS extend standard SQL GROUP BY to compute several aggregation levels from a hierarchy in a single query, closely mirroring cube drill-down/roll-up.
DuckDB angle
DuckDB doesn't provide a dedicated MOLAP cube structure, but its fast, vectorized aggregation over a star schema makes the ROLAP pattern above practical to compute on demand rather than pre-materializing every level: SUM/GROUP BY ALL queries against a fact table joined to its dimensions typically return in milliseconds to seconds even over large tables, and DuckDB supports standard SQL GROUPING SETS/ROLLUP/CUBE for multi-level aggregation in one query.
Related terms
A complete guide to Online Analytical Processing (OLAP). Learn about OLAP cubes, the differences between OLAP and OLTP, and the types of OLAP systems (MOLAP, ROLAP, HOLAP).
CUBE →CUBE is a GROUP BY extension that computes aggregates for every possible combination of a set of grouping columns, including subtotals for each subset and a grand total.
GROUPING SETS →GROUPING SETS lets a single GROUP BY compute multiple different grouping combinations at once, producing subtotal rows for each specified combination in one query.
ROLLUP →ROLLUP is a GROUP BY extension that produces hierarchical subtotals -- aggregating at each level of a column list, from the most detailed grouping down to a grand total.
Star schema →A star schema is a dimensional data warehouse design where a central fact table of numeric measures connects directly to denormalized dimension tables, forming a shape like a star.
Dimension table →A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.
FAQS
The core operations are slice (fix one dimension), dice (filter across several dimensions), drill down and roll up (move between levels of a hierarchy, like year to month), and pivot (rotate the view to a different dimensional axis).
A star schema queried with GROUP BY, ROLLUP, or GROUPING SETS achieves the same slice/dice/drill-down/roll-up results as an OLAP cube, computed on demand. Dedicated MOLAP cube engines pre-compute aggregations for instant response, which matters more at very large scale or for interactive BI tools than for ad hoc SQL analysis.
