Header/Line Fact Tables
Operational transaction systems often store transactions using a header/line (parent/child) structure: one header row represents the overall transaction, while multiple line rows represent its individual items.
In the dimensional model, the line level is typically the atomic grain. Header-level dimension foreign keys and degenerate dimensions should therefore be carried onto the line-level fact table.
| order_id | line_id | date_key | store_key | customer_key | product_key | quantity | sales_amount |
|---|---|---|---|---|---|---|---|
| ORD-100 | 1 | Jan 1 | Store A | Alice | Laptop | 1 | $1,000 |
| ORD-100 | 2 | Jan 1 | Store A | Alice | Mouse | 1 | $50 |
Both rows share the same order-level context, but each represents a distinct order line at the atomic grain.
Allocated Facts
Header/line transactions often contain facts at different grains. For example, a $100 freight charge may apply to the entire order rather than to an individual line.
Storing the header fact on every line would double-count it: a two-line order would appear to have incurred $200 of freight. Instead, where appropriate, the business can define a rule to allocate the header-level fact down to the line level. This allows the allocated measure to be analyzed and aggregated using the same dimensions as the line-level facts without double-counting.
For example, freight can be allocated based on each line's share of the order's sales:
| order_id | line_id | product_key | sales_amount | allocated_freight |
|---|---|---|---|---|
| ORD-100 | 1 | Laptop | $1,000 | $95.24 |
| ORD-100 | 2 | Mouse | $50 | $4.76 |
The allocation rule should be defined and approved by the business. A separate header-level fact table may still be useful when the header-level data is needed independently or provides query-performance benefits.
NOTE
Allocation is not exclusive to header/line schemas. This pattern generalizes to any coarser-grained fact - such as pushing a monthly warehouse overhead cost down to individual daily shipments - so it can be sliced by more detailed dimensions.
Profit and Loss Fact Tables Using Allocations
A profit and loss fact table extends the allocation concept to provide profitability analysis at the atomic revenue transaction grain.
The basic equation is:
Revenue - Costs = Profit
A true P&L table combines multiple costs originating from different systems at different levels of detail. Some costs arrive already at the line level, while others arrive at the order level (or higher) and require different allocation rules.
For example, consider an order with two items. The product_cost is already known at the line level. However, a $50 shipping charge might be allocated by revenue share, while a $10 warehouse handling fee might be allocated evenly by unit count (1 unit each).
| order_id | line_id | product | revenue | product_cost | allocated_shipping | allocated_handling | profit |
|---|---|---|---|---|---|---|---|
| ORD-100 | 1 | Laptop | $1,000 | $600 | $47.62 | $5.00 | $347.38 |
| ORD-100 | 2 | Mouse | $50 | $30 | $2.38 | $5.00 | $12.62 |
Now the revenue and all cost components are at the same atomic grain, allowing profit to be analyzed by any dimension (e.g., customer, product, channel, or store).
Because compiling these costs requires pulling from multiple systems and gaining executive approval on various allocation rules, profit and loss fact tables are often implemented after the foundational dimensional models are established.