OLAP (Online Analytical Processing)

OLAP (Online Analytical Processing) refers to technologies and techniques designed to support fast, interactive analysis of large volumes of data.

OLAP systems are optimized for complex analytical queries that aggregate, filter and explore data across multiple dimensions. Typical OLAP workloads involve aggregation, filtering, grouping, drill-down, roll-up, and slicing and dicing of data.

Modern OLAP systems achieve fast analytical performance through techniques such as columnar storage, indexing, partitioning, caching, parallel processing, and - where beneficial - precomputed aggregations.

OLAP Cubes

An OLAP Cube (often referred to as a multidimensional cube) is a multidimensional representation of data that organizes measures along multiple dimensions.

For example:

Sales Amount analyzed by Product × Region × Time

A cube conceptually allows users to:

• Slice → filter the cube to a particular dimension value, such as Region = East
• Dice → filter across multiple dimensions, such as Region = East and Year = 2026
• Drill Down → move from a higher-level summary to more detail, such as Year → Quarter → Month → Day
• Roll Up → aggregate from detail to a higher level, such as City → State → Country

OLAP cubes may be pre-aggregated to provide extremely fast query performance, although modern OLAP systems can also perform many aggregations dynamically.

NOTE

Even though it's called a "cube" (implying 3 dimensions), OLAP cubes can contain more than three dimensions (often mathematically called a hypercube).

OLTP vs OLAP

OLTP (Online Transaction Processing) systems support the day-to-day operational activities of a business, such as creating orders, processing payments, updating customer records, and recording inventory movements. They are optimized for speed, data integrity, and high concurrency when writing data (inserting, updating, and deleting).

OLAP systems support analysis and decision-making over data collected from operational and other sources.

Characteristic	OLTP	OLAP
Primary purpose	Run the business	Analyze the business
Workload	Transactions	Analytical queries
Data	Current Operational data	Analytical, often historical and integrated
Queries	Short, frequent, predictable	Complex, long-running, ad hoc
Operations	INSERT / UPDATE / DELETE	Mostly SELECT / aggregation
Typical design	Highly normalized	Dimensional or analytical
Users	Applications / operational users	Analysts / BI users
Example	Order-entry system	Sales analytics platform

Not All DW/BI Systems are OLAP

Operational systems are generally OLTP systems, but not every data warehouse or BI system is an OLAP system.

A data warehouse can simply store and serve analytical data without using a dedicated OLAP engine or cube. Similarly, many modern DW/BI systems (like Snowflake, BigQuery, and Redshift) rely on massively parallel processing (MPP) and columnar relational structures rather than traditional multidimensional OLAP cubes. They provide OLAP-like analytical performance without requiring the strict structural constraints of a physical OLAP cube.

Star Schema vs OLAP Cubes

When designing a DW/BI system, a common point of confusion is how a Star Schema relates to an OLAP Cube.

A Star Schema and an OLAP Cube are not competing alternatives. They describe different aspects of an analytical system.

• Star Schema → A relational database design technique. It organizes data into Fact and Dimension tables within a standard RDBMS (like PostgreSQL or SQL Server). It describes how analytical data is modeled.
• OLAP Cube → A specialized multidimensional structure built specifically for analytical querying. It describes how analytical data is represented.

In a traditional Kimball architecture, the Star Schema acts as the foundation for the OLAP Cube. An OLAP engine can use the Star Schema as its underlying data source and expose its measures and dimensions for interactive analysis.

Historically, OLAP systems often transformed relational warehouse data into multidimensional cubes. Modern platforms may instead query the Star Schema directly using SQL and optimize the analytical workload without physically creating a traditional cube.

OLAP Technologies

OLAP implementations are generally categorized by how they physically store and process data.

MOLAP (Multidimensional OLAP)

Data is physically stored in an optimized multidimensional cube rather than a relational database. It offers the fastest query performance but requires data duplication and regular reprocessing.

• Examples: SSAS Multidimensional, Oracle Essbase.
• When to use: When query performance is the absolute highest priority and the dataset fits within your processing windows.

ROLAP (Relational OLAP)

Analytical queries are executed directly against the underlying relational database (e.g., Star Schema). It avoids data duplication and scales better for massive datasets, but queries can be slower.

• Examples: MicroStrategy, or traditional BI tools querying a DW directly.
• When to use: When data volume is too large for MOLAP, or direct access to the most granular data without duplication is required.

HOLAP (Hybrid OLAP)

A compromise combining MOLAP and ROLAP. Aggregations are stored in a fast multidimensional cube, while granular detail data remains in the relational database.

Tabular (In-Memory OLAP)

Stores data in a highly compressed, columnar, in-memory relational structure rather than a traditional multidimensional grid.

• Examples: SSAS Tabular, Power BI (VertiPaq engine).
• When to use: When you need blazing-fast performance without the design complexity of MOLAP, provided sufficient RAM is available.

Modern Cloud OLAP

Modern cloud data warehouses and lakehouse platforms can perform OLAP workloads directly using massively parallel processing, columnar storage, caching, partitioning, clustering, and other optimization techniques. The necessity of a physical OLAP cube has diminished in these environments.

• Examples: Snowflake, Google BigQuery, Databricks SQL.
• When to use: When you are operating in the cloud and want scalable OLAP-like analytical performance querying directly against your relational dimensional models.

Many organizations now query relational or dimensional models directly from cloud data warehouses and use a semantic layer to define business metrics, dimensions, relationships, and calculations. As a result, a traditional dedicated OLAP cube is not always necessary.

Semantic Layer

A semantic layer sits between the underlying data platform and BI tools, providing a business-friendly view of the data. It defines and standardizes metrics, dimensions, relationships, and business logic so that users and BI tools can analyze data consistently without needing to understand the underlying warehouse structure.

Examples include LookML in Looker and the dbt Semantic Layer.

OLAP Deployment Considerations

When evaluating whether to deploy physical OLAP cubes in your architecture, consider the following technical constraints and historical shifts:

• Performance vs Modern RDBMS: Traditionally, OLAP cubes offered significantly better query performance over RDBMSs. However, that distinction has become much less important with advances in computer hardware (such as massive in-memory databases) and RDBMS software (such as columnar databases).
• Rich Analytical Capabilities: OLAP cubes offer significantly richer analysis capabilities than RDBMSs, which are often saddled by the constraints of SQL. Languages like MDX or DAX can calculate complex time-intelligence or cross-dimensional metrics effortlessly.
  • Example: Calculating "Year-to-Date (YTD) Sales" in SQL requires complex window functions or self-joins. In DAX, it is a single, intuitive function: TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]).
• Portability & Vendor Lock-in: Because OLAP cube data structures differ wildly between vendors, it is difficult to port BI applications between different OLAP tools. This is a stark contrast to the relative ease of porting between relational databases that share a common SQL standard.
• Data Volatility (SCDs & Snapshots): Physical cubes struggle with updating volatile data.
  • They gracefully support transaction and periodic snapshot fact tables, but do not handle accumulating snapshot fact tables well because of severe limitations and inefficiencies when overwriting existing data.
  • Similarly, when processing Slowly Changing Dimension (SCD) Type 2 changes, the cubes often need to be reprocessed partially or totally.

What are MDX and DAX?

• MDX (Multidimensional Expressions) is a query language specifically designed for interacting with multidimensional OLAP cubes. While it looks somewhat similar to SQL, it is fundamentally optimized for querying multi-dimensional arrays rather than relational tables.
• DAX (Data Analysis Expressions) is a functional language used in Microsoft's modern in-memory data models (like Power BI and SSAS Tabular). Unlike MDX, DAX focuses on relational tables and columns, making it incredibly powerful for calculating dynamic metrics and time-intelligence across massive tabular datasets.

Think in Layers

A useful way to distinguish the concepts is:

• Star Schema → How is the analytical data modeled?
• OLAP → How is the analytical data processed and analyzed?
• OLAP Cube → How can multidimensional analytical data be represented?
• Semantic Layer → How are business metrics and analytical concepts defined consistently?
• BI → How is the analytical data presented to users?