Skip to content

OLAP — Analytical Processing & Real-Time Analytics Engines

Last reviewed: 2026-09-17

Purpose: Online Analytical Processing and data warehousing concepts, with Apache Pinot as the reference real-time OLAP engine.

Contents

Overview

OLAP (Online Analytical Processing) is the query workload built for aggregation over large datasets — scanning millions of rows to answer "how many, how much, over which period". It is the opposite of OLTP, which is built for many small, single-row transactions.

OLTP OLAP
Query shape Point reads/writes by key Scans, filters, GROUP BY aggregations
Data shape Current state, normalised Historical, denormalised (star/snowflake)
Storage layout Row-oriented Column-oriented
Optimised for Transactions per second, latency per row Throughput of scanned columns, compression
Typical engines PostgreSQL, MySQL, SQL Server Pinot, ClickHouse, Druid, BigQuery, Snowflake

This article covers dimensional modelling, columnar storage and real-time analytical architectures.

OLAP Concepts

  • Star Schema & Snowflake Schema — Fact tables vs dimension tables, designing for analytical queries
  • Columnar Storage — How column-oriented databases differ from row-oriented (OLTP) databases for aggregation-heavy workloads
  • Pre-aggregation & Rollups — Materialized views, pre-computed aggregations for faster queries
  • MOLAP vs ROLAP vs HOLAP — Multidimensional, Relational, and Hybrid approaches
  • Partitioning & bucketing — Time partitioning for retention and pruning, bucketing for join locality
  • Ingestion modes — Offline/batch segments vs real-time consuming segments

Real-Time Analytics Pipelines

The canonical real-time OLAP shape — an event stream, a streaming-ingest OLAP engine, and a thin query layer:

Application / services
        │  events
        ▼
   Event bus (Kafka)
        ▼
   OLAP engine — REALTIME table, consuming continuously
        │
        ├── SQL / REST query API
        └── BI & dashboards (Streamlit, Plotly Dash, Grafana, Superset)

Design points that make or break this pattern:

  • Real-time ingestion — The engine consumes events directly and makes them queryable within seconds, without a separate batch load. Apache Pinot does this with a REALTIME table wired to a Kafka topic.
  • Transform-at-ingest — Derive columns (timestamps, extracted JSON fields, computed dimensions) in the ingestion pipeline so queries stay simple and cheap.
  • Query language — Most real-time OLAP engines speak an SQL dialect: aggregation functions (count, distinctcount, filter), time bucketing (DATETRUNC), epoch conversion, and approximate functions for high-cardinality counts.
  • Two-tier storage — Recent segments are served from memory/SSD for low latency; older segments are compacted and moved to object storage. Retention is expressed per table, not per cluster.
  • Dashboard layer — Keep visualisation thin: the dashboard issues SQL/REST queries and renders. All heavy lifting belongs in the OLAP engine, otherwise every dashboard refresh becomes a load generator.

To-do

Status: incomplete. Still to write:

  • Expand "OLAP Concepts" beyond named bullets: explain star and snowflake schemas with an actual fact table plus dimension tables (the request/event shape used in PINOT.md can serve as the running example).
  • Explain columnar storage versus row storage — how column-wise layout and compression change aggregation cost — with one worked aggregation that shows the difference from an OLTP point read.
  • Write the MOLAP vs ROLAP vs HOLAP section the bullet list only names: which storage each uses, and which engines are representative of each.
  • Add the pre-aggregation, partitioning and bucketing detail: materialised views/rollups, time partitioning for retention, and how those appear in a real table configuration (Pinot's retentionTime* settings and its sorted/partitioned columns are the reference already documented in this folder).
  • Add a query example to "Real-Time Analytics Pipelines" (a time-bucketed aggregation over the event stream, of the kind Pinot runs) and a Related Articles link to PINOT.md — the article names Pinot as its reference engine but never shows it in use.