Data warehousing, star schemas and OLAP

Data warehousing is the practice of copying data from the systems that run a business into a separate database built for analysis, where it is cleaned, integrated and kept with its history. Chaudhuri and Dayal’s survey describes the warehouse as kept apart from the operational databases and designed for decision support (Chaudhuri and Dayal). Inside it, star schemas are the usual design: one fact table of measurements, such as sales, surrounded by dimension tables that describe them, such as date, shop and product. OLAP, online analytical processing, is the kind of querying a warehouse serves: totals and averages grouped by the dimensions, rolled up to coarser levels or drilled down to finer ones. Where an operational database answers “what is in order 1001?” thousands of times a second, a warehouse answers “revenue by state and month, this year against last” over millions of rows.

A star schema, starting from the grain

The first decision is the grain: what one row of the fact table records. The Kimball Group calls declaring it the pivotal step of a dimensional design and says it must come before choosing dimensions or facts (Kimball Group: grain). For a chain of six shops, a grain of “one product, one shop, one day” gives this star:

The fact table holds keys to the dimensions and two measures, the quantity and the amount. Each dimension holds the attributes people group and filter by, and those attributes form concept hierarchies: day, month, quarter and year for time; shop, city and state for place. Two variations have their own names:

• A snowflake schema normalises some dimensions into further tables, for example city and state moved out of dim_store into their own tables.
• A fact constellation has several fact tables that share dimensions, for example sales and stock levels both keyed by date, shop and product.

Star schemas stay the default because each query is one join from the fact table to every dimension it needs.

OLAP operations in SQL

Each OLAP operation is a way of choosing which dimensions and levels to group by:

• Roll-up moves up a hierarchy: from city to state to the whole country.
• Drill-down moves the other way, from a state to its cities to its shops.
• Slice fixes one dimension to one value, such as only the Beverages category.
• Dice keeps a sub-cube: several values on several dimensions at once.
• Pivot turns one dimension’s values into columns, such as one column per month.

SQL has three operators for producing several groupings in one query. GROUPING SETS lists the groupings wanted, ROLLUP produces every prefix of its list of columns, and CUBE produces every combination of them (PostgreSQL 18: table expressions). PostgreSQL supports all three; SQLite supports none, so the same roll-up is written as one GROUP BY per level, stacked with UNION ALL:

-- A star schema for a chain of six shops: one fact table at the grain "one product, one shop, one day",
-- and three dimension tables around it.
CREATE TABLE dim_store (store_key INTEGER PRIMARY KEY, store TEXT, city TEXT, state TEXT);
INSERT INTO dim_store VALUES (1, 'S1', 'Pune', 'Maharashtra'), (2, 'S2', 'Mumbai', 'Maharashtra'),
  (3, 'S3', 'Nagpur', 'Maharashtra'), (4, 'S4', 'Bengaluru', 'Karnataka'), (5, 'S5', 'Mysuru', 'Karnataka'),
  (6, 'S6', 'Kochi', 'Kerala');
CREATE TABLE dim_product (product_key INTEGER PRIMARY KEY, product TEXT, category TEXT, price INTEGER);
INSERT INTO dim_product VALUES (1, 'Rice 5 kg', 'Staples', 520), (2, 'Wheat flour 10 kg', 'Staples', 480),
  (3, 'Tea 500 g', 'Beverages', 260), (4, 'Coffee 200 g', 'Beverages', 310), (5, 'Soap 4-pack', 'Personal care', 180);
CREATE TABLE dim_date (date_key INTEGER PRIMARY KEY, day TEXT, month TEXT, quarter TEXT, year INTEGER);
WITH RECURSIVE d(n) AS (SELECT 0 UNION ALL SELECT n + 1 FROM d WHERE n < 90)  -- 91 days: April to June
INSERT INTO dim_date
SELECT CAST(strftime('%Y%m%d', '2025-04-01', '+' || n || ' days') AS INTEGER), date('2025-04-01', '+' || n || ' days'),
       strftime('%Y-%m', '2025-04-01', '+' || n || ' days'), '2025-Q2', 2025
FROM d;
CREATE TABLE fact_sales (
  date_key INTEGER REFERENCES dim_date, store_key INTEGER REFERENCES dim_store,
  product_key INTEGER REFERENCES dim_product, qty INTEGER, amount INTEGER,
  PRIMARY KEY (date_key, store_key, product_key));
-- Made-up but repeatable quantities: bigger shops sell more (the factor after the remainder).
INSERT INTO fact_sales
SELECT date_key, store_key, product_key, qty, qty * price FROM (
  SELECT d.date_key, s.store_key, p.product_key, p.price,
         (1 + (d.date_key * 31 + s.store_key * 17 + p.product_key * 7) % 9)
           * CASE s.store_key WHEN 2 THEN 5 WHEN 4 THEN 4 WHEN 1 THEN 3 WHEN 6 THEN 3 ELSE 2 END AS qty
  FROM dim_date d, dim_store s, dim_product p);
SELECT count(*) AS fact_rows, count(DISTINCT date_key) AS days FROM fact_sales;

-- Roll-up with subtotals: PostgreSQL writes GROUP BY ROLLUP (state, city). SQLite has no ROLLUP:
SELECT state, city, sum(amount) FROM fact_sales JOIN dim_store USING (store_key) GROUP BY ROLLUP (state, city);

-- The same roll-up in SQLite: one GROUP BY per level, stacked with UNION ALL.
SELECT state, city, revenue FROM (
  SELECT state, city, sum(amount) AS revenue, 0 AS lvl FROM fact_sales JOIN dim_store USING (store_key) GROUP BY state, city
  UNION ALL
  SELECT state, '(all cities)', sum(amount), 1 FROM fact_sales JOIN dim_store USING (store_key) GROUP BY state
  UNION ALL
  SELECT '(all states)', '(all cities)', sum(amount), 2 FROM fact_sales)
ORDER BY lvl = 2, state, lvl, city;

-- Slice (one category), then pivot: months become columns.
SELECT s.state,
       sum(CASE WHEN d.month = '2025-04' THEN f.amount END) AS apr,
       sum(CASE WHEN d.month = '2025-05' THEN f.amount END) AS may,
       sum(CASE WHEN d.month = '2025-06' THEN f.amount END) AS jun
FROM fact_sales f JOIN dim_store s USING (store_key) JOIN dim_date d USING (date_key)
JOIN dim_product p USING (product_key)
WHERE p.category = 'Beverages'
GROUP BY s.state ORDER BY s.state;

┌───────────┬──────┐
│ fact_rows │ days │
├───────────┼──────┤
│ 2730      │ 91   │
└───────────┴──────┘
┌──────────────┬──────────────┬──────────┐
│    state     │     city     │ revenue  │
├──────────────┼──────────────┼──────────┤
│ Karnataka    │ Bengaluru    │ 3179120  │
│ Karnataka    │ Mysuru       │ 1593800  │
│ Karnataka    │ (all cities) │ 4772920  │
│ Kerala       │ Kochi        │ 2380860  │
│ Kerala       │ (all cities) │ 2380860  │
│ Maharashtra  │ Mumbai       │ 3979700  │
│ Maharashtra  │ Nagpur       │ 1590900  │
│ Maharashtra  │ Pune         │ 2392800  │
│ Maharashtra  │ (all cities) │ 7963400  │
│ (all states) │ (all cities) │ 15117180 │
└──────────────┴──────────────┴──────────┘
┌─────────────┬────────┬────────┬────────┐
│    state    │  apr   │  may   │  jun   │
├─────────────┼────────┼────────┼────────┤
│ Karnataka   │ 516060 │ 535500 │ 519840 │
│ Kerala      │ 261180 │ 261030 │ 256050 │
│ Maharashtra │ 843690 │ 886090 │ 846120 │
└─────────────┴────────┴────────┴────────┘

Parse error near line 30: no such function: ROLLUP

The roll-up shows the three levels in one result: six cities, three state subtotals and the grand total of 15,117,180. The last query slices the cube to Beverages and pivots the three months into columns.

Measures: distributive, algebraic and holistic

Roll-ups are only correct if each measure is combined the right way, and Gray and colleagues sorted aggregate functions into three classes for exactly this reason (Gray et al.):

• Distributive measures, such as SUM, COUNT, MIN and MAX, combine from the answers for the parts.
• Algebraic measures, such as the average, combine from a fixed number of distributive values per part: a sum and a count.
• Holistic measures, such as the median and the mode, need all the underlying data; no fixed summary of the parts is enough.

The most common mistake follows from the second line: averaging averages.

-- Orders from two cities of one state: many small ones in Pune, two large ones in Nagpur.
CREATE TABLE orders (id INTEGER PRIMARY KEY, city TEXT NOT NULL, amount INTEGER NOT NULL);
INSERT INTO orders VALUES (1, 'Pune', 300), (2, 'Pune', 250), (3, 'Pune', 350), (4, 'Pune', 300),
  (5, 'Pune', 300), (6, 'Pune', 300), (7, 'Nagpur', 1200), (8, 'Nagpur', 1000);

-- The partials a summary table keeps per city.
SELECT city, count(*) AS orders, sum(amount) AS total, avg(amount) AS average FROM orders GROUP BY city ORDER BY city;

-- The state's average order value, three ways.
SELECT (SELECT avg(average) FROM (SELECT avg(amount) AS average FROM orders GROUP BY city)) AS average_of_averages,
       (SELECT sum(total) * 1.0 / sum(n) FROM (SELECT sum(amount) AS total, count(*) AS n FROM orders GROUP BY city))
         AS from_sums_and_counts,
       (SELECT avg(amount) FROM orders) AS over_all_orders;

┌────────┬────────┬───────┬─────────┐
│  city  │ orders │ total │ average │
├────────┼────────┼───────┼─────────┤
│ Nagpur │ 2      │ 2200  │ 1100.0  │
│ Pune   │ 6      │ 1800  │ 300.0   │
└────────┴────────┴───────┴─────────┘
┌─────────────────────┬──────────────────────┬─────────────────┐
│ average_of_averages │ from_sums_and_counts │ over_all_orders │
├─────────────────────┼──────────────────────┼─────────────────┤
│ 700.0               │ 500.0                │ 500.0           │
└─────────────────────┴──────────────────────┴─────────────────┘

Pune’s six orders average 300 and Nagpur’s two average 1,100. Their plain average, 700, weights each city equally, but the state’s real average is 4,000 divided by 8 orders, 500, which the sums and counts give back exactly. An average of averages is right only when every group has the same number of rows.

Exercise

-- Roll the city summary up to states: state, orders, revenue, avg_order_value (2 decimals).
SELECT state, sum(orders) AS orders, sum(revenue) AS revenue,
       round(avg(revenue * 1.0 / orders), 2) AS avg_order_value
FROM city_summary
GROUP BY state;

# Sample tests: each one builds the table, runs your query on it and compares the rows it returns (in any order).
tests:
  - name: "cities of very different sizes"
    setup: |
      CREATE TABLE city_summary (state TEXT NOT NULL, city TEXT NOT NULL, orders INTEGER NOT NULL, revenue INTEGER NOT NULL);
      INSERT INTO city_summary VALUES
        ('Maharashtra', 'Pune', 6, 1800), ('Maharashtra', 'Nagpur', 2, 2200),
        ('Karnataka', 'Mysuru', 3, 1000), ('Karnataka', 'Bengaluru', 9, 4500);
    expected:
      columns: [state, orders, revenue, avg_order_value]
      rows:
        - [Maharashtra, 8, 4000, 500.0]
        - [Karnataka, 12, 5500, 458.33]
  - name: "a state with one city"
    setup: |
      CREATE TABLE city_summary (state TEXT NOT NULL, city TEXT NOT NULL, orders INTEGER NOT NULL, revenue INTEGER NOT NULL);
      INSERT INTO city_summary VALUES ('Kerala', 'Kochi', 7, 2450);
    expected:
      columns: [state, orders, revenue, avg_order_value]
      rows:
        - [Kerala, 7, 2450, 350.0]
  - name: "an average that is not a whole number, and a state with no orders"
    setup: |
      CREATE TABLE city_summary (state TEXT NOT NULL, city TEXT NOT NULL, orders INTEGER NOT NULL, revenue INTEGER NOT NULL);
      INSERT INTO city_summary VALUES ('Goa', 'Panaji', 2, 700), ('Goa', 'Margao', 1, 300), ('Sikkim', 'Gangtok', 0, 0);
    expected:
      columns: [state, orders, revenue, avg_order_value]
      rows:
        - [Goa, 3, 1000, 333.33]

Check yourself

Rolling up every kind of measure correctly, with numbers Pro: opens with a Pro, Premium or Ultimate pass

GROUPING SETS, ROLLUP and CUBE: expansions, row counts and the NULL trap Pro: opens with a Pro, Premium or Ultimate pass

Slowly changing dimensions and concept hierarchies, worked Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• Data warehousing keeps integrated history for analysis in a database apart from the systems that run the business.
• A star schema puts measures in a fact table at a stated grain and describes them in dimension tables.
• Roll-up, drill-down, slice, dice and pivot choose which dimensions and levels to group by; PostgreSQL has GROUPING SETS, ROLLUP and CUBE, and SQLite needs one GROUP BY per level.
• Distributive measures combine from the parts’ answers, algebraic ones from a few numbers per part, and holistic ones only from all the data: never average averages.