Product
What Is OLAP? OLAP vs OLTP, Cubes, and Modern Engines
13 min read · Last updated BY
OLAP (Online Analytical Processing) is a class of systems built for fast, multidimensional analysis of historical data across millions to billions of rows.
You slice sales by region and quarter, roll revenue up from store to country, and drill from year into day.
E.F. Codd, S.B. Codd, and C.T. Salley named it in a 1993 white paper commissioned by Arbor Software, Providing OLAP to User-Analysts: An IT Mandate, per Wikipedia's OLAP article.
DuckDB, MotherDuck, ClickHouse, Snowflake, BigQuery, Redshift, Druid, Pinot, StarRocks, and Databricks SQL are OLAP engines; PostgreSQL and MySQL are OLTP (Online Transaction Processing) systems. Those engines scan and aggregate; PostgreSQL and MySQL insert and look up rows.
Asking Postgres to answer those questions is like hauling bricks in a race car: it can move the load but it is the wrong vehicle. You want a truck: columnar storage, pre-aggregation or a vectorized scan, and a model that thinks in dimensions and measures.
Key takeaways
- OLAP answers analytical questions over history. OLTP records and looks up individual transactions. Most companies run both.
- An OLAP cube is a conceptual grid of dimensions (time, geography, product) and measures (sales, quantity). You rarely store a literal cube in 2026.
- MOLAP pre-aggregates into arrays. ROLAP generates SQL against relational tables. HOLAP mixes the two. Most modern engines are ROLAP-shaped: SQL on columnar storage.
- Cubes such as Oracle Essbase and Microsoft SSAS were succeeded for compute by columnar warehouses, materialized views, and lakehouse formats (Iceberg, Delta, DuckLake), and for modeling by semantic layers (Cube Store, AtScale, dbt MetricFlow).
- DuckDB is an in-process OLAP database: columnar storage, vectorized execution, and SQL for scans and aggregations. MotherDuck runs that engine in the cloud.
What is OLAP vs OLTP?
OLAP systems answer analytical questions over years of history with scans and aggregations; OLTP systems run day-to-day operations with millisecond inserts, updates, and point lookups on individual rows.
| OLAP | OLTP | |
|---|---|---|
| Purpose | Analyze the business | Run the business |
| Query pattern | Aggregations, scans, GROUP BY, slice/dice | Point lookups, short inserts/updates |
| Typical schema | Star / snowflake, wide facts | Normalized 3NF |
| Storage | Columnar (usually) | Row |
| Writes | Batch loads, appends | Many small transactions |
| Latency that matters | Seconds for a hard question, sub-second for a dashboard (< 1s) | Milliseconds per transaction (sub-50ms p99) |
| Data volume | Hundreds of gigabytes to petabytes | Gigabytes to terabytes |
| Concurrency | Tens to thousands of concurrent reads | Thousands of concurrent transactions |
| Indexing | Sparse primary keys, data skipping, materialized views | B-tree, hash, primary-key |
| Consistency | ACID at write; eventual consistency acceptable on read | ACID, serializable transactions |
| Data source | Historical, aggregated data from multiple sources | Real-time, transactional data from a single source |
| Example engines | DuckDB, ClickHouse, Snowflake, BigQuery | PostgreSQL, MySQL, SQLite |
You put a sale into OLTP. You ask "sales of running shoes in California vs New York, by quarter, last two years" of OLAP.
When should you not use OLAP?
OLAP is the wrong tool for point lookups, single-row updates, high-frequency small writes, and strict per-row transactional guarantees. Those jobs belong to OLTP systems such as PostgreSQL and MySQL.
A checkout, a SELECT … WHERE id = ?, and an UPDATE of one row's status are OLTP. Putting that work on DuckDB, ClickHouse, or Snowflake gives up the per-row transactional path OLTP is built for. Use both: OLTP to record the business, OLAP to analyze it.
How does OLAP work?
An OLAP database stores data column-by-column, executes queries in vectorized batches, skips large portions of storage with zone maps, and uses compaction or materialized views so repeated aggregations do less work.
| Component | What it does | How engines implement it |
|---|---|---|
| Columnar storage | Reads only the columns a query names | DuckDB stores tables in columns and reads Parquet the same way; Snowflake, BigQuery, and ClickHouse do too |
| Vectorized execution | Processes values in batches instead of one row at a time | DuckDB's vectorized engine is the in-process version of this path |
| Data skipping / zone maps | Skips row groups whose min/max cannot match the filter | DuckDB uses Parquet row-group statistics to skip data |
| Compaction or materialized views | Pre-aggregates or merges so repeated queries scan less | ClickHouse merge-tree and insert-time rollups; Druid and Pinot roll up at ingest. DuckDB re-scans instead — vectorized scans over columnar storage are fast enough that most workloads skip pre-aggregation |
On ClickBench — ClickHouse's own benchmark (100 GB, 43 queries, c6a.4xlarge) — both engines answer in under a second: DuckDB posts a 348ms median after a 2-minute load, ClickHouse a 148ms median after a 5-minute load. DuckDB gets there in-process, with no cluster to run — and the numbers come from a competitor's own suite.
What is an OLAP cube?
An OLAP cube organizes data as measures (sales amount, quantity, profit) stored at the intersections of dimensions (time, geography, product), so you can slice, dice, drill down, and roll up. Past three axes it is technically a hypercube; either way, it is a model.
- Dimensions are the categories: Time (year → quarter → month → day), Geography (country → region → city → store), Product (category → brand → SKU). Hierarchies are what make drill-down and roll-up possible.
- Measures are the numbers at the intersections: sales amount, quantity, profit.
Example: sales amount for Electronics in North America in Q4 2025.
What operations does OLAP support?
- Slice. Fix one dimension. Time = Q4 2025, then look at all products and regions.
- Dice. Fix several. Laptops in Europe, all time.
- Drill-down. Year → quarter → month.
- Roll-up. City → country.
- Pivot. Swap the axes of the view.
BI tools still expose these verbs. Underneath, a 2026 engine usually runs SQL against columns.
What are MOLAP, ROLAP, and HOLAP?
MOLAP (Multidimensional OLAP) pre-aggregates data into cube-style arrays, ROLAP (Relational OLAP) runs SQL against relational tables at query time, and HOLAP (Hybrid OLAP) combines the two.
| How it stores data | Query language | Strength | Weakness | |
|---|---|---|---|---|
| MOLAP | Pre-aggregated multidimensional arrays (Oracle Essbase, Microsoft SSAS) | MDX → cube lookup | Fast slice/dice on known queries | Hours-long builds, cube explosion, rigid dimensions |
| ROLAP | SQL against relational (star/snowflake) tables (Snowflake, BigQuery, ClickHouse, DuckDB) | SQL → on-demand aggregation | Scales with the warehouse; flexible schema | Complex queries compute on the fly |
| HOLAP | Summaries in MOLAP, detail in ROLAP | Both | Fast tops, drill to grain | Two stores to operate |
In 2026 the default is ROLAP with columnar files: Snowflake, BigQuery, Redshift, DuckDB, Databricks SQL. Druid and Pinot pre-aggregate at ingest; ClickHouse gets there with materialized views. HOLAP survives as "materialized view plus base table."
Which engines are OLAP databases in 2026?
The major OLAP databases in 2026 are DuckDB, MotherDuck, ClickHouse, Snowflake, BigQuery, Redshift, Apache Druid, Apache Pinot, StarRocks, and Databricks SQL, ranging from an in-process library to serverless cloud warehouses. Trino (or Starburst), Apache Doris, Firebolt, and legacy cube products such as Oracle Essbase and Microsoft SSAS complete the map. They fall into five categories: cloud data warehouse, real-time OLAP, embedded and single-node, lakehouse query engine, and legacy specialised.
| Engine | Category | Latency tier | Best fit | Deployment |
|---|---|---|---|---|
| DuckDB | Embedded & single-node | Sub-second on local data | Local analytics, notebooks, embed in an app | Library / CLI |
| MotherDuck | Embedded & single-node | Sub-second on local and cloud data | Interactive and embedded analytics, local-to-cloud | Cloud + local DuckDB |
| ClickHouse | Real-time OLAP | Sub-second to single-digit seconds | Event logs, observability, sub-second dashboards | Self-host or ClickHouse Cloud |
| Apache Druid | Real-time OLAP | Sub-second to single-digit seconds | High-ingest event streams | Self-host or Imply |
| Apache Pinot | Real-time OLAP | Sub-second to single-digit seconds | Extreme QPS in product analytics | Self-host or StarTree |
| Apache Doris | Real-time OLAP | Sub-second to single-digit seconds | Real-time dashboards | Self-host or cloud |
| StarRocks | Real-time OLAP | Sub-second to single-digit seconds | Lakehouse SQL, dashboard concurrency | Self-host or cloud |
| Snowflake | Cloud data warehouse | Single-digit seconds to minutes | Enterprise BI and batch ETL | SaaS |
| BigQuery | Cloud data warehouse | Single-digit seconds to minutes | GCP, ad-hoc SQL, zero cluster ops | SaaS |
| Redshift | Cloud data warehouse | Single-digit seconds to minutes | AWS-native BI | Managed / Serverless |
| Firebolt | Cloud data warehouse | Single-digit seconds to minutes | Cloud-warehouse SQL | SaaS |
| Databricks SQL | Cloud data warehouse | Single-digit seconds to minutes | BI on the same Delta Lake as ML | Cloud |
| Trino | Lakehouse query engine | Single-digit seconds to minutes | Federated queries across lakehouse + warehouses | Self-host or Starburst |
| Essbase / SSAS | Legacy specialised | Sub-second after hours-long builds | Financial close, Excel and enterprise BI cubes | On-prem / Azure (SSAS) |
PostgreSQL is an OLTP database. pg_duckdb can run OLAP inside Postgres; that extension leaves Postgres an OLTP system with an OLAP engine attached.
Why is OLAP still relevant in 2026?
OLAP is how teams answer multidimensional questions over history in 2026. Cube shaped products still ship; they are no longer the default architecture.
| System | Status | Primary use |
|---|---|---|
| Oracle Essbase | Active, legacy customer base | Financial close, regulatory reporting |
| Microsoft Analysis Services (SSAS) | Active, on-prem and Azure | Enterprise BI, Excel PowerPivot integration |
| Pentaho Mondrian | Maintenance | Open-source MDX-over-relational layer |
| Apache Kylin / Kyligence | Active, niche | Cube-on-Hadoop pre-aggregation |
| Atoti | Active, niche | In-memory cubes for financial services |
Five dimensions of 100 members each produce 10 billion pre-aggregated cells (100^5). Production cubes routinely run 8 to 20 dimensions.
A compact timeline: Express 1970, Essbase 1992, Codd's paper 1993, Sybase IQ 1994, MDX 1997, SSAS 1998, Vertica 2007, Dremel paper 2010, DuckDB 2019.
What changed:
- Columnar storage (Sybase IQ 1994, Vertica 2007, Dremel paper 2010) replaced most pre-built cubes for scan-heavy SQL. Same reason OLAP was fast: you only read the measures and dimensions you asked for.
- Materialized views and insert-time rollups (ClickHouse merge-tree; Snowflake and BigQuery ship them managed) are the modern pre-aggregation.
- Lakehouse table formats (Iceberg, Delta, DuckLake) version the fact table so many engines can query one copy.
- SQL replaced MDX (1997) for almost everyone.
- Streaming ingest (Druid, Pinot, ClickHouse) made the batch-updated cube optional.
The storage-layout picture still applies:
If the question is multidimensional, historical, and aggregate, you are doing OLAP, even if the vendor never prints the word.
What replaced the OLAP cube?
Columnar databases took over the cube's compute job; semantic layers took over its modeling job.
Essbase (Arbor Software, 1992; now Oracle Essbase) and Microsoft's OLAP Services (1998, renamed Analysis Services in 2000) were the MOLAP products teams actually ran. Columnar warehouses, materialized views, and lakehouse table formats (Iceberg, Delta, DuckLake) replaced the pre-aggregated cube file for most analytical SQL.
The model layer moved to semantic layers. Cube ships Cube Store, a Rust distributed OLAP engine with pre-aggregations. AtScale translates OLAP-style queries into warehouse SQL. dbt MetricFlow was open-sourced under Apache 2.0 at Coalesce in October 2025 and aligned with the Open Semantic Interchange initiative alongside Snowflake and Salesforce.
Why do AI agents need OLAP?
An agent asking many exploratory questions is an OLAP workload with a latency budget.
On Snowflake's internal 150-question evaluation, Cortex Analyst reached ~90% text-to-SQL accuracy against ~51% for single-shot GPT-4o — roughly 2x, and the gap comes from a semantic model plus multi-step agentic generation rather than a better base model. Apache Doris documented a Tencent deployment where LLM inference took over ten seconds per response while the OLAP query underneath returned in milliseconds — the model, not the engine, is the bottleneck, and the engine has to return aggregations while the model is still thinking. See Best analytics database for LLM and AI agents.
How do you choose an OLAP database?
Pick an OLAP database by ranking five dimensions against the actual workload: latency requirement, ingest model, deployment shape, data volume, and concurrency.
- Latency requirement. Sub-second on streaming events → real-time OLAP (ClickHouse, Druid, Pinot). Seconds to minutes on warehouse SQL → a cloud data warehouse.
- Ingest model. Continuous appends → real-time OLAP. Batch or micro-batch → warehouse or lakehouse engine.
- Deployment shape. In-process or laptop → DuckDB. Local-to-cloud → MotherDuck. Fully managed SaaS → Snowflake, BigQuery, or Redshift. SQL in place on the lake → Trino or Starburst.
- Data volume. A 100 GB analytical extract fits DuckDB on a c6a.4xlarge (ClickBench). Shared lakehouse scale → a warehouse or DuckLake.
- Concurrency. A handful of analysts → DuckDB, MotherDuck, or a warehouse. Extreme user-facing QPS → Pinot or ClickHouse. Isolated tenants → MotherDuck Ducklings.
OLAP database vs data warehouse vs OLAP cube vs wide-column store
An OLAP database is the engine that answers multidimensional analytical queries with scans and aggregations. DuckDB, ClickHouse, and Snowflake run this workload.
A data warehouse is a system for storing integrated historical data from multiple sources. Snowflake, BigQuery, and Redshift are warehouses that run OLAP; DuckDB is an OLAP engine that can query warehouse-style files without being a warehouse.
An OLAP cube is a pre-aggregated model of dimensions and measures, not the engine. Oracle Essbase and Microsoft SSAS shipped cubes; DuckDB and ClickHouse answer the same class of question with SQL on columns.
Cassandra and HBase are NoSQL key-value stores, not columnar OLAP databases. They optimize row-key lookups and wide sparse records. They do not provide the scan-and-aggregate path that defines OLAP.
What is OLAP used for?
OLAP is used for multidimensional questions over history: retail comparisons, product dashboards, financial roll-ups, and BI that is too heavy for OLTP.
- Retail / e-commerce. Databricks walks a retail scenario where sales show an 8% decline overall and a 22% decline in the West region — slice by region, roll up by quarter.
- SaaS product analytics. A customer-facing dashboard (retention of users who adopted a feature in Q1) is OLAP with a latency SLO — the real-time tier targets sub-second responses on streaming ingest. MotherDuck isolates each tenant on its own Duckling.
- Finance. Oracle Essbase is still used for financial close and regulatory reporting. The same roll-up pattern — millions of loans to region-level risk, then drill into a branch — is what SSAS and modern ROLAP engines serve.
- Reporting and BI. Microsoft SSAS remains active for enterprise BI and Excel PowerPivot. Cloud warehouses (Snowflake, BigQuery, Redshift) run the same dashboards when the OLTP database cannot; Snowflake still has a 60-second warehouse-resume minimum.
- A consistent analytical model. dbt MetricFlow (open-sourced under Apache 2.0 in October 2025, aligned with Open Semantic Interchange alongside Snowflake and Salesforce) keeps the same measure definitions for every report.
Further reading
Columnar storage is the layout most OLAP engines use: Columnar storage guide. For agent workloads see Best analytics database for LLM and AI agents. For embedded analytics warehouses see Best cloud data warehouses for embedded analytics. Platform comparison: Top 10 data warehouse platforms for 2026.
Start using MotherDuck now!
FAQS
OLAP (Online Analytical Processing) is software for fast, multidimensional analysis of historical data across millions to billions of rows. Codd, Codd, and Salley defined the term in a 1993 white paper commissioned by Arbor Software. You slice, dice, drill, and roll up measures such as sales across dimensions such as time, product, and region.
OLAP runs large aggregations over history: sales by month, page views per day. OLTP stores and fetches individual rows: insert an order, look up a user by email. OLAP is usually columnar and answers in sub-seconds to seconds; OLTP is usually a row store and answers in milliseconds (sub-50ms p99). Most organizations run both.
OLAP is the right tool when a question aggregates many rows across a few dimensions. Common cases are business intelligence, financial and budget analysis, marketing and sales reporting, supply chain and inventory, product analytics, and forecasting. Databricks' retail example — an 8% decline overall, 22% in the West region — is that shape of question.
Cloud warehouses, real-time OLAP engines, embedded columnar databases, and lakehouse query engines all offer OLAP. The 2026 roster includes DuckDB, MotherDuck, ClickHouse, Apache Druid, Apache Pinot, Apache Doris, StarRocks, Snowflake, BigQuery, Redshift, Firebolt, Databricks SQL, and Trino. PostgreSQL and MySQL are OLTP systems built for point lookups and short transactions.
Yes. DuckDB is an in-process columnar SQL engine with vectorized execution, the OLAP shape for scans and aggregations. MotherDuck runs the same engine in the cloud. On the ClickBench benchmark (100 GB, 43 queries, c6a.4xlarge), DuckDB recorded a sub-second 348ms median and loaded the dataset in 2 minutes.
Columnar warehouses, materialized views, lakehouse tables, and semantic layers replaced cubes such as Oracle Essbase and Microsoft SSAS. You still model dimensions and measures. Cube Store, AtScale, and dbt MetricFlow (Apache 2.0, October 2025) now hold the modeling job; SQL on Parquet or a warehouse table is the 2026 compute default.
Yes. Product dashboards are OLAP with a latency SLO. ClickHouse and Pinot specialize in high QPS on event streams; real-time OLAP targets sub-second to single-digit seconds. MotherDuck isolates each tenant on its own Duckling so one customer's query does not contend with another's.
Yes. On ClickBench (100 GB, 43 queries, c6a.4xlarge), DuckDB loaded in 2 minutes and recorded a 348ms median. MotherDuck adds a shared catalog, read scaling, and DuckLake for lakehouse tables. A single DuckDB process is the local and embedded tier; MotherDuck is how that engine is shared.
MotherDuck costs less than a traditional warehouse for analytical SQL, with no minimum-billing floor. Columnar OLAP engines avoid scanning every column of every row, which is the tax OLTP databases pay. MotherDuck bills per second with no 60-second floor; Snowflake and Redshift Serverless both meter with a 60-second minimum charge.
No. PostgreSQL is an OLTP database built for millisecond inserts, updates, and point lookups on individual rows. pg_duckdb can run OLAP inside Postgres by using DuckDB for analytical SQL; that extension leaves Postgres an OLTP system with an OLAP engine attached. For scans and aggregations over history, use an OLAP engine.
