Designing Sales Promotion for Packed Products

We have a scenario to design Sales Promotions. The sales in this case if of products sold together ( in Combo offers) as part of Promotions. The requirement is to have a report which shows the Sales Quantity at Product level, another report to show in the sales report which sales were made through Promotions and which were normal sales with the help of a flag of Promotion Code and the third requirement is to have a report which shows sales quantity, volume etc at Promotion Code level.

We already have a sales fact table which can capture data at date, product and invoice level.

The design options i can think of is as follows:

1. To have a Promotion Dimension table with all details of Promotion ( e.g. the number of products in a combo offer), and other Promotion details along with Promo Description.

2. To split the Promo sales based on the number of products in a combo offer and store in the sales fact table with the Promotion_id also added to the table to link the sales to Promotion Dimension.

3.a. To have a separate fact table to capture Promotion Sales at Promotion Level ( with all fields in Sales table + Promotion details), so as to get the Quantity sold at Promo Code level.

OR

3.b. To design #1 and #2 above as it is and to have a calculated Measure to calculate the Sale Qty at Promotion Level using the Sales at Product Level and Number of Components/ Products in a Promotion.

OR

3.c. To design # and #2 as it is and get the data from source to have only Promocode and quantity and cross join with the Sales fact.

The #1 and #2 above satisfies the need to have report at Product Level and to have flag of PromoCode to distinguish between Sales via Promotion or regular sales.

The #3 (a,b,c), is to accomplish the need for Report at Promo Level and idea is not to duplicate the existing data in Sales fact table.


Can you please help me to reach the best design from the above or suggest a more better one ???

In one of my projects, we handled this situation by adding price type dimension that includes price types like list, different contract price, promo, rebate, ...... and made the fact table more granular by including price type surrogate key.

Hello,

Thanks for your suggestion.

The system we are dealing here is not so huge and may not have many types of different sales, also the promotion functionality for packed products is used by some specific markets, i would say only 5% of the markets will use this. So i think it will not be worth changing the sales fact table granularity.

Do you think any of the points i initially posted will work and which would be the best one. The idea here is to have minimal change on the existing system to implement the requested functionality.

I am assuming you have unique product key for each promotion item as well as a promotion key in the basic sales fact table (if you have one or the other, you are still ok, but have one less option).

The best way to handle this is to treat the component products as a multi-valued dimension. You can create one of two bridge tables depending on which key you have in sales facts. You can have a bridge table that uses the promotion key as the parent or the promotion product key as the parent. The child key would be the component product key, one row for each component product. Included in the bridge would be an allocation factor (either a percentage or a dollar amount) that reflects each components contribution to the sale. It is possible there may be multiple allocation factors depending on how the sale is accounted for (revenue, cost, etc...).

If you build the bridge based on promotion key, the bridge can only be used for promotion reporting. If you build the bridge on product key, you can report all sales, but it also means creating additional 'identity' rows for the non-promotional products. These rows would have identical parent and child keys with allocations set to 100%.

Thanks for your reply ngalemmo.

To understand your suggestion more clearly, can you please help to design the below scenario with the same approach of bridge table:

Promo1: 1 Unit of Product A + 2 Units of Product B
Promo2: 1 Unit of Product A + 1 Unit of Product C


Sales Quantity at Promo Level:


PromoCode SaleQty Date InvoiceNumber
Promo1: 20 20130120 INV1
Promo1: 10 20130120 INV2
Promo1: 10 20130125 INV3
Promo2: 30 20130201 INV4

Sales Qunatity at Product Level:

ProductCode SaleQty Date InvoiceNumber
Product A: 20 20130120 INV1
Product B: 40 20130120 INV1
Product A: 10 20130125 INV3
Product B: 20 20130125 INV3
Product A: 30 20130201 INV4
Product A: 10 20130120 INV2
Product B: 20 20130125 INV2

If the above product level data is maintained in the sales fact with the PromoID linked to each of these sales, aggregating at Promolevel gives me incorrect SaleQty.