Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Recipe Table

4 posters

Go down

Recipe Table Empty Recipe Table

Post  nirst Mon Aug 05, 2013 9:30 am

Hi I've got a nice design problem which I'm hoping someone can give me some advice on...

My data relates to the sales for a restaurant chain. I have a star schema in my DWH with a factSales (containing restaurant orders) and a factMenuItems (containing all the products on the menu) linked by a surrogate MenuItemKey field.

Now I have been given a new data source to bring into the mix - a recipe breakdown. This data shows how each menu item is broken down into the constituent ingredients. But it's not a simple 1:n relationship here - some menu items contain what are called 'batch ingredients' which are themselves a recipe in this recipe breakdown table. For example:


Recipe IDRecipe NameQtyUnitIngredient
1234Tomato Ravioli100gRavioli
1234Tomato Ravioli250gTinned Tomatoes
5678Ravioli20gFlour
5678Ravioli1itemEgg

Notice that the 'Ravioli' ingredient in Recipe 1234 is itself a recipe with ID 5678. The 'Tomato Ravioli' will relate back to the dimMenuItems. The real data has more detail/business keys etc, but this illustrates the problem.

I'm struggling to work out the best design to put this into my existing DWH schema. Should I have an ingredients dimension? Do I need a design with a link table?

Has anybody come across a design like this before? How did you deal with it within the rules of good DWH design?

Thanks,

Nick

nirst

Posts : 3
Join date : 2013-08-05

Back to top Go down

Recipe Table Empty Re: Recipe Table

Post  ngalemmo Mon Aug 05, 2013 10:31 am

It's no different than any manufacturer. You have sales orders for products you make. Products have a bill of materials (recipe). The recipe is a bridge table between products and raw materials.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Recipe Table Empty Re: Recipe Table

Post  nirst Tue Aug 06, 2013 6:04 am

Thanks for the reply but I'm not clear on how to deal with these 'batch items' - they are ingredients, but are also themselves recipes. So where do I store them? In the dimIngredients table?

nirst

Posts : 3
Join date : 2013-08-05

Back to top Go down

Recipe Table Empty Re: Recipe Table

Post  ngalemmo Tue Aug 06, 2013 8:46 am

Just have one dimension for everything. How it is used is a role.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Recipe Table Empty Re: Recipe Table

Post  nirst Tue Aug 06, 2013 10:44 am

Forgive me I'm not sure what you mean by a role? And how do I deal with the required self joining?

nirst

Posts : 3
Join date : 2013-08-05

Back to top Go down

Recipe Table Empty Re: Recipe Table

Post  LAndrews Tue Aug 06, 2013 4:33 pm

Think of the menu as a ragged hierarchy of ingredients. (i.e. numerous parent-child relationships).

The bridge table contains all the combinations that make up the hierarchy.

The grain of your fact table and the type of analysis will dictate how you join to the bridge table.

Take a look at :

http://www.kimballgroup.com/1998/09/02/help-for-hierarchies/


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

Back to top Go down

Recipe Table Empty Re: Recipe Table

Post  gvarga Sun Aug 11, 2013 9:40 am

Hi!
You didnít clarify the analysis purposes in your DW, therefore first I suggest a model ( normalized for storing the ingredients of a recipe), than a solution for the dimensional model based on the first model.

1. Normalized model:
Ingredient table
1 Flour
2 Egg
3 Tinned Tomatoes

Recipe table
1234 Tomato Ravioli
5678 Ravioli

Ingredients of Recipes table is an intersection table: each row refers to either an ingredient row or to a recipe row:
Recipe id Ingredient id Ingr.Recipe id Qty Unit
1234 † † 3 † † † † † † † † NULL † † † 250 g
1234 † † † †NULL † † † † † 5678 † † † † 100 g
5678 † † † † 1 † † † † † † † NULL † † † † † 20 g
5678 † † † † 2 † † † † † † † NULL † † † † † † † † † 1 item

2. Dimensional model: I suppose you want to analyze from the ingredients. You have to generate a simple bridge table on the basis of the previous intersection table ( ingredients of recipes). The only difference is that you substitute the recipe ingredients with the raw ingredients ( maybe they are nested on several levels). The quantities may vary ( we donít know at the moment what is †the weight of a portion ravioliÖ)

Recipe id Ingredient id Qty Unit
1234 3 250 g
1234 1 20g
1234 2 1 item
5678 1 20 g
5678 2 1 item

gvarga

Posts : 43
Join date : 2010-12-15

Back to top Go down

Recipe Table Empty Re: Recipe Table

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum