Dimension table

What is a dimension table?

A dimension table holds the descriptive attributes that give context to the numbers in a report. Where a fact table records what happened and how much, an order line, a payment, a stock reading, a dimension table answers who, what, where, when and under which category. Customer, product, date, region and sales channel are each a dimension.

Dimensions are what make a report readable. Without a customer dimension you have account codes, without a product dimension you have article numbers, and without a date dimension you cannot group cleanly by month, quarter or fiscal year. There is a quick test for what belongs here: the things a manager wants to see data by, by salesperson, by month, by product category, are the dimensions, and the things they add up are the facts. Kimball and Microsoft both treat it as the first design question.

In a star schema, dimension tables sit around one or more fact tables. That arrangement is the standard structure in dimensional modelling, the approach Ralph Kimball defined in the 1990s. Dimension tables are usually far smaller than the facts they describe, yet they largely shape how people navigate the data.

What goes inside a dimension table

A well-built dimension has a few recognisable groups of columns.

• A surrogate key. A single integer column, generated inside the warehouse, that the fact table points to. It is kept separate from the source system's own identifier, so the model stays stable when source codes change and you can store more than one version of a row.

• A business key. The natural identifier from the source, such as a customer number or article code. It stays visible next to the surrogate key so people recognise the record and the load process can match rows back to the source.

• Descriptive attributes. The columns you actually report with: name, category, brand, country, segment, channel. A dimension can carry a handful or several hundred, as many as the questions demand.

• Hierarchies. Attributes that roll up, such as product to subcategory to category, or date to month to quarter to year. These are what a report drills down through.

Take a sales model whose fact table stores invoice lines with a quantity and an amount. The product dimension carries article code, product name, brand and category; the customer dimension carries customer number, name, country, segment and account manager; the date dimension carries the calendar date, month, quarter and fiscal year. With those three in place, the same revenue can be read by month, by product category, by customer segment or by account manager without touching the fact table.

Wide and denormalised on purpose

Dimension tables break the usual database rule about avoiding repetition. They are kept flat: a product dimension stores the subcategory and category next to every product row, even though that repeats the same category name thousands of times. This is denormalisation, and in a dimension it is a deliberate choice rather than a mistake.

It works because dimensions are small next to fact tables, so the repeated text costs little while the flat shape lets a query filter and group without extra joins. Split those levels back into separate linked tables and you have a snowflake schema instead, which in most models queries slower and reads less clearly. The usual advice is to flatten to a star unless you have a specific reason not to.

Flatness is also why dimensions get wide, sometimes hundreds of columns, because every attribute is one more way to filter or group. The fact table still sets the grain, the level of detail one row represents, while the dimensions set the axes you analyse along. Granularity is a property of the fact; breadth is a property of the dimension.

Dimensions that play special roles

Some dimensions do more than describe a single fact table.

Conformed dimensions are shared across several fact tables so everyone counts the same way. One date and one product dimension can serve a sales fact table and an inventory snapshot at once, so product category means the same thing in every report that uses them. A shared date dimension is the classic case, and it lets a transaction fact table and a Snapshot fact table line up on the same calendar.

Role-playing dimensions are one physical dimension referenced more than once. A sales fact with an order date, a ship date and a delivery date relates to a single date dimension three times, each relationship playing a different role. An airline model does the same when one airport dimension is both the departure and the arrival airport.

Degenerate dimensions have no table of their own. An order number is the standard example: a value you want to filter and group by, but which sits in the fact table itself, because a one-column lookup table would add nothing.

When a dimension changes

Dimensions are not frozen. A customer moves to a new account manager, a product shifts category, a sales region is redrawn. The real question is what a report should show once that happens.

The simplest option overwrites the old value, so history moves with it and last year's sales quietly re-file under the new category. Sometimes that is what you want; often it misleads. The alternative keeps versions of the row, each with its own surrogate key and a validity period, so January's sales stay tied to the customer as it stood in January. That technique is covered in the Slowly Changing Dimensions entry, worth reading before you decide how a dimension should behave.