modelling Product dimension for Pizza outlet

View previous topic View next topic Go down

modelling Product dimension for Pizza outlet

Post  dkalyan on Thu Oct 08, 2009 12:43 am

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

ProductProduct type
HawaiianPizza
Chicken CarbPasta

Components are the mandatory selections a customer is required to make (where applicable)
Component TypeComponent
CrustsThin
CrustsDeep
SauceBBQ
SauceTomato
Pasta PackageIn Box
Pasta PackagePuff Bowl


below is what I am thinking of as a product dimension

ProductIDProductProduct TypeCrustSaucePackaging
1HawaiianPizzaThinBBQn/a
2HawaiianPizzaThinTomaten/a
3HawaiianPizzaDeepBBQn/a
4Chicken CarbonaraPastan/an/aIn Box
5Chicken CarbonaraPastan/an/aPuff Bowl

What are your thoughts on this?

dkalyan

Posts : 5
Join date : 2009-10-08
Location : Australia

View user profile

Back to top Go down

Re: modelling Product dimension for Pizza outlet

Post  ngalemmo on Thu Oct 08, 2009 11:19 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: modelling Product dimension for Pizza outlet

Post  dkalyan on Thu Oct 08, 2009 7:32 pm

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
ProductToppingQtyDD OrderNo,LineNo
HawaiianHam-1123
HawaiianBacon+1123

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?

dkalyan

Posts : 5
Join date : 2009-10-08
Location : Australia

View user profile

Back to top Go down

Re: modelling Product dimension for Pizza outlet

Post  ngalemmo on Fri Oct 09, 2009 3:12 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Minidimension

Post  Andrea Vincenzi on Mon Oct 12, 2009 8:30 am

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
avatar
Andrea Vincenzi

Posts : 8
Join date : 2009-02-04
Age : 62
Location : Rome (Italy)

View user profile http://www.olap.it

Back to top Go down

Re: modelling Product dimension for Pizza outlet

Post  BoxesAndLines on Mon Oct 12, 2009 6:30 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: modelling Product dimension for Pizza outlet

Post  dkalyan on Mon Oct 12, 2009 7:23 pm

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?????

dkalyan

Posts : 5
Join date : 2009-10-08
Location : Australia

View user profile

Back to top Go down

Create Junk Dimension instead of Mini Dimension or Cross Join

Post  madhuker190783 on Wed Sep 09, 2015 5:12 am

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.

madhuker190783

Posts : 1
Join date : 2015-09-09

View user profile

Back to top Go down

Re: modelling Product dimension for Pizza outlet

Post  LAndrews on Mon Sep 14, 2015 7:33 pm

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.

LAndrews

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

View user profile

Back to top Go down

Re: modelling Product dimension for Pizza outlet

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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