Fact type: order-line revenue
Recognize when revenue analysis needs a transaction fact at order-line grain.
Concept
fact-table-grain
The primary modeling idea this problem reinforces.
Requirements
2
Business needs the model must satisfy.
A commerce finance team needs revenue, discount, tax, and product mix by individual purchased item. One order can contain several products and charges.
If the model collapses revenue to order-header grain, product mix and charge-level analysis become guesses.
- Pick the fact type that represents one purchased line item per row.
- Keep measures such as revenue, discount, and tax at the row grain where they are observed.
- The answer chooses a transaction fact rather than a snapshot or workflow row.
- The explanation states that one row represents one order line.
- Ask whether the row describes an event, a periodic state, or a workflow.
- Revenue is measured when an item is purchased.
- Choosing an order-header fact and losing product-level revenue detail.
- Calling every table with measures a snapshot even when it records an event.
Kimball transaction fact at order-line grain
One row represents one purchased order line, with line-level measures and joins to product and customer context. A Kimball transaction fact.
Optimizes for
- • Line-level revenue
- • Product mix analysis
- • Finance reconciliation
Trade-offs
- • Order-level reporting groups several rows per order
- • Header-only attributes need either degenerate identifiers or a separate header context
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.