Also: data model, dimensional modelling, star schema, entity-relationship model, schema design, semantic model, normalisation
Data modelling is the design of how data is structured for a given use: which entities are recorded, which attributes describe them, how the tables relate through keys, and, for analytical use, which table holds the measurements and which hold the context, decided before the data is loaded so that the questions the model must answer run correctly and fast.
Assessment. The model is the decision that every later query inherits, and it is the step most often skipped by teams who load the data first and discover the questions afterwards. For analytical work the dimensional model has been the reliable default for three decades: measurements in a fact table at a declared grain, context in dimension tables, one star per business process. Teams that adopt it obtain answers that agree across reports; teams that query operational tables directly spend much of their time reconciling figures.
Two kinds of model serve two kinds of work. An operational system is modelled for correctness under constant change: entities and relationships drawn as an entity-relationship diagram, tables normalised so that each fact is stored once and an update touches one row. An analytical system is modelled for reading: the same data denormalised into a small number of wide tables that a query can scan without joining a dozen others. The Kimball Group's dimensional techniques describe the second kind, and the terms recur in every analytics course and exam. A fact table records measurements at a stated grain: one row for each order line, daily balance or sensor reading. Dimension tables carry the descriptive attributes by which the measurements are filtered and grouped: date, customer, product, location. Drawn with the fact at the centre, the result is the star schema.
The grain is the first decision and the one that cannot be changed cheaply afterwards. A fact table at order level cannot answer a question about individual products in the order; one at order-line level can answer both. Conformed dimensions, a single customer or date dimension shared by every fact table, are what allow a question to cross business processes, sales against support tickets against deliveries. Slowly changing dimensions settle what happens when context changes: when a customer moves region, the choice between overwriting the region and adding a new row with an effective date decides whether last year's sales stay in last year's region.
Choose the business process: orders, shipments, support tickets.
Declare the grain of the fact table.
Name the dimensions that give each row its context.
Identify the numeric facts recorded at that grain, and whether each is additive across every dimension.
Term
Meaning
Design error it prevents
Grain
What one row of the fact table represents
Mixing order totals and order lines in one table
Surrogate key
A warehouse-assigned key independent of the source system
Breaking history when the source renumbers its customers
Conformed dimension
One dimension shared across fact tables
Two incompatible customer lists in two reports
Role-playing dimension
One dimension used in several roles, such as order date and ship date
Four copies of the calendar table
Semi-additive fact
A measure that sums across some dimensions and not others, such as a balance
Adding month-end balances across months
The same vocabulary now appears inside the business intelligence tools, where the model is called a semantic model and holds the relationships, the calendar table and the measures that reports read. Microsoft's Power BI analyst exam tests the design directly, from creating fact and dimension tables and setting a relationship's cardinality to role-playing dimensions and a common date table, which is why the data warehouse and business intelligence entries both return to the model. The data engineering and visualisation and BI courses teach it from their respective ends, and both begin with the grain.
In practice
A subscription business loads its billing table into a reporting tool and asks for monthly recurring revenue by plan. The billing table records invoices, some covering a month and some a year, and the figure is wrong in every month that an annual invoice falls in. The remodel declares a grain of one row per subscription per month, with a fact for the revenue recognised in that month, a subscription dimension carrying the plan with its history, and a date dimension. The question is then a sum, and so are the questions about churn and upgrades that follow.
The warehouse is the database and the programme; data modelling is the design of the tables inside it. A warehouse can be built with a poor model, and a good model can be applied to a schema in an ordinary database.
A machine-learning model is a trained function that predicts; a data model is a designed structure that stores. The word is shared and nothing else is; a data scientist builds the first on data arranged by the second.
Key takeaways
→Operational models are normalised for writing; analytical models are dimensional for reading.
→Process, grain, dimensions, facts, in that order; the grain is the decision that cannot be undone cheaply.
→Conformed dimensions let questions cross processes; slowly changing dimensions decide what history looks like.
Is the star schema still relevant with cloud warehouses?
Yes. Cloud warehouses tolerate wide, flat tables better than their predecessors did, and some teams flatten a star into one table for a specific report, but the design decisions about grain, conformed dimensions and history are unchanged, and the BI tools still expect facts and dimensions.
What is normalisation, and why do warehouses avoid it?
Normalisation stores each fact once, across many small related tables, so that an update is a single change. It suits operational systems. Analytical queries read rather than update, and the joins across many small tables make them slow and hard to write, so the warehouse denormalises into dimensions.
Where does a learner start?
With one business process and a worked star: pick the grain, name the dimensions, list the facts, load a sample and write the five questions the business asks most. Kimball and Ross's book is the standard text; the Power BI exam guide is the shortest statement of what employers expect.
Sources
The primary text this definition rests on. Read it before relying on this one.
