Different dimensional attributes in different 'dimension-like' source tables

[123dw456]

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?

[Mj1978]

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

[ngalemmo]

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.

[Sponsored content]