MS SQL Data Warehousing - Multivalued dimensions

Author

Multivalued dimensions - modelling

karenmiddleol@yahoo.com

2005-07-29, 3:23 am

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

Chandu

2005-07-29, 11:23 am

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

Mark Malakanov

2005-08-03, 8:23 pm

From your explanation it comes out that Color and Material are quite
independent things.
Where to place a Color? it depends how do you want to query it.
Will your DWH have to answer questions like "What 3 colors did we sell
the most last months?" or "What color combinations we dont sell?"

If you have a limited number of colors, you can place them as separate
attributes of Material. Red, Orange, Yellow...etc. Each of colors will
have 1:1 rel.
Also you can place a Color as a separate dimension, with members that
comprise of all combinations of colors. It can be big. If you have a 16
base colors, a list will be 2^16. Also you can place here not all
combinations, but only "supported" ones.

If you have many base colors, but one material can have only a limited
number of colors, i.e. 4, you can have 4 attributes Color1, Color2,
Color3, Color4 in Material dim.

Or you can create small Color dimention and place Color1, Color2,
Color3, Color4 IDs into your fact table referencing a Color dim in
different roles.

BTW, what do you have in your fact table?

You see. It is difficult to advise without knowing the business.

karenmiddleol@yahoo.com wrote:
> 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
>

karenmiddleol@yahoo.com

2005-08-04, 8:23 pm

Thanks for the update.

The fact table currently as only Material and the business as asked for
supporting multiple colors for the
same material.

We need to get color into the fact table but for analysis I do not
think we can afford to create a
new color dimension since apart from color we have so many attributes
like length, width, height,
weight,etc for materials sometimes they can be multivalued.

So putting all of them as seperate dimensions I guess will causes a
dimension explosion we have atleast
about 60 such attributes on the material but not all of them are
multivalued.

Thanks
Karen

Mark Malakanov

2005-08-05, 3:23 am

It is very difficult to make a design when business doesnt know how it
will query on colors, what kind of questions are expected. It means that
some generic design should be done that will cover most of the future
business questions.
In generic case, R. Kimball recommends to use a "bridge table". That is,
from my perspective, a step from purely dimensional "star" design to a
NF one.
In your "color" case the "bridge table" Materials_to_Colors(

MaterialKey,ColorKey
) should be created.

Fact_______ Materials__ Materials_to_Colors Colors_
Material_ID >--- MaterialID ----< MaterialID Name
Amount Name ColorID >----ColorID

That will allow you to store any number of colors for material.

karenmiddleol@yahoo.com wrote:
> Thanks for the update.
>
> The fact table currently as only Material and the business as asked for
> supporting multiple colors for the
> same material.
>
> We need to get color into the fact table but for analysis I do not
> think we can afford to create a
> new color dimension since apart from color we have so many attributes
> like length, width, height,
> weight,etc for materials sometimes they can be multivalued.
>
> So putting all of them as seperate dimensions I guess will causes a
> dimension explosion we have atleast
> about 60 such attributes on the material but not all of them are
> multivalued.
>
> Thanks
> Karen
>

karenmiddleol@yahoo.com

2005-08-05, 3:23 am

Many thanks for the explanation. Is a bridge table similar to
snow-flaking dimensions
in Analysis Services.

In terms of the kind of questions the business will ask is especially
on a attribute like
length or width on material they will ask questions like show all
materials which are less
than 5M or > 5M and < 25M.

Sometimes I am wondering how we can support these kinds of relational
querying on
the material attributes.

Thanks
Karen

Mark Malakanov

2005-08-06, 11:23 am

karenmiddleol@yahoo.com wrote:
> Many thanks for the explanation. Is a bridge table similar to
> snow-flaking dimensions
> in Analysis Services.

No, I'd say it will be mapped to MSAS as multiple fact table schema.
Where Fact and Materials_to_Colors will be facts. And Colors and
Materials will be dimensions. Materials_to_Colors will have just Count
measure.

However, to answer Color aggregation questions you have to create a
calculated member that allocates Material's measure to Color's level.
I.e. equal allocation: Material Cost=90; Material has 3 colors; Cost of
every color will be calculated as 30.

>
> In terms of the kind of questions the business will ask is especially
> on a attribute like
> length or width on material they will ask questions like show all
> materials which are less
> than 5M or > 5M and < 25M.

It depends on user's OLAP or reporting tool.
For example, in MSAS cube browser, you can place Lengh attribute into
Filter Fields field, and then manually checkmark all lengths you are
interested in. (Set a property Sort by Key, not by Name, otherwise 9
will be "bigger" than 10 :)
In Reporting Service report you can set a range filter on Length
attribute populated with two parameters.

>
> Sometimes I am wondering how we can support these kinds of relational
> querying on
> the material attributes.
It will look like you have set of discrete members, although they can
have all kinds of values, like 1, 2, 3.5, 18.99, 100...
User will haave to choose some of them or to set range, again depending
on OLAP tool.

>
> Thanks
> Karen
>

Sponsored Links