Coupons At the Order level not the Product Level

View previous topic View next topic Go down

Coupons At the Order level not the Product Level

Post  rayishome on Tue Sep 11, 2012 12:37 pm

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

rayishome

Posts : 7
Join date : 2012-08-23

View user profile

Back to top Go down

Re: Coupons At the Order level not the Product Level

Post  snpr01 on Tue Sep 11, 2012 4:17 pm

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?

snpr01

Posts : 13
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Coupons At the Order level not the Product Level

Post  ngalemmo on Tue Sep 11, 2012 11:58 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Coupons At the Order level not the Product Level

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum