§ 1.7.2section 9 of 25·~6 min
OLAP: analytical queries
OLAP is analysis: questions that combine a lot of data, usually history from several systems, to support a decision.
- Revenue by product category, per year.
- Sales compared across regions.
- How many customers who signed up in January are still ordering in June.
- Seasonal buying patterns.
- Actual performance against targets.
Where an OLTP transaction touches a few rows, one OLAP query often reads millions or billions.
A typical query
A shop has 500 million order lines from five years. Management wants monthly revenue by category and region:
SELECT
DATE_TRUNC('month', order_date) AS month,
region,
category,
SUM(amount_paise) AS revenue_paise
FROM fct_order_lines
GROUP BY month, region, category;
It groups by three dimensions and sums one measure. Depending on the engine, storage layout and partitions, it may read a large part of the table to return a few thousand rows.
What OLAP systems are built for
- Complex queries: many joins,
GROUP BY, window functions, subqueries. - Big scans, small results: millions of rows in, a summary out.
- History: years of data kept so you can compare day to day, quarter to quarter.
- Mostly reads: data arrives in bulk loads or continuous ingestion, not one-row updates from users.
- Aggregation:
SUM,AVG,COUNT,MIN,MAX, percentiles. - Parallelism: the scan is split across many CPUs or machines (MPP, massively parallel processing).
- Decision support: results feed dashboards, reports, notebooks and ML models.
"Fast" here means a short query time over a huge amount of data, not milliseconds per transaction.
Selectivity: how much a filter keeps
Selectivity is how narrowly a filter cuts the data. On a table of 100 million orders:
| Filter | Rows kept | Usually fastest with |
|---|