SSAS : Parent-Child Hierarchy

Question

Parent-Child Hierarchy

What's the best way to represent a Parent-Child hierarchy in the Cube? I've seen the Usage that can be applied as Parent. I've applied this, for example,

ID ParentID CompanyName Rank
1 1 AAA Excellent
2 1 BBB Good
3 1 CCC Ok
4 1 DDD None

I can link the ID and ParentID Usage appropriately by setting the ParentID to a Usage of Parent. I've also set the Parent Key, so that if the Company rolls up to itself, it doesn't show the Parent as its own child (NonLeafDataHidden).

When using only the CompanyName in the Cube Browser / Excel, this is working okay.

The problem occurs when trying to bring in other attributes, such as Rank.

Dragging the following into the Cube Browser:
- (Parent Company, Company...hierarchy), Rank - If the Rank is at the end of the table, Cube Browser doesn't show the Rank column at all, only the heading appears.
- Rank, (Parent Company, Company...hierarchy) - If the Rank is at the start of the table, Cube Browser displays correctly.

In Excel, - (Parent Company, Company...hierarchy), Rank - If Rank is at the end of the table, Excel doesn't display the Rank of the Parent Company, but does display the Rank of the Child Companies on a second line. By displaying the Rank on a second line - any measures also get repeated:

AAA 73 - here for AAA the Rank is not showing. The measure is correct, 23 from the Child and 50 for the Parent
BBB 10 - repeated measures 10
Good 10 CCC 8 - repeated measures 8
Ok 8
DDD 5 - repeated measures 5
None 5

• Rank, (Parent Company, Company...hierarchy) - If Rank is at the start of the table, Excel groups correctly and sums correctly. Although, the Parent Company will still appear in each of the Ranks, as it has children that are in that particular Rank, but the Measures will only be from the Children that are in that Rank.

What is the Best way to bring Attributes of a Parent-Child Hierarchy? From the issues I've seen above, I'm tempted to go a create a new Table (Parent Company) using Named Query and join to this directly with the ParentID - effectively Aliasing.

Answer 1

Hi,

I've found a couple of things for the reasons why certain things are happening when including additional attributes to a P-C hierarchy:

• Note, I've set the Parent Key, so that if the Company rolls up to itself, it doesn't show the Parent as its own child
• Note, Row structure example is: P-C hierarchy, Rank, Measure

1) In Cube Browser, it depends at what stage in the hierarchy you include the additional attribute. e.g. If the row structure only contains P-C hierarchy and Measure and then you continually expand the hierarchy until you see Total (ie lowest level of the hierarchy). Then by adding the Rank attribute at this stage, so that the Row structure becomes: P-C hierarchy, Rank, Measure, the Rank attribute header appears, but no details appear in the Row. If you double-click the empty cell, you get the message : "Cannot drill into a total member".

If you don't expand the row structure completely, so only expand one level in the example I've provided above, then at this point add the Rank attribute - this will show the Rank attribute details.

So, this issue of not being able to see the Rank attribute in Cube Browswer, is resolved.

2) Now, comes the issue of not being able to see the Parent attributes, as the hierarchy is setup so that if the Company rolls up to itself, it doesn't show the Parent as its own child. E.g. Results in Cube Browser:

Parent ------- Child ------- Rank

AAA ---------- BBB ---------- Good

AAA ---------- CCC ---------- Ok

AAA ---------- DDD ---------- None

I would like to see that AAA is Ranked as Excellent, so the only way to get around this I guess is to show the Child where it is itself a Parent. I guess this is resolved to show the following:

Parent -------- Child -------- Rank

AAA ----------- AAA ---------- Excellent

AAA ----------- BBB ---------- Good

AAA ----------- CCC ---------- Ok

AAA ----------- DDD ---------- None

• Note, following examples will now use the above approach.

3) Repeating Measures - how do we only show measures once? This is what is shown in Cube Browser, there's a Total after each Child Hierachy level, there's also the Measure details of the level - in this case it's the same, so it's repeated. Then finally, there's a GrandTotal. Again this issue of the Measure repeating is also seen in Excel. I would like only the Total / GrandTotal levels to be displayed - is this possible?

Parent ----------- Child ----------- Rank ----------- Measure

AAA --------------- AAA ------------ Excellent -------- 50

AAA --------------- AAA ------------ Total ------------ 50

AAA --------------- BBB ------------ Good ------------- 10

AAA --------------- BBB ------------ Total ------------ 10

AAA --------------- CCC ------------ Ok ---------------- 8

AAA --------------- CCC ------------ Total ------------- 8

AAA --------------- DDD ------------ None -------------- 5

AAA --------------- DDD ------------ Total ------------- 5

GrandTotal -------------------------------------------- 73

In Excel it comes through as the attributes on a separate line and the measures are still repeated, for each line:

Parent ----------- Child ----------- Rank ----------- Measure

AAA ------------------------------------------------- 73

AAA --------------- AAA ----------------------------- 50

AAA --------------- AAA ------------ Excellent ------ 50

AAA --------------- BBB ----------------------------- 10

AAA --------------- BBB ------------ Good ----------- 10

AAA --------------- CCC ------------------------------ 8

AAA --------------- CCC ------------ Ok -------------- 8

AAA --------------- DDD ------------------------------ 5

AAA --------------- DDD ------------ None ------------ 5

Is there a way to remove the repeating of the Measures?

thanks