Data warehousing formulas

Data warehouse modelling: multidimensional schemas, concept hierarchies and measures. 8 formulas for GATE DA Database Management and Warehousing, each with when to use it. Each is explained in the topic notes.

All 8 Data warehousing formulas

CUBOIDS WITH NO HIERARCHIES

number of cuboids = 2^{n}

n dimensions, each with a single level (apart from 'all'). Each dimension is either present or rolled up to all.

CUBOIDS WITH EXACTLY K DIMENSIONS (NO HIERARCHIES)

(k n)

Choose which k of the n dimensions stay; summing over k = 0 to n gives 2^n.

CUBOIDS WITH CONCEPT HIERARCHIES

T = i = 1 \prod n (L_{i} + 1)

L_i is the number of distinct levels of dimension i, excluding the virtual top level 'all'; the +1 accounts for 'all'. A cuboid picks one level (or 'all') per dimension, so this holds for a total order and for a lattice; in a lattice count every level once (day, week, month gives L = 3).

GROUPINGS COMPUTED BY CUBE AND ROLLUP

CUBE (a_{1}, \dots, a_{n}) : 2^{n} ROLLUP (a_{1}, \dots, a_{n}) : n + 1

Both counts include the grand total ().

MAXIMUM CELLS IN THE BASE CUBOID

i = 1 \prod n c_{i}

c_i is the number of distinct values of dimension i at its lowest level; this is an upper bound that is reached only if every combination occurs.

CELLS IN THE WHOLE CUBE (NO HIERARCHIES)

i = 1 \prod n (c_{i} + 1)

Each dimension contributes its c_i values or the single value 'all' (written *). Counts every cell of every cuboid, assuming all combinations are non-empty. With hierarchies, replace c_i + 1 by 1 plus the total number of distinct values over all levels of dimension i.

AVERAGE FROM DISTRIBUTIVE PARTS

\overset{x}{ˉ} = \frac{\sum _{j} s _{j}}{\sum _{j} n _{j}}

s_j and n_j are the sum and count of partition j. The average of the partition averages differs from this in general; it is guaranteed to equal it only when all n_j are equal.

VARIANCE FROM SUMS (POPULATION)

σ^{2} = \frac{\sum x ^{2}}{N} - (\frac{\sum x}{N})^{2}

Needs only count N, sum and sum of squares, each distributive, so variance is algebraic with M = 3. Population variance (divide by N).

Where this was asked: 2025 (1), 2026 (2). See the 3 previous-year questions on Data warehousing. This shows what was asked, not what will be asked.

KEEP GOING

Data warehousing: the notes and 3 solved previous-year questions