Bill Inmon's definition from 1992 still holds in its four adjectives: subject-oriented (organised around customers, products and orders rather than around the applications that record them), integrated (one set of keys and definitions across sources), time-variant (history is kept, so that last year's figures can be re-run as they were) and non-volatile (loaded in batches, read many times, rarely changed in place). An operational database is built for the opposite job, thousands of small writes a second with the current state only, and the two workloads interfere, which is why the copy exists at all.
Ralph Kimball's dimensional model is how most warehouses are arranged, and the Kimball Group's technique list reads as the curriculum. A fact table holds measurements at a declared grain, one row per order line, say, with the numeric facts and keys to the dimensions; dimension tables hold the descriptive context, the customer, the product, the date, the store. Drawn out, the fact in the middle and the dimensions around it form a star schema, which analysts and BI tools read without a map. Conformed dimensions, the same customer table shared by the sales and the support facts, are what let a question cross departments; slowly changing dimensions decide what happens when a customer moves house, and the choice between overwriting and adding a row is the first design decision every warehouse course sets as an exercise.
| Element | Holds | Example |
|---|---|---|
| Fact table | Measurements at one grain, with foreign keys | One row per order line: quantity, net amount, keys to date, product, customer |
| Dimension table | Descriptive context for slicing and filtering | Product: name, category, brand, launch date |
| Grain | What one fact row represents | Order line, not order; daily balance, not transaction |
| Conformed dimension | One dimension shared by several facts | The same customer table under sales and support |
| Slowly changing dimension | The rule for an attribute that changes | Keep the old address as its own row so past orders stay where they were |
- Select the business process to model, such as orders or support tickets.
- Declare the grain: what one row of the fact table represents.
- Identify the dimensions that describe each fact: date, customer, product, location.
- Identify the numeric facts to record at that grain.
The cloud changed the economics and not the model. Warehouses that charge by storage and query, with compute scaled on demand, removed the capacity planning that made the old appliances a capital project, and made ELT the usual loading pattern because transformation inside the warehouse became cheap. The data lake grew up beside it for raw and unstructured data, and the two have been converging into platforms that hold both. The modelling is unchanged, and so is the hard part: Microsoft's Power BI analyst exam weights the modelling of fact and dimension tables as heavily as the visuals, and the data engineering courses still begin with grain.