Header/Line & Allocated Facts
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.
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.
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 |
| Total | $1,050 | $100.00 |
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.
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
Consider an order containing two product lines:
| order_id | line_id | product | revenue |
|---|---|---|---|
| ORD-100 | 1 | Laptop | $1,000 |
| ORD-100 | 2 | Mouse | $50 |
The revenue is recorded at the order-line grain, but some costs may apply to the entire order rather than to an individual line. For example, suppose the order has a $50 shipping cost.
To calculate profit at the order-line grain, the shipping cost must be allocated across the lines according to a business-defined rule. If the business allocates shipping based on each line's share of revenue:
| order_id | line_id | product | revenue | allocated_shipping_cost | profit |
|---|---|---|---|---|---|
| ORD-100 | 1 | Laptop | $1,000 | $47.62 | $952.38 |
| ORD-100 | 2 | Mouse | $50 | $2.38 | $47.62 |
Now the revenue and allocated cost are at the same atomic grain, allowing profit to be analyzed by dimensions such as customer, product, promotion, channel, and store.
In practice, a profit and loss fact table may contain many cost components, such as shipping, sales commissions, discounts, and other allocated expenses. These costs may originate from different systems and at different grains, making the allocation process complex and dependent on business-approved rules.
For this reason, profit and loss fact tables are often implemented after the foundational dimensional models are established.