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.

Data cubes and OLAP

Data cubes and OLAP let engineers pre-aggregate and explore multidimensional data quickly by organizing measures along dimensions and performing operations such as roll-up, drill-down, slice, dice and pivot.

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

Data cubes and OLAP are used by engineers and analysts to answer fast, interactive business questions (for example: "total sales by region and quarter"). They let reporting systems pre-compute and store aggregated values so queries on large historical data run fast, enabling dashboards, ad-hoc analysis and decision support.

Key ideas

Start from first principles and build up definitions.

  • Relation / Table: a set of rows (tuples) with named columns (attributes). We assume a read-only analytic copy (data warehouse) rather than a transactional system. In a typical star schema, a central fact table stores numeric measures and foreign keys to surrounding dimension tables; dimension tables store descriptive attributes and hierarchies for each dimension.

  • Measure: a numeric quantity that we want to analyze (for example, SalesAmount). Measures are the values to be aggregated. Measures are classified by additivity: additive (summable across all dimensions, e.g., SalesAmount), semi-additive (summable across some but not all dimensions, e.g., account balance not across Time), and non-additive (cannot be summed, e.g., ratios like AveragePrice).

  • Dimension: a categorical attribute along which we want to analyze measures (for example, Product, Time, Region). A dimension may have a hierarchy (for Time: day → month → quarter → year).

  • Cuboid: a relation that stores aggregated values of one or more measures for a particular combination of levels of the cube's dimensions. Precisely: given n dimensions, a cuboid fixes, for each dimension, either a particular level (e.g., Month) or the special top-level value ALL. The base cuboid is the one where every dimension is at its most detailed (no aggregation).

  • Data cube: the set of all cuboids for a given set of dimensions and hierarchies. It is often visualised as a lattice of cuboids ordered by the roll-up relation.

  • Cube lattice: a partial order of cuboids where an edge from node A to node B means B is a one-step roll-up of A (one dimension moved to a higher (less detailed) level). In exam answers draw nodes for cuboids and directed edges upward for roll-up.

  • OLAP (Online Analytical Processing): the set of tools and operations for interactive, multi-dimensional data analysis. Contrast with OLTP (transaction processing) which is row-level, update-heavy.

  • OLAP operations (define each):

    • Roll-up: aggregate data by climbing up a dimension hierarchy (e.g., from Month to Quarter) or by reducing the number of dimensions (e.g., from Region+Product to Region with other dims set to ALL).
    • Drill-down: the inverse of roll-up; go to finer-grained levels (e.g., Year → Quarter → Month) or add dimensions.
    • Slice: select a single value for one dimension, producing a sub-cube of one lower dimension (e.g., Time = 2025 gives a 2-D slice from a 3-D cube).
    • Dice: select ranges or sets of values on multiple dimensions (e.g., Region in {North, South} and Product in {A,B}).
    • Pivot / Rotate: reorder dimensions in the user view; no data change, just a different projection.
    • Drill-across / Drill-through: drill-across queries join facts of different fact tables (drill-across) or link to detailed transaction records in OLTP (drill-through).
  • Aggregation function classification (important in design):

    • Distributive: an aggregate function F is distributive if F on a union of partitions can be computed by applying F to each partition and then combining those results with the same operator. Example: SUM, COUNT, MAX, MIN. Formally, there exists a function G such that F(⋃P_i) = G({F(P_i)}).
    • Algebraic: F can be computed by applying a fixed-size summary to partitions, then combining summaries. Example: AVERAGE (keep SUM and COUNT as fixed-size summary). Formally, there exists k and H so that F(⋃P_i) = H({G(P_i)}) where each G(P_i) is k values.
    • Holistic: no constant-size summary suffices (e.g., MEDIAN, MODE). These are expensive for cube computation.
  • Storage / implementation approaches:

    • MOLAP (Multidimensional OLAP): stores data in optimized multidimensional arrays (fast queries, good for dense cubes, may precompute many aggregates).
    • ROLAP (Relational OLAP): stores base data and computes aggregates using SQL on relational structures at query time or with materialized views (scales to large, sparse data).
    • HOLAP (Hybrid OLAP): stores some aggregates (popular cuboids) in MOLAP structures and base/detail in ROLAP.
  • Materialization trade-off: fully materializing a cube (store all cuboids) gives fastest queries but costs storage and update time. A common strategy is to materialize a subset (those frequently queried) and compute others on demand. Efficient cube computation techniques you should know for exams include Multiway array aggregation, BUC (Bottom-Up Computation), and Star-Cubing; iceberg cubes materialize only cells/cuboids whose measures exceed a threshold.

Exact statements and derivations often asked in exams:

  • Number of cuboids: let n be the number of dimensions and let L_i be the number of levels (excluding the implicit ALL level) in dimension i. Then the number of distinct cuboids in the full cube is ∏_{i=1..n} (L_i + 1). If each dimension has only the base level (L_i = 1), the formula simplifies to 2^n (each dimension either present at base level or rolled-up to ALL).

  • Lattice drawing instructions (exam diagram): for 3 dimensions with only base and ALL (n=3) draw 8 nodes arranged in layers by the number of ALLs (0 to 3). Label each node with the set of dimensions kept at base (e.g., {P,T,R}) at bottom, then {P,T}, {P,R}, {T,R}, then {P}, {T}, {R}, then { } (the apex ALL). Draw directed edges upward from each node to nodes that have exactly one more ALL (one less detailed dimension). Annotate one edge as a roll-up (e.g., {P,T,R} → {P,T}).

Where assumptions matter: the cuboid-count formula assumes a fixed, finite set of hierarchy levels for each dimension and that we count distinct level combinations; if dimensions have many levels or ragged hierarchies, replace L_i accordingly.

Formulas

Number of cuboids = ∏_{i=1..n} (L_i + 1) — L_i = number of hierarchy levels for dimension i (excluding the ALL top); applies when counting one distinct cuboid per level combination. No unit.

If L_i = 1 for all i (only base level) then number of cuboids = 2^n — n = number of dimensions.

SQL group-by syntax to compute a cuboid: SELECT D1, D2, ..., AGG(M) AS measure FROM fact_table GROUP BY D1, D2, ...; Where D1, D2 are dimension columns kept at that cuboid's levels; AGG is SUM / COUNT / AVG etc. To compute multiple cuboids at once, many SQL systems support GROUP BY ROLLUP (D1, D2, ...), GROUP BY CUBE (D1, D2, ...), or GROUP BY GROUPING SETS ((D1,D2), (D1), ()). Use GROUPING(Dk) to detect ALL-level rows produced by ROLLUP/CUBE.

Distributive property example (SUM): SUM(⋃P_i) = Σ SUM(P_i) — applies to partitions P_i of the dataset.

No SI units apply.

Worked examples

Example 1 — small sales cube (numerical, step-by-step): Base table rows (units: Indian rupees, INR):

  1. Product=A, Region=North, Time=Jan, Sales=1000
  2. Product=A, Region=South, Time=Jan, Sales=700
  3. Product=B, Region=North, Time=Jan, Sales=1500
  4. Product=B, Region=North, Time=Feb, Sales=800 (Assume Time has no hierarchy beyond Month and ALL; Product and Region only base and ALL.)

Step 1: n=3 dimensions (Product, Region, Time). Each L_i = 1 → number of cuboids = 2^3 = 8.

Step 2: Compute base cuboid (no ALL): group by (Product, Region, Time) Formula: SUM_sales = SUM(Sales)

  • (A, North, Jan): 1000
  • (A, South, Jan): 700
  • (B, North, Jan): 1500
  • (B, North, Feb): 800

Step 3: Example roll-up to cuboid (Product, Region, ALL-Time): group by (Product, Region) SELECT Product, Region, SUM(Sales) results:

  • (A, North): 1000
  • (A, South): 700
  • (B, North): 2300 (1500+800)

Step 4: Apex cuboid ALL (ALL for all dims): single value SELECT SUM(Sales) → 1000+700+1500+800 = 4000 INR (final answer bold).

Show storage count: we could store 8 cuboids; the number of rows in each cuboid equals the number of distinct combinations of the chosen dimension-level values — careful when data are sparse.

Example 2 — cube-lattice trace (algorithmic/step example): Input: 3 dims {P,T,R} at base; we want to compute SUM for all cuboids using a bottom-up aggregation strategy. Algorithmic steps (bottom-up):

  1. Compute base cuboid by scanning fact table and grouping by (P,T,R).
  2. To make cuboid {P,T} aggregate base cuboid by grouping by (P,T) summing over R.
  3. Similarly get {P,R} and {T,R} from base by aggregating away one dim.
  4. From two-dim cuboids compute one-dim cuboids by aggregation, then apex. Trace with the small data above: start with base rows from Example 1 and apply step 2 to get {P,T} e.g. (A,Jan): 1700, (B,Jan):1500, (B,Feb):800.

Example 3 — materialization decision (theory/model exam answer): Question: For the data above, which cuboids to materialize if queries are mostly by Region and Time? Model answer: materialize {Region,Time}, {Region}, and base cuboid if point lookups are frequent; leave others unmaterialized and compute on demand. Explain costs: precompute frequency-weighted benefit vs storage cost.

Common mistakes

  • Using a holistic aggregate (like MEDIAN) and expecting constant-size summaries to be combinable — median is expensive to compute in distributed/materialized settings.
  • Thinking MOLAP always outperforms ROLAP; MOLAP is faster for dense, small cubes but can be impractical for high-dimensional sparse data.
  • Drawing the cube lattice incorrectly: missing nodes for some level combinations or drawing edges between nodes that differ by more than one aggregation step.

For your exam

Kinds of questions you will get:

  • Definitions and short notes (define data cube, cuboid, lattice, OLAP operations) — include short, precise definitions and examples.
  • Derivations (show number of cuboids) — show assumptions (how many hierarchy levels per dimension) and derive product formula step-by-step.
  • Diagrams (draw cube lattice for 2 or 3 dimensions) — label nodes with dimension sets and edges as roll-up.
  • Numericals (compute aggregates and counts) — give the base table, show SQL or grouping steps, carry units and give final values in bold.
  • Small programs or SQL (write GROUP BY to compute a cuboid) — show exact SQL syntax and explain columns.

What full-marks answers include:

  • Clear definitions of bolded terms, explicit assumptions (levels per dimension), full derivation steps, a correctly labelled lattice diagram when required, and correct numerical aggregation with units and final bold result.

Quick check

  1. What is a cuboid?
  2. Name the two OLAP operations that change aggregation level.
  3. If you have 4 dimensions and each has only base and ALL, how many cuboids are there?

Answers: 1) A cuboid is an aggregated relation for a particular choice of dimension levels (keeps some dims at levels, others at ALL). 2) Roll-up and Drill-down (these change aggregation level; pivot only reorients the view). 3) 16 (2^4).

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

Stuck on something here?