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 transaction.

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 transaction 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. So, what the best way to
design this?

No answer from my previous post, so I am going to have a three hundred
dimention data warehouse?

Thanks a lot!

DNG

I'm not sure why you think you'd need 300 dimensions based upon your
description, but no, you shouldn't need that many. (Good [insert your diety
of choice here]!!! Could anyone imagine a 300 dim cube?! ZOINKS!!

Me being a smart-alec aside, what it sounds like you need is a typical
product dimension, and a date/time dimension, that drills down to the hour,
and a distinct count measure for orders/transactions.

As far as distinct transactions, which is probably the tricky part here,
you'd want to be sure your fact data has a "transaction ID" or "order id"
that makes each order unique from the transactional source, even though it
has multiple products (possibly) in that transaction (meaning it wouldn't be
unique in the Warehouse, in the grain your talking about here, it appears
that order id/product id really is the unique qualifier, or line item per
se). You could set the the "order ID" to a distinct count type of aggregate
so that when you do your analysis, the number of orders won't be overstated.

Also, the order ID distinct count measure should be in a cube of its own,
due to the resource intensive nature of distinct count measures, and should
be joined to the other cube that has no distinct count measures in it via a
virtual cube. (This isn't as bad as it seems, just be sure to include ALL
dimensions in both cubes, one with the distinct count measure only, and the
other with all other non distinct measures when creating the virtual)
There's a good article on how to do this:

http://support.microsoft.com/default...b;en-us;304215

Very interesting project, you're working on. Good luck!!

- Phil




"DNG" wrote:

Sorry, I left one aspect out of my last post. It sounds like each line item
(transaction id/product id) in your fact table/view should have a combo
qualifier (like a 'Y' if it is or an 'N' if it's not) so you can do your
analysis of whether or not it was included in a combo. If that's included in
your product dimension, then you wouldn't have to worry.

For example, if you had a "hamburger" and "hamburger/part of combo" as two
different product members.

Another way to do it would be to have a combo dimension that indicates which
combo the product is part of, if any. If the product on that line doesn't
belong to a combo, it can have the "not part of combo" member assigned to it
from that combo dimension.

Good luck!

- Phil




"DNG" wrote:

Thank you very much, Phil.

Let's say we build a fact table with columns like:

OrderDateTime DATETIME,
StoreID INT,
OrderID INT,
ProductItemID INT,
PartOfCombo BIT,
ItemAmount INT

I still am not clear how to build cudes and queries to get the files
results. More help please!

Many many thanks,

DNG


"SQL McOLAP" <SQLMcOLAP (AT) discussions (DOT) microsoft.com> wrote