May 8, 2008 at 11:19 am
Hi there,
I have a table for Items, which has a size column. There are 5 possible values for size, and any given Item can be in one or more sizes.
I am trying to model inventory assortment of items by various dimensions, including size.
Currently I have a FactShop table which has nothing but FKs to DimCustomer and DimItem. There is a many-to-many relationship that joins Customers to Items via FactShop so I can see how many people have a given Item, and see how many Items a given Customer has.
I need to add in this size data for further detail, and am wondering if it makes sense to add it to the dimension table (which would mean I would have multiple rows for a given item where it fit more than one size), or if it should become a Fact.
Thanks!
Chris
May 20, 2008 at 12:57 pm
In general, I'd say yes, there should be separate items. If something can be different sizes, then generally they can't truely be the same thing.
The exception to this is when you have things like "size of x". In that case, size could (should) be a measure. The great thing about MSAS, is that it'll allow you to treat that field as both a measure and a dimension.
May 20, 2008 at 3:05 pm
It sounds like you're still missing a fact table...what in your design is being aggregated? I tend to approach these challenges by looking at all my data points independently and thinking of how they are to be consumed by the user. If they are aggregated in some way, generally they are facts, if they are used to 'slice and dice' then usually you're working with dimension data. Sometimes there are those that can be both (eg. Dates) but so far the approach I describe above has worked for me...
Ben Sullins
bensullins.com
Beer is my primary key...
May 21, 2008 at 2:51 am
It depends by the use/functionality you want to have.
By example, Do you will use Size as filter in some analysis/reports? Or do you want to have some histogram with size as one of the axis?
If the answer is yes, it is good to have size as dimension.
If you use size as a number in a report (i.e. a cell, not a row/column header) it is good to have as measure/fact.
If you think that both the cases are valid, use as a dimension and as measure.
BTW, I believe you have also the time dimension in your fact. If not, you will have some trouble. 🙂
May 21, 2008 at 10:51 am
Hi all,
Thank you for the help and info.
This is all really great stuff, but I have re-architected the data and am now down another path, which has questions listed in this thread:
http://www.sqlservercentral.com/Forums/Topic497288-147-1.aspx
If any of you would be so kind as to check that out and give forth some more wisdom, I would be very grateful!
Cheers,
Chris
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply