modelling Product dimension for Pizza outlet

I have product dimension modelling query for a pizza outlet. This is for a sales data mart I am designing.

The products we sell are Pizzas, Pasta etc...,
for each Pizza the customer selects a crust(base) and sauce. Similarly for Pasta the customer selects to have that in a box or in a PUFF(edible) bowl.
(**at this stage I am not interested in analysing toppings.**)
For the product dimension, would I only store Product attributes and not the base and sauce or should I create a cross join between all Pizzas, crusts and sauces and insert these combinations as individual products in my product dimension.
My thoughts are the cross join, although my colleagues are leaning towards creating 3 separate dimensions for Crusts, Sauces and Pasta selections.

Below is a mockup of the source data

Source data
Products available for a customer to buy

Product	Product type
Hawaiian	Pizza
Chicken Carb	Pasta

Components are the mandatory selections a customer is required to make (where applicable)

Component Type	Component
Crusts	Thin
Crusts	Deep
Sauce	BBQ
Sauce	Tomato
Pasta Package	In Box
Pasta Package	Puff Bowl

below is what I am thinking of as a product dimension

ProductID	Product	Product Type	Crust	Sauce	Packaging
1	Hawaiian	Pizza	Thin	BBQ	n/a
2	Hawaiian	Pizza	Thin	Tomate	n/a
3	Hawaiian	Pizza	Deep	BBQ	n/a
4	Chicken Carbonara	Pasta	n/a	n/a	In Box
5	Chicken Carbonara	Pasta	n/a	n/a	Puff Bowl

What are your thoughts on this?

It makes sense. For toppings, I would use a multivalued dimension.

Thanks for the reply,

for topping, what do you mean by multivalued?
Is that basically extending the product dimension with a new column and putting all toppings in the one column as a string?

There is a requirement to be able to analyse what additional toppings were added/removed from a pizza - we would like to be able to see what toppings are being used extensively over time.
For this, I was more thinking on another fact table that is based on capturing amendments to a pizza (toppings added/removed). e.g. below

Product	Topping	Qty	DD OrderNo,LineNo
Hawaiian	Ham	-1	123
Hawaiian	Bacon	+1	123

Which would then use an additional Toppings dimension.


Also, for my Bill of materials of the products, would this be a bridge table between Product and Topping dimensions?

A multivalued dimension is a dimension that can have one or more values associated with it, such as diagnosis on a medical claim. Its discussed in Toolkit, or you can Google the term.

But since you are tracking amendments (i.e. a Hawaiian with no pineapple ... not sure if that makes any sense...) a fact table may be better for some analysis. But, I would still suggest considering the multivalued dimension structure as it makes it real easy to look at combinations of things.

Another possibile solution is to use a minidimension (DW Toolkit, page 155). Basically, you create a dimension with the attributes that you want to analyze and then connect both the product dimension and the minidimension to the facts.
In your case, the minidimension would contain something like this:



Crust Sauce Packaging
Thin BBQ n/a
Thin Tomate n/a
Deep BBQ n/a
n/a n/a In Box
n/a n/a Puff Bowl
... ... ...

The minidimension can be generated statically with the catesian product of all the attribute values or dynamically, inserting a new row each time a new combination is found (each approach has its pros and cons).
To track individual additions/deletions of single components you will still need a dedicated fact table. One possibility could be to create a fact table with the order number and two links to the minidimension: the original, "standard" configuration (if there is such a thing) and the new "custom" configuration.
Andrea

I don't know. This sounds a little too much like homework. Along that vein, are colleges actually teaching this stuff now? This looks like a great class if it is.

thanks for all the suggestions, I guess the mini dimension is a very good alternative, starts me thinking on the lines of there is not a direct link between the product and the mandatory selection options, e.g. product = 'Hawaiian' and selection = 'Thin & BBQ'

Decisions,,, should I create a cross join between product and selections or split into products and selection mini dimension?????

Creation of Junk Dimension will help the cause.  Create Junk Dimension on Topup's.

Junk Id Crest   Sauce Paste Package
1         Thin   BBQ INBOX
2         Thin   BBQ Puff Bowl
3         Thin   Tamato INBOX
4         Thin   Tamato Puff Bowl
5         Deep   BBQ INBOX
6         Deep   BBQ Puff Bowl
7         Deep   Tamato INBOX
8         Deep   Tamato Puff Bowl

This way, it will give all the combination of Crest, Sauce and Paste Package for every product even if new products are added.

Creation of Multi valued dimension will increase Redundancy data and mini dimension are maintenance issue.

Please correct me if my approach is wrong.

Its actually an interesting modelling problem, many things to consider. I looked at it for a pizza client a couple years ago.

First, we had to decide on what a "product" was. In our original thinking a product equated to a menu item. (e.g. "Hawaiian Deluxe Pizza")

Second, we separated mandatory selections from customization. For example, each Pizza item had mandatory selections. (e.g. Size, Crust, Sauce). The combination of Menu item + manditory selection equated to "Product" (Grain of product dimension)

The challenging wrinkle became the customizations. Consider these modifiers as items that are not on the menu themselves, but are additions/subtractions to the menu items. (i.e. Ingredients).
These also have a portion aspect to them. (e.g. Double Ham on 1/2 pizza, double pinapple on other 1/2 pizza). There can be zero-to-many modifiers for each ordered product.

Once you get your head around that - the modelling becomes clearer. Modifiers may be modelled with their own fact, or perhaps with a bridge table. It will depend on the POS your are getting info from, and the type of analysis you are hoping to achieve.