In Cognos EP the calculated cells provide you the ability of editing them and then breaking back the changes to source cells.
I am trying to replicate the same functionality in TM1 using rules as described below:-
Rule on the cube: ['FY12 - FY11 % Growth']=N:(['FY12']-['FY11'])/['FY11'];
Although the actual requirement is much more complex but let's assume that the requirement is that there is a hold on FY11 data and the user can change the FY12 data or the Percentage column. If the user changes value of FY12 data, the percentage column should change and if the user changes the value of percentage column, then the value of FY12 column should change. Since the break back does not work here as the rule calculated cell is read only, is there any workaround to achieve this?
I have only played with EP at a very high level but one thing I noticed is that it is very limited in the complexity which you can (easily) apply to calculation when compared to rules in TM1.
Obviously the rule you have listed is a very simple one, so the functionality you mention wouldn't be that hard to build into the TM1 product... HOWEVER... imagine an extremely complex rule in TM1 that takes values from umpteen different sources... how would TM1 know what it needed to change in order to make the result be X instead Y?
If you are wanting to look at that sort of functionality you could move away from rules a bit and look at TI, in the TI you could have parameters that decide which value needs to be changed. For your rule listed below it would be a pretty simple TI but it also possible to do it for more "interesting" things, for example I have built quite a few TIs that in essence use a while loop to keep adding "a bit" on until the result is what you want (very simplified explanation there.)
Synopsis - This functionality isn't available "out of the box"
In TM1 you can breakback over a rule calculated value but only if there is only a single input variable. Where there is ambiguity over which input variable to flex to achieve the end result the breakback will fail.
When determining whether the breakback can happen or if there is ambiguity it is only the data structure that is examined. Although the ambiguity might be "resolved" by a hold in the scenario you are talking about TM1 doesn't see it the same way.
Note there may be other ways to achieve the same or a very similar result. For example by splitting the growth driver and key figure input into separate cubes. If you did this I think you could get the result you want but then not inside the same cube view.
@Lotsaram - I have tried writing a rule with just one variable but even then I can't update the rule calculated cell.
For example, if the rule says ['FY12']=N:2*['FY11'] and I try to update the cell ['FY12'], I still get data spread failed. Ideally I would expect it to update the value of cell ['FY11']. Am I missing something?
I will look into creating additional cubes, but it's weird that we have to do so much to achieve simple functionality which is available out of the box in Cognos EP.
@Declanr - I understand that this functionality can be easily implemented through TIs, but the solution I am trying to develop will be in TM1 contributor. Since Contributor apps still don't have any way of providing user an ability to run TI processes within the cube views, I am trying to avoid the TIs for this functionality. To use TIs I will either need to move away from Contributor interface or develop an additional Web Sheet tab which will have buttons for the TIs and user will need to swicth to this tab each time he wants to run the TI.
Hi there,
Honestly, this EP functionality for a set of simple rules in combination with holds would be a nice enhancement for TM1.
A good example of generic enhancements I was hoping for, when they started to "integrate" EP functionality.
EP has some nice BiFs, which on the other hand are very limiting from a TM1 point of view. At least, when not integrated directly into TM1 and crudely put on top instead.
Still, there are a few ways for specific workarounds, depending on the use case:
1) Case-based Calcs: You could put all use cases needed into the rules, and use another (string) value to define which rule will be used. I did this a lot using an pick list attribute and quite generic rules. Putting this into a spread/websheet and combining it with conditional formatting (highlighting input and output cells) is even more luxurious from an end-user perspective than using a hold.
It is a little bit tricky to put the rule defining value in a cube view, but it is a nice way if an admin/control cube state etc. decides which rule to put to use when or where.
2) Hierarchy Trigger: A client and a colleague of mine came up with a solution for a multiplication break back, where you predefine which multiplier will be changed, when you change the result itself.
In the dim structure, simply add the multiplier element below the result element, change the N: rule to a C: rule and calcs will work on all elements. No need to adjust the feeder. As mentioned, there is a but: You will no longer be able to use a typical hold with the multiplier element below the result element.
3) Unit Translations: In some circumstances, it will help to use more or less stable hierarchy weights to calculate more specific break backs. Think of a brewery, that wants to sell an overall amount of beer, and wants the total on top to spread down to regions, clients, etc. AND into different amounts of of bottles, barrels or other trading units. You could simply define the KPIs of the amounts of these units by adding unit elements with its specific translation below the unit of the total amount (e.g. hectoliter). This way, you will get adjusted quantities automatically bottom-up as well as top-down.
