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

Interview Candidate
Nov 7th, 2014
3
7993

Data Modeling

Answer

Showing Answers 1 - 3 of 3 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.

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