Computer Engineering is coming soon

We're still writing and reviewing the notes for every subject, and we'll open it when every subject is ready. Meanwhile you can practise aptitude, ask Aria about any subject, or see the branches that are ready.

OLAP operations

OLAP operations let engineers explore and summarise multi-dimensional data (data cubes) using operations like roll-up, drill-down, slice, dice and pivot; these notes define each operation, give the algebra and formulae, worked examples, common exam mistakes and quick checks.

Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.

Why it matters

Analytical systems and business intelligence use OLAP operations to answer questions such as “total sales by state this quarter” or “compare product categories over time”. Engineers use OLAP operations when designing data warehouses, writing aggregate queries (SQL ROLLUP/CUBE), and building dashboards that let analysts navigate and summarise large fact tables quickly.

Key ideas

Start with the basic words before using them.

  • Dimension — a named attribute or set of attributes used to view facts (for example, Time, Product, Location). A dimension usually has a hierarchy of levels (for Time: Day → Month → Quarter → Year).
  • Measure — a numeric quantity stored in the fact table that we want to aggregate (for example, Sales in currency units). Measures are what OLAP aggregates (SUM, COUNT, AVG, MIN, MAX). Aggregation functions are categorised as distributive (SUM, COUNT, MIN, MAX), algebraic (AVG), or holistic (MEDIAN). Measures can be additive across all dimensions, semi-additive across some (e.g., inventory over time), or non-additive (e.g., ratios); this affects which roll-ups are valid.
  • Cell — an entry in a data cube defined by a specific value for every dimension (for example, {Time=Jan-2025, Product=Pen, Location=Mumbai}). The cell contains the measure values (for example, sales amount).
  • Cuboid — a version of the cube aggregated to a choice of level for each dimension. For n dimensions, a cuboid corresponds to a particular selection of levels (possibly the special ALL level) for each dimension.
  • Data cube — the set of all cuboids formed by aggregating the base fact table along all combinations of dimension levels. Logically, it organizes all such aggregates; in practice, only some cuboids may be materialized (precomputed) for fast OLAP.

Core OLAP operations (define then explain):

  • Roll-up — aggregate (move) from a finer-grained level to a coarser-grained level along a dimension hierarchy. Exact definition: given dimension D with levels L0 (base) → L1 → ... → Lk (coarsest), a roll-up from Li to Lj (j>i) replaces values at Li by their parent groupings at Lj and applies an aggregation operator (usually SUM). Example action: City → State.

    • Assumption: hierarchy is well-formed (each lower-level member maps to exactly one higher-level member).
    • Result: a cuboid where that dimension uses level Lj; other dimensions unchanged.
  • Drill-down — the inverse of roll-up: move from coarser to finer level, showing more detail. Formally: replacing a level Lj by a lower level Li (i<j) and showing atomic groups. It may require accessing base facts if aggregates for the finer level are not precomputed.

  • Slice — select a single value for one dimension producing a (n-1)-dimensional sub-cube. For example, slicing Time=2025 yields a cube of Product × Location for that year.

  • Dice — select a range or set of values on two or more dimensions (a multidimensional sub-cube). Example: {Time in {Jan, Feb}, Product in {Pen, Pencil}}.

  • Pivot (Rotate) — change the presentation orientation of a cuboid by swapping which dimensions are shown on rows, columns, or pages. It does not change data, only the view.

Algebraic properties and exact statements commonly asked in exams:

  1. Closure: Roll-up and drill-down produce another cuboid in the cube lattice (changing aggregation level). Slice and dice produce a sub-cube (a subset of cells of an existing cuboid), not necessarily an entire cuboid at a level. Pivot changes only presentation (orientation) without changing the underlying cuboid.

  2. Cuboid count formula (derivation): for a cube with n dimensions, treat each dimension i as having Li available levels (counting distinct hierarchical levels at which you may aggregate, excluding the special ALL aggregate). For each dimension you may choose any of its Li levels or choose ALL. So number of possible cuboids = product over i of (Li + 1). Derivation step-by-step: for each dimension choose one of (Li + 1) options independently; multiply because choices are independent by rule of product.

  3. For binary presence/absence (only base level and ALL), Li = 1 for each dimension and cuboids = 2^n; this is the common simple case often given in exam statements.

What to draw (diagrams required in exams):

  • To show a cuboid lattice for 3 dimensions (A, B, C): draw nodes for each cuboid; label a node by the set of dimensions that are aggregated (or by the levels chosen). Place the full-detail cuboid at the bottom and the ALL cuboid at the top; connect nodes by edges representing one-step roll-up (aggregation on one dimension). The lattice will have 2^3 = 8 nodes in the simple case.

When using SQL: know that ROLLUP, CUBE and GROUPING SETS implement aggregation across multiple levels. They are language-level ways to compute many cuboids in one query. Use GROUPING or GROUPING_ID to identify super-aggregate rows and distinguish NULLs that mean 'ALL' from actual NULL data values.

Formulas

#cuboids = ∏_{i=1..n} (L_i + 1) — where L_i = number of hierarchical levels available for dimension i (excluding the special ALL), unit: count, applies when each dimension's levels are independent.

#cells_in_cuboid = ∏_{i=1..n} |Dom_i(level_i)| — where |Dom_i(level_i)| is the number of distinct values of dimension i at the chosen level; unit: count, applies when computing storage or size of a cuboid.

#cuboids_simple = 2^n — special case when each dimension has only base level and ALL, n = number of dimensions, unit: count.

SQL (syntax examples to remember): SELECT D1, D2, SUM(measure) FROM fact_table GROUP BY ROLLUP(D1, D2); SELECT D1, D2, SUM(measure) FROM fact_table GROUP BY CUBE(D1, D2); — SQL syntax, applies in relational OLAP (ROLAP).

Closure (informal equation): OLAP_op(cube) → cuboid/sub-cube — applying an OLAP operation on a cube yields another cuboid (for roll-up/drill-down) or a sub-cube (for slice/dice).

Worked examples

Example 1 — small numeric roll-up and slice (step-by-step): Fact table (Sales in ₹):

  1. {Time=Jan, Product=Pen, City=Mumbai, Sales=100}
  2. {Time=Jan, Product=Pen, City=Pune, Sales=60}
  3. {Time=Jan, Product=Pencil, City=Mumbai, Sales=40}
  4. {Time=Feb, Product=Pen, City=Mumbai, Sales=120}
  5. {Time=Feb, Product=Pencil, City=Pune, Sales=30}
  6. {Time=Feb, Product=Pen, City=Pune, Sales=50}

Step A — Roll-up City → State (assume Mumbai and Pune both map to Maharashtra): Formula before numbers: SUM over cities grouped by (Time, Product, State). Compute groups (State=Maharashtra):

  • Time=Jan, Product=Pen: 100 + 60 = 160
  • Time=Jan, Product=Pencil: 40
  • Time=Feb, Product=Pen: 120 + 50 = 170
  • Time=Feb, Product=Pencil: 30 Final answers (state-level): {Jan, Pen, Maharashtra, 160}, {Jan, Pencil, Maharashtra, 40}, {Feb, Pen, Maharashtra, 170}, {Feb, Pencil, Maharashtra, 30} (units: ₹)

Step B — Slice Time = Jan from the rolled-up cuboid: keep only Jan rows. Result: two rows: Pen=160, Pencil=40 (₹). Bold final numbers: 160 ₹, 40 ₹.

Example 2 — cuboid count with hierarchies (derivation and numbers): Given 3 dimensions with hierarchical levels available (excluding ALL): Time: L1=3 (Day, Month, Year), Product: L2=2 (Product, Category), Location: L3=2 (City, State). Apply formula: #cuboids = (3+1)·(2+1)·(2+1) = 4·3·3 = 36. Step-by-step: for Time choose one of {Day, Month, Year, ALL} (4 choices), for Product one of {Product, Category, ALL} (3), for Location one of {City, State, ALL} (3). Multiply to get 36 possible cuboids (each cuboid is a unique combination of chosen levels).

Example 3 — trace of dice and pivot (short): Start with full cube. Dice: Time in {Jan, Feb}, Product in {Pen, Pencil}, Location in {Mumbai, Pune} → this is a 3-d sub-cube with the same measure values as in Example 1 but limited to those values. Pivot: show Product on rows and Location on columns; no numerical recomputation, just reorientation.

Common mistakes

  • Forgetting the ALL level when counting cuboids or counting levels twice.
  • Using Li that includes ALL in the cuboid count formula (double-count); be explicit whether Li includes ALL.
  • Thinking slice is the same as dice — slice fixes one dimension to a single value; dice selects ranges/sets on multiple dimensions.
  • In numeric examples, failing to state or check the hierarchy mapping (e.g., which city maps to which state) before aggregating.
  • Not labelling axes or nodes in the cuboid lattice diagram.

For your exam

Kinds of questions:

  • Definitions: define data cube, cuboid, roll-up, drill-down, slice, dice, pivot.
  • Short notes: explain cuboid lattice and closure property.
  • Derivations: derive #cuboids formula from first principles and the simple case 2^n.
  • Numericals: perform roll-up/aggregation on a small fact table (show groups and sums), compute number of cuboids, compute size of a cuboid.
  • Programs/SQL: write queries using ROLLUP, CUBE or GROUPING SETS and explain results.
  • Diagrams: draw a cuboid lattice for 2 or 3 dimensions and label nodes and edges.

What a full-marks answer includes:

  • Precise definitions of terms used (bold them if asked), clear assumptions (hierarchies well-formed), and step-by-step derivation where required.
  • For numericals: show the grouping key, formula used (SUM or other), each intermediate sum, units, and final answers.
  • For diagrams: clean labels, the position of ALL and base cuboids, edges showing single-dimension roll-ups.
  • For SQL tasks: exact SELECT ... GROUP BY ROLLUP/CUBE and explanation of which grouping sets are produced.

Quick check

  1. What operation would you use to get more detailed data from Year-level to Month-level?
  2. For 4 dimensions each with only base and ALL levels, how many cuboids exist?
  3. Which OLAP operation selects Time=2025 and Product in {A, B} and returns a sub-cube?

Answers: 1) Drill-down. 2) 2^4 = 16. 3) Dice (or equivalently slice+filter if Time fixed and Product set).

Finished this topic? Mark it so your progress, study plan and readiness keep up.

Stuck on something here?