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

View previous topic View next topic Go down

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

Post  123dw456 on Sat Mar 27, 2010 1:18 pm

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?

123dw456

Posts : 1
Join date : 2010-03-27

View user profile

Back to top Go down

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

Post  Mj1978 on Mon Apr 05, 2010 12:39 pm

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
avatar
Mj1978

Posts : 8
Join date : 2010-03-10

View user profile

Back to top Go down

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

Post  ngalemmo on Mon Apr 05, 2010 1:15 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum