Data Warehouse Modelling — Cubes, Schemas, Concept Hierarchies and Measures
What a data warehouse is and how it differs from an operational database (OLTP vs OLAP), ETL and architecture, the multidimensional data model (facts, dimensions, measures, the data cube and its lattice of cuboids), star, snowflake and fact-constellation schemas, concept hierarchies, OLAP operations (roll-up, drill-down, slice, dice, pivot), the categorisation of measures into distributive, algebraic and holistic, cube computation and materialisation, and ROLAP/MOLAP/HOLAP.
What is a data warehouse?
A data warehouse is a "subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision-making process" (Inmon).
| Property | Meaning |
|---|---|
| Subject-oriented | organised around subjects (customers, sales, products), not applications |
| Integrated | data from many sources cleaned and made consistent (units, codes, names) |
| Time-variant | keeps history; every record is associated with time |
| Non-volatile | loaded in bulk and read; not updated by day-to-day transactions |
OLTP vs OLAP
| OLTP (operational DB) | OLAP (data warehouse) | |
|---|---|---|
| Users | clerks, applications | analysts, managers |
| Workload | many short read/write transactions | few, complex, read-mostly aggregate queries |
| Data | current, detailed | historical, summarised and detailed |
| Design | normalised (3NF/BCNF), ER model | star/snowflake, dimensional model |
| Size | GB–TB | TB–PB |
| Priority | throughput, consistency, concurrency | query speed, flexibility |
Architecture
ETL: extract from operational sources, transform (clean, integrate, convert, aggregate), load into the warehouse (with periodic refresh). A typical three-tier design: warehouse database server (bottom), OLAP server (middle), front-end reporting/mining tools (top). Data marts are departmental subsets; metadata describes sources, transformations and schemas.
The multidimensional data model
Data is viewed as a cube:
- Dimensions — perspectives for analysis (time, location, item, supplier), each described by a dimension table of attributes.
- Facts — numerical measures (dollars sold, units sold) stored in a fact table with foreign keys to every dimension.
- A cell holds the measure for one combination of dimension values.
The lattice of cuboids
Grouping by every subset of dimensions gives a cuboid: the base cuboid groups by all dimensions; the apex cuboid (0-D) is the grand total. Without hierarchies there are cuboids; if dimension has hierarchy levels (excluding "all"), there are
For 3 dimensions (time, item, location): 1 base cuboid, 3 two-dimensional, 3 one-dimensional, 1 apex — 8 in total. SQL's GROUP BY CUBE(time, item, location) computes all of them; ROLLUP computes only the hierarchical prefixes ( groupings).
Schemas for multidimensional data
| Schema | Structure | Pros | Cons |
|---|---|---|---|
| Star | central fact table + one denormalised table per dimension | simple, fewest joins, fast queries | redundancy in dimension tables |
| Snowflake | dimension tables normalised along hierarchies (city → state → country tables) | less redundancy, easier maintenance | more joins, slower browsing |
| Fact constellation (galaxy) | several fact tables sharing dimension tables (e.g. Sales and Shipping share Time, Item) | models multiple processes | more complex |
Concept hierarchies
A concept hierarchy maps low-level values to higher-level concepts:
- Schema hierarchy (total order of attributes): street < city < state < country; day < month < quarter < year.
- Lattice (partial order): day < {month, week}; month < quarter < year; week < year.
- Set-grouping hierarchy: values grouped into ranges or categories (price $0–100 = "cheap"; age 13–19 = "teen").
They define the levels for roll-up and drill-down, and can be generated automatically from discretisation (see data transformation) or from the number of distinct values (attributes with fewer distinct values sit higher in the hierarchy).
OLAP operations
| Operation | Effect | Example |
|---|---|---|
| Roll-up (drill-up) | climb a hierarchy or drop a dimension; aggregates | city → country; month → quarter |
| Drill-down | step down a hierarchy or add a dimension | quarter → month |
| Slice | fix one dimension to a single value | time = Q1 |
| Dice | select a sub-cube with conditions on several dimensions | time ∈ {Q1, Q2} and location ∈ {Delhi, Mumbai} |
| Pivot (rotate) | reorient the axes of the view | swap rows and columns |
| Drill-across / drill-through | across fact tables / down to the source rows |
Measures: categorisation and computation
Measures are classified by how they can be computed when the data is partitioned (e.g. across cells, regions or machines):
| Category | Definition | Examples |
|---|---|---|
| Distributive | the result over the whole equals the same function applied to the results over the parts | COUNT, SUM, MIN, MAX |
| Algebraic | computable by an algebraic function of a bounded number of distributive measures | AVG = SUM/COUNT, variance (from count, sum, sum of squares), MIN_N, centre of mass |
| Holistic | no constant-size summary of the parts suffices | median, mode, rank, number of distinct values (exactly) |
Distributive and algebraic measures make cube computation efficient: higher-level cuboids are computed from lower-level ones instead of from raw data. Holistic measures need approximation (e.g. quantile sketches) or recomputation.
Cube materialisation and storage
- Full materialisation precomputes every cuboid (fast queries, huge storage); no materialisation computes on the fly; partial materialisation picks the most useful cuboids (greedy selection) — the usual choice. Iceberg cubes store only cells with measure above a threshold.
- ROLAP stores the cube in relational tables (star schema, scales well); MOLAP in multidimensional arrays (fast, but sparse data wastes space); HOLAP combines them (detail in relational, aggregates in arrays).
Quick revision
Practice questions
GATE-style questions: 4 one-mark and 6 two-mark. Wrong MCQs cost ⅓ of their marks in the real exam; MSQ and NAT have no negative marking. Attempt each before opening the solution.
A data cube has 4 dimensions and no concept hierarchies. The total number of cuboids (including the apex and base cuboids) is ______.
A cube has three dimensions: time with hierarchy day < month < year (3 levels), location with city < country (2 levels), and item with no hierarchy (1 level). The total number of cuboids is ______.
The median is classified as which kind of aggregate measure?
Which of the following aggregate measures are distributive?
One or more options are correct.
Moving from monthly sales figures to quarterly sales figures along the time dimension is an example of
How does a snowflake schema differ from a star schema?
Selecting the sub-cube for time = 'Q1' (fixing one value of one dimension) is called
A sales fact table records one row per (day, store, product) combination that had sales. There are 365 days, 10 stores and 100 products. The maximum possible number of rows in the fact table is ______.
Which statements comparing OLTP systems and data warehouses (OLAP) are correct?
One or more options are correct.
Sales are partitioned into two regions. Region 1 has total sales 120 over 4 transactions; region 2 has total 80 over 6 transactions. The average sale per transaction over both regions is ______.