Sorry, I'm not great at Data Warehouse design. =( I'm modeling a bakery for a friend of mine. He purchases ingredients and the prices for them may change over time. (Flour prices are crazy). The design for that part of the problem is kinda like this:
BakeryProduct --- (1,m)---Recipe(BaleryProductKey, IngredientKey, Weight)--(M,1)--Purchase (IngredientKey, DateKey, VendorKey, Units, UnitPrice)---(M,1)--Ingredient(IngredientKey, UnitWeight, IngredientName....)
Given that he bakes every week, and may purchase ingredients every week, how do I deal with the price changes? Do I expire an IngredientKey (validFromDate, validToDate)? The part I don't totally understand is how I would relate the Recipe(BakeryProductKey, IngredientKey, Weight) All the ingredients are just weighed... much simpler that way!
How do I handle ingredient price changes? Do I have to generate a new ProductKey with the same alternate key to indicate a new "recipe" (well, the prices of the ingredients are different). Gotta read Data Warehouse Toolkit chapter, I'm sure, but is there a good tutorial on this part of the problem somewhere? His sale prices are pretty constant unless ingredient prices go completely haywire, but the price of flour (for example) has increased by 50% or more in the past 18 months... and given that bread is primarily flour, that's a significant cost increase. Articles and/or Data Warehouse Toolkit chapters or Star Schema: Complete Reference chapters are all fine.
Don't mean to be lazy, I just don't totally get how to model the primary keys etc and how to expire old prices without having to update the recipe fact for each Bakery Product.