Coupons At the Order level not the Product Level

The scenario is that coupons are linked directly to the order and not the Product. Also more than one coupon may be used and each one needs tracked, so it's a one to many between the coupon the the order. Example Coupon 1 is 10% off the total order while coupon two is free shipping. How would I setup the fact tables to support this scenario?

Thanks

As the relationship between Discount and Order is many-to-many and as there is a % discount you might have to design a Bridge table between Order fact and the Discounts dimension. For example, If the order has 2 discounts then the bridge table will have one group with these 2 discounts (insert new groups as you encounter or predefine groups .. you can get creative in ETL).

It will also have a weighting factor. in your example 0.1 (for 10% discount) and for free shipping, you might have to calculate shipping cost as the fraction of the order amount. While reporting, you'll multiply the discount with the order amount across different orders. Kimball calls this type of weight factor reporting as Correctly Weighted reporting.

Does this approach work for you?

Coupons/tenders are a separate fact with whatever dimensions you can throw at it. You do not try to relate them to line items other than using normal fact query patterns.