OLAP Operations Explained: Roll-Up, Drill-Down, Slice, Dice and Pivot With Examples

OLAP operations are the most repeated short question in the TU BSc CSIT Data Warehousing and Data Mining paper (CSC420) and in the BIT equivalent (BIT454). The question is almost always "Explain the OLAP operations with suitable examples" for 5 marks, or a 10-mark version that adds the data cube and the multidimensional model. The marks are easy if you can draw one small cube and show each operation on it, and easy to lose if you only give definitions.

This article builds one example cube and applies each of the five operations to it, then covers the related questions that get asked in the same paper. The unit notes and solved past questions are on the Data Cube Technology notes page.

The data cube

A data warehouse stores facts, which are numeric measures such as sales amount or units sold, organised by dimensions, which are the perspectives from which the facts are viewed, such as time, location and product. Each dimension has a concept hierarchy: time goes day, month, quarter, year; location goes city, district, province, country; product goes item, category, department.

A data cube is the multidimensional view of these facts. With three dimensions it is literally a cube; with more it is called a cuboid or a hypercube. A cell of the cube holds one measure for one combination of dimension values.

The example used below is a sales cube for a retailer in Nepal with three dimensions:

• Time: quarter (Q1, Q2, Q3, Q4) rolling up to year
• Location: city (Kathmandu, Pokhara, Biratnagar) rolling up to province
• Item: category (mobile, laptop, accessory) rolling up to department (electronics)

The measure is sales in lakh rupees. One slice of it, for Kathmandu, looks like this:

1. Roll-up (drill-up)

Roll-up summarises data by climbing up a concept hierarchy or by removing a dimension. Climbing the time hierarchy from quarter to year on the table above gives a single row:

Rolling up the location hierarchy from city to province would combine Kathmandu with the other cities in Bagmati Province. Removing the item dimension entirely would leave total sales per quarter per city. Roll-up answers questions such as "what were annual sales of mobiles in Kathmandu?" and always reduces the number of cells.

2. Drill-down

Drill-down is the reverse of roll-up: it moves from summarised data to more detailed data by stepping down a concept hierarchy or by adding a dimension. Drilling down from quarter to month on Q1 for mobiles in Kathmandu might give:

Drilling down on the item dimension from category to individual item would split "Mobile" into brands and models. Drill-down answers "which month drove Q1 sales?" and always increases the number of cells.

3. Slice

A slice selects one value on one dimension, producing a sub-cube with one fewer dimension. The Kathmandu table shown at the start is a slice of the full cube: it fixes location = Kathmandu and leaves a two-dimensional table of time against item. Slicing on time = Q1 instead would give a table of location against item for the first quarter.

The word to use in the answer is "selection on one dimension". The result is a lower-dimensional view that can be printed as an ordinary table.

4. Dice

A dice selects values on two or more dimensions, producing a smaller sub-cube with the same number of dimensions. For example: location in {Kathmandu, Pokhara}, time in {Q1, Q2}, item in {Mobile, Laptop}. The result is still a three-dimensional cube, but a 2 x 2 x 2 one instead of 4 x 3 x 3.

Slice is selection on one dimension; dice is selection on several. That one-line contrast is a frequent 1-mark question.

5. Pivot (rotate)

Pivot rotates the axes of the view to give a different presentation of the same data, without changing the values. Taking the Kathmandu slice and swapping its axes gives:

In a three-dimensional cube, pivot can also swap one of the displayed dimensions with the one held on the page axis. It is purely a reorientation for the analyst's convenience.

Summary table

Some textbooks also list drill-across, which queries more than one fact table, and drill-through, which goes from the cube down to the underlying detailed records in the operational database. Mention them in one line if the question asks for "other OLAP operations".

OLAP versus OLTP

The comparison is a regular companion question.

OLAP server types

ROLAP (relational OLAP) stores the warehouse in relational tables and computes the cube operations with SQL; it scales to large data but is slower. MOLAP (multidimensional OLAP) stores data in a pre-computed multidimensional array; it is fast for the operations above but limited in size and needs long load times. HOLAP (hybrid OLAP) keeps detailed data in relational tables and summaries in multidimensional storage to get both.

Answer outline

For 5 marks: define OLAP and the data cube in two sentences, then give each of the five operations with one line of definition and the small table or a phrase showing it on the sales cube. For 10 marks: add the dimensions, measures and concept hierarchies, the summary table, and either the OLAP versus OLTP comparison or the three server types, whichever the question hints at.

The cube itself, including the star and snowflake schemas it is built from, is covered in the Introduction to Data Warehousing unit. BIT students find the same material in Data Warehouse Fundamentals in BIT454. Past papers for the full subject are on the CSC420 page.