Data warehouse modelling

An operational database (OLTP) supports day-to-day transactions: many short reads and updates on current, detailed data. A data warehouse (OLAP) supports analysis: it is subject-oriented, integrated from several sources, time-variant (it keeps history) and non-volatile (data is loaded and read, not updated in place).

A fact table holds the numeric measures (for example Sales.amount, Sales.units) plus a foreign key to each dimension. A dimension table describes one business perspective (time, product, location, customer) with descriptive attributes. A data cube is the logical picture of this: each dimension is an axis and each cell stores the measure for one combination of dimension values.

A cuboid is the cube aggregated at one chosen level of each dimension. The cuboid with every dimension at its lowest level is the base cuboid. The cuboid with every dimension rolled up to 'all' is the apex cuboid and has exactly one cell (the grand total). The lattice of all cuboids is the full data cube.

The word 'schema' here describes the layout of tables. In a star schema a whole concept hierarchy (such as day, month, quarter, year) is flattened into columns of one dimension table. In a snowflake schema the same hierarchy is split across tables.

A concept hierarchy maps low-level values to higher-level concepts, for example street, city, state, country for location, or day, month, quarter, year for time. It can be a total order (day to month to quarter to year) or a partial order / lattice (day to week and day to month, where week does not roll up to month). Convention used throughout these notes: the implicit top level 'all' is not counted as a level of the hierarchy.

Convention for SQL here: keywords in capitals; SQL works on bags (duplicates kept). GROUP BY CUBE (a, b) computes the aggregate for every one of the $2^2=4$ groupings: $(a,b)$, $(a)$, $(b)$ and the grand total $()$. In the output, a column that has been aggregated over is shown as NULL; the function GROUPING(col) returns 1 for such a super-aggregate NULL and 0 otherwise, which separates it from a NULL stored in the data.

SELECT
  region,
  year,
  SUM(amount)
FROM Sales
GROUP BY CUBE (region, year);

GROUP BY ROLLUP (a, b) computes only the groupings obtained by dropping columns from the right: $(a,b)$, $(a)$ and $()$, that is $n+1$ groupings for $n$ columns. The column order matters for ROLLUP (it models one hierarchy, such as year then month) but not for CUBE.

SELECT
  region,
  year,
  SUM(amount)
FROM Sales
GROUP BY ROLLUP (region, year);

Materialisation. Pre-computing every cuboid (full materialisation) answers any aggregate query fastest but can need very large storage; computing nothing in advance (no materialisation) saves space but every query aggregates from the base data; partial materialisation pre-computes a chosen subset of cuboids. An iceberg cube stores only the cells whose measure (typically count) meets a minimum threshold, which avoids storing the many sparse cells of a high-dimensional cube.

Measures are categorised by how an aggregate over a whole data set can be computed from aggregates over its partitions (for example the cells of a finer cuboid). This decides whether a coarse cuboid can be built from a finer one without rereading the base data.

A second, independent classification asks along which dimensions a stored measure may be summed. It is separate from the distributive / algebraic / holistic classes: for example, average is algebraic and also non-additive.