Do I need a recursive query for this? bill of materials (sort of)

Question

Post reply

Do I need a recursive query for this? bill of materials (sort of)

pietlinden

SSC Guru

Points: 63503
More actions
April 17, 2024 at 11:05 pm

#4412571

I am working on a data warehouse project for a bakery. For the most part, the basic structure is like this:
Purchase--(M,1)--Ingredient--(1,M)--Recipe--(M,1)--Product
with one exception the "Recipe" part works fine. (IngredientKey, ProductKey, Weight). The part I'm having trouble with is that one ingredient (starter) consists of other ingredients (kinda like a subassembly, if you wanted to compare to AdventureWorks).
Starter consists of {a parts Water, b parts Flour, c parts Potato Flakes} - all of which exist in the Ingredients table. How can I model this so that I can do something like SUMX('Recipe',[Weight] * RELATED('Ingredient'[UnitCost]) ) to calculate the total cost of ingredients? Do I need recursion for this? (Seems like overkill). I was thinking I could create a Common Table Expression in SQL Server to join to "Starter" and pass the [final] weight of that down the to "recipe" and just express that as X parts water, Y parts flour, Z parts potato flakes. (X / (X+Y+Z))
The reason for all this is that then the unit cost for Starter will change properly whenever a component cost changes.
Any thoughts how to model this? Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

I'd have to see more but I'd start with the idea that the "Starter" is actually a "Product" and that product becomes an "Ingredient" as if you bought it from a vendor. The vendor, in this case, would be the bakery itself.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

As a guess, I imagine a few joins could help and no loop would be required.

BUT It depends on how you designed things. Is that 4 tables? If so, I see no reason why you can't just join the 4 tables. If that is all in 1 table, that's going to be a bit more messy, but still no reason why you can't do self-joins.

I would do a select on product, join on recipe, join on ingredient, join on purchase filtered down to a single Product for initial testing. Then you look at all the data and determine where to do the math on it.

Problems MAY come in if you need to jump around. What I mean is if your product is A where the recipe needs B and C and C is a recipe.

To explain it better, lets say you want to make the product "dinner". The recipe for dinner is Steak Dinner. The ingredients for Steak Dinner is Marinade and Steak. Marinade is a recipe not an ingredient in my scenario, so you have to go back to recipe to get the ingredients for the marinade.

The other problem I see is that you are saying the purchase to ingredient mapping is many to 1. So 1 ingredient can have many entries in the purchase table. How do you decide which entry to use in the purchase table for Steak (using my example again)? Are you looking for the cheapest option, most expensive option, cheapest with least travel (ie all from the same store) option, all possible options?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

pietlinden SSC Guru Points: 63503 More actions · Answer 4

(I wonder if they can merge these two posts... Drat!)

I think Jeff is right. If I can express the two or three "Starter" recipes as input into another recipe, then I can have maybe a CTE to "expand" the starter recipe and add that to the ingredients for the bread recipes.

Express the proportions for the Starter recipes in a table

(recipeID, IngredientID, PercentByWeight (Decimal) )

and then I can just multiply by the Total Weight of the batch. (Since he does everything by weight anyway). So then I just "remove" the Starter from the ingredient list, pass the starter amounts to say a TVF, and return a table of (IngredientID, WeightInGrams) or whatever, and then union that to the original recipe.

(Well, what happened when you tried it? Sorry, right now I'm thinking out loud). More later after I've worked that bit out.