I have a Product 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.

To explain the problem in more details:

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. Not sure how this can be done from DTS or
from a Stored Procedure or whatever.

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

I'd go
CREATE TABLE Attributes
(
AttributeId INT NOT NULL PRIMARY KEY,
AtttributeName VARCHAR(50) NOT NULL
)

CREATE TABLE Products
(
ProductId INT NOT NULL PRIMARY KEY,
ProductName VARCHAR(50) NOT NULL
)

CREATE TABLE Attribute_Products
(
ProductId INT NOT NULL REFERNCES Product (ProductId),
AttributeId INT NOT NULL REFERNCES Attributes(AttributeId )
)

Note: I don't know your business requirements , does the product have only
one attr or many?
You can add primary key on Productid and AttributeId otherwise add a column
to identify a row.


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

But this still does not answer my question.

I want to report as I said in the following form:

ProductId, Attirbute1, Attribute2...Attributen ........Then the values
in the cube attached to the product.

Seperating the attributes this way still does not solve my problem

Thanks
Karen

Well, can you please DDL+ sample code+ expected result?



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

have you look at SQL 2005?
handling attributes is very easy in 2005.
the problem come from the end user interface, today there is no or only a
few number of tools on the market.

With AS2000, you have to choose the most requested attributes.
or, maybe, create a combination (concatenation) of attributes.
Like attribute color + inner color become: full color attribute
this could reduce the number of attributes without loosing the information.

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

Karen,
There are two problems here...
1. Maintaining the product dimension itself.
2. Putting it into the cube.


In terms of maintaining the dimension table...I have released C source
code for handling large dimensions at the database level.....
it is on this page
http://www.peternolan.com/Downloads/...8/Default.aspx -> various
source code -> customer dimension table code.

The direct link is
here...http://www.peternolan.com/LinkClick.... =138&mid=564


You can learn all you need from there...though you will have to
implement it in something else as MSFT no longer support embedded SQL
in 2005.


However, this is in the database, not in the cube.....in cube products
you generally need to be somewhat careful of building very large and
complex dimensions and then building measures for those
dimensions.....as volumes and complexity go up performance goes
down...and the designer needs to be fully aware of how the particular
cube product performs in various circumstances....As far as I am aware
this comes from experience with the particular cube product because
they are different.

What I have generally done is build the underlying dimensional model
such that any cube can be delivered. I have not done this with AS. I
have done this on many projects with Cognos and Essbase which are
similar cube products to AS...

Best Regards

Peter Nolan
www.peternolan.com