Proportional Spread calculation in TM1

[rsnrekha]

Hello,
I have recently been working on one of the projects: Migration from Planning to TM1.
Question 1)
Studying the model reveals that there are too many break back dlinks are being used in Cognos Planning(very straight forward to implement though).
However in TM1 we cannot feed any consolidated item which sees the proportion in which it has to break back among the children items based on any values there, if no value in children it will be equally spread.
However i have written logics (If conditions) manually in TM1 to do such breakback.
Is there any other way to achieve automatic breakback in TM1?

Question 2)
Have prepared a generic case to explain the issue i am facing.
There are two information cubes, using which i have to arrive at one more cube at desired level.
1) Historic Plans - Gives patterns at desired level(City in this case) based on historic data
2) Current plans Country level - Based on the historic patterns, Country plans are multiplied with the respective city proportional spreads for that country.
3) Current Plans at City level - this is the final result i am trying to achieve.

How should i get the parent value of the city to calculate the spread in the division?
How can i refer to Country level data to arrive at city level plans?

Please guide me.

Also attaching the excel sheet - case i prepared

Thanks a lot !

[paulsimon]

Hi

This looks more like an allocation than a spreading / break back.

You can do this with a rule. You might need to amend some of the following since I don't know the exact structure of your cubes, and I obviously can't test that my syntax is perfect.

In the Current Plans at City level

[] = N:
DB( 'Country Plans', !Version , !Time, ELPAR( 'City' , !City , 1 ) , !City Plan Measure ) ;
*
DB( 'Historical Plans' , !Version, !Time , !City , !City Plan Measure )
\
DB( 'Historical Plans' , !Version, !Time , ELPAR( 'City' , !City , 1 ) , !City Plan Measure ) ;

The ELPAR( 'City' , !City , 1 ) gets you from a a City to a Country, although ideally I would define a Country Text Attribute against each City and use attrs instead. ELPAR is fine so long as there are no alternate hierarchies but the meaning of parent 1 can be unpredictable if there are.


For the feeder you just need to put this in the Historical Plans cube

[] => DB( 'Current Plans at City Level' , !Version , !Time , !City , !Historical Plans Meas ) ;

There is probably no need for a Calculate % of cube unless you really want to see that, and if you do put the % in to the Current Plan at City Level cube.

Now I think that there is an error in the example in your spreadsheet. The Time in Current Plans is shown as FY16 but the Current Plans at City level shows FY15.

You probably really want to say that the plan for last year drives the plan for this year. Therefore define text attributes on the Time dimension for Next Year and Prev Year. Enter the appropriate values.

Forget about having separate Historical and Current Year Plan cubes at City level. Lots of cubes is a very Cognos Planning approach due to its inability to handle sparsity properly. Have one City cube, and implement the suggestions about attributes, then your rules become:

City Plan cube

skipcheck ;

[] = N:
DB( 'Country Plan', !Version , !Time, attrs( 'City' , !City , 'Country' ) , !City Plan Measure ) ;
*
DB( 'City Plan' , !Version, attrs( 'Time' , !Time , 'Prev Year' ) , !City , !City Plan Measure ) ;
\
DB( 'City Plan' , !Version, attrs( 'Time' , !Time , 'Prev Year' ) , attrs( 'City' , !City , 'Country' ), !City Plan Measure ) ;

and the feeders in the City Plan cube become

[] => DB( 'City Plan' , !Version, attrs( 'Time' , !Time , 'Next Year' ) , !City , !City Plan Measure ) ;

Take a look at the rules guide.

If your real example has thousands of elements, then you might want to consider implementing this using a TI instead of rules. However, the approach is similar.

Regards

Paul Simon

[rsnrekha]

Thank you Paul for your response

Yes figured out the same approach and it is working as desired.