Design a DWH Table based on the given source

We have a source which provides data in the following format? Design DWH tables based on this . Suggest whether you would keep the existing structure or whether you would break them apart into Separate Tables?
ItemId PropertyId Value
A1 10 Black
A1 20 16gb
A1 30 Iphone6
A1 1000 Apple

you have a Property Reference Table as well
PropetyID Value
10 Color
20 Memory Size
30 Item Name
100 Product Group

There can be more than 2000 distinct Properties
The table designed at the end should be capable of answering the following question?

How many White Iphone of 16gb Size were sold?
Thanks

Showing Answers 1 - 9 of 9 Answers

Nilesh

  • Nov 17th, 2014
 

It depends on your WH. It is EDW then you may require more normalized data. Looking at the data you can normalize it.

  Was this answer useful?  Yes

Damo

  • Feb 26th, 2015
 

Since this is DWH scope, i read this as 1 row (attributes of a product & order together)

ItemID ColorPropertyID ColorPropertyVal SizePropertyID SizePropertyVal ProductPropertyID ProductPropertyVal
A1 10 Black 20 16gb 30 iphone6

Benefits:

You can report them at once
depending upon your length of attributes, you may eventually want to group/categorise them, unlike 1000 attributes....assess benefits for each case
If these are slowly changing, you can still capture changes

  Was this answer useful?  Yes

Rajesh

  • Dec 5th, 2018
 

this is just another fact data.. So keep as one table with 2 dim keys. No change.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions