Dimensional Modeling

The database a product runs on was never built for analysis. It is designed so that no value is written in two places, which means account details sit in one table, payments in another, plans in a third, discounts in a fourth. For an application reading and writing one row at a time, that shape is exactly right.

It stops being right the moment someone tries to analyze it. At Pagedeck, Mia asks for this month's revenue broken out by plan. Theo writes a query that joins seven tables in the correct order, and to get it right he has to remember which column links to which, that refunded payments need excluding, and that internal test accounts need filtering out. It takes two days.

The next week the request is the same revenue by signup source. Only the angle changed, but the query gets written from scratch again. Worse, different people write it differently. If Theo excludes refunds and someone else doesn't, the same month's revenue shows up at the meeting as two numbers, and the meeting turns into an argument about which query was right.

Dimensional modeling inverts the priority. Instead of minimizing storage, it arranges tables so that a reader never gets lost. One table in the middle collects the events, and tables around it hold the descriptions used to group those events. The middle one is a fact table; the surrounding ones are dimension tables. Arranged this way, changing the angle stops being a rewrite and becomes a matter of picking a different column.

Four steps, in order: choose what to model, fix what one row means, choose the angles, then choose the numbers.

1. Choose the event — pick one thing that happens repeatedly, such as a payment, a document being created, or a teammate being invited. Don't try to model the whole company at once. Start with whichever event carries the questions people ask most, which at Pagedeck is payments.
2. Fix the grain — write one sentence saying what a single row of the fact table represents. "One payment" and "one account's monthly payment total" are different decisions, and this is where you commit to one. Finer is safer, because fine rows can always be rolled up later. Commit to monthly totals and no one will ever be able to answer a weekly question without rebuilding the table.
3. Choose the dimensions — decide which angles that row should be sliceable by, and give each angle its own table. For payments that means date, account, plan, and region. Walking through when, who, what, and where usually surfaces the angle you forgot.
4. Choose the measures — decide which numbers live on the fact table, and keep only values that can be added. Payment amount and payment count belong there; a ratio like conversion rate does not. Roll several rows up and a stored ratio produces an average of ratios, which is not the same as the overall ratio. Store the numerator and the denominator as separate columns and let the reader divide.

What comes out the other end is one fact table surrounded by three or four dimension tables — a shape usually called a star, since the diagram looks like one. These tables normally live in the mart layer, where analysts pull from directly.

It fits when the same question keeps coming back with a different angle attached, and when the people reading the answer don't work with data for a living. A team that checks new payments by plan one week, by signup source the next, and by region the week after is exactly the case. Once the model is built, Mia changes a filter on a dashboard and gets her own answer without waiting on a data analyst. Because the aggregation rules are baked into the table rather than into each person's query, the report a business intelligence analyst publishes and the dashboard Mia opens agree with each other.

Three limits are worth knowing before starting.

1. It is early for a team that doesn't yet know what it wants to look at. Step 3 asks you to name the angles in advance, and a product that just launched can't produce that list honestly. Digging through the raw data directly is faster at that stage.
2. Getting the grain wrong is expensive to undo. Changing step 2 means rebuilding the table and backfilling history, and the dashboards built on it are wrong or absent while that happens.
3. Questions that follow chains of relationships don't fit. "Who was invited by someone this user invited" traverses links rather than grouping rows, and this structure handles it badly.

"What's the grain on this fact table? Is a row one payment, or one account-month?"

Checking the unit before trusting any total read off the table.

"Don't store the ratio on the fact — give me the numerator and denominator separately."

Rolled-up ratios average incorrectly, so the division should happen at read time.

"Getting that view means adding another dimension, so it won't happen this week."

The angle being asked for has no dimension table behind it yet.

Pagedeck modeled payments first. The grain was one payment, with date, account, and plan as the dimensions. Six months later, when Ravi asked for revenue by signup source, nothing had to be rebuilt — a source column was added to the account dimension and the question was answerable that afternoon.

The document-creation model went the other way. The grain was originally set at one account-day, and when a request came in to look at usage by hour of day there was no way to answer it. Resetting the grain to one document and backfilling four months of history took two weeks.

The most common reaction is that repeated values indicate a badly designed table. Anyone who learned databases was taught to remove duplication, so a plan name repeating across hundreds of rows looks like an error. The two designs have different jobs. A product database keeps each value in one place so that edits can't go half-applied. An analytical table exists so a reader can ask a question without assembling tables first. Split the dimensions apart to remove duplication and storage drops, but the person who doesn't know how to write the join just abandons the question.

Treating the design as finished once is the other one. The word design suggests a blueprint you draw and then build from. In practice dimensions keep gaining columns, and when a source system changes, the rules that populate them change too. With no one assigned to maintain it, six months later the numbers are stale, nobody trusts the table, and everyone quietly goes back to querying the raw data on their own.