Building Alternate Hierarchy using TI

[gbehel]

Hello All,

Following the use case :
Child1, Child2, Child3 rolling into Parent1
Child1, Child2, Child3 also rolling into Parent2

There needs to be a alternate hierarchy like below :
Grand Total
--Parent1
------Child1
------Child2
------Child3
--Parent2
------Child1
------Child2
------Child3
However, the grantotal will have only Parent1 rolled up into it. Parent2 will have weightage of zero so that it doesnt contribute to Grand total.

The client has given me 2 seperate files(one each for parent1 and Parent2) to process this dimension. Need a suggestion on how to go about it. To reduce the complexity, i have requested the client to give one source file with 3 columns.

Any idea how to go about this alternate hieracy buildup?

[David Usherwood]

Think you should stick to the two files, and write 2 TIs:
For the first file, add all parents to Grand Total with weighting 1
For the second file, add all parents to Grand Total with weighting 0
Doesn't sound too desperate.

[Alan Kirk]

Following the use case :
Child1, Child2, Child3 rolling into Parent1
Child1, Child2, Child3 also rolling into Parent2

There needs to be a alternate hierarchy like below :
Grand Total
--Parent1
------Child1
------Child2
------Child3
--Parent2
------Child1
------Child2
------Child3
However, the grantotal will have only Parent1 rolled up into it. Parent2 will have weightage of zero so that it doesnt contribute to Grand total.

The client has given me 2 seperate files(one each for parent1 and Parent2) to process this dimension. Need a suggestion on how to go about it. To reduce the complexity, i have requested the client to give one source file with 3 columns.

Any idea how to go about this alternate hieracy buildup?

If you haven't over-simplified and Grand Total only consists of Parent 1 and Parent 2, I would be inclined to dump Grand Total entirely since it would add no value. Think about it; those two do not add together in any way, shape or form, there's no logical reason for them to be grouped together, so why put them under a single parent? All you'll end up with is end users who aren't across the concept of weighting sitting there going "Lemmie see now, 100+100=.... uh, 100? Whuck?? TM1 is broken!"

Better to let Parent 1 and Parent 2 be the grand totals and itemskip the Grand Total or, better yet, have it excluded from the source file(s).

If you have oversimplified and Grand Total consists of other items as well then personally I'd be inclined to use two completely independent Grand Total elements (with slightly different names, obviously), one with Parent1 in it and one with Parent2. This is not for any technical reason, but simply because when the end users are drilling down and rolling up through the subset editor it's likely to be less confusing for them than coming across two hierarchies which appear to add together (until / unless they check the Properties Pane to see the weightings) but really don't. And two files will be a much more effective way of achieving that, incidentally.

[gbehel]

thanks Alan,

However this is a very specific requirement by client to have grand parent and then parent1/parent2 rolling up into it with parent2 weighing zero. Here, the grand parent should only gets numbers from what they call as primary hierarchy (parent1) and parent2 is called derived hierarchy, which they need in the rollup and it should not contribute to the final total.

You guess it right, this is a simplified case and there are other elements rolling into grand parent at the level of parent1 and parent2. in such a case, what should be the approach? is using 2TI processes to update one dimension a good method?

your help and advise is appreciated

[Alan Kirk]

thanks Alan,

However this is a very specific requirement by client to have grand parent and then parent1/parent2 rolling up into it with parent2 weighing zero. Here, the grand parent should only gets numbers from what they call as primary hierarchy (parent1) and parent2 is called derived hierarchy, which they need in the rollup and it should not contribute to the final total.

You guess it right, this is a simplified case and there are other elements rolling into grand parent at the level of parent1 and parent2. in such a case, what should be the approach? is using 2TI processes to update one dimension a good method?

Sounds daft and pointlessly confusing but if that's what they want then David's post covers what you need to do.

[gbehel]

i fully agree with you on this. I tried talking them out of this but they want this hierarchy to look as close to their excel based model as possible... I shall try the approach suggested by david and post back in case of any issues

thanks for your help

[gbehel]

i know this is a real wierd scenerio. First let me explain the reason for this. Client gets the esbasse cube built every month and they mark which month or which level data to be visible to them when they first open the view. Hence every month, new weightage are set and dimensions and cube built. Leaf level data is loaded for all elements. Because of this reason, they are setting weight of the element zero in some cases and 1 in some cases. Now I need to build a dimension with multiple hierarchy as follows :

Grand_Total (1)
--Upper_level1(1) - Primary Hierarchy
--------Level1(1)
--------Level2(1)
--------Level3(1)
--Upper_level2(0) - Dervied Hierachy
--------Level1(0)
--------Level2(-1)
--------Level3(+)

The values in bracket give the element weight for roll-up.

Level1, Level2, Level3 have multiple leaf level elements below them but the leaf level elements may not be same in both hiereachy for one level. Now as you can see from above, Level2 has a positive rollup in first hierachy and negative in second hierarchy. similary Level1 has a weight of 1 in first hierarchy and zero in the second one... as mentioned earlier, upper_level1 and upper_level2 come from 2 different files.

Is there a way to do this. can someone suggest a design approach. I guess its a case of combining 2 dimensions into one. But esbasse has this functionality and client is looking to have a similarly functioning cube in TM1.

Help will be appreciated

[declanr]

i know this is a real wierd scenerio. First let me explain the reason for this. Client gets the esbasse cube built every month and they mark which month or which level data to be visible to them when they first open the view. Hence every month, new weightage are set and dimensions and cube built. Leaf level data is loaded for all elements. Because of this reason, they are setting weight of the element zero in some cases and 1 in some cases. Now I need to build a dimension with multiple hierarchy as follows :

Grand_Total (1)
--Upper_level1(1) - Primary Hierarchy
--------Level1(1)
--------Level2(1)
--------Level3(1)
--Upper_level2(0) - Dervied Hierachy
--------Level1(0)
--------Level2(-1)
--------Level3(+)

The values in bracket give the element weight for roll-up.

Level1, Level2, Level3 have multiple leaf level elements below them but the leaf level elements may not be same in both hiereachy for one level. Now as you can see from above, Level2 has a positive rollup in first hierachy and negative in second hierarchy. similary Level1 has a weight of 1 in first hierarchy and zero in the second one... as mentioned earlier, upper_level1 and upper_level2 come from 2 different files.

Is there a way to do this. can someone suggest a design approach. I guess its a case of combining 2 dimensions into one. But esbasse has this functionality and client is looking to have a similarly functioning cube in TM1.

Help will be appreciated

What don't you understand? The requirement may be odd but that is irrelevant since the task of completing the requirement is about as simple as it gets in TM1. Between this and your other post on the same subject you have been given all the required functions to use and after David told you that there is no harm in using more than 1 TI you said you would try that and come back if there was a problem:

i fully agree with you on this. I tried talking them out of this but they want this hierarchy to look as close to their excel based model as possible... I shall try the approach suggested by david and post back in case of any issues

thanks for your help

So what is the problem? Why did that approach not work? Can you post your code that you have tried?


I apologise for being direct and "to the point" but it has been the holiday period which means I have been forced to spend too much time with other human beings and to add to this you have on a number of occasions mentioned your "client" which suggests that someone is paying for your services and this truly is TM1 101, I would expect developers to understand these concepts within a few days of working with the product.


On another note; if the only reason for this hierarchy is to keep it looking the same as "their excel based model" as you said, then why use a consolidation at all? Just use a subset with all the relevant elements; Parent 1 would still be a consolidation etc but they don't all need to group into "Grand Total."

[gbehel]

Thanks Rodger,

I know some of my queries are of basic level and im seeking guidance from this forum while getting hands-on on the product. As i mentioned in one of my other posts, Im new to TM1 and have been modelling in Cognos EP since years. Tm1 uses a different approach towards many things. Advice from you all will surely help in understanding the concept of TM1.

I appreciate you taking out time during your holidays.

Regards
Gbehel