SCD2 Product Dim has multiple categories which can change over time
5 posters
Page 1 of 1
SCD2 Product Dim has multiple categories which can change over time
Hi,
I have a product dimension that has SCD2 columns. Each product can be in multiple categories. The assignment of a product to a category can change over time.
I am considering a table of categories with a bridge table to the dimension. The bridge table would have effective and end date to accommodate the changes in assignment of a product to a category. Assuming this is fine, my question is what key to use in the bridge table to relate to the product dimension? I have thought of a couple of options:
 Use the natural key to join the bridge table to the current or past row of the product dim, but I would prefer not t use the natural key.
 Use the dim's surrogate key, but then the bridge table could be related to a prior version of the product dim if product changes. That would require another join to get the current product (using the natural key, so maybe using the natural key on the bridge table is not so bad?).
 Generate a new set of bridge table rows whenever a new row was created in the product dim, but that seems clumsy.
I would appreiciate any feedback.
Thanks,
Greg
I have a product dimension that has SCD2 columns. Each product can be in multiple categories. The assignment of a product to a category can change over time.
I am considering a table of categories with a bridge table to the dimension. The bridge table would have effective and end date to accommodate the changes in assignment of a product to a category. Assuming this is fine, my question is what key to use in the bridge table to relate to the product dimension? I have thought of a couple of options:
 Use the natural key to join the bridge table to the current or past row of the product dim, but I would prefer not t use the natural key.
 Use the dim's surrogate key, but then the bridge table could be related to a prior version of the product dim if product changes. That would require another join to get the current product (using the natural key, so maybe using the natural key on the bridge table is not so bad?).
 Generate a new set of bridge table rows whenever a new row was created in the product dim, but that seems clumsy.
I would appreiciate any feedback.
Thanks,
Greg
Greg Posts : 2
Join date : 20120220
Re: SCD2 Product Dim has multiple categories which can change over time
Try framing the problem a little differently...
It's nothing to do with the product dimension. Its a category dimension that is driven by product. So, yes, you use a bridge as it is a multivalued dimension. And you can use dates in the bridge to accommodate history. But the bridge (as is any bridge) is between facts and the category dimension. The foreign key off the fact is based on the product, but you want a type 1 key to make life easier (since history is in the bridge).
You can either create a separate table with nothing but the product natural key and a surrogate primary key for lookup purposes, or add a 'type 1 key' attribute in the product dimension, again solely for lookup purposes. This key, in addition to the category dimension keys, would be used to build the bridge. You would not join this bridge to the product dimension in normal use. Using the second option would make it easier to do dimension only reporting, such as lists of products by category.
It's nothing to do with the product dimension. Its a category dimension that is driven by product. So, yes, you use a bridge as it is a multivalued dimension. And you can use dates in the bridge to accommodate history. But the bridge (as is any bridge) is between facts and the category dimension. The foreign key off the fact is based on the product, but you want a type 1 key to make life easier (since history is in the bridge).
You can either create a separate table with nothing but the product natural key and a surrogate primary key for lookup purposes, or add a 'type 1 key' attribute in the product dimension, again solely for lookup purposes. This key, in addition to the category dimension keys, would be used to build the bridge. You would not join this bridge to the product dimension in normal use. Using the second option would make it easier to do dimension only reporting, such as lists of products by category.
Re: SCD2 Product Dim has multiple categories which can change over time
Thank you, that would easier to model and to query on. To follow up , using the bridge table to the fact table means the participation of a product in a each category must be represented for every sale (we have quite a few, hundreds of millions presently) instead of for every product, maybe 100,000. Plus the fact table will have seven extra FKs to new dimensions (we have many types of categories (some with low cardinality, such as 2 per product (I did not denormalize these into the dim since it is likely there will be more), but one has 16 categories per product). This is a webbased retail sales dw, so products are categorized in all sorts of ways to be able to present them to a customer.
I can see that what you suggest would be easier to model, but it would consume more space and I wonder about the performace of the bridge tables to the fact tables. I have used bridge tables to fact tables before, but not in a situation they could be related to a dim.
I can see that what you suggest would be easier to model, but it would consume more space and I wonder about the performace of the bridge tables to the fact tables. I have used bridge tables to fact tables before, but not in a situation they could be related to a dim.
Greg Posts : 2
Join date : 20120220
Re: SCD2 Product Dim has multiple categories which can change over time
That is what the bridge does. The fact would only have one FK per product (part of the fact grain) and the bridge contains every known productcategory relationship. The category dimension would contain something like a category type code to select a particular kind of category. The dates in the bridge would allow filtering down to a point in time.
Re: SCD2 Product Dim has multiple categories which can change over time
Depending on how dynamicly the productcategory corelation changes, to capture the changing relationship, you may have a periodical snapshot factless fact table (aka. coverage fact in your case) or an effective dated factless fact table. In either case, you should use SK to connect fact to any dimension table.
In my view, once you need to cater for the historical relationship in a bridge table, it becomes a factless fact table capturing the changing relationship along a time series. The time series is a date key in periodic snapshot, but can also be derived by effective dated fact (relationship). So in this type of fact table, the grain is not product, not category, but the relationship and you should use dimension conformance to connect this fact with other fact tables.
In my view, once you need to cater for the historical relationship in a bridge table, it becomes a factless fact table capturing the changing relationship along a time series. The time series is a date key in periodic snapshot, but can also be derived by effective dated fact (relationship). So in this type of fact table, the grain is not product, not category, but the relationship and you should use dimension conformance to connect this fact with other fact tables.
hang Posts : 528
Join date : 20100507
Location : Brisbane, Australia
Re: SCD2 Product Dim has multiple categories which can change over time
Hello,
as my issue seems to be closely related to this one I will post my question here:
I am building a data warehouse for an ecommerce shop (clothing) and building the dimensions around the sales order article fact table.
Every product has
a) one or more colors (usually not more than 4 different colors)
b) and can also belong to one or more categories (in the worst case 15+ categories)
While it makes sense to me to make a bridge to the categorie dimension I hestitate to do so for the colors, as it would be a really small dimension. Would it be better if I added 4 attributes in the product dimension or should I make a bridge and a color dimension?
as my issue seems to be closely related to this one I will post my question here:
I am building a data warehouse for an ecommerce shop (clothing) and building the dimensions around the sales order article fact table.
Every product has
a) one or more colors (usually not more than 4 different colors)
b) and can also belong to one or more categories (in the worst case 15+ categories)
While it makes sense to me to make a bridge to the categorie dimension I hestitate to do so for the colors, as it would be a really small dimension. Would it be better if I added 4 attributes in the product dimension or should I make a bridge and a color dimension?
boernard Posts : 13
Join date : 20120119
Re: SCD2 Product Dim has multiple categories which can change over time
Colors are usually classified as a primary and secondary color, so you may be able to get away with just two relationships to the Color dimensions (i.e. no bridge required). For category, it would certainly appear like a bridge is required although I'm not quite sure what category represents in your business.
BoxesAndLines Posts : 1212
Join date : 20090203
Location : USA
Re: SCD2 Product Dim has multiple categories which can change over time
thx for the reply. unfortunately we have cases where a product has 4 colours. the categories are the product categories like shirts, tshirts, pants, gloves, etc...
boernard Posts : 13
Join date : 20120119
Similar topics
» How to model article dimension for unpredictable changes with multiple versions and multiple article categories
» SCD2 Type Change Question
» What to do when a single attribute rolls up to multiple categories?
» Modeling goals which may slowly change over time
» Capturing Age at point in time  SCD2 / Calculated Member/ New Dimension??
» SCD2 Type Change Question
» What to do when a single attribute rolls up to multiple categories?
» Modeling goals which may slowly change over time
» Capturing Age at point in time  SCD2 / Calculated Member/ New Dimension??
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum

