TM1 Forum

Hi,

my client wants to aggregate specifically selected elements from hierarchy. Example hierarchy:

-ItemGroup1
--Item A
--Item B
--Item C
--Item D
-ItemGroup2
--Item E
--Item F

My client wants to select ItemGroup1, ItemA, Item B, ItemGroup2, Item E, and expects to see this (text in italics is my comments):
-ItemGroup1 <sum A + B>
--Item A
--Item B
-ItemGroup2 <sum E>
--Item E

They key issue here is that client expects ItemGroup1 and ItemGroup2 values to change.

I've suggested him 2 solutions:
1) Use Rollup functionality in subset editor
2) Create another hierarchy in same dimension, which includes only Item A, Item B, Item E and their parents.

First solution is not good, because rollup does not save dimension structure, and second solution is not good because another hierarchy has to be created beforehand, when client does not know yet which items he wants to see.

Is there any other solution I can suggest him, or are these the only options?

Change the weighting of the elements you wish to suppress to zero.

David Usherwood wrote:Change the weighting of the elements you wish to suppress to zero.

How is this going to solve the user's problem? I am reading it as they want to add any additional way of consolidating the data, besides the natural hierarchy. If you give an element a weighting of zero in the natural hierarchy, then the natural hierarchy won't work. You're still going to need multiple hierarchies and if you are going to need multiple hierarchies, why bother with the zero weighting? All it does is confuse users by displaying a rollup that doesn't add up.

Something in the line of what David was saying:

You could setup an additional (set of) 'shadow' measure(s) (or using a dimension containg original and derived value) that are calculated based on multiplying the original value with a 'flag' (or weight) value which contains the value 1 or 0. The user can set the flag value (in an attribute or lookup cube - perhaps in combination with sandboxing) to 1 or 0 for the value that (s)he wants to include or exclude. The 'shadow' measures will contain the forwarded values for all items with the flag set to 1. This way the consolidations will only contain/show and add-up the requested values in the hierarchy.

Michel

you as developer can provide form where user will set up new hierarchy and then run process that will build new hierarchy (additional to base or replacing base). this solution is not nice for complicated hierarchy.

kudzis wrote:Hi,

my client wants to aggregate specifically selected elements from hierarchy. Example hierarchy:

-ItemGroup1
--Item A
--Item B
--Item C
--Item D
-ItemGroup2
--Item E
--Item F

My client wants to select ItemGroup1, ItemA, Item B, ItemGroup2, Item E, and expects to see this (text in italics is my comments):
-ItemGroup1 <sum A + B>
--Item A
--Item B
-ItemGroup2 <sum E>
--Item E

They key issue here is that client expects ItemGroup1 and ItemGroup2 values to change.

I've suggested him 2 solutions:
1) Use Rollup functionality in subset editor
2) Create another hierarchy in same dimension, which includes only Item A, Item B, Item E and their parents.

First solution is not good, because rollup does not save dimension structure, and second solution is not good because another hierarchy has to be created beforehand, when client does not know yet which items he wants to see.

Is there any other solution I can suggest him, or are these the only options?

"



How about: ConsolidateChildren(DimName1, DimName2, ...)

BariAbdul wrote:How about: ConsolidateChildren(DimName1, DimName2, ...)

Are you serious? I mean really ....
Think about it, this would require coding a completely custom and hardcoded rule every time the user wanted a custom rollup. (not to mention that it wouldn't actually work as the elements to include/exclude are all members of the same dimension!)

The suggestion from Michel is probably the only workable solution. (but there is also a very good argument that the definition of the sub-total is the definition of the sub-total regardless of the elements "selected" underneath and to show a different total is a data governance issue, depends on the use case).

ioscat wrote:you as developer can provide form where user will set up new hierarchy and then run process that will build new hierarchy (additional to base or replacing base). this solution is not nice for complicated hierarchy.

We have used this method with pretty decent results. A simple form with the new hierarchy name and a SUBNM allowed them to create their own hierarchies on the fly. The main drawback for us was that this was a secured dimension and we had to do a security refresh with the addition of the new hierarchy, severely slowing down the server. Beyond that our users took to the concept pretty easily and they were able to accomplish alot of their reporting needs with it.

Hello everyone,

thank you for your replies and contributions.

Changing aggregation weights will not solve my problem, since user cannot change the weights - only developer/administrator can edit the dimension. Also, as tomok said, it'll break the original hierarchy.

Michel, I really liked your suggestion of shadow measures and lookup cube/sandbox'ing. I'll suggest this approach to the client.

ioscat - developing a web form for a specific dimension seems like too complicated solution; I don't think client would want to invest so much time into this.

ConsolidateChildren(DimName1, DimName2, ...) will not work, since it's user who chooses the elements, and this approach will require a developer/administrator to code a rule for each custom rollup.

lotsaram - business users are used to Cognos BI Query Studio on relational models, so they expect tm1 dimensions hierarchies function in the same way. It takes time to break this habit.

my idea is not specific web form, but typical contributor form (simple xcel-like tab)