Stacked promotions
Recognize when applied promotions need a bridge between order lines and promotion context.
Concept
many-to-many-modeling
The primary modeling idea this problem reinforces.
Requirements
2
Business needs the model must satisfy.
A commerce team allows multiple promotions on one order line. Analysts need revenue and discount by promotion campaign without counting the same line revenue once for every promotion.
A direct promotion key on the order line can only store one promotion, while duplicated order lines inflate revenue.
- Pick the structure that stores one row per order line and applied promotion.
- Preserve line revenue on the order-line fact and allocated discount on the bridge.
- The answer chooses an order-line promotion bridge.
- The explanation separates order-line revenue grain from promotion application grain.
- Ask what one order-line row means before adding promotion columns.
- Discount allocation happens on the relationship between a line and a promotion.
- Adding only promotion_key to the order-line fact and losing stacked promotions.
- Duplicating the order line for each promotion and inflating revenue.
Kimball bridge for applied promotions
Use an order-line promotion bridge keyed by order_line_key and promotion_key, with allocated discount as the bridge measure. The Kimball bridge pattern again.
Optimizes for
- • Promotion attribution
- • Stacked discount analysis
- • Protecting order-line revenue grain
Trade-offs
- • Promotion reporting must use allocated discount instead of summing line revenue through every bridge row
- • Single-promotion stores may not need the extra bridge
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.