June 17, 2022 at 10:10 pm
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.
Thanks!
Pieter
June 18, 2022 at 11:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply