Data warehouse modelling: schemas, cubes, hierarchies and measures

What a data warehouse is

Definition. A data warehouse is a database built for analysis, not for day-to-day transactions. It has four defining properties:

• Subject-oriented: it is organised around business subjects, such as sales, customers and products, rather than around applications.
• Integrated: data from many sources is cleaned and made consistent, with the same units, codes and keys.
• Time-variant: it keeps history, often over years, and every fact is stamped with a time.
• Non-volatile: it is loaded in bulk and then mostly read. Rows are rarely updated or deleted.

	OLTP (operational database)	OLAP (data warehouse)
Purpose	run the business: orders, payments	analyse the business: trends, reports
Typical query	read or update a few rows by key	scan and aggregate millions of rows
Data	current, detailed	historical, summarised as well as detailed
Design	normalised (3NF) to avoid update anomalies	denormalised (star schema) for fast reads
Users	many clerks and applications	fewer analysts and managers
Workload	many short transactions	few long, complex queries

ETL (extract, transform, load) moves the data: extract from the sources, transform (clean, integrate, convert units, generate surrogate keys, aggregate), and load into the warehouse, usually on a schedule. A data mart is a smaller warehouse for one department.

The multidimensional model

Analysis questions look like "total sales amount by product and month in each region". The model has two parts:

• Facts are the numeric events being measured. Their numeric fields are measures, such as units sold or revenue.
• Dimensions give the context for each fact: time, product, store, customer. Each dimension has attributes, such as product name, brand and category.

A fact table stores one row per event, at a chosen grain, for example "one product on one bill". Each row holds foreign keys to the dimension tables, plus the measures. Dimension tables are wide (many descriptive columns) and relatively small. Fact tables are narrow and very long.

Schemas

Star schema

One central fact table, joined directly to one denormalised table per dimension.

Table	Columns
Sales (fact)	time_key, item_key, store_key, units, revenue
Time	time_key, day, month, quarter, year
Item	item_key, name, brand, type
Store	store_key, city, state, country

Queries need only one join per dimension, so they are fast and simple. The dimension tables repeat data: every row for a city repeats its state and country.

Snowflake schema

The dimension tables are normalised into several tables. For example, Store → City → State, or Item → Brand. This saves space and avoids some redundancy, but queries need more joins.

Fact constellation (galaxy schema)

Several fact tables share dimension tables. For example, Sales and Shipping both use Time and Item. Enterprise warehouses usually look like this.