How to Model Store-specific Product attributes

Hello -

I'm trying to determine the best way to model Store specific product attributes and need a second (or third) opinion. Here's the deal...

Pretty straightforward retail scenario - Products are sold thru stores. Most Products are sold in all stores, though some products are unique to certain stores. There are about 100,000 products total (all stores), and there are about 20 stores. Most product attributes are inherent to the product (size, brand, etc) but naturally, some are store specific (Store Department, Price, etc). The stores have the ability to define their own departments and set their own prices.

In my Model, I have the following:
- a Product dimension, which will contain all SKUs for all products.
- a Store dimension
- a Sales fact table which is individual Sale transactions.

My question is what to do with the store specific attributes?
Option 1) I'm thinking that there should be some sort of 'StoreProductDetails' dimension which would contain a record for each product/store combination, and that this would be related directly to the fact table with it's own fk.

Option 2) A colleague suggested a single Product dimension which would be at the Store/product grain. This would mean that products would be in the dimension multiple times - once for each store in which they are sold. I don't like this approach, but the 'StoreProductDetails' seems like it's essentially that, so now I'm not sure (though I can't imagine that this is the best approach - please confirm).

Option 3) A junk dimension for the Store/Product attributes - though this could get quite large as the client is thinking of adding more store-specific attributes in the future.


I'm also wondering about the best way to relate this data. If the two dimension approach is indeed best (i.e. Option 1), then since dimStoreProductDetails has both the Store key and the Product key, should it be related to the Store and the Product dimensions - like a bridge table? Or is it best to relate it directly to the fact table (or both???)

Of course, any other ideas are welcome. Thanks in advance for your help.

- Jim

I'm not sure Store Department should be in the Product dimension.

Store department could be it's own dimension or part of a junk dimension. I would think Price should be treated as a fact - the same product at the same store can have many prices over time.

I'm leery of option 1. Sounds to me like Product and Store are two discreet entities and should be modeled as two discreet dimensions.

I like option 2. The natural key on your ProductStoreDim table would be a composite of ProductID & StoreID. Then you'd insert the surrogate key from your ProductStoreDim into your fact table, along with the StoreDim surrogate key from your StoreDim table. As Jeff mentioned, your product price could then go into the fact. Now it would be stored at the correct grain.

If you need a dimension containing a single row per ProductID, then you could create a shrunken dimension off of ProductStoreDim (call it ProductDim, for instance). You'd drop any columns related to the store, leaving only product attributes that are ubiquitous across all the various permutations of the product at a store level. You could even store facts at this level too, but you'd need some business rules about how you'd choose which store-level price to assign for a single instance of a product. Maybe an average? It's likely the business would drive that decision.

I'm not close to your data, but reality sounds to me like ProductID just isn't unique by itself. If at any time the same ProductID could have two different prices and attribute values, then it's just not unique. Including the StoreID along with the ProductID as the ProductStoreDim table's natural key would solve that.

Your fact table now clearly answers the question, what is the Department and Price of Product "123" at store "XYZ".

I've successfully implemented something very similar to this at my present employer. I can provide examples if that helps.

Take care,
Brian

Thanks for the thoughts. I don't think I was clear enough on the differences between option 1 and option 2. BTW - some additional information to help clarify - there are roughly 25 attributes for a given product, of which 20 are inherent to the product (i.e. regardless of store, like Brand), and 5 are specific to the store they are sold in (like Product Department, etc).

So, the choice is pretty much this:

Original Option 2 is basically all 25 product-related attributes in a single dimension, which is at a Product-Store grain. This dimension would have roughly 2 Million rows (100,000 products x 20 stores).
OR
Original Option 1 - a Product-store dimension for only the 5 store specific attributes (i.e. dimStoreProductDetails) which would have ~ 2 million rows, and then also a separate Product dimension for the 20 inherent product attributes. This dimension would only have ~ 100k rows.

My original thought was that Option 1 would be better from a performance standpoint. I agree that Product by itself isn't unique enough for all analysis, but it is for many. This is the reason I considered Option 1.

I guess price could go in the fact table, but it's not additive, and it might be used like an attribute (like for bucketing), which is why I thought it should be in a dimension (too?)

So I guess I'm really wondering if a 2 Million row Product table will perform well enough (I know there are lot so factors for performance including server(s), indexes, etc, but I'm just thinking from a design standpoint and choosing between a 100k record table vs a 2 Million record table). After writing that last statement, I think maybe option 1 is still best, but I'm still very interested if you have any additional thoughts.

Thanks again...

A product is a product is a product. It has a SKU and attributes related to that SKU. Who sells it, where it is sold, who gets the revenue and how much is charged are NOT attributes of a product... they are context of the activity and should be represented by other dimensions or measures.

In other words, price is a measure (or more accurately a derived measure from quantity and extended price) and department and store are their own dimensions.

If you need to keep track of what products are sold by which departments in which stores, you can create a separate fact table to track that. It is not something you should need to deal with in your sales fact table.

Thanks for the reply and the reminder that the Product dimension should really only have the intrinsic atttributes of the Product. I think what was confusing me was that the client calls this Product Division, but it is really a separate entity and requires a separate dimension. It's related to how the product is used, and therefore needs to be related via the fact table(s).

Thanks again for the sanity check.