Consolidation of n-level (leaf) elements

[alexhancock]

Hello,

I was just wondering whether there is an easy way of consolidating only n-level elements within a hierarchy.

I.e. a Member at the top level (e.g. level 4) should consolidate only the leaves at level 0 (because of rules or other issues [e.g. multiple parenting] at the intermediate consolidation levels). I thought of adding the leaves directly to a dummy consolidation directly below the top level and having all other consolidations be weighted at 0, but this would be clunky, not easily scalable and potentially confusing). Any other thoughts?

Many thanks!

[Duncan P]

Is the problem that each leaf item must contribute only once to the top level, regardless of there being potentially multiple parentage paths between it and the top?

[Duncan P]

If so, and if you would be happy to maintain a separate dimension with only the leaf items in it, then you could create a cube weight to calculate the contribution of each leaf to the top. It would have the dimensions hierarchy and leaves and the following rule

Code: Select all

[] = N: IF( !hierarchy @= !leaves, 1, 0);

In your main cube you would then create another measure weighted which would be the value of measure divided by the value of the corresponding item at the top of the hierarchy in the weight cube. The top item of your measure should then be set to the top aggregation of the weighted measure, like so

Code: Select all

['weighted'] = N:['measure'] \ DB('weight', 'top', !hierarchy);
['top','measure'] = C:['top','weighted'];

It appears to do the trick but it's quite an effort. I'd be glad to find out there was an easier way.

Duncan.

[alexhancock]

Is the problem that each leaf item must contribute only once to the top level, regardless of there being potentially multiple parentage paths between it and the top?

Exactly. This is where an element could exist under multiple parents, but ultimately be counted only once at levels above.

As you say above, there are hacking-type workarounds that could work, but it would be great if there were an easier way to do this.

If this doesn't exist, I'd say an enhancement request would be in order.

Thanks,

[paulsimon]

Alex

Can you explain a little more about your requirement. It seems strange. From what I have gather you have a dimension like

A
/ \
B C
/ \ / \
D E F

You want B to show D + E and C to show E + F but you want A to be D + E + F ? Is that right?

I have not seen this requirement before. The only thing that is remotely close to this is when you have a mix of P&L and Balance Sheet in the same cube and the P&L Numbers are movements, while the Balance Sheet is point in time, then the Balance Sheet Q1 needs to be March while the P&L Q1 must be Jan + Feb + March. Is that the reason you want to do something like this?

A simple solution would be (Had to repeat D&E as I couldn't draw that with a few lines.


B C A
/ \ / \ / | \
D E F D E

That would seem to give a better representation of the requirements. I think that the previous layout will confuse people into thinking that A should be B + C when it isn't.

Regards

Paul Simon

[alexhancock]

You want B to show D + E and C to show E + F but you want A to be D + E + F ? Is that right?

Paulsimon - Your summary above is correct, and it is an interesting use case.

We are classifying items and wish to record measures against them. However the classification of the items is fuzzy, such that D could be classified equally validly as B or as C (the business does not want to make it a hard classification) - and we would like to know the values summarised at B and at C. However, these all contribute to the total measures at A.

Agreed that you could set the relations so that all the leaves are direct children of A, but then you lose drill down ability, and the dimension gets broad very quickly.

I'll open a request on this - could be quite a unique and powerful feature.

[tomok]

I'll open a request on this - could be quite a unique and powerful feature.

Go ahead and open a request with IBM for this but I've got to say it is a VERY long shot you'll see it any time within the next few years. I've been working with TM1 for longer than I care to remember and I can't recall a single time I needed this type functionality, nor can I recall anyone else requesting something like that either.