OLAP explained: cubes, slicing and dicing, and what replaced them

OLAP, online analytical processing, is a way of storing and querying data so it can be summarized across several dimensions at once: revenue by region, by product, by quarter, and any combination of the three. The data is arranged as a cube of measures that can be rolled up, drilled into, sliced, diced and pivoted. A transaction system records each sale; an OLAP system answers questions about all of them.

What OLAP is

OLTP (online transaction processing) records individual transactions: one order, one payment, written quickly and safely.

OLAP (online analytical processing) reads those transactions in bulk and summarizes them across dimensions: total revenue by region and quarter.

The two are tuned for opposite work. An order system writes thousands of small rows a minute and must never lose one. An analytical system reads millions of rows to return a few totals, and is judged on how quickly it does it. OLAP sits in the analysis layer of a business intelligence stack, between the warehouse and the reports.

The idea is older than the name. Jim Gray and colleagues described the cube as a database operator in Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals (Data Mining and Knowledge Discovery, 1997): the operator "treats each of the N aggregation attributes as a dimension of N-space," and every combination of values is a point in that space.

Cubes, dimensions and measures

• Facts are the rows being counted: invoice lines, orders, payments.
• Measures are the numbers on those rows that get aggregated: revenue, quantity, cost.
• Dimensions are the ways of cutting them: region, product, customer, time.
• Hierarchies are levels within a dimension: day, month, quarter, year; or customer, territory, region.

In a warehouse this is usually a star schema: one fact table in the middle holding the measures and a key for each dimension, joined to one table per dimension holding its attributes and hierarchy levels. A "cube" is the set of aggregates that schema can produce. It can have far more than three dimensions; the word is a metaphor.

Worked example: an 8-row sales cube

Two regions, two products, two quarters. Each fact row is already one cell of the cube.

The aggregates along each single dimension:

The five operations

Roll-up summarizes to a higher level by dropping a dimension or climbing a hierarchy. Rolling the eight cells up to region gives Northeast 385 and South 270. A real sales hierarchy, rep to territory to region, works the same way; see the sales roll-up hierarchy.

Drill-down is the reverse: Northeast 385 opens into Northeast Hardware 255 (120 + 135) and Northeast Services 130 (60 + 70).

Slice fixes one dimension to one value. Quarter = Q2 leaves a two-dimensional table of region by product: 135 + 70 + 80 + 55 = 340.

Dice selects a sub-cube with conditions on two or more dimensions. Region = Northeast and Product = Hardware, across Q1 to Q2, gives 120 + 135 = 255.

Pivot rotates which dimensions are rows and which are columns, with no change to the numbers. Regions as rows, quarters as columns:

That grid is exactly what an Excel PivotTable builds from a flat list; how to make a pivot table in Excel walks through it on a sales ledger.

The check

Every roll-up must return the grand total, whichever dimension you roll up along: 385 + 270 = 655, 425 + 230 = 655, 315 + 340 = 655. The slices across a dimension must add back to the total too: the Q1 slice (120 + 60 + 90 + 45 = 315) plus the Q2 slice (340) is 655. In the pivot, the row totals and the column totals reach the same 655 from two directions.

If any of those disagree, a row was dropped, double-counted or filtered in one place and not another. Run the check on every level before anyone reads the numbers.

MOLAP, ROLAP and HOLAP

The three classic designs differ in where the aggregates live. Microsoft's documentation for SQL Server Analysis Services multidimensional models defines them as partition storage modes:

• MOLAP (multidimensional): the aggregations and a copy of the source data are stored in a multidimensional structure on the OLAP server. Fastest to query; needs processing to refresh.
• ROLAP (relational): the aggregations stay in the relational database, in indexed views, and no copy of the source data is kept on the OLAP server.
• HOLAP (hybrid): aggregations in the multidimensional structure, detail left in the relational source. Summary queries behave like MOLAP; queries for detail go back to the database.

Analysis Services now offers two model types. Microsoft's comparison of tabular and multidimensional models says multidimensional mode is available only in SQL Server Analysis Services, not in Azure Analysis Services or Power BI semantic models, and that tabular models "are now more widely accepted as the standard enterprise-grade BI semantic modeling solution on Microsoft platforms."

OLAP in SQL: GROUP BY ROLLUP and CUBE

You do not need a cube server to get cube results. SQL has the operators built in. On the example table:

SELECT Region, Product, Quarter, SUM(Revenue) AS Revenue
FROM Sales
GROUP BY CUBE(Region, Product, Quarter);

Microsoft's GROUP BY reference describes CUBE as producing "all combinations of the specified columns (the full 2^n lattice) plus the grand total." Three dimensions give 2^3 = 8 grouping sets: (Region, Product, Quarter), the three pairs, the three single dimensions, and the grand total. On this data that is 8 + 4 + 4 + 4 + 2 + 2 + 2 + 1 = 27 rows, the last one being 655.

For one hierarchy, ROLLUP is the right tool, because it only climbs from right to left:

SELECT Year, Quarter, Month, SUM(Revenue) AS Revenue
FROM Sales
GROUP BY ROLLUP(Year, Quarter, Month);

That returns n + 1 = 4 grouping sets: month, quarter subtotals, year subtotals and the grand total.

What replaced the cube

Few teams now build a separate cube server for new work. The same ideas moved into other places:

• Columnar warehouses store each column separately and compress it, so summing one measure over billions of rows is fast enough without pre-built aggregates.
• In-memory data models load the star schema into memory and aggregate on demand. Analysis Services tabular models, Power BI semantic models and Excel Power Pivot all work this way; Microsoft describes Power Pivot as built on the same tabular model infrastructure.
• Semantic layers define each measure, its filters and its hierarchies once, so every report computes "revenue" the same way.

The vocabulary did not change. Facts, dimensions, hierarchies, roll-up and drill-down are still how analysts talk about the work, whatever runs underneath.

Where it goes wrong

• Non-additive measures summed across dimensions. Margins, rates and distinct counts cannot be added; they have to be recomputed from their parts at each level. Count-weighted and value-weighted shows why.
• Semi-additive measures summed over time. A bank balance or inventory level adds across regions but not across months; take the period-end value.
• Slowly changing dimensions. A customer moves from the South to the Northeast and all of their history rolls up to the new region unless the dimension keeps the old one.
• A cube refreshed overnight that users think is live. Today's orders are missing and nobody can see why the totals disagree with the order system.
• Two cubes with two definitions of the same measure. Gross versus net revenue, or bookings versus billings, and the meeting is spent reconciling them.

Roll-ups from your own files

Covirage does the roll-ups an OLAP cube would, by customer, product, region and period, directly from your uploaded files, with every level checked to sum to the grand total; the external AI model explains the result and never does the arithmetic. If you are choosing between a BI platform, a warehouse and something lighter, compare analytics tools, with sources on every claim, or read BI tool alternatives for teams without a data team. For the same reshaping in a query, see SQL PIVOT; for asking a database in plain English, text-to-SQL.

Product names are trademarks of their owners; Covirage is not affiliated with them. Facts about Microsoft products were checked on October 1, 2026 from Microsoft's own pages.