DataWare House Design for Sales Promotion


  • 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.


    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.


    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.

    Below is an example:

    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.

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

    Vinish Viswanathan

    • Bearbeitet Vini875 Dienstag, 5. Februar 2013 02:11 Edited the example
    Sonntag, 3. Februar 2013 09:25


Alle Antworten