What are OLAP operations?

OLAP operations are the canonical analytical actions performed on a multidimensional data model: drill-down, roll-up, slice, dice, and pivot. The vocabulary originated with OLAP cubes in the 1990s, after E.F. Codd's 1993 paper Providing OLAP to User-Analysts, and persists in columnar OLAP, where each operation maps directly to a SQL pattern over a fact table and its dimensions. For the broader category overview, see the OLAP primer.

How OLAP operations map to SQL #

Every OLAP operation has a direct SQL equivalent. The cube model treats data as a multidimensional structure where dimensions (time, geography, product) intersect with measures (revenue, units), but the underlying mechanics are filters and groupings. Drill-down and roll-up change the GROUP BY. Slice and dice change the WHERE. Pivot changes the result orientation. Columnar engines like ClickHouse, Snowflake, and BigQuery execute these operations against fact tables on demand, with no pre-aggregated cube required. The examples below use a small star schema with a sales fact table dimensioned by date, product, and region.

Drill-down #

Drill-down navigates from summary to detail by adding a finer-grained dimension to the grouping. An analyst viewing annual revenue per region drills down by adding month or day to the GROUP BY clause, exposing the same measure at a smaller grain. The operation does not change the underlying data, only the resolution at which it is read. Drill-down is the inverse of roll-up.

-- Starting view: revenue by region
SELECT region, SUM(revenue) AS revenue
FROM sales
GROUP BY region;

-- Drill down to month within region
SELECT region, toStartOfMonth(date) AS month, SUM(revenue) AS revenue
FROM sales
GROUP BY region, month
ORDER BY region, month;

Roll-up #

Roll-up aggregates from detail to summary by removing a dimension from the grouping, or by replacing a fine-grained column with a coarser one (day → month → year). Roll-up produces the totals that dashboards present at the top of the page (revenue across all regions, units across all products) without exposing transaction-level rows. The ROLLUP keyword, standardised in SQL:1999, performs hierarchical roll-up across multiple columns in a single query.

-- Hierarchical roll-up: subtotals at region, region+product, and grand total
SELECT region, product, SUM(revenue) AS revenue
FROM sales
GROUP BY ROLLUP(region, product);

Slice #

Slice filters on a single dimension to extract a two-dimensional view of the cube. From a sales cube spanning region × product × time, slicing on region = 'EMEA' returns only the EMEA plane. In SQL, slice is a WHERE clause with one predicate against one dimension. The result narrows the analytical surface without changing what is measured.

-- Slice: EMEA only, revenue by product over time
SELECT product, toStartOfMonth(date) AS month, SUM(revenue) AS revenue
FROM sales
WHERE region = 'EMEA'
GROUP BY product, month;

Dice #

Dice filters on multiple dimensions simultaneously to extract a sub-cube. Where slice picks a plane, dice picks a smaller box. A typical predicate set might combine region IN ('EMEA','APAC') with date BETWEEN '2026-01-01' AND '2026-03-31' and product = 'Pro'. Dice maps to a WHERE clause with multiple predicates across multiple dimensions. It is the most common operation in production, since analytical queries rarely scan the full cube.

-- Dice: EMEA + APAC, Q1 2026, Pro tier only
SELECT region, product, SUM(revenue) AS revenue
FROM sales
WHERE region IN ('EMEA', 'APAC')
  AND date BETWEEN '2026-01-01' AND '2026-03-31'
  AND product = 'Pro'
GROUP BY region, product;

Pivot #

Pivot re-orients the result so that values of one dimension become column headers rather than rows, turning a long table into a wide one. Microsoft SQL Server introduced a PIVOT keyword in 2005, and Oracle, Snowflake, BigQuery, and DuckDB followed; PostgreSQL exposes equivalent behaviour through the crosstab function in tablefunc. ClickHouse does not have a PIVOT keyword, instead it is expressed with conditional aggregates such as sumIf, documented in the ClickHouse pivot knowledge base.

-- Pivot: revenue per product, with one column per region
SELECT
  product,
  sumIf(revenue, region = 'EMEA') AS emea,
  sumIf(revenue, region = 'APAC') AS apac,
  sumIf(revenue, region = 'AMER') AS amer
FROM sales
GROUP BY product;

Drill-across #

Drill-across combines measures from two separate fact tables that share at least one common dimension. A common case is joining a sales fact and a returns fact on product and date to compute net revenue. The operation appears constantly in production analytics, where the cube model fragments into multiple star schemas that Kimball-style modelling calls a fact constellation.

-- Drill-across: net revenue = sales − returns
SELECT
  s.product,
  s.revenue - r.refund_amount AS net_revenue
FROM (SELECT product, SUM(revenue) AS revenue FROM sales GROUP BY product) AS s
LEFT JOIN (SELECT product, SUM(refund_amount) AS refund_amount FROM returns GROUP BY product) AS r
  USING (product);

MOLAP, ROLAP, and HOLAP #

The five operations are vocabulary rather than architecture. The same drill-down or pivot runs differently depending on the OLAP implementation model the engine uses.

ROLAP became the standard model after columnar engines made on-demand aggregation fast enough to displace pre-built cubes. See what is an OLAP cube for the cube-to-columnar history, and what is a columnar database for the storage model that ROLAP runs on.

FAQ #