OLAP and Multidimensional Data Analysis

1. The Multidimensional Data Model

Data is modelled as a data cube: a set of dimensions (subjects of analysis) plus measures (numeric facts). Each cell of the cube holds an aggregated measure value for the combination of dimension members.

Terminology:

Term	Meaning	Example
Dimension	Perspective of analysis	Time, Product, Location, Customer
Member	One value on a dimension	'Q1-2026', 'Mumbai'
Hierarchy	Parent-child levels in a dimension	Year → Quarter → Month → Day
Lattice	Partial order of aggregation levels	all → year → quarter → month
Measure	Numeric fact stored in cells	Sales Amount, Units, Profit
Cube / cuboid	Aggregation over a subset of dimensions	3-D cube 'Time × Product × Store'

2. A 3-D Sales Cube

                         Product
                    P1        P2        P3
              +-------------------------------+
   Q1   Delhi | 120  80   90 | 60  55  40 | ... |   cells = SUM(amount)
        Mumbai| 200 110   95 | 70  65  52 | ... |
   Q2   Delhi | 140  85   88 | 55  50  45 | ... |
        Mumbai| 210 130   99 | 65  60  58 | ... |
              +-------------------------------+
        ^ Time (Q1, Q2)      ^ Location (Delhi, Mumbai)

Tuple representation: (Q1, P1, Delhi, 120) — a point in the cube.
Full cube with 4 dimensions and 5 years × 4 quarters × 20 products × 10 stores = $5 \times 4 \times 20 \times 10 = 4000$ cells at the finest level.

Number of cuboids to materialise for $d$ dimensions:

N_{cuboids}=\sum_{k=0}^{d}\binom{d}{k}=2^{d}, \qquad \text{Full materialisation cost} \propto 2^{d}\cdot\text{base cost}

3. The Five Core OLAP Operations

Operation	What it does	Example
Roll-up (consolidation)	Move up a hierarchy, coarser grain, fewer cells	Quarter → Year: `Sales(2026) = Σ Sales(Q1..Q4)`
Drill-down	Move down a hierarchy, finer grain	Year → Quarter → Month → City
Slice	Select one value of one dimension	Cube where Year = 2026
Dice	Select a range/subset on two or more dimensions	Year ∈ {2025, 2026} AND City ∈ {Delhi, Mumbai}
Pivot (rotate)	Reorient the cross-tab axes to view data from another perspective	Product on rows ↔ Time on columns

Additional operations: drill-across (combine cubes sharing dimensions), drill-through (jump from an aggregate cell to the underlying detail rows), ranking/top-N, computed measures (ratios, share, growth).

Roll-up and slice algebraically:

\text{Rollup}_{T}(\text{Sales}) = \sum_{t \in T}\text{Sales}(t), \qquad \text{Slice}_{Year=2026}(\text{Cube}) = \{\,c \in \text{Cube} \mid c[Year] = 2026 \,\}

SELECT { [Time].[2026].[Q1], [Time].[2026].[Q2] } ON COLUMNS,
       { [Product].[P1], [Product].[P2] }        ON ROWS
FROM   [SalesCube]
WHERE  ( [Measures].[SalesAmount], [Location].[Delhi] );

6. Efficient Computation & Indexing

Materialised views / summary tables chosen by query rewrite — Query → rewrite → smallest adequate view.
Bitmap index: bit-vector per dimension member; slice/dice = bitwise AND/OR.

 Product P1 bitmap: 1 0 1 1 0 1 ...
 Delhi   bitmap:    1 0 0 1 1 0 ...
 AND result:        1 0 0 1 0 0  -> tuple ids matching P1 AND Delhi

Join index / star join: pre-joined RID pairs to avoid joining every query.
Bitmapped join index, partitioning by date, columnar storage, in-memory cubes.
Aggregation cache: reuse identical/subsumed query results.

Cost of answering a query from a summary table versus base table:

C_{view} \approx C_{\text{scan}}\left(\frac{|D_{c}|}{\prod_{i \in A}|D_i|}\right)^{?} \quad\text{(rule of thumb: choose view } V \text{ with smallest size such that } Q \sqsubseteq V)

Practical rule: pick the smallest materialised cuboid whose dimensions are a superset of the query's grouping dimensions.

7. Applications

Trading/sales performance, financial reporting and consolidation, customer profitability, inventory and supply-chain tracking, HR analysis, clickstream traffic analysis, budget vs actual variance analysis.

8. Exam Points

Explain multidimensional data model with a 3-D cube example — 7-10 marks.
Explain roll-up, drill-down, slice, dice, pivot with diagrams — 10 marks (very frequent).
Compute number of cells/cuboids — short numerical.
Role of bitmap and join indexes in OLAP performance — 3-5 marks.

OLAP and Multidimensional Data Analysis