OLAP (Online Analytical Processing)

Definition

OLAP (Online Analytical Processing) refers to database systems and workloads optimized for complex analytical queries — aggregations, multidimensional analysis, and large scans over historical data — typically used for business intelligence and reporting rather than day-to-day transactions.

Overview

OLAP systems are designed to answer questions that span large volumes of data, such as 'what were total sales by region and product category over the last three years,' rather than the narrow, fast lookups that characterize OLTP. Because these queries often scan and aggregate millions or billions of rows, OLAP databases prioritize read throughput on wide scans and aggregation efficiency over the low-latency, high-concurrency point transactions that OLTP systems optimize for. Data in OLAP systems is commonly organized using dimensional modeling — star or snowflake schemas with central fact tables (holding measurable events like sales) linked to dimension tables (holding descriptive attributes like time, product, or customer) — which makes multidimensional slicing, dicing, drilling down, and rolling up intuitive and efficient. Many OLAP systems are columnar rather than row-oriented, since analytical queries typically read a small number of columns across many rows (e.g., summing a 'revenue' column), and columnar storage lets the engine read only the relevant columns from disk, apply strong compression, and use vectorized execution — the opposite of OLTP's row-oriented layout, which is optimized for reading or writing whole records at once. Modern OLAP platforms include cloud data warehouses like Snowflake, Google BigQuery, and Amazon Redshift, as well as open-source engines like ClickHouse, Apache Druid, and DuckDB, alongside older on-premises OLAP cube technologies like Microsoft Analysis Services. Because OLTP and OLAP have conflicting optimization goals, data typically flows from OLTP systems (where it's created) into OLAP systems (where it's analyzed) via ETL or ELT pipelines, often landing first in a data lake or staging area before being transformed into the dimensional models that OLAP tools query. This separation lets each system be tuned for its own workload without one degrading the other, and it underlies the classic data warehouse architecture used throughout business intelligence and analytics.

Key Concepts

• Optimized for complex, large-scale analytical queries and aggregations
• Commonly uses dimensional modeling (star/snowflake schemas) with fact and dimension tables
• Often built on columnar storage engines for efficient scans and compression
• Supports multidimensional operations: slicing, dicing, drilling down, rolling up
• Prioritizes read throughput over transactional write latency
• Populated via ETL/ELT pipelines from OLTP source systems
• Used heavily for business intelligence, dashboards, and reporting
• Scales to querying billions of historical rows efficiently

Use Cases

Frequently Asked Questions

Related in Database