Part 6 — Data at work › Chapter 39

OLTP vs OLAP and dimensional modelling

Star schemas, fact and dimension tables, grain, and slowly changing dimensions type 1 and type 2.

From the advert

“Reporting con SSRS e Power BI” — the query behind a report is usually the easy part. Designing the model the reports sit on is the job.

Two workloads, two designs

🧒 Simple

A shop till and a shop's annual accounts want opposite things. The till needs to add one sale, right now, without disturbing anybody. The accounts need to add up two million sales, and do not mind waiting a minute. Designing one thing to do both means it does neither well.

	OLTP	OLAP
Unit of work	One order	Two years of orders
Reads	A few rows by key	Millions, aggregated
Writes	Constant, small, concurrent	Bulk, scheduled, single-writer
Design	Normalised — 3NF	Denormalised — star schema
Indexes	Narrow, selective, many	Few; columnstore, or none at all
Optimised for	Latency and concurrency	Throughput per scan

🎓 Why analytical queries want a different shape

Normalisation exists to prevent update anomalies from concurrent piecemeal writes. A warehouse is loaded in bulk by one controlled process, so those anomalies largely cannot arise — and the cost of normalisation, an eleven-table join on every question, is paid on every query instead. A star schema trades storage and update flexibility for fewer joins and sequential scans, which is the right trade for that workload and the wrong one for a till.

The star schema

Every question is one join per dimension. Never more.

🎓 Grain is the first decision and the one that matters

What does one row of the fact table represent? One order line? One order? One day per product? Everything else follows: which dimensions apply, which measures are additive, what the row count will be. Getting it wrong is not a tuning problem, it is a rebuild — and "what is the grain?" is the question a data-warehouse interviewer is waiting to hear you ask.

Term	Means
Fact	A measurement: quantity, revenue, duration. Numeric and usually additive
Dimension	The context you slice by: who, what, when, where
Grain	What one fact row means. Decide it first, write it down
Additive	Summable across every dimension — revenue
Semi-additive	Summable across some — a stock level sums over products but not over time
Non-additive	A ratio or percentage. Store the numerator and denominator; compute the ratio on read
Snowflake	Normalised dimensions. More joins, less redundancy, usually not worth it
Conformed dimension	One `dim_customer` shared by several fact tables, so figures reconcile across reports

⚠ Trap — averaging a semi-additive measure

Summing a daily stock level over a month gives a number with no meaning. Summing a percentage across regions gives a wrong one. Non-additive measures must be stored as their components: keep delivered_count and total_count, and compute the rate in the report. Storing the percentage is the commonest fatal mistake in a warehouse design, because every subsequent aggregation of it is silently wrong.

Slowly changing dimensions

🧒 Simple

A customer moves from Modena to Milano. Do last year's sales now count as Milano sales, or do they stay Modena sales because that is where they happened? Both answers are defensible; the disaster is not deciding, because then different reports give different totals.

Type	Behaviour	Use when
Type 1	Overwrite. History is lost	Correcting a mistake — a misspelled name
Type 2	New row, with `valid_from`/`valid_to` and an `is_current` flag	The change is real and history matters. The default
Type 3	A `previous_city` column beside the current one	You need exactly one prior value. Rare

A slowly changing dimension, type 2 — and why the warehouse invents its own keys.

-- Type 2 in practice. The SURROGATE key is what makes it work:
dim_customer
 customer_key  customer_id  name        city     valid_from   valid_to     is_current
 ------------  -----------  ----------  -------  -----------  -----------  ----------
 501           42           Rossi SpA   Modena   2024-01-01   2026-06-30   0
 902           42           Rossi SpA   Milano   2026-07-01   9999-12-31   1

-- A fact row records customer_key 501, so it stays permanently attached to the
-- version of the customer that existed when the sale happened.

How to read the table, and why it is shaped this way

Two rows, one real customer. customer_id 42 is the source system's id; customer_key is the warehouse's own, and it identifies a version of the customer rather than the customer.
A fact recorded in 2025 points at key 501. When Rossi SpA moved to Milano, a new row was added rather than the old one updated — so last year's revenue by city does not silently move cities.
valid_to = 9999-12-31 rather than NULL is deliberate: a date range with no NULLs can be filtered with a plain BETWEEN and needs no three-valued logic (chapter 09).
is_current is redundant — it can be derived from valid_to — and it is kept anyway because it makes the common query trivial and indexable. That is a deliberate denormalisation, which is what a warehouse is for.
A natural key cannot express “this row, as it was then”. That is the whole reason a warehouse uses surrogate keys.

✔ Say this in an interview

“Dimension surrogate keys are not just for narrowness — they are what lets a fact reference a version of a dimension row, which is how type 2 history works at all.” That one sentence covers surrogate keys, SCDs and grain in a way that is hard to fake.

Where this lands in the Italian market

Most jobs advertising “Power BI” or “SSRS” are asking you to be the layer underneath: the views, the summary tables, the nightly load and the dim_date. A useful, concrete thing to know is that Power BI's own model is a star schema — its engine (VertiPaq) is columnar and optimised for exactly this shape, and a report built on one wide flat table or on a normalised OLTP schema will be slow in ways no amount of DAX fixes. “I would give Power BI a star schema, not a view over the OLTP tables” is a strong, specific answer.

The smallest possible star query, over the reference database's real date dimension.

-- The reference database already has a dim_date. This is the shape of every
-- warehouse query you will ever write: facts, joined to dimensions, grouped.
SELECT d.year, d.month,                        -- from the DATE dimension
       r.name AS region,                       -- from the geography dimension
       COUNT(*) AS orders,                     -- a fact: how many
       SUM(o.total_cents)/100.0 AS revenue_eur -- a fact: how much, in euros
FROM orders o                                  -- the fact table
JOIN dim_date d  ON d.d  = date(o.placed_at)   -- date() truncates the timestamp to a day
JOIN customers c ON c.id = o.customer_id       -- …then walk out to the geography…
JOIN cities   ci ON ci.id = c.city_id          -- …one level…
JOIN regions   r ON r.id = ci.region_id        -- …at a time
WHERE o.status <> 'cancelled'                  -- cancellations are not revenue
GROUP BY d.year, d.month, r.name               -- the grain of the report
ORDER BY d.year, d.month, revenue_eur DESC;    -- chronological, biggest region first

What makes this an OLAP query rather than an OLTP one

It touches every order and returns a handful of rows. An OLTP query touches one order and returns one row. That difference — not the SQL — is what decides the schema, the indexes and even the storage format (chapter 40).
The three joins out to regions are the price of a normalised OLTP schema. A warehouse would flatten them into one dim_geography row per city, deliberately, so the query is one join instead of three.
date(o.placed_at) on the join is not sargable. In a report over the whole table that is irrelevant — it is a scan either way — but the same expression in a WHERE on a live table is chapter 07's mistake. Context decides.
A calendar dimension is what lets you group by fiscal quarter, working day or week number without writing date arithmetic in every query.

OLTP optimises latency and concurrency; OLAP optimises throughput per scan. One design cannot do both.
A warehouse is denormalised because it is bulk-loaded by one process, so update anomalies largely cannot arise.
Decide the grain first and write it down. Everything else follows from it.
Facts are measurements; dimensions are the context you slice by.
Never store a ratio as a measure. Store the numerator and denominator.
Semi-additive measures — stock levels, balances — cannot be summed over time.
Type 1 overwrites, type 2 adds a versioned row. Type 2 is the default when history matters.
Surrogate dimension keys are what let a fact point at a version of a dimension row.
A conformed dimension is what makes two reports agree.
Power BI's engine wants a star schema. A view over the OLTP schema will be slow whatever the DAX.

Interview questions this chapter answers

What is a star schema and why is it denormalised?

A central fact table of measurements surrounded by dimension tables you slice by, so every query is one join per dimension. It is denormalised because the workload is bulk-loaded and read with wide aggregate scans — the anomalies normalisation prevents cannot really arise, and the joins it would cost are paid on every query.

A customer changes city. How do you handle it in the warehouse?

First ask whether historical sales should follow them. If not — usually the case — it is a type 2 slowly changing dimension: close the current row with a valid_to, insert a new one with a new surrogate key, and leave existing facts pointing at the old key. If it is a correction rather than a change, type 1 and overwrite.

Why not store a conversion rate in the fact table?

Because it is non-additive: summing rates is meaningless and averaging averages is wrong. I store the numerator and denominator as separate additive measures and compute the rate at report time, so it is correct at every level of aggregation.