Phil,

Not sure I totally answered your question before... To make a long story short(er), I'm basically trying to see/analyze/understand how profitable the business is over time (with the crazy grain...

This design works assuming that you may want to add the costs of a product's ingredients, but only ever want to aggregate the selling price of products by using an...

but only ever want to aggregate the selling price of products by using an average across time...or the latest value which is similar to what you would have done if...

• This reply was modified 5 months, 2 weeks ago by  pietlinden.

Martin,

I think I understand your answer. (Took a couple days!) Basically, the ProductIngredient table (ProductID, IngredientID, Weight...) factless fact handles the slowly changing dimension by pointing each new version of...

Martin,

Oh, like thaaaaaat!!!  That's a lot to digest, but I have my Ralph Kimball secret decoder ring working. Makes sense!  now to figure out how to create a table function...

Wow, Martin! Thanks! I understand most of it. The part I was waaaay wrong on is that YES, the changing prices DO get somewhat merged into the "Recipe"/ProductIngredient table.  (I...

Eirikur,

(Oh wait, before I go too far, all of this mess is in DAX/PowerBI...  "Run away, run away!" --King Arthur)

What do you mean by Scopes? ISINSCOPE() kinds of scopes?  the...

There's a Sales fact, but that part is easy, once all the dimension stuff is fixed, so I left it out.  Product is related to Sales too. I'm just having...

I always think of EXISTS as being useful for testing if one table has ANY related records in another. (So the exact records in the related table aren't important to...

At least the cert exams when I did them forced me to learn about and use features I didn't know about. That part was super useful.

Think I'm gonna bite the bullet and rebuild with sane field types etc and see what happens. =)

But to answer your question, yes - the goal is to take a...

• This reply was modified 6 months, 4 weeks ago by  pietlinden.

Oh I get it! (duh!)

split the recipe into two parts in the CTE.

Part 1 is all the ingredients, weights for everything except the starter

UNION ALL

(For each ingredient in the Starter,...

Oh I get it! (duh!)

split the recipe into two parts in the CTE.

Part 1 is all the ingredients, weights for everything except the starter

UNION ALL

(For each ingredient in the Starter,...

So does Poolish (a recipe) ALSO have a starter?

No. Poolish / Starter is what the yeast grows in.  Not that it matters that much, but Poolish is 50% flour, 50%...

yeah, you know, the database version of a weeping sore.

No, I didn't. gotta try that.  Okay, seems to be okay now. Thanks for the tip!