Sonntag, 3. Februar 2013 09:25
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 ???
Sonntag, 3. Februar 2013 23:33
Do let me know if any more info is required to better understand the scenario.
It would be a great help if i can get some suggestions on this asap.
Donnerstag, 7. Februar 2013 17:04Moderator
Promotion Effectiveness of Sales Scenarios in the data warehouse are demonstrated in the Analysis Services Tutorial in SQL Server Books Online and in the AdventureWorksDW2008R2 sample project. Please refer to:
TechNet Community Support
- Als Antwort markiert Eileen ZhaoMicrosoft Contingent Staff, Moderator Montag, 25. Februar 2013 01:55