June 17, 2022 at 10:11 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.
June 22, 2022 at 10:29 am
I think your Ingredient table containing the price should be set up as a slowly changing dimension (even if it changes fast). That way the Recipe fact doesn't have to change, but you obtain the price using a combination of IngredientKey and date.
June 22, 2022 at 4:59 pm
Okay, I think I get it. Thanks, Chris!
So I would have an Ingredient table (IngredientKey, IngredientSK, IngredientName, Category, UnitPrice, ActiveDate) and then use FILTER() and/or TOPN() to get the most recent active Ingredient value relative to "Manufacture" date of Product.
I know how to get the latest price for a product using T-SQL ( CROSS APPLY and TOP), but how do I do it in DAX? (Wait, I'm pretty sure I've read/seen a video/article where Alberto Ferrari shows how to do it)... I'd just do that and use FILTER(Purchases) instead of just Purchases to return the latest value before a given manufacture date. Right?
June 23, 2022 at 10:15 am
So I would have an Ingredient table (IngredientKey, IngredientSK, IngredientName, Category, UnitPrice, ActiveDate) and then use FILTER() and/or TOPN() to get the most recent active Ingredient value relative to "Manufacture" date of Product.
Yes, that's the gist of what you need to do. I'm not familiar with DAX syntax, so I can't be more specific.
June 23, 2022 at 3:10 pm
No problem. Just looking for general directions. I think I can figure the rest out myself. (Well, I hope I can!)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply