Analytics and Data Warehousing: OLTP vs OLAP, Star Schema
Why the database that runs your shop is the wrong shape for analysing it
- distinguish an OLTP workload from an OLAP one and say why one schema cannot serve both well;
- identify the fact table and the dimension tables in a design, and state the grain;
- build a star schema and write aggregate queries against it;
- explain why warehouses deliberately denormalize;
- handle changing descriptive data with slowly changing dimensions (Type 1 vs Type 2).
1. Two very different jobs: OLTP vs OLAP
You already know how to design a normalized schema, add indexes and wrap changes in transactions. Those skills serve OLTP — Online Transaction Processing — where the database backs a running application: a checkout, a booking, a bank transfer. The workload is many tiny reads and writes, each touching a handful of rows, all needing to be correct right now.
OLAP — Online Analytical Processing — is the reporting and analytics side: "revenue by category by quarter", "the ten cities with the fastest-growing sales", "average basket size on weekends". A single such query may read millions of historical rows, group and aggregate them, and it does not need this second's newest order — data that is a few hours old is fine.
| Aspect | OLTP (the app database) | OLAP (the warehouse) |
|---|---|---|
| Typical query | read/insert a few rows by key | scan and aggregate millions of rows |
| Writes | constant, small, one row at a time | bulk loads on a schedule (nightly / hourly) |
| Schema goal | normalized, no redundancy | denormalized, easy to aggregate |
| Rows per query | 1 to a few | thousands to millions |
| Users | the application, end customers | analysts, dashboards, reports |
| Freshness | must be up to the millisecond | hours-old data is acceptable |
The two workloads want opposite things, so mature organisations keep them apart: the app writes to its OLTP database, and a periodic ETL (Extract, Transform, Load) job copies and reshapes that data into a separate warehouse for OLAP. Running heavy analytics directly on the live OLTP database competes with real customers for locks and buffer memory, and can slow checkout to a crawl.