---
title: "What are OLAP operations?"
date: "2026-05-27"
author: "Al Brown"
category: "Engineering"
excerpt: "OLAP operations are the canonical analytical actions performed on multidimensional data: drill-down, roll-up, slice, dice, and pivot. Each maps to a specific SQL pattern."
---

# 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](/resources/engineering/what-is-olap).

## 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.

```sql
-- 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.

```sql
-- 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.

```sql
-- 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.

```sql
-- 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](https://clickhouse.com/docs/knowledgebase/pivot).

```sql
-- 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.

```sql
-- 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.

| Model | How data is stored | Example engines | Trade-off |
|---|---|---|---|
| MOLAP | Pre-aggregated multidimensional cubes | Essbase, Microsoft Analysis Services | Fast reads, slow rebuilds, cube explosion at high cardinality |
| ROLAP | Relational tables, aggregates computed on demand | ClickHouse, Snowflake, BigQuery, Druid | Flexible schema, scales with columnar engines, no pre-build cost |
| HOLAP | Hybrid (summaries pre-aggregated, detail relational) | SAP BW, older Microsoft AS modes | Compromise on both axes; complex to operate |

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](/resources/engineering/what-is-olap-cube) for the cube-to-columnar history, and [what is a columnar database](/resources/engineering/what-is-columnar-database) for the storage model that ROLAP runs on.

## FAQ

**** 

**** 

**** 

**** 


## Frequently asked questions

### Question 1: Are OLAP operations specific to cubes?

No. The vocabulary originated with cubes in the 1990s but applies to any multidimensional analytical workload. Drill-down, roll-up, slice, dice, and pivot are taught as cube operations in textbooks; in practice, they describe SQL patterns that run on columnar engines without any cube layer.### Question 2: What is the difference between slice and dice?

Slice filters on one dimension and reduces the cube by one axis. Dice filters on multiple dimensions and extracts a smaller sub-cube. Both map to `WHERE` clauses; the distinction is the number of predicates.### Question 3: Does ClickHouse support PIVOT?

Not as a keyword. ClickHouse expresses pivot with `sumIf`, `countIf`, and other conditional aggregates. The [ClickHouse pivot knowledge base](https://clickhouse.com/docs/knowledgebase/pivot) covers the full set of patterns.### Question 4: What replaces OLAP operations in columnar analytics?

Nothing — the operations are still the canonical analytical actions. What has been replaced is the cube as the execution model. See [what is OLAP](/resources/engineering/what-is-olap) for the broader category overview.