OLAP

Online Analytical Processing

Pronunciation: OH-lap

Updated October 3, 20262 min read

In short

OLAP (online analytical processing) describes systems built to answer complex analytical questions over large amounts of historical data quickly.

What is OLAP?

Where OLTP systems record individual transactions, OLAP systems analyze them in bulk. Questions such as "How did sales change by region over the last three years?" or "Which marketing channel brings the customers who spend most?" scan millions or billions of rows, group them and calculate totals, averages and trends.

To make that fast, OLAP systems usually store data by column rather than by row. A query that sums one column reads only that column, and values of the same type compress very well. Data warehouses such as BigQuery, Snowflake, Amazon Redshift and ClickHouse are built this way, and they spread queries across many machines.

Data is often modeled in a star schema: a central fact table of events, such as sales, surrounded by dimension tables, such as products, customers and dates. The term OLAP also refers to cubes, pre-aggregated data structures that let analysts slice and drill down by dimensions in business intelligence tools.

A common misconception is that OLAP data is always current. It usually arrives through ETL or streaming pipelines, from minutes to a day later. OLAP systems are also poor at frequent single-row updates, which remain the job of OLTP databases.

Key takeaways

OLAP systems run complex analytical queries over large historical data.
They usually store data by column for fast scans and compression.
BigQuery, Snowflake, Redshift and ClickHouse are OLAP systems.
Star schemas organize facts around dimension tables.
Data arrives later through pipelines; single-row updates are slow.

Example

A typical OLAP query over a star schemasql

SELECT
    d.year,
    d.month,
    p.category,
    r.region,
    SUM(f.revenue)              AS revenue,
    COUNT(DISTINCT f.customer_id) AS customers
FROM fact_sales AS f
JOIN dim_date    AS d ON d.date_id    = f.date_id
JOIN dim_product AS p ON p.product_id = f.product_id
JOIN dim_region  AS r ON r.region_id  = f.region_id
WHERE d.year >= 2024
GROUP BY d.year, d.month, p.category, r.region
ORDER BY revenue DESC;
-- Scans millions of rows but reads only the columns it needs

Readers ask

What is an OLAP cube?

A pre-calculated, multidimensional summary of data, such as sales by product, region and date, that lets analysts slice and drill into numbers quickly. Modern columnar warehouses often compute these views on the fly instead.

Is a data warehouse the same as OLAP?

A data warehouse is the store that holds cleaned historical data for analysis. OLAP is the kind of processing done on it. Data warehouses are the most common OLAP systems.

Why are OLAP databases columnar?

Analytical queries usually read a few columns across many rows. Storing each column together means only the needed columns are read, and similar values compress well, which makes scans much faster.

Often compared

OLTP vs OLAPOLTP systems handle many small, fast transactions such as orders and payments, while OLAP systems run large analytical queries over historical data.