Designing a Dimensional Model That Reflects the Business

A request to “build a sales fact table” sounds specific until the modelling questions begin.

Should one row represent an order, an order line, a shipment, an invoice line, or a daily product balance? Is revenue recorded when an order is placed, dispatched, invoiced, or paid? Can freight be summed by product if the source records it once per order?

This article uses order fulfilment as a continuing worked example so that the modelling decisions remain concrete. The method isn’t specific to sales or physical products: the same questions apply to claims, payments, subscriptions, service cases, clinical activity, and other measurable processes.

Those choices affect every report built on the model. If they remain implicit, technically valid queries can return misleading results.

Dimensional modelling turns those questions into an explicit analytical design. It organises measurable business activity as facts and the descriptive context around that activity as dimensions.

Design from the Business Activity

An operational database is usually organised around application behaviour, transactions, validation, and efficient updates. Its tables are valuable evidence about the data, but they aren’t an analytical design brief.

Copying source tables into a warehouse can preserve the source’s complexity without establishing:

A dimensional model starts from the activity people need to understand. Sales, order fulfilment, inventory, procurement, claims, subscriptions, and support cases are examples of business processes that produce measurable events or states.

The model should be developed with people who understand both the business process and the available data. A business definition that isn’t captured by the source won’t become implementable simply because it appears in a workshop diagram.

Kimball’s Four Design Decisions

The Kimball Group’s four-step dimensional design process provides a useful sequence:

The decisions stay the same when the subject changes. Their answers do not:

These examples aren’t interchangeable templates. They show why the business process and grain must be established before a team chooses familiar-looking dimensions or measures.

The sequence is important. Beginning with a convenient measure or a collection of dimensions can conceal that they describe different events or levels of detail.

1. Select the Business Process

“Sales” may be too broad to describe one process. An organisation could separately record:

These events are related, but they occur at different times, may come from different systems, and answer different questions. Treating them as one undifferentiated fact can confuse demand, fulfilment, revenue, cash, and returns.

Suppose the immediate requirement is to understand fulfilment performance: what was dispatched, when, from where, for which customer, and whether the dispatch met its commitment. The selected process is therefore order fulfilment, with dispatch as the measurable event.

2. Declare the Grain

The grain is a plain-language statement of what one fact row represents. It should be declared before choosing dimensions and facts.

For the fulfilment example, a candidate declaration is:

One row represents one recorded dispatch of one product line from one sales order.

In another domain, the same discipline might produce “one row per claim-item adjudication decision”, “one row per loan account at the end of each business day”, or “one row per completed support-case handling event”. Each statement creates different rules for uniqueness, valid measures, and descriptive context.

That sentence is a design contract. It establishes that:

Grain is more precise than saying “daily”, “customer level”, or “transactional”. Those phrases describe only part of a row’s identity.

The same broader process can be viewed in several legitimate ways. One model can record each dispatch event. Another can take a daily picture of open work. A third can keep one row per order line and update milestone dates as it progresses.

The formal names help practitioners recognise familiar patterns, but the row meaning is the important decision. These designs aren’t interchangeable: one records events, one deliberately repeats a state across dates, and one updates a row as a process progresses.

Microsoft’s fact-table modelling guidance describes these transaction, periodic-snapshot, and accumulating-snapshot patterns in more detail.

3. Identify the Dimensions

Dimensions provide the descriptive context used to filter, group, label, and navigate facts. They answer questions such as who, what, when, where, how, and why.

For a dispatched order line, candidate dimensions might include:

The dimensions must apply at the fact’s grain. If a carrier isn’t known for each dispatched line, the design needs to establish whether it can be inherited safely from a shipment, represented as unknown, or modelled differently.

Dimensions should use business language

A dimension should translate operational codes and structures into terminology its intended users recognise. A product dimension might bring together item, brand, category, and lifecycle status. A policy dimension might expose coverage family, scheme, and regulatory classification; a loan dimension might expose facility type, repayment basis, and risk category. Readers shouldn’t need to reconstruct these meanings through several coded source tables.

This denormalised shape is characteristic of a star schema. A central fact table joins directly to comparatively wide dimensions, which makes common analytical paths easier to understand and often reduces query joins.

Shared descriptions prevent conflicting answers

Dispatch, invoice, return, and inventory models may all need compatible customer, item, and calendar descriptions. A lender may need the same borrower, account, branch, and risk definitions across applications, payments, balances, and arrears. If each team invents incompatible categories or hierarchies, cross-process analysis becomes unreliable.

Agreeing which descriptions should be reusable is therefore part of the design. The optional vocabulary section later in this article gives formal names to this and several other recurring modelling patterns.

4. Identify the Facts

Facts are measurements, events, or states recorded at the declared grain. For one dispatched order line, possible numeric facts include:

• Dispatched quantity
• Dispatch-event count, usually stored or interpreted as one
• Extended selling value assigned to the dispatched quantity
• Elapsed time between order and dispatch
• Variance from the promised dispatch date
• Weight or volume dispatched

Each candidate needs a source, definition, unit, and aggregation rule. A useful measure isn’t automatically a valid fact for this table.

Not every number can be totalled in the same way

A dispatched quantity can usually be added across items and dates. An end-of-day loan balance can be added across accounts, but adding Monday’s balance to Tuesday’s is normally meaningless. A percentage should usually be recalculated from its components. Without these rules, a dashboard can display a plausible but invalid total.

For a rate such as on-time dispatch percentage, storing additive components is often safer: eligible dispatch count and on-time dispatch count. Consumers can sum those components within a valid context and then divide them.

Currency needs similar care. Revenue recorded in several currencies isn’t additive until a conversion policy establishes a common basis, rate date, rate type, and correction approach.

The Star Schema That Emerges

The fulfilment model now has a central dispatch fact linked directly to descriptive dimensions. The fact contains dimension keys, compatible measurements, and potentially transaction identifiers such as order number and line number.

The star shape isn’t imposed before the business decisions. It emerges from them:

Microsoft’s dimensional-modelling guidance for Fabric Warehouse similarly describes fact tables as measurements of observations or events and dimensions as the business entities that provide analytical context. Those principles aren’t specific to Fabric.

Useful Modelling Vocabulary

The four design decisions are the main story. The following terms help practitioners discuss situations that recur across dimensional models, but decision makers don’t need to memorise them to understand the design.

For example, the warehouse may store one physical calendar dimension while a model exposes it through several named date roles. Likewise, dimensions can be reused without every fact table relating to them at exactly the same level of detail; their definitions and roll-ups still need to remain compatible.

Star or Snowflake?

A descriptive hierarchy can be presented in one analyst-facing dimension or divided into related tables. The choice applies to structures such as product categories, organisational units, account classifications, clinical specialties, and geographic hierarchies.

The diagram continues the fulfilment example: a product can be presented in one table containing its item, subcategory, category, brand, and supplier descriptions, or some of those levels can be stored in separate related tables.

The first arrangement contributes to a star schema, with a direct path from facts to comparatively wide dimensions. The second is a snowflake schema, in which some dimension attributes are normalised into related tables.

For many reporting models, a star is the clearer default. Snowflaking can still be reasonable when a subdimension is large, shared, independently governed, or genuinely reused in ways that justify the added relationships.

This isn’t a contest for the fewest tables. The design should make valid analysis understandable and maintainable within the platform and operating context.

Common Modelling Failures

Evidence the Model Is Usable

A design review shouldn’t end when the boxes look tidy. Useful evidence includes:

For readers who want a concrete implementation after the general design method, an optional AdventureWorks case study on this site applies these ideas to a sales-order-line star schema, including surrogate keys, unknown members, dimensional loading, and financial reconciliation.

Questions to Take into Historical Design

Dimensions describe facts, but those descriptions change. Customers move, products or policies are reclassified, borrowers change risk bands, organisational structures are reorganised, and corrections arrive after results have been published.

Those decisions lead into the next article, which examines current-state replacement, versioned dimensions, effective dates, late-arriving data, corrections, and the limits of reconstructing history that was never captured.