OLAP Cubes & Rollups

Concepts covered: OLAP Cubes

Many groupings, one table

OLAP, online analytical processing, is the name for queries that slice a measure by one grouping column after another: trips by city, then by vehicle type, then by city and vehicle type together, then the company total. Each slice is a GROUP BY with a different column list, and a summary that holds only (city, day) rows cannot serve the vehicle type view. A column used for grouping like this is called a dimension, and a cube is a summary that holds every combination of its dimensions in one table.

Take 2 dimensions, city and vehicle_type. There are 4 combinations: both, city only, vehicle_type only, and neither, which is the grand total. You could build them with 4 separate queries, or with 1 query using CUBE.

SELECT
  city,
  vehicle_type,
  count(*) AS trip_count,
  sum(fare) AS fare_total,
  grouping(city, vehicle_type) AS grouping_id
FROM trips
WHERE started_at >= date '2026-10-22'
  AND started_at < date '2026-10-23'
GROUP BY CUBE (city, vehicle_type)

NULL in a dimension column means 'all values', and grouping_id records which columns were collapsed: bit value 1 for vehicle_type, bit value 2 for city. The id column matters because a real city value can be missing too, and a trip with an unknown city would otherwise look like the all-cities row. A dashboard reading the cube filters on grouping_id to pick a view: grouping_id = 1 gives one total per city, grouping_id = 3 gives the single company row, and the Austin row with id 1 holds 19,006 trips, the sum of its 2 rows with id 0.

Rollups for hierarchies, grouping sets for choice

Dimensions multiply. A cube over d dimensions holds 2 to the power d groupings: 3 dimensions give 8, 6 give 64, 10 give 1,024, and most of them answer questions nobody asks. Grouping by vehicle_type and hour without city, for a dashboard with no hourly view, produces rows nobody reads and a longer job every night.

ROLLUP covers a hierarchy. ROLLUP (country, city) produces (country, city), (country) and (), and skips (city) alone, since a city belongs to one country and the (country, city) rows already hold its total. GROUPING SETS lists the exact combinations you want and nothing else. The trips table has a country column, so the 3 dimensions country, city and vehicle_type can be trimmed to 5 groupings.

SELECT
  country,
  city,
  vehicle_type,
  count(*) AS trip_count,
  sum(fare) AS fare_total
FROM trips
GROUP BY GROUPING SETS (
  (country, city, vehicle_type),
  (country, city),
  (country, vehicle_type),
  (country),
  ()
)

The cube's 8 groupings include (city, vehicle_type), which repeats (country, city, vehicle_type) row for row, because every city sits inside exactly one country. The grouping sets drop that repeat and keep (country, vehicle_type), which the rollup would never produce. Per day the row counts are all small; across 6 years, the cube stores 1,500 redundant rows a day that someone has to document and keep correct.

A cube over (city, vehicle_type) is stored for each day in trips_cube. An analyst wants the sedan fare total across all cities on 2026-10-22 and runs the query below. What does it return?

SELECT
  sum(fare_total)
FROM trips_cube
WHERE vehicle_type = 'sedan'
  AND trip_date = date '2026-10-22'

• The correct sedan total, because the WHERE clause selects the sedan rows and the sum adds the per-city rows together, which is how a cube is meant to be read.
• Zero, because the all-cities sedan row has city set to NULL and a NULL comparison removes the row from the sum.
• The correct total only if the cube was built with ROLLUP, since CUBE stores the per-city sedan rows without a separate all-cities sedan total.
• Double the sedan total, because the per-city sedan rows and the all-cities sedan row (city NULL, grouping id 2) both match the filter and both are summed.

About this lesson

This section is part of the Pre-Aggregation lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.