d1cjm1ex
asked on
Dimensional Data Model Help
I am trying to break a large data set that contains purchase information into a dimensional model. I have various attributes related to the Item purchased. I do have an Item Number but it is not unique across different manufacturers. The Item is unique by MFR_ID and Item_ID. I have a Manufacturer dimension that contains a MFR_ID and MFR_NAME.
In my Item dimension, would it be best to include both these fields as separate attributes in this dimension or should I concatenate them to create one unique item identifier?
In my Item dimension, would it be best to include both these fields as separate attributes in this dimension or should I concatenate them to create one unique item identifier?
As an alternative you can select MFR_ID and Item_ID as composite key containing two columns (you can do this in the data source view definition).
IMHO if you want to have two separate dimensions (Manufacturer and Product) you shouldn't use MFR_ID as an attribute in the Product dimension - users will have two MFR_ID attributes - one in the Manufacturer dimension and one in the Product diemnsion. It could be confusing.
Of course if you plan to have a hierarchy Manufacturer --> Product you can create one dimension containing manufacturer attributes and product attributes. Then you have to set proper attribute relationships.
HTH
Best regards
Grzegorz
IMHO if you want to have two separate dimensions (Manufacturer and Product) you shouldn't use MFR_ID as an attribute in the Product dimension - users will have two MFR_ID attributes - one in the Manufacturer dimension and one in the Product diemnsion. It could be confusing.
Of course if you plan to have a hierarchy Manufacturer --> Product you can create one dimension containing manufacturer attributes and product attributes. Then you have to set proper attribute relationships.
HTH
Best regards
Grzegorz
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
nmcdermaid: I agree entirely.
Reassuring to know :) Of course the final solution is dictated by the problem and the constraints, of which we don't know all, but its good to start with a 'theoretical' approach and work backwards as constraints are encountered.
Manfacturer could form a level in one of your hierarchies within the Item dimension, as well as product type / classification etc. - this could remove the need the need to have separate dimensions for Maufacturer and product, unless you have a specific need for them.