mini dimension, attribute scd2 or fact measure?

[oskyimporto]

Hi guys,
Quick question to see how would you approach the following requirement in a retail data mart.

If you have different type of prices for each product...let say Price1, Price2, Price3 and Price4 (i.e retail, discounted, wholesale, online) and those prices can change every couple of weeks or so. Also, they wanna see the impact of the price change in the market.
How would you design your star schema and handle the changes?
Here are some ideas but still have doubt about the design cons and pros:
A- Prices in the fact table. Query prices with distinct clause to see how many times the price changed? Is that feasible?
B- Products attributes and handle changes with scd2 and time stamps? Seems complex right?
C- Mini dimension of prices? How do I do that?
D- Any other idea?

Thanks so much for your feedback!

[VHF]

This is one area where it seems like dimensional design becomes more art than science! Technically, product prices are attributes of the product, in which case B is the way to go. However, as you already mentioned this can get messy and makes for a lot of SCD2 changes in your product dimension.

B -- Store prices as SCD2 product attributes
D1 -- Daily snapshot fact table containing product prices for each day.
D2 -- Price history table with effective dates.

Option D2 isn't quite standard Kimball methodology, but it is what I have been using to store product costs & prices over time.

ProductKey	BeginEffectiveDate	EndEffectiveDate	StandardCost	ListPrice
123	01/01/2013	12/31/2999	$4.05	$9.95
124	01/01/2013	04/15/2013	$3.00	$6.00
124	04/16/2013	12/31/2999	$3.25	$6.50

The current record for each product has a fixed far-future date (12/31/2999) for the end date. That way I can do a BETWEEN query to find the cost of an item on any particular date. Any time a price changes, the existing current record is expired by updating the end date to the day before the change. A new record is then created for that product with the begin date reflecting the date of this change.

This design allows storing historical price changes for each product with a minimum of rows, because new rows are created only for changes. The primary key on this table is a compound key consisting of the product key and begin date. It is VERY IMPORANT that there are no overlapping or missing dates in the history. The next historical row for a given product must always begin exactly one day after the previous row expires so that only a single row is returned for any given product on any given day.

If your various costs tend to change together, put them all togeter in a single history table. If they change independently, put them each in their own history table. I actually have three separate pricing history tables (standard cost, blended cost, and list price) because they tend to change at different times in my enironment.

This design makes it particularly easy to report on when price changes occured, as well as retrieve the price for any product on any given date.