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 analyzed using more detailed dimensions.
Profit and Loss Using Allocations
A profit and loss (P&L) fact table extends this allocation concept to analyze profitability at the lowest transaction level.
To calculate profit, you must subtract all costs from the revenue. A true P&L table combines costs originating from different systems and at different levels of detail. Some costs are already known at the line level, while higher-level costs 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 based on each item's share of the revenue, while a $10 warehouse handling fee might be split evenly across the two items.
| 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 building this requires gathering data from multiple systems and getting executive approval on the various allocation rules, P&L fact tables are usually built only after the foundational dimensional models are established.