Hi to all,
I'm trying to develop a custom hierarchical dimension from a table with
this layout:

ID, ID_ATTRIB1,ID_ATTRIB1_ATTRIB2
1, 1-a, 1-a-x
1, 1-a, 1-a-y
1, 1-a, 1-a-z
1, 1-c, 1-c-f
1, 1-d, 1-d-f
2, 2-b, 2-b-s
2, 2-b, 2-b-t
3, 3-a, 3-a-k

i'd like to build a dimension in which i can drilldown from the ID
attribute, going to the ID__ATTRIB1 attribute and so on to the
ID_ATTRIB1_ATTRIB2 attribute, with a result tree like this:

(All)
result!!!
thank you in advance!!!
Gabriele!

Hi,

is it Analysis 2000?
Then just put the table in the Dimensions-Editor.
In order of the columns right click on it and select "Insert as Level" (I
hope this is the right caption, because I am firm in German AServer).
I tried it with Your demodata and it works,

hth

Bernhard



--
CS Result GmbH
Bischof-von-Henle-Str. 2
93051 Regensburg


"Gabriele!" wrote:

no Bernhard, it's Analysis 2005 and i cannot reach the wanted result..
Any suggestion?

Bernhard Saemmer ha scritto:

Hi Gabriele,

Make ID_ATTRIB1_ATTRIB2 (assuming it is the leaf level of your
hierarchy) the key attribute of the dimension, with ID_ATTRIB1 and ID as
the other attributes. Then you can create a user-defined hierarchy:
ID->ID_ATTRIB1->ID_ATTRIB1_ATTRIB2 for the dimension, with 3 levels
corresponding to the 3 attributes. If this is also a natural hierarchy,
you can move the attribute relationship for "ID" from ID_ATTRIB1_ATTRIB2
to ID_ATTRIB1.

http://msdn2.microsoft.com/en-us/library/ms174557.aspx
Attribute Relationships
...
Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the
user-defined hierarchy has a one to many relationship with the attribute
immediately below it.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak Puri,
I succeeded in building the hierarchical dimension in the way you
explained, but when I put it on rows, the measures in the area aren't
calculated correctly.

For example, when I have the hierarchical dimension on rows and
measure1 in the area, the measure1 isn't divided proportionately to the
member of the dimension and it is always reported as its total (i.e.
12) in all the rows..

(All) Measure1
what did I miss??
Thank you in advance!
Gabriele!

Deepak Puri ha scritto:

Can you explain how measure1 is defined; what the fact table data looks
like for the example you gave; and how it relates to the dimension in
question?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi,
i think that it's a problem related to the dimension, because i don't
have the same problem with others dimensions:
the measure meas1 is just taken from the fact table F and it is a field
of this table.
the dimension dim1 which generates the problem is defined as you
suggested me : there is a view D with fields
ID,ID_ATTRIB1,ID_ATTRIB1_ATTRIB2 and the dimension is built with a
hierarchy ID->ID_ATTRIB1->ID_ATTRIB1_ATTRIB2, where ID_ATTRIB1_ATTRIB2
is the key field.
for a better understanding i'd like to explain that the two fields
ID_ATTRIB1 and ID_ATTRIB1_ATTRIB2 (in the view) are just a
concatenation from 3 distinct fields from a primitive table having 3
separate fields: ID,ATTRIB1 and ATTRIB2.
My time is getting over...I hope you could help me!!
Thank you endlessly!
Gabriele!


Deepak Puri ha scritto:

Then maybe the problem lies in how the fact table F is related to the
dimension view D - what fields are used to join them, is the dimension
relationship regular (see the Dimension Usage tab) and what is the
granularity attribute?

http://msdn2.microsoft.com/en-us/library/ms365371.aspx

Defining a Regular Relationship and Regular Relationship Properties

When you define a new cube dimension or a new measure group, Analysis
Services will try to detect if a regular relationship exists and then
set the dimension usage setting to Regular. You can view or edit a
regular dimension relationship on the Dimension Usage tab of Cube
Designer.

When you define the relationship of a cube dimension to a measure group,
you also specify the granularity attribute for that relationship. The
granularity attribute defines the lowest level of detail available in
the cube for that dimension, which is generally the key attribute for
the dimension. However, sometimes you may want to set the granularity of
a particular cube dimension in a particular measure group to a different
grain. For example, you may want to set the granularity attribute for
the Time dimension to the Month attribute instead of to the Day
attribute, if you are using a Sales Quotas or a Budget measure group.
When you specify the granularity attribute to be an attribute other than
the key attribute, you must guarantee that all other attributes in the
dimension are directly or indirectly linked to this other attribute
through attribute relationships. If not, Analysis Services will be
unable to aggregate data correctly.

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi deepak puri,
i checked what you suggested me and i found that:
In my dimension dim1 i have 3 attributes: ID,ID_ATTRIB1 and
ID_ATTRIB1_ATTRIB2. their usage is set to regular, a part from
ID_ATTRIB1_ATTRIB2 which use is set to key.
I have a hierarchy ID->ID_ATTRIB1->ID_ATTRIB1_ATTRIB2
In my cube I can see that dim1 has a regular relationship and its
granularity attribute is ID.

As a result, in my cube, I have a right result concerning only the
first level (the ID attribute) but incorrect results on the other
levels:

this is the situation when i expand the first level:
(All) ID Measure1

this is the situation when i expand the second (or third) level:
(All) ID ID_ATTRIB1 Measure1



Deepak Puri ha scritto:

Hi deepak puri,
i checked what you suggested me and i found that:
In my dimension dim1 i have 3 attributes: ID,ID_ATTRIB1 and
ID_ATTRIB1_ATTRIB2. their usage is set to regular, a part from
ID_ATTRIB1_ATTRIB2 which use is set to key.
I have a hierarchy ID->ID_ATTRIB1->ID_ATTRIB1_ATTRIB2
In my cube I can see that dim1 has a regular relationship and its
granularity attribute is ID.

As a result, in my cube, I have a right result concerning only the
first level (the ID attribute) but incorrect results on the other
levels:

this is the situation when i expand the first level:
(All) ID Measure1

this is the situation when i expand the second (or third) level:
(All) ID ID_ATTRIB1 Measure1

Thank you very much for your kindness and for your skill!!
Gabriele!


Deepak Puri ha scritto: