For a sales model, declare the grain as one sold order line before naming dimensions or measures. Quantity and line revenue belong at that grain. An order-level shipping fee repeated on every line requires an allocation rule or a separate order-grain fact.
Star and snowflake layouts
A star keeps descriptive attributes within dimensions surrounding a fact. Date, customer and product keys identify the context of each measurement. Dimensions can use surrogate keys so facts reference the intended historical version.
Swipe or scroll sideways to read the full diagram.
Date, customer and product dimensions each join to order-line sales facts. Many facts can reference the same dimension version.
A snowflake separates a hierarchy into related dimension tables, such as product, subcategory and category. It reduces repeated hierarchy attributes and can simplify centralized maintenance, while consumers navigate more joins. Query engines, cached data and table sizes determine whether this improves performance.
Swipe or scroll sideways to read the full diagram.
A sales line joins to a product; product joins to subcategory, then category. The hierarchy is normalized across dimension tables.
Choose the hierarchy’s history policy explicitly. Moving a product between categories can either restate historical sales under today’s category or preserve the category at sale time.
Fact-table patterns describe different measurements
| Pattern | One row represents | Example | Update behavior |
|---|---|---|---|
| Transaction fact | One event or event line | Sale, payment, meter reading | Append events; reconcile corrections |
| Periodic snapshot | One entity at one regular cutoff | Account balance per day | Publish a complete interval |
| Accumulating snapshot | One process instance with milestones | Order with placed/shipped/delivered times | Update as milestones arrive |
| Factless fact | One event or eligibility relationship | Attendance or promotion eligibility | Relationship/event itself is the measurement |
| Aggregate fact | One summarized combination | Daily store/product revenue | Rebuild affected groups after detail changes |
An accumulating snapshot may conceal changes to earlier milestones unless another history mechanism preserves them. A periodic snapshot needs a completeness rule: absence can mean no activity, missing extraction or an inactive entity.
Additivity determines valid aggregation
Line revenue is usually additive across compatible dimensions after currency and tax conventions agree. A daily closing balance is semi-additive: summing accounts at a point in time is useful, while summing the same account across days usually is not. A conversion percentage is non-additive; aggregate conversions and eligible attempts, then recompute the ratio.
Returns and reversals need explicit event semantics. Recording a refund as a negative revenue event can preserve an audit trail, while restating an earlier sale answers a different question. A metric contract should identify the policy.
Shared dimensions and bridge allocation
Conformed dimensions let several business processes use compatible customer, date or product definitions. A bus matrix lists business processes against these shared dimensions, revealing where cross-process analysis is possible.
Swipe or scroll sideways to read the full diagram.
Sales has one row per sale line; inventory has one row per product, warehouse and day. Date and product definitions are shared. Aggregate each fact to compatible keys before combining its measures.
A sale credited to two representatives is many-to-many. A bridge can assign weights 0.6 and 0.4, producing attributed revenue of 60 and 40 for a 100-unit sale. Without weights, counting full revenue for both representatives yields 200; that may be deliberate participation reporting, but it is not allocated company revenue. Check that weights sum to one for the relevant effective interval.
When comparing separate facts, aggregate each fact to compatible conformed keys before joining. Joining detailed sales lines directly to detailed support tickets by customer produces fanout.
Swipe or scroll sideways to read the full diagram.
Customer-account membership implements a many-to-many relationship. Transactions retain account grain. A transaction can match several owners; attributing its amount to them needs an explicit allocation policy.
Grain, key and temporal validation
Validate the declared fact key, dimension-key uniqueness, orphan rates, temporal match counts and reconciliation totals. A metric that changes after adding a descriptive join suggests non-unique dimension keys or temporal overlaps. A growing unknown-member rate suggests missing dimensions, late-arriving references or a failed mapping.
A wide reporting table may simplify a repeated workload; One Big Table and training grain covers its maintenance costs. Slowly changing dimensions explains how dimension changes affect these facts.