Applied Dimensionality

TM1 Input in Calculated Cells or Break Back

Posted at — Feb 13, 2016
TM1 Input in Calculated Cells or Break Back

Break-back was one of the famous features in Cognos Enterprise Planning, it allowed data input in a calculated cell and system would adjust all the participating elements to match the new result. Combined with ‘holds’ it was a really awesome feature. TM1 allows input in consolidated cells, but not in calculated ones, so this functionality is kind of missing.  Not entirely, I’ll show how to achieve similar behaviour in TM1 (although in a much more restricted manner). I think it’s isn’t actually documented anywhere, so I’ll put it out here.

 How To

So the trick about inputting data in calculated cells in TM1 is in to do the following:

  1. you create a Consolidated Element to allow normal spreading functionality on input
  2. you write a C: level formula on this element where only 1 element is changeable (others are static or DB’s)
  3. voila, inputting in this C level element will force the ‘break-back’

For example, you want to make Sales = Units * Price adjustable, so that we can input new Sales value and it’ll adjust the units accordingly:

Sales = C: Units * DB(‘Price’,….)

You can see the limitations straight-away, it’s complex to do for anything more sophisticated, we’re hardcoding the break-back logic (adjusting only Units, not Price) and it’s not very flexible. So that’s exactly why nobody uses this approach )

 When to use this

Over the years I’ve seen only one good application of this technique: multi-currency input in multinationals.

For example, you’re collecting budget from 10 countries in the region where each country inputs data in local currency (AUD, NZD, SGD, etc) and then headquarters controllers want to adjust the overall budget in single global currency (SGD) and have it recalculated back to local currency. You can play around with different ‘Original Input’ & ‘HQ Adjustment’ slices and so forth (a really valid approach), but having an ability to directly recalculate local currency values based on global currency input is sometimes really simpler.

There is a complicated issue  on how to work with time consolidations (as FX rates don’t really sum up by time), but I wouldn’t go into this here, it’ll take much more screen space and confuse everyone. An A+ problem: solvable & hard :)

PS:  Analyst break-back was the topic one of the first posts. I wrote on this blog 9 years ago (just setup a reminder not to miss a decade anniversary).

comments powered by Disqus