Hi,

I have (this is an example) an Item dimension with the item data and a
ItemAmount measure containing item amounts. The ItemAmount fact table is the
following

ItemID Amount
-------------
1 100
1 200
2 400

Then I have an ItemHierarchy table, based on which I want to create a
ItemHierarchy dimension which contains a hierarchy named Hierarchy. The
dimension table for the ItemHierarchy is the following:

TopLevel SubLevel ItemID
------------------------
Top1 Sub1 1
Top1 Sub2 2
Top2 Sub1 1
Top2 Sub2 2

TopLevel, SubLevel and ItemID are created as attributes (ItemID is the
key-attribute). The key field for TopLevel is TopLevel and the combined key
fields for SubLevel are TopLevel & SubLevel. The key item for ItemID is
ItemID. Then I set the references: TopLevel is the parent of SubLevel and
SubLevel is the parent of ItemID. TopLevel is also set as non-aggregatable.
The Hierarchy is defined as: TopLevel-> SubLevel. With these settings I get
the Hierarchy working when browsing it in the dimension browser.

In the cube, the granularity is set as regular between Item and ItemAmount
(ItemID as the joining field) and the granularity is set as regular between
ItemHierarchy and ItemAmount (ItemID as the joining field).

But in the cube browser when I set the Hierarchy from ItemHierarchy as the
row-source and ItemAmount as the measure I get the following output:

Top1 100
Top2 400

When the output that I want is:

Top1 700
Top2 700

And futher drilling in:

Top1 Sub1 300
Top1 Sub2 400
Top2 Sub1 300
Top2 Sub2 400


I guess the fact that the ItemID 1 and 2 are leaf members in multiple
branches produces the problem. What is the solution for this? How should I
define the item hierarchy. I am completely out of ideas.

Hi,

I had a similar problem and the solution was to asign a new ID to the
leaf and also to all his former branch knots. In your case i guess
should be something like:
i.e.:

Top1(YELOW FRUITS) Sub1(ROUND) ID1 = Apple
Top1(YELOW FRUITS) Sub2(LONG) ID2 = Banana
Top2(FOOD) Sub3(Healthy) ID3 = Apple

However, depending of the real meaning that those categories have for
you, maybe you could use two different Hierarchies for the same
dimension?

Hierarchy equal to Top1
Hierarchy equal to Top2

That would give you the correct subtotal amounts maybe.

Sorry if that doesn't help at all, it is my first post!

/Marc