SQL, data modeling, data quality, EDA, visualization · insight-1040
Allocate header-level amounts to the line grain with a written rule before summing by line attributes
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist
- Industries
- aviation, retail, e-commerce
Summary: situation, action, reason, boundary
Situation
An order-like header carries a booking fee or baggage charge once, and a line table has one row per flight segment; the report sums revenue by route from the joined result.
Action
Choose an allocation rule with the business (equal share, proportional to segment fare, or first segment only), store the allocated amount as its own column at line grain, and assert that allocated amounts sum back to the header total.
Reason
A measure belongs to one grain; carrying it to a finer grain without allocation turns a single fact into several.
Boundary
Shipping, taxes, discounts, ancillary charges and any parent-level measure analysed by child attributes.
Symptom or risk
The header amount repeats on every segment, so a three-segment booking counts its fee three times, and the total differs from finance by a factor that depends on itinerary mix.
Exceptions
Header-level analysis only, where the header table itself is the right grain.
Trade-offs
Allocation rules are conventions; two defensible rules give different route-level numbers.
Pitfalls
Allocating by a field that is itself missing for some lines, which loses the remainder silently.
Lessons and concepts
Claims and their sources
Kimball recommends allocating header-level facts such as freight to the line grain with business-defined rules, and carrying header dimension keys and degenerate dimensions onto line rows.