Multivalued attribute on Dimension
3 posters
Page 1 of 1
Multivalued attribute on Dimension
I would like to know how to model this. I've searched the forums and the Data Warehouse Tooolkit but don't see it covered.
I have a Product dimension with a multi-valued attribute called Characteristic. There are 25 characteristics currently. A given product could have anything between zero and many of theses characteristics. Queries/reports may constrain on this attribute.
I have a Product dimension with a multi-valued attribute called Characteristic. There are 25 characteristics currently. A given product could have anything between zero and many of theses characteristics. Queries/reports may constrain on this attribute.
RobLL- Posts : 7
Join date : 2013-12-09
Location : UK
Re: Multivalued attribute on Dimension
Just to add to my question...
I have found in the Data Warehouse Toolkit (second edition), page 205, the highlighted statement, "An open-ended many-valued attribute can be associated with a dimension row by using a bridge table to associate the many-valued attributes with the dimension." However, the example is all about associating many customers to an account by putting a bridge table between a Customer Dimension Table and an Account Dimension table. The fact is just associated with the Account Dimension. I can see the point of this where the Customer Dimension has more than one attribute. Is a simpler approach possible in the scenario I described in my original post? Do I need a Product Characteristic Dimension that just holds a surrogate primary key and the attribute 'Characteristic'? Can't I dispense with the Product Characteristic Dimension and bridge table and, instead, just have a Product Characteristic table that has a many to one relationship with the Product Dimension?
I have found in the Data Warehouse Toolkit (second edition), page 205, the highlighted statement, "An open-ended many-valued attribute can be associated with a dimension row by using a bridge table to associate the many-valued attributes with the dimension." However, the example is all about associating many customers to an account by putting a bridge table between a Customer Dimension Table and an Account Dimension table. The fact is just associated with the Account Dimension. I can see the point of this where the Customer Dimension has more than one attribute. Is a simpler approach possible in the scenario I described in my original post? Do I need a Product Characteristic Dimension that just holds a surrogate primary key and the attribute 'Characteristic'? Can't I dispense with the Product Characteristic Dimension and bridge table and, instead, just have a Product Characteristic table that has a many to one relationship with the Product Dimension?
RobLL- Posts : 7
Join date : 2013-12-09
Location : UK
Re: Multivalued attribute on Dimension
Characteristics are meaningless from a product reporting perspective. What is meaningful is color, size, weight, etc. What I would attempt to do is to undo the OLTP EAV (entity attribute value) model and build a product dimension(s) to support the various product characteristics. For example, if "Color" is a characteristic, create a Color dimension to allow filtering of metrics by color.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Multivalued attribute on Dimension
Here are some of the characteristics: telescopic arms, sprung pads, ultraflex, folding frame, comfort bridge. The list will be added to as new characteristics are developed. For any given product, the characteristic is either present or not present. Your suggestion is reasonable but does not suit this scenario.
RobLL- Posts : 7
Join date : 2013-12-09
Location : UK
Re: Multivalued attribute on Dimension
Have a characteristic dimension and a bridge. Don't take shortcuts.
Once you have it implemented, the next step in the evolution of its use would be to add attributes classifying the various characteristics. That is much easier to do if you build a characteristic dimension table.
Once you have it implemented, the next step in the evolution of its use would be to add attributes classifying the various characteristics. That is much easier to do if you build a characteristic dimension table.
Similar topics
» Multivalued attribute
» Dimension Attribute or Fact Attribute
» Multivalued attributes for dimension
» Design of a multivalued dimension,
» Healthcare, diagnosis and Dimension model
» Dimension Attribute or Fact Attribute
» Multivalued attributes for dimension
» Design of a multivalued dimension,
» Healthcare, diagnosis and Dimension model
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum