"DNG" <dotnetguy@earthlink.net> wrote in message
news:uA8G%2333VFHA.2172@tk2msftngp13.phx.gbl...
> Fast food chain store (Think the likes of Burger King or McDonald's).
Items
> are sold separately or combined. Need to know, for each item sold, what
are
> the ratios that any other items are sold in the same order.
>
> For example, one day during 5-6PM, 200 Cokes were sold in 160 transactions
> (orders). 75 of them were part of the combo's. Within those 160
> transactions, there also sold 140 cheese burgers, 130 French fries (85 of
> them were part of the combo's), . That is, in that time period, 200 Cokes
> were sold in transactions that also sold 85 French fries in combo, 45
French
> fries not in combo; 140 cheese burgers, etc. The data warehouse is to
> provide information like: at 8-9AM, 234 Fries were sold in transaction
that
> also sold 48 Cokes and 93 Coffee; at 7-8PM 435 Fries were sold in
> transactions that also have 238 Cokes and 49 Diet Coke, and so on.
Business
> calendar, store structure, and order size (how much per order) are other
> dimensions.
>
> There are hundreds of items in tens of categories. And also hundrends of
> stores. So, what the best way to
> design this?
>
> Thanks a lot!
>
> DNG
>
>
>
>

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:uparCVYWFHA.2124@TK2MSFTNGP14.phx.gbl...
> What you need to define is a Categories dimension that includes
> subcategories and sub-sub-categories (which will probably be items?)
>
> Something along the lines of:
>
> Categories
> -----------
> CategoryId (surrogate PK)
> CategoryName
> SubCategoryName
> ItemName
>
>
> I'm not sure what a category means to you, but I'll assume it's something
> like 'Lunch - Sandwiches'. So you'd populate rows like:
>
> 'Lunch - Sandwiches', 'Burgers', 'Cheeseburger'
> 'Lunch - Sandwiches', 'Burgers', 'Hamburger'
> 'Lunch - Sandwiches', 'Chicken Sandwiches', 'Fried Chicken Sandwich'
> 'Lunch - Sandwiches', 'Chicken Sandwiches', 'Grilled Chicken Sandwich'
> 'Lunch - Side Dishes', 'French Fries', 'Large French Fries'
>
> Now, you can easily create combos. For instance, a combo with a
> cheeseburger and large fries:
>
> 'Lunch - Combos', 'Combo Number 1', 'Cheeseburger'
> 'Lunch - Combos', 'Combo Number 1', 'Large French Fries'
>
> Now a user can easily answer any questions about french fry sales --
> whether
> or not those fries were purchased in a combo.
>
> Make sense?
>
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>
> "DNG" <dotnetguy@earthlink.net> wrote in message
> news:uA8G%2333VFHA.2172@tk2msftngp13.phx.gbl...
>> Fast food chain store (Think the likes of Burger King or McDonald's).
> Items
>> are sold separately or combined. Need to know, for each item sold, what
> are
>> the ratios that any other items are sold in the same order.
>>
>> For example, one day during 5-6PM, 200 Cokes were sold in 160
>> transactions
>> (orders). 75 of them were part of the combo's. Within those 160
>> transactions, there also sold 140 cheese burgers, 130 French fries (85 of
>> them were part of the combo's), . That is, in that time period, 200 Cokes
>> were sold in transactions that also sold 85 French fries in combo, 45
> French
>> fries not in combo; 140 cheese burgers, etc. The data warehouse is to
>> provide information like: at 8-9AM, 234 Fries were sold in transaction
> that
>> also sold 48 Cokes and 93 Coffee; at 7-8PM 435 Fries were sold in
>> transactions that also have 238 Cokes and 49 Diet Coke, and so on.
> Business
>> calendar, store structure, and order size (how much per order) are other
>> dimensions.
>>
>> There are hundreds of items in tens of categories. And also hundrends of
>> stores. So, what the best way to
>> design this?
>>
>> Thanks a lot!
>>
>> DNG
>>
>>
>>
>>
>
>

"DNG" <dotnetguy@earthlink.net> wrote in message
news:OD7XtPkWFHA.2684@TK2MSFTNGP09.phx.gbl...
> Thank you very much, Adam.
>
> My biggest challenge is not with combo. The most important thing is to
find
> what people buy in a single order. For example, with information like 24%
of
> order which include cheeseburger also include French fries; while 78% of
> order with "big and tasty" also include French fries, so we know it makes
> more sense to have a combo of "big and tasty" plus French fries.
>
> I need some advice on cube design.
>
> Thanks,
>
> DNG
>
>
> "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
> news:uparCVYWFHA.2124@TK2MSFTNGP14.phx.gbl...
> > What you need to define is a Categories dimension that includes
> > subcategories and sub-sub-categories (which will probably be items?)
> >
> > Something along the lines of:
> >
> > Categories
> > -----------
> > CategoryId (surrogate PK)
> > CategoryName
> > SubCategoryName
> > ItemName
> >
> >
> > I'm not sure what a category means to you, but I'll assume it's
something
> > like 'Lunch - Sandwiches'. So you'd populate rows like:
> >
> > 'Lunch - Sandwiches', 'Burgers', 'Cheeseburger'
> > 'Lunch - Sandwiches', 'Burgers', 'Hamburger'
> > 'Lunch - Sandwiches', 'Chicken Sandwiches', 'Fried Chicken Sandwich'
> > 'Lunch - Sandwiches', 'Chicken Sandwiches', 'Grilled Chicken Sandwich'
> > 'Lunch - Side Dishes', 'French Fries', 'Large French Fries'
> >
> > Now, you can easily create combos. For instance, a combo with a
> > cheeseburger and large fries:
> >
> > 'Lunch - Combos', 'Combo Number 1', 'Cheeseburger'
> > 'Lunch - Combos', 'Combo Number 1', 'Large French Fries'
> >
> > Now a user can easily answer any questions about french fry sales --
> > whether
> > or not those fries were purchased in a combo.
> >
> > Make sense?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.datamanipulation.net
> > --
> >
> >
> > "DNG" <dotnetguy@earthlink.net> wrote in message
> > news:uA8G%2333VFHA.2172@tk2msftngp13.phx.gbl...
> >> Fast food chain store (Think the likes of Burger King or McDonald's).
> > Items
> >> are sold separately or combined. Need to know, for each item sold, what
> > are
> >> the ratios that any other items are sold in the same order.
> >>
> >> For example, one day during 5-6PM, 200 Cokes were sold in 160
> >> transactions
> >> (orders). 75 of them were part of the combo's. Within those 160
> >> transactions, there also sold 140 cheese burgers, 130 French fries (85
of
> >> them were part of the combo's), . That is, in that time period, 200
Cokes
> >> were sold in transactions that also sold 85 French fries in combo, 45
> > French
> >> fries not in combo; 140 cheese burgers, etc. The data warehouse is to
> >> provide information like: at 8-9AM, 234 Fries were sold in transaction
> > that
> >> also sold 48 Cokes and 93 Coffee; at 7-8PM 435 Fries were sold in
> >> transactions that also have 238 Cokes and 49 Diet Coke, and so on.
> > Business
> >> calendar, store structure, and order size (how much per order) are
other
> >> dimensions.
> >>
> >> There are hundreds of items in tens of categories. And also hundrends
of
> >> stores. So, what the best way to
> >> design this?
> >>
> >> Thanks a lot!
> >>
> >> DNG
> >>
> >>
> >>
> >>
> >
> >
>
>