OLAP (Online Analytical Processing) is a technology designed for fast, multi-dimensional data analysis on large datasets, enabling business intelligence through structured queries across dimensions like time, geography, and product hierarchies. Unlike OLTP systems that handle transactional workflows, OLAP specializes in complex aggregations, drill-downs, and cross-tabulations that power dashboards, trend analysis, and strategic reporting. The foundation rests on dimensional modeling — organizing data into fact tables (measurable events) and dimension tables (descriptive context) — allowing analysts to slice business metrics by any combination of attributes without rewriting queries. Understanding OLAP means mastering not just the cube operations and schema patterns, but also the performance trade-offs between storage architectures (MOLAP pre-aggregates for speed; ROLAP queries on-demand for flexibility) and the subtle art of declaring grain, designing slowly changing dimensions, and choosing the right modern columnar engine for your workload.
What This Cheat Sheet Covers
This topic spans 15 focused tables and 87 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
Table 1: OLAP Data Model Core Components
Every dimensional design is built from this small vocabulary — facts and measures capture the numbers you want to analyze, dimensions and hierarchies give you the angles to slice them by, and the grain pins down exactly what a single fact row means. Get these building blocks straight and the star schema, surrogate keys, and everything downstream fall into place naturally.
| Component | Example | Description |
|---|---|---|
sales_amount, quantity_sold, profit | Numeric measures stored in fact tables representing business events at a declared grain. | |
SUM(revenue), AVG(order_value), COUNT(transactions) | • Aggregatable numeric values derived from facts • the quantitative data analyzed across dimensions | |
dim_date, dim_customer, dim_product, dim_geography | • Categorical attributes providing context for slicing and filtering facts • the "by" in analysis | |
Year → Quarter → Month → DayCountry → State → City | Parent-child paths within dimensions enabling drill-down and roll-up navigation. | |
One row = one order line item | • The most atomic level of detail captured in a fact table • defines what each row represents | |
Fact table at center, denormalized dimensions radiate outward | • Denormalized design with single-level dimension tables joined to central fact • optimized for query speed |