OLAP Explained: Cubes, Columnar Engines and Semantic Layers, a Practical Guide for Leaders and Engineers

OLAP, or Online Analytical Processing, is the discipline of answering business questions quickly over large volumes of historical data: "Which region grew fastest last quarter?", "Which product lines are dragging margin?", "How did this month compare with the same month last year?". Your ERP, billing and CRM systems are built to record business. OLAP is built to understand it.

Each section below starts with what leaders need to know, then goes deeper for engineers.

OLTP vs OLAP: two jobs, two kinds of system#

For leaders: your operational systems (OLTP, Online Transaction Processing) handle thousands of small, precise actions: create an invoice, update stock, post a payment. OLAP systems handle a smaller number of very large questions that scan months or years of those records. Running heavy analysis on the operational database slows the systems your staff and customers depend on.

For engineers:

Aspect	OLTP	OLAP
Typical query	Read or write a few rows by key	Scan and aggregate millions of rows
Data model	Normalised (3NF)	Denormalised (star or snowflake schema)
Storage layout	Row-oriented	Usually column-oriented
Optimised for	Concurrency, integrity, low-latency writes	Read throughput, compression, aggregation
Data freshness	Real time, current state	Historical, batch or streaming loads
Examples	PostgreSQL, MySQL, SQL Server for ERP/CRM	Snowflake, BigQuery, ClickHouse, Databricks SQL

The usual pattern is to move data from OLTP sources into an analytical store through a pipeline (ETL or ELT), model it, and point BI tools at the model. That pipeline is where most of the risk lives, and it is the core of our data engineering practice.

Cubes, dimensions and measures: the mental model#

For leaders: think of a cube as a spreadsheet with more than two sides. Sales can be viewed by product, by region and by month at the same time. The things you slice by are dimensions. The numbers you add up are measures.

• Dimensions: customer, product, branch, salesperson, date, channel.
• Measures: revenue, quantity, gross margin, order count, average ticket size.
• Hierarchies: dimensions often nest, such as Day to Month to Quarter to Year, or City to State to Region.

For engineers: a measure also needs an aggregation rule. Revenue is additive across every dimension. Closing stock is semi-additive: you can sum it across warehouses but not across months. Ratios such as margin percentage are non-additive and must be computed from their components after aggregation, never averaged. Wrong rules are a common reason two dashboards disagree.

The five classic operations#

• Slice: fix one dimension to a single value, for example only FY 2025-26.
• Dice: filter on several dimensions at once, for example Karnataka and Maharashtra, retail channel, Q2.
• Drill-down: move to finer detail, from quarter to month to day, or from region to branch.
• Roll-up: the reverse, aggregating detail into totals.
• Pivot: rotate the view, swapping rows and columns to compare products by region instead of regions by product.

In SQL, these map to WHERE filters, GROUP BY at different grains, and ROLLUP or CUBE.

MOLAP, ROLAP and HOLAP#

For leaders: these are three ways of storing a cube. The choice matters less today, but vendors still use the terms.

For engineers:

• MOLAP (Multidimensional OLAP): aggregates are precomputed and stored in a proprietary multidimensional format. Queries are fast, but cubes grow quickly with many dimensions and changes need a rebuild.
• ROLAP (Relational OLAP): data stays in relational tables and the OLAP tool generates SQL at query time. It is flexible and scales with the database, but performance depends entirely on how good that database is at aggregation.
• HOLAP (Hybrid OLAP): detail stays relational while selected aggregates are precomputed. A compromise, with the complexity of both.

The modern story is essentially that ROLAP won, because the relational engines underneath became fast enough to aggregate raw data on demand.

Star and snowflake schemas#

For leaders: a good schema lets non-technical users build their own reports and get the same answer for "revenue".

For engineers:

• Star schema: a central fact table (one row per sale, shipment or ticket, at a declared grain) joined to denormalised dimension tables (product, customer, date). Few joins, easy for BI tools and humans to understand.
• Snowflake schema: dimensions are further normalised, for example product to sub-category to category. Tidier reference data, more joins.

Declare the grain of every fact table, build a date dimension with the Indian financial year, and use slowly changing dimensions for attributes such as a customer moving city. On columnar engines, wide denormalised tables are usually fine.

How columnar and cloud engines changed OLAP#

For leaders: you no longer need to pre-build every report. Modern engines scan raw detail fast enough to answer most questions on the fly, and cloud services charge for compute as you use it.

For engineers: three shifts did the heavy lifting: columnar storage (read only the columns a query needs, compress them well), vectorised execution, and separation of storage from compute. The main options today:

• Snowflake: a managed cloud data warehouse with independent compute clusters (virtual warehouses) over shared storage. It is available in the AWS Asia Pacific (Mumbai) region, which helps with data-residency conversations.
• Google BigQuery: serverless warehousing, available in the Mumbai region (asia-south1). Its BI Engine adds an in-memory acceleration layer that caches frequently used data for dashboard queries.
• Databricks SQL: SQL warehouses on the lakehouse, querying open table formats, governed through Unity Catalog.
• ClickHouse: an open-source (Apache 2.0) columnar database known for fast aggregations, popular for event analytics, observability and product analytics. Self-hosted or managed.
• Apache Druid and Apache Pinot: real-time OLAP stores that ingest directly from streams such as Kafka and Kinesis and make events queryable almost immediately. Pinot in particular targets user-facing analytics, where dashboards are embedded in your product and served to many customers concurrently.
• DuckDB: an in-process analytical database with no server to run. It reads Parquet, CSV and JSON directly, including from object storage,, ideal for local analysis and modest workloads.

The cube has not disappeared; it lives on as materialised views and aggregate tables, used selectively. Choosing and sizing these platforms is a large part of our cloud and DevOps work.

Semantic layers: the cube, reborn#

For leaders: a semantic layer is a single, governed dictionary of business metrics. "Net revenue" is defined once, and every dashboard, spreadsheet and AI assistant uses that definition.

For engineers: a semantic layer holds dimensions, measures, joins and access rules as code, separate from any one BI tool. Warehouses now ship this natively:

• Snowflake semantic views are schema-level objects that define a semantic model. They became generally available in 2025.
• Databricks Unity Catalog metric views are defined in YAML, registered in Unity Catalog and queried like ordinary views, with measures referenced through a MEASURE() function. They support star schemas, and snowflake schemas on recent runtimes.
• Independent tools such as dbt's semantic layer or Cube serve several BI tools at once.

This matters more than ever for AI. A text-to-SQL assistant on raw tables guesses at joins and definitions; on a semantic layer it inherits agreed logic. If you are planning natural-language analytics, the semantic layer is the foundation; see our AI and LLM engineering services.

A practical decision guide for Indian SMEs and enterprises#

Start from the questions, not the tools.

If you are an SME with one ERP and a few reports#

• Use a read replica of your operational database, or a small managed warehouse, with a clean star schema.
• Consider DuckDB or PostgreSQL for modest volumes before committing to a large platform.
• Spend effort on agreed metric definitions first. Our business process consulting team often starts here.

If you are a growing company with multiple systems#

• Move to a cloud warehouse (Snowflake, BigQuery or Databricks SQL, usually aligned with your existing cloud).
• Adopt ELT with version-controlled transformations and automated data tests.
• Introduce a semantic layer before the number of dashboards becomes unmanageable.

If you need real-time or customer-facing analytics#

• Evaluate ClickHouse, Druid or Pinot for high-concurrency, low-latency queries on streaming data.
• Keep the warehouse for finance-grade history.

Checks for every organisation#

• Data residency: confirm the region and the Digital Personal Data Protection Act implications for personal data.
• Cost control: set auto-suspend, query limits and budgets from day one; pay-per-use can surprise you.
• Skills: pick a platform your team can operate, or plan for a partner who can.

For more on data and AI architecture, browse our AI and data articles.

FAQs#

Is OLAP the same as a data warehouse?#

Not exactly. A data warehouse stores and models analytical data; OLAP is the style of multidimensional querying it serves.

Do we still need OLAP cubes in 2026?#

Rarely as a separate cube server. Columnar engines aggregate detail fast enough for most needs, and semantic layers now provide the dimension-and-measure model cubes used to offer.

Can we run analytics directly on our ERP database?#

For light reporting on a read replica, yes. For heavy or cross-system analysis, use a separate analytical store.

Which is better, Snowflake, BigQuery or Databricks?#

There is no universal winner. It depends on your existing cloud, volumes, skills and cost model; a short proof of concept on your own data decides best.

Planning an analytics platform or untangling conflicting reports? Talk to VCNR Technologies about a practical OLAP roadmap for your organisation.