Demo Events: This is a product demonstration (1 or more products) at a store on a certain date. So an event has a date, a store and 1 or more products.
Sales: I have sales of all stores (POS â€“ point of sale - data).This comprises product ID, price, date/time and store name/ID.
To determine effectiveness of a demo â€“ I need to know the date of the demo, the store it was held in and the products in the demo. I will sum the sales that fall for that particular date and store and products (1 or more). This gives me the sales of relevant products on demo date. Next I will sum average sales for same group of products for 2 weeks before demo date. Difference of these 2 will give me increase in sales due to demo.
Step 3 gives me demo effectiveness of 1 demo. Eventually I need the demo effectiveness of a bunch of demos between user specified start date and end date.
Goal: given a certain start and end date, find all demos falling within that range.
For each demo, find increase in sales (for relevant store/products).
We need the MDX and/or the cube design.
Event â€“ contains event Id, event date, event store ID
Event Details â€“ contains event id and product id (1 to many mapping from event to product)
Sales â€“ contains product id, price, store ID and date.