When transactional data spans header and line levels, the standard practice is to build the primary fact table at the line-item grain for detailed product analysis. Header-level amounts like shipping fees and cart discounts must either be allocated down to the lines by a business rule or maintained in a separate order-level header fact table.
Choosing the line-item grain
Building the table at the line-item grain allows analysts to filter and group by product attributes, categories, and suppliers. The order number itself does not need a separate dimension table. Instead, store order_id directly in the fact table as a degenerate dimension:
fact_order_lines order_id (degenerate dimension) line_number customer_key product_key store_key order_date_key item_quantity item_extended_price allocated_shipping_fee allocated_discount_amount
A short look at the degenerate dimension pattern:
- The
order_idacts as a grouping key without requiring an extra join to an order dimension. - Line-level attributes like quantity and price map directly to the purchased item.
Allocating header fees to lines
Never copy the full header shipping fee directly onto every line item row. If an order has four items and a twenty dollar shipping fee, placing twenty dollars on each row causes SUM(shipping_fee) to report eighty dollars, inflating company financials. Instead, allocate header values proportionally using a defined allocation rule:
- Proportional by price: Allocate fees based on the line item value relative to the order subtotal.
- Proportional by weight: For shipping costs, allocate based on physical item weight if available.
- Unit count split: Divide the fee equally across line items.
The dual fact alternative
If allocating header fees is impractical or finance requires exact reconciliation to invoices, build two conformed fact tables: fact_order_header (grain: one row per order) for shipping fees and order totals, and fact_order_line (grain: one row per line item) for SKU sales and quantities.