Different dimensional attributes in different 'dimension-like' source tables
3 posters
Page 1 of 1
Different dimensional attributes in different 'dimension-like' source tables
This is a simplified version of the modeling issue that I am facing. Looking to the experts for advice.
The product dimension has three attributes- sku, color and weight. Each attribute is stored in its own table with its own transaction date and effective date.
1) The product_sku table is like this:
Product_id, Sku, Transaction_date, Effective_date
P001, S001, 02/01/2010, 02/15/2010
2) The product_color table is like this
Product_id, Color, Transaction_date, Effective_date
P001, Blue, 01/05/2010, 02/01/2010
P001, Black, 01/31/2010, 02/17/2010
P001, White, 02/14/2010, 01/31/2010
3) The product_weight table is like this
Product_id, Weight, Transaction_date, Effective_date
P001, 1lb, 01/03/2010, 01/30/2010
P001, 2lb, 02/02/2010, 02/01/2010
P001, 3lb, 02/03/2010, 02/16/2010
Now, I want to create a product dimension table, product_dim, which has these fields:
product_sk, product_id, sku, color, weight, transaction_date, effective_date
1000, P001, S001, Color?, Weight?, Transaction_date?, Effective_date?
I am struggling with populating the right Color, Weight, transaction_date, and effective_date corresponding to SKU, S001.
Any advice from experts?
The product dimension has three attributes- sku, color and weight. Each attribute is stored in its own table with its own transaction date and effective date.
1) The product_sku table is like this:
Product_id, Sku, Transaction_date, Effective_date
P001, S001, 02/01/2010, 02/15/2010
2) The product_color table is like this
Product_id, Color, Transaction_date, Effective_date
P001, Blue, 01/05/2010, 02/01/2010
P001, Black, 01/31/2010, 02/17/2010
P001, White, 02/14/2010, 01/31/2010
3) The product_weight table is like this
Product_id, Weight, Transaction_date, Effective_date
P001, 1lb, 01/03/2010, 01/30/2010
P001, 2lb, 02/02/2010, 02/01/2010
P001, 3lb, 02/03/2010, 02/16/2010
Now, I want to create a product dimension table, product_dim, which has these fields:
product_sk, product_id, sku, color, weight, transaction_date, effective_date
1000, P001, S001, Color?, Weight?, Transaction_date?, Effective_date?
I am struggling with populating the right Color, Weight, transaction_date, and effective_date corresponding to SKU, S001.
Any advice from experts?
123dw456- Posts : 1
Join date : 2010-03-27
Re: Different dimensional attributes in different 'dimension-like' source tables
I would like to ask a question first of all.
As per the product_sku table
P001 with sku=S001 has a transaction recorded on 02/01/2010.
This product must have a Color and a Weight attribute attached to it.
So there should be a record each in both product_color and product_weight tables where the Transaction_date = 02/01/2010.
But I don't see any such transaction in these tables. I doubt if they are refrentially integrated.
And could you please explain what this Effective date means ?
Thanks
Manik
As per the product_sku table
P001 with sku=S001 has a transaction recorded on 02/01/2010.
This product must have a Color and a Weight attribute attached to it.
So there should be a record each in both product_color and product_weight tables where the Transaction_date = 02/01/2010.
But I don't see any such transaction in these tables. I doubt if they are refrentially integrated.
And could you please explain what this Effective date means ?
Thanks
Manik
Mj1978- Posts : 8
Join date : 2010-03-10
Re: Different dimensional attributes in different 'dimension-like' source tables
Product_id, Color, Transaction_date, Effective_date
P001, Blue, 01/05/2010, 02/01/2010
P001, Black, 01/31/2010, 02/17/2010
P001, White, 02/14/2010, 01/31/2010
Is this an accurate example, or are there typos in the data? What color is the product on 1/31/2010? Black? Blue? Or did it get into a fight and was both?
Assuming the date ranges really don't overlap, your biggest challenge would be loading history. Its a bit of tricky code, but not too bad. You need to join the three sources and figure out the effective date range of each valid combination. Current extracts are much simpler since you only need to combine those rows that are in effect at the time you are extracting.
If your example is accurate, how on earth does your company keep inventory? Standard practice is to assign unique SKU's to each form of a product. If there is a red one and a blue one, each should have a different SKU.
Similar topics
» Dimensional Attributes
» Best practice for date attributes of dimension tables
» dimension table design question for around 100 attributes and higher level calculated attributes
» Big dimensional attributes
» Dimensional Attributes
» Best practice for date attributes of dimension tables
» dimension table design question for around 100 attributes and higher level calculated attributes
» Big dimensional attributes
» Dimensional Attributes
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum