I have a material dimension on which I expect about 90 attributes
mainly they are related to classifying the material.

Can somebody please share ideas how I can implement such a large set of
dimension attributes any other alternatives you can think off.

Sometimes the attrbutes can be dynamic how can I implement such a
scenario in analysis services dimensions.

Thanks
Karen

If you are using Analysis Services 2005, then Display Folders can be
used to conveniently group attributes within a dimension (eg: see the AS
2005 Tutorial Lesson 3).


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak

My problem was not so much of grouping these attributes to be precise
let me clearly explain what my problem is:

I have an extract provided to me for the product dimension the extract
file structure is as follows:

Product, AttributeName, AttributeValue
---------------------------------------------------------
110000,Color,Red
110000,Gauge,0.5M
110000,Length,5M
110000,Sealant,F
110000,Market,US
110000,MarketSegment,NA
110000,MarketApplication,102
110000,Industry,Healthcare
110000,PrintDescription,XXXXXXXXXXXXXXX
110000,PrintLabelType,ABC
--------------------------------------------------------

Now my challenge with this for a given product I might have anywhere
from 10 such attributes to 80 or 90 such attribute name & value pairs.

So, given this my cubes currently have data only at a product level I
need to report using these attributes. So, I face the following
challenges:

1. How do I model this dimension to provide reporting on all these
attributes
2. If I model my dimension as follows:

Product, Attribute1, attribute2,.....Attribute80

then how do I flatten the data in the above structure and load it so
that for a given product I populate the attribute values into
attribute1 to attributeN.

3. Even if I flatten and load the dimension how does the user when he
wants all Red colors or 5M length or for a particular mkt segment to do
the reporting

Storing the attributes in the cube is next to impossible I cannot store
80 or 90 attributes in the cube.

I would greatly appreciate if others having faced a similar problem how
you have done modelling and handling this kind of variable attribute
set dimensions and modelling and reporting of this kind of dimensions.

Thanks
Karen

Hi Karen,

Whereas I don't have answers for all your questions, AS 2005 (and SQL
2005) can address at least some of them:

- If the Product dimension is modelled with many attributes (assuming
the complete attribute list is known), then the extract data, if
exported to a SQL Server 2005 table, could be flattened and loaded to
the product dimension table using the new TSQL "PIVOT" option.

- All relevant reporting attributes can have their own attribute
hierarchy made visible, so that the user can combine whatever attributes
they need in a report.

- There should be no problem storing 80 or 90 attributes, since AS 2005
dimensions don't all have to be fully loaded in server memory, unlike
with AS 2000.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Karen,

I'm not sure all of these attributes can be lumped together all at once.
From your example, some of these attributes form your natural product
business dimensions (Market->Segment->Application, Industry); one of them
will probably contain Product at the leaf level while the others will become
virtual dimensions.

The others can be grouped as a single level OtherProduct dimension with
members being the AttributeName and value = AttributeValue.

Sonny

<karenmiddleol (AT) yahoo (DOT) com> wrote