sql server data warehouse: I have a data modelling challenge I need to store multiple values in
the attributes
of a dimension. The dimension I need to model is the Materials
dimension for each material
I have multiple color attributes and many times I have the following
scenario the same material #
will have different color attributes.

-----------------------------------------------
Material Color
-----------------------------------------------
1908900 Red
1908900 Green
1908900 Yellow
-----------------------------------------------


I would appreciate if somebody can point me what is the best way to
model this dimension so I can report on these multivalued attribute may
be as a virtual dimension. What I still cannot
understand is how do we model these kind of multivalued dimensions in a
typical dimension the relationship between the attributes to the
dimension is always 1:1 or 1:N but here I have a scenario where the
relationship as you can see between a dimension and its attribute is
N:N

Also, many times this relationship from the material to the color of
the material is coming from the same table that is the source for the
fact table of the cube.

Appreciate your help on this.

Thanks
Karen

Try Creating the Dimension with Material as Level1 and Color as Level2.

The above said structure will have data like :
+ Material1
+ Red
+ Green
+ Yellow
+ Material2
+ Red
+ Blue
+ Material3
+ Brown


I guess, this is what you required.

Thank you,
Chandu