OLAP Databases: What They Are, Real Use Cases and the Main Vendors

What an OLAP database is, how it differs from OLTP, practical use cases with real SQL, and the leading vendors in 2026.

What OLAP is (and what it isn't)
- What OLAP is not
OLAP vs OLTP: the difference that explains everything
- Why column orientation changes everything
The history: from cubes to columnar (and why you should care)
The star schema: how OLAP data is modeled
Practical use cases with real SQL
The main OLAP vendors in 2026
How to choose: my criterion in four questions
A reflection on why this matters more than it seems
Conclusion
Frequently asked questions

What OLAP is (and what it isn't)

OLAP stands for Online Analytical Processing. It's a category of workload, not a specific product.

That distinction matters more than it seems. When someone says "we need an OLAP", what they're describing is a pattern: queries that scan and aggregate millions to billions of rows to answer business questions. "Monthly revenue by product and region for the last three years" is an OLAP query whether you run it in Excel against a 1998 cube or fire it against a billion-row table today.

The term was coined by E. F. Codd —the same Codd of the relational model— in a 1993 paper titled Providing OLAP to User-Analysts: An IT Mandate, where he defined twelve rules for analytical systems.

An honest footnote almost nobody mentions: that paper was sponsored by Arbor Software, the makers of Essbase, one of the first OLAP products. The twelve rules described, suspiciously well, what Essbase already did. It was a brilliant marketing move disguised as an academic paper, and yet the underlying distinction it drew —analytical workloads need a different design than transactional ones— turned out to be completely correct, and still defines the field thirty years later.

The word "online" is also misleading. It has nothing to do with the internet: in 1993 it meant interactive, in opposition to the batch reports generated overnight.

What OLAP is not

It's not the same thing as a data warehouse. OLAP is a category of processing; a data warehouse is an infrastructure pattern. Warehouses are built to serve OLAP workloads, but OLAP also runs on real-time databases, embedded engines and semantic layers.

They're not the "wide-column" databases. This is a classic, understandable mistake because of the name. Cassandra, HBase and Bigtable are described as "column-oriented", but internally they are row stores: they group rows by partition key and store columns as key-value pairs inside each row. They serve OLTP workloads with flexible schemas, not analytical aggregation. If someone proposes Cassandra for your dashboard, there's a misunderstanding.

It's not synonymous with "big data". You can have a perfectly legitimate OLAP workload with 5 GB of data. The pattern is defined by the shape of the queries, not the volume.

OLAP vs OLTP: the difference that explains everything

OLTP (Online Transaction Processing) is what PostgreSQL, MySQL or Oracle do: read and write a few rows very fast, with transactions and strong consistency. "Give me order 8821." "Decrement one unit from stock."

OLAP is the opposite on almost every axis:

	OLTP	OLAP
Storage	Row-oriented	Column-oriented
Read pattern	Few columns, few rows	Many rows, few columns
Write pattern	Single-row mutations, high frequency	Bulk inserts, mostly append-only
Target latency	Milliseconds	Sub-second to seconds
Concurrency	Thousands of users, simple queries	Tens or hundreds, complex queries
Schema	Normalized (3NF)	Star or wide denormalized
Typical question	"What's the state of order X?"	"How did orders evolve by region?"
Examples	PostgreSQL, MySQL, Oracle	ClickHouse, Snowflake, BigQuery, DuckDB

The two designs are incompatible by nature, and that's the reason both exist. An engine optimized to write one row with transactional integrity cannot also be optimized to scan four hundred million of them and reduce them to a number.

Why column orientation changes everything

The physical difference is easy to see. A row-oriented database stores this on disk:

[id:1|país:ES|ingresos:42|fecha:...] [id:2|país:MX|ingresos:17|fecha:...]

To sum ingresos you also have to read país, fecha and the other thirty columns you don't care about. A columnar database stores each column separately:

ingresos: [42, 17, 88, 12, ...]
país:     [ES, MX, ES, AR, ...]

If your table has 40 columns and your query uses 2, you read 5% of the data. And because adjacent values are of the same type and tend to repeat, they compress dramatically better: fewer bytes on disk, less I/O, faster queries.

On top of that, modern engines add vectorized execution (processing batches of columns with SIMD instructions instead of row by row), data skipping (sparse indexes and min/max statistics to skip whole blocks without reading them) and separation of compute and storage.

The history: from cubes to columnar (and why you should care)

This part looks like museum trivia, but it explains why the documentation you find online is full of concepts nobody uses anymore.

MOLAP, ROLAP, HOLAP

For decades, the standard OLAP taxonomy was this:

Model	Storage	Query path	Strength	Problem
MOLAP	Pre-aggregated cubes (Essbase, Analysis Services)	MDX → query the cube	Sub-second answers on predefined dimensions	Hour-long builds; storage explodes when adding dimensions; rigid schema
ROLAP	Relational tables (Snowflake, BigQuery, ClickHouse)	SQL → on-demand aggregation	Ad-hoc queries, arbitrary dimensions, flexible schema	More latency per query, unless the engine is very fast
HOLAP	A mix of both	Cube for summaries, SQL for detail	A legacy compromise inside proprietary stacks	The cost of operating two systems

MOLAP was king in the nineties and two-thousands. The idea of the OLAP cube was to pre-compute every possible aggregation over hierarchical dimensions (time, geography, product) so that querying was a lookup instead of a calculation.

It worked, with two brutal costs. The first: builds took hours, so your data was always from yesterday. The second, more insidious: the cube only answered the questions anticipated when it was designed. If an analyst wanted to cross two dimensions nobody had anticipated, you had to redesign and rebuild the cube. Weeks to answer a new question.

Why the cube died

The transition was gradual. Sybase IQ shipped a columnar engine in 1994. Vertica, built by the authors of C-Store, went commercial in 2007. Google published the Dremel paper —the basis of BigQuery— in 2010. ClickHouse was open-sourced in 2016.

By the late 2010s something decisive happened: the latencies that used to require pre-aggregating in a cube were now achievable on raw data. At that point the cube layer stopped being a benefit and became pure friction. All the cost (slow builds, rigidity, one more system to operate) in exchange for nothing.

Cubes haven't disappeared entirely: Essbase, Microsoft Analysis Services and MDX in Excel PowerPivot are still alive in large enterprises. But for any new project the answer is a columnar engine, and pre-aggregation, when needed, is handled with materialized views that are computed incrementally and queried like a normal table.

My opinion on why this matters even if you'll never touch a cube: when you search for information about OLAP you'll find a lot of material that takes the MOLAP paradigm for granted —talking about MDX, dimensions, builds— and that will lead you to design your system with a 2003 mental model. The taxonomy survives mostly in textbooks and certification exams. Take those concepts as history, not as a guide.

The vocabulary that did survive

Even though cubes died, the language for talking about analytical queries is still the same. Translated to modern SQL:

Roll-up (move up an aggregation level): go from daily to monthly sales.

SELECT toStartOfMonth(fecha) AS mes, sum(importe) AS ingresos
FROM ventas GROUP BY mes ORDER BY mes;

Drill-down (go to the detail): from the month to the days of that month.

SELECT fecha, sum(importe) AS ingresos
FROM ventas WHERE toStartOfMonth(fecha) = '2026-07-01'
GROUP BY fecha ORDER BY fecha;

Slice (cut one dimension with a fixed value): only Spain.

SELECT fecha, sum(importe) FROM ventas WHERE pais = 'ES' GROUP BY fecha;

Dice (subcube with several filters): Spain and Mexico, a specific category, one quarter.

SELECT pais, categoria, sum(importe)
FROM ventas
WHERE pais IN ('ES','MX') AND categoria = 'hardware'
  AND fecha BETWEEN '2026-04-01' AND '2026-06-30'
GROUP BY pais, categoria;

Pivot (rotate rows to columns): in modern OLAP this is conditional functions.

SELECT
    toStartOfMonth(fecha)         AS mes,
    sumIf(importe, pais = 'ES')   AS espana,
    sumIf(importe, pais = 'MX')   AS mexico,
    sumIf(importe, pais = 'AR')   AS argentina
FROM ventas GROUP BY mes ORDER BY mes;

Five concepts that in the nineties required a dedicated server and a language of their own, and today are five SQL queries.

The star schema: how OLAP data is modeled

The dominant logical model in analytics is the star schema: a fact table at the center, surrounded by dimension tables.

The fact table contains the measurable events, with many rows and few columns: a sale, a click, a sensor reading. The dimension tables contain the descriptive context: who that customer is, what category that product has, what region that store is in.

-- Fact table: grows without end
CREATE TABLE hechos_ventas (
    fecha       Date,
    producto_id UInt32,
    cliente_id  UInt32,
    tienda_id   UInt16,
    unidades    UInt32,
    importe     Decimal(12, 2)
) ENGINE = MergeTree
ORDER BY (tienda_id, fecha, producto_id);

-- Dimension: small and stable
CREATE TABLE dim_producto (
    producto_id UInt32,
    nombre      String,
    categoria   LowCardinality(String),
    marca       LowCardinality(String)
) ENGINE = MergeTree ORDER BY producto_id;

There's a nuance here that separates theory from practice. In a classic data warehouse, the star schema is dogma. In modern columnar OLAP engines, it often pays to denormalize and put categoria and marca directly in the fact table. It sounds like heresy —you're duplicating data— but because those columns compress brutally well with LowCardinality, the storage cost is almost nil and you save a JOIN on every query.

My rule: start by denormalizing what almost never changes (product category, store country) and keep as a separate dimension what changes often or what is large.

Practical use cases with real SQL

This is where OLAP stops being theory. These are the patterns you'll actually run into.

1. Product analytics: conversion funnels

The most common product case. You want to know how many people go from viewing a page to signing up and then to buying, and where they drop off.

In standard SQL this is a nightmare of CTEs and self-joins. In a modern OLAP engine it's a function:

SELECT nivel, count() AS usuarios
FROM (
    SELECT
        usuario_id,
        windowFunnel(86400)(
            timestamp,
            evento = 'pageview',
            evento = 'signup',
            evento = 'purchase'
        ) AS nivel
    FROM eventos
    WHERE fecha >= today() - 30
    GROUP BY usuario_id
)
GROUP BY nivel
ORDER BY nivel;

windowFunnel(86400) counts how many consecutive steps each user completed within a 24-hour window. The result gives you the shape of the funnel directly.

Why you need OLAP here: this query touches every event of every user for a month. In Postgres with tens of millions of rows, it's a sequential scan that brings down the production database.

2. Observability: logs, metrics and traces

Logs are the OLAP case par excellence, and a lot of people don't see it because they associate them with Elasticsearch. They're timestamped events, written a lot, read by aggregation and almost never updated.

SELECT
    toStartOfMinute(timestamp)                    AS minuto,
    servicio,
    count()                                       AS total,
    countIf(nivel = 'ERROR')                      AS errores,
    round(countIf(nivel = 'ERROR') / count(), 4)  AS tasa_error,
    quantile(0.95)(duracion_ms)                   AS p95,
    quantile(0.99)(duracion_ms)                   AS p99
FROM logs
WHERE timestamp >= now() - INTERVAL 3 HOUR
GROUP BY minuto, servicio
HAVING tasa_error > 0.01
ORDER BY minuto DESC;

Percentiles, error rates and grouping by minute in a single pass. Add a TTL so data older than 90 days deletes itself and you have a complete observability platform.

An economic note that changed my math: moving logs from a SaaS observability solution to a self-hosted OLAP database is one of the biggest cost reductions available for a small project. Log platform prices are calculated by ingested volume, and log volume only grows.

3. E-commerce and BI dashboards

The classic case: business metrics aggregated along multiple dimensions.

SELECT
    toStartOfWeek(fecha)                       AS semana,
    categoria,
    sum(importe)                               AS ingresos,
    count()                                    AS pedidos,
    uniq(cliente_id)                           AS clientes,
    round(sum(importe) / count(), 2)           AS ticket_medio,
    round(sum(importe) / uniq(cliente_id), 2)  AS ingreso_por_cliente
FROM hechos_ventas
WHERE fecha >= today() - 180
GROUP BY semana, categoria
ORDER BY semana DESC, ingresos DESC;

Notice uniq(): it uses HyperLogLog to count distinct values approximately, with a typical error below 1% and ridiculous memory consumption compared to uniqExact(). For a business dashboard, that approximation is perfectly acceptable and the performance difference is huge.

4. Time series and IoT

Sensors, infrastructure metrics, market prices. Data that arrives at high frequency and is queried at lower resolution.

SELECT
    toStartOfFifteenMinutes(timestamp)  AS intervalo,
    sensor_id,
    round(avg(temperatura), 2)          AS media,
    min(temperatura)                    AS minima,
    max(temperatura)                    AS maxima,
    count()                             AS lecturas
FROM metricas_sensores
WHERE timestamp >= now() - INTERVAL 7 DAY
  AND sensor_id IN (101, 102, 103)
GROUP BY intervalo, sensor_id
ORDER BY intervalo;

The pattern that makes this sustainable is progressive resolution reduction: you keep per-second readings for a week, per-hour for a year, and per-day indefinitely. With an aggregating TTL, this is automatic.

5. User-facing analytics (embedded analytics)

The most demanding case. It's not an internal dashboard that three people look at: it's a "Statistics" tab inside your product that all your customers query at once.

SELECT
    toDate(timestamp)     AS dia,
    count()               AS visitas,
    uniq(visitante_id)    AS visitantes,
    countIf(rebote)       AS rebotes
FROM eventos_web
WHERE tenant_id = {tenant:UInt32}      -- always first in the ORDER BY!
  AND timestamp >= {desde:DateTime}
GROUP BY dia
ORDER BY dia;

Here the key design point is that tenant_id is the first column of the sort key. That way each customer only scans their own data, and latency stays in milliseconds even if the table has billions of rows from all customers combined. It's the difference between a feature that scales and one that falls over when a hundred customers show up.

6. Retention and cohort analysis

How many users who signed up in a given month are still active months later.

SELECT
    cohorte,
    mes_relativo,
    uniq(usuario_id) AS usuarios
FROM (
    SELECT
        usuario_id,
        fecha,
        toStartOfMonth(min(fecha) OVER (PARTITION BY usuario_id)) AS cohorte,
        dateDiff('month', cohorte, toStartOfMonth(fecha))         AS mes_relativo
    FROM eventos
)
GROUP BY cohorte, mes_relativo
ORDER BY cohorte, mes_relativo;

Be careful where you put the OVER: it has to be attached to min(fecha), inside toStartOfMonth(). If you write toStartOfMonth(min(fecha)) OVER (...), ClickHouse interprets that toStartOfMonth is the window function and fails with Aggregate function with name 'toStartOfMonth' does not exist.

This kind of query —windows over the whole table— is exactly what sinks a transactional database and what a columnar one solves in a couple of seconds.

The main OLAP vendors in 2026

The market splits into five fairly sharp categories. I've ordered them by how they fit real projects, not by market share.

Real-time OLAP engines (open source)

Sub-second latency, continuous ingestion, designed for live dashboards and embedded analytics.

ClickHouse is the category reference. Columnar, a single binary, Apache 2.0 license, scales from a laptop to hundreds of nodes. It's the default option for self-hosting and has its own managed service (ClickHouse Cloud). If you want the detail, I have a complete ClickHouse guide.

Apache Druid has been doing real-time time-series analytics at scale for years. Very powerful for ingestion from Kafka, but with a high operational cost: six process types plus ZooKeeper.

Apache Pinot was born at LinkedIn to serve analytics to hundreds of millions of users. It's the most specifically designed for minimum latency with very high concurrency, with the same drawback of operational complexity.

StarRocks and Apache Doris are close cousins, with columnar MPP and a stronger JOIN optimizer than ClickHouse. A good choice if your schema is a normalized warehouse.

Managed cloud data warehouses

Zero operations, practically unlimited scale, consumption-based billing.

Snowflake popularized the separation of compute and storage and dominates the enterprise segment. Very mature in data governance and permissions. It charges by warehouse compute time.

Google BigQuery is truly serverless: you don't manage a cluster, you write SQL. It charges mainly by data scanned, which means badly written queries get expensive.

Amazon Redshift is the AWS ecosystem option. Older, requires more manual tuning, but integrates natively with the rest of Amazon's services.

Databricks SQL is the reference for the lakehouse architecture, uniting data engineering, machine learning and analytics on the same storage. Strong when there's unstructured data and AI pipelines in play.

Azure Synapse rounds out the hyperscaler trio for those already living in Microsoft.

My warning about this category, repeated from the comparison but relevant here: the consumption-based pricing model punishes exactly the pattern of interactive analytics, many small frequent queries. A dashboard with auto-refresh can generate a disproportionate bill. If you're on a tight budget, the variable invoice is a business risk, not just a technical one.

Embedded engines

No server, they run inside your process.

DuckDB is SQLite for analytics: pip install duckdb and you have a vectorized columnar engine with a PostgreSQL-compatible dialect. It reads Parquet and CSV directly. For datasets that fit on one machine, it's unbeatable in simplicity. MotherDuck is its cloud layer.

chDB is the ClickHouse engine embedded in Python, with a pandas-style API.

Specialized

kdb+ dominates high-frequency trading. Extremely fast on time series, with its own language (q) and a price to match its niche.

QuestDB is open source and oriented to time series with extended SQL.

TimescaleDB —from the company that in 2025 was renamed TigerData, although the open source extension keeps the name— turns PostgreSQL into a time-series database. If you already use Postgres and your volume is moderate, it's the lowest-friction migration possible.

Firebolt and SingleStore compete in the high-performance niche with cloud management.

Query engines over data lakes

Trino (formerly PrestoSQL) and Apache Spark SQL query data that lives in open formats like Apache Iceberg, Delta Lake or Parquet over object storage. They're not databases: they're engines that put SQL on top of your files.

This category is blurring the line between "data lake" and "OLAP database". Many columnar engines —ClickHouse among them— already read Iceberg and Parquet directly, which means you can query your lake without ingesting anything.

And the layers on top

It's worth mentioning that OLAP is the processing layer, not the presentation layer. Tableau, Looker, Power BI, Metabase and Grafana are BI tools that connect to an OLAP database to run the queries underneath. The database provides speed and structure; the BI provides the interface.

How to choose: my criterion in four questions

Translated into practical decisions.

Do your data fit on one machine (say, less than a few hundred GB)? Start with DuckDB. Zero operations, excellent performance and familiar SQL. It's the recommendation most people ignore and the one that turns out right most often.

Do you need a shared service with continuous writes and several readers? ClickHouse. It's the best balance point between performance and operational complexity that exists today, and it runs on a modest VPS.

Are you already living in PostgreSQL and your volume is moderate? TimescaleDB. You add an extension instead of a new service. The boring option is usually the right one.

Do you have a team, budget and a priority on zero infrastructure? Snowflake, BigQuery or Databricks. Set up cost alerts on day one.

And the question that precedes all of these: do you really need OLAP? If your biggest table has two million rows and your queries take 200 ms in Postgres, the answer is no. Adding an analytical system is adding a service to maintain, back up and monitor. The signal that the moment has arrived is concrete: when a central aggregation for your product takes more than five seconds and you've already tried to index it.

A reflection on why this matters more than it seems

For years I thought "OLAP" was corporate jargon for people who work with cubes in banks. It's a fairly widespread prejudice among developers, and it cost me dearly.

What changed my mind was realizing that almost every digital product generates event data, and almost none of it gets leveraged because the default infrastructure —a relational database— makes querying it painful. So it gets thrown away, or stored in a table nobody dares touch, or paid out to an analytics SaaS that gives you 10% of what you could have.

Adopting an OLAP database didn't change what I could measure. It changed the frequency with which I asked. When a query takes 40 seconds, you explore two hypotheses per session. When it takes 300 milliseconds, you explore twenty. And that difference in frequency is what ends up producing findings you weren't looking for.

That's the real argument in favor of OLAP, and it doesn't show up in any benchmark: it's not that queries go faster, it's that you ask more questions.

Conclusion

OLAP is neither a specific technology nor a fad: it's the category of systems built to answer questions over large volumes of data, and it's been around for thirty years under different names. What's changed radically is the implementation: from rigid cubes built overnight to columnar engines that aggregate over raw data in milliseconds.

For a small project today, that evolution means something very concrete: analytical capabilities that in 2005 required a dedicated team and a six-figure license today fit in a binary that runs on a twenty-euro-a-month VPS.

If you want to move from theory to something running, the shortest path is:

What ClickHouse is and how to install it — from zero to real queries in five minutes.
Alternatives to ClickHouse: a comparison — to choose with criteria between the options in this post.
Advanced optimization guide — materialized views, projections, codecs and TTL.

Frequently asked questions

What does OLAP mean?

Online Analytical Processing. It designates a category of analytical workloads —aggregations and groupings over millions of rows— and the systems built to serve them. The term was coined by E. F. Codd in 1993, and "online" means interactive, not related to the internet.

What's the difference between OLAP and OLTP?

OLTP is row-oriented and optimized to read and write a few records with millisecond latency. OLAP is column-oriented and optimized to scan and aggregate millions of rows. Their designs are opposite, which is why they coexist in the same architecture rather than replacing each other.

Is PostgreSQL an OLAP database?

No. PostgreSQL is OLTP: its row-based storage and B-tree indexes are designed for point lookups and small writes. Extensions like TimescaleDB, Citus or pg_duckdb add limited analytical capacity, but at scale the standard pattern is Postgres for writes plus an OLAP database for reads, connected via CDC.

Is an OLAP cube still useful in 2026?

Only in legacy environments. Columnar engines achieve equivalent latencies by computing aggregations on demand, and when pre-aggregation is needed, incremental materialized views are used instead of cubes.

Is Cassandra an OLAP database?

No. Cassandra, HBase and Bigtable are "wide-column" stores but row-oriented at the storage level. They serve OLTP workloads with flexible schemas, not analytical aggregation.

Can I use OLAP in a small project?

Yes, and it's getting easier. DuckDB runs as a library inside your application and ClickHouse as a single binary on a VPS. You no longer need enterprise infrastructure to start.

How much does an OLAP database cost?

Self-hosted open source options (ClickHouse, DuckDB, StarRocks) have no license cost; you pay for the server and your operating time. Managed services charge for compute and storage, with models that vary a lot: BigQuery charges mostly for data scanned and Snowflake for active compute time.