A database that manages orders, invoices and stock is designed to do one thing well: record lots of small operations, quickly and without errors. When someone asks for «revenue by region and category over the last three years, compared month by month», that same database struggles, and while it struggles it slows down the people entering orders. This is not a flaw: they are two different jobs that call for different structures.
Two kinds of workload
OLTP (Online Transaction Processing) is the workload of operational systems: business software, e-commerce, banking applications. Its typical characteristics:
- a great many short operations, each on a few rows;
- reads and writes mixed together;
- many concurrent users;
- a normalised schema, to avoid inconsistencies on update;
- interest in the current state: today's stock level, the customer's current address.
OLAP (Online Analytical Processing) is the workload of analysis: reports, dashboards, studies of trends. Its characteristics are almost the opposite:
- few queries, but each reads millions of rows;
- almost only reads, with periodic loads of new data;
- few users, usually analysts and managers;
- interest in history: how something has changed over time.
Running heavy analytical queries on the operational database has two consequences: the queries are slow, because the normalised schema requires many joins, and they slow down day-to-day operations, because they consume the same resources.
The data warehouse
A data warehouse is a separate database, fed with data from the operational systems and organised for analysis. It often gathers data from several sources: the business system, the e-commerce platform, the CRM, the sales department's spreadsheets, reconciling them into a single consistent structure.
The process that moves data from the sources to the warehouse is traditionally called ETL: extract, transform, load. Today the ELT variant is also common, in which data is loaded almost raw and transformed inside the warehouse itself, using its computing power.
The star schema
The most widespread structure in data warehouses is the star schema, described systematically by Ralph Kimball in his books on dimensional modelling. At the centre is a fact table, surrounded by dimension tables.
The fact table
It contains measurable events, one per row, at the chosen level of detail. For sales, a row might be a receipt line or an invoice line. The columns are of two kinds: keys to the dimensions and numeric measures.
CREATE TABLE fact_sales (
date_key INTEGER NOT NULL REFERENCES dim_date,
product_key INTEGER NOT NULL REFERENCES dim_product,
customer_key INTEGER NOT NULL REFERENCES dim_customer,
store_key INTEGER NOT NULL REFERENCES dim_store,
quantity INTEGER NOT NULL,
net_amount NUMERIC(12,2) NOT NULL,
cost NUMERIC(12,2) NOT NULL
);
The most important decision is the grain: exactly what one row represents. It should be as detailed as possible, because summaries can be derived from detailed data, but not the other way round.
The dimension tables
They describe the context of the facts: who, what, where, when. They are deliberately denormalised. The product dimension holds code, name, brand, subcategory, category and department in a single table, even though in an operational database they would sit in separate tables.
The date dimension deserves a note: instead of calculating month, quarter or day of the week every time, you create a table with one row for each day and all its properties already calculated, including public holidays and company periods. Queries become simple:
SELECT d.year, d.quarter, p.category, SUM(f.net_amount) AS revenue
FROM fact_sales f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
WHERE d.year IN (2024, 2025)
GROUP BY d.year, d.quarter, p.category;
Every join starts from the fact table and reaches one dimension: that is the star shape that gives the schema its name.
The problem of history: dimensions that change
A customer moves from Milan to Naples. In the operational database you update the address and that is that. In the data warehouse you have to ask: should the sales made while they lived in Milan show up under Milan or Naples?
The classic solutions, known as slowly changing dimensions, are mainly two:
| Type | What is done | Effect on reports |
|---|---|---|
| Type 1 | The value is overwritten | The customer's entire history appears under Naples |
| Type 2 | A new row is added for the customer, with validity dates | Past sales stay in Milan, new ones go to Naples |
Type 2 is the reason dimensions have their own surrogate key, distinct from the business system's customer code: the same customer can have several rows, one for each period.
Do you really need a data warehouse?
For a small business, less is often enough: a read-only replica of the operational database on which to run reports, or a few views or summary tables refreshed every night. A data warehouse becomes necessary when there is more than one source, when history must be preserved reliably, or when analyses are so heavy that they disturb daily work.
In short
- OLTP records many small operations; OLAP analyses large volumes of historical data.
- Heavy reports on the operational database are slow and slow down daily work.
- A star schema has a fact table with the measures, surrounded by denormalised dimensions.
- The grain of the fact table should be as detailed as possible.
- Dimensions that change over time are handled by overwriting or by versioning rows.
