When I lived in TM1 World, we would always build our 'cubes' (metrics) such that the 'rules' (formulae) ONLY applied to a SINGLE 'working version'. Then, when a forecast was signed off, we would run a process to copy data from this working version (in all cubes) to an archive version, e.g. 'Forecast May', which was not subject to rule calculations. This meant that 1) the system no longer had to undertake any calculations (other than addition) for the archive versions and, more importantly, 2) any subsequent formula changes would not impact the archive versions so what was reported couldn't change.
One way to achieve this in Pigment is to just back-up the whole application and call it something like 'Forecast May Reporting', but this is unsatisfactory for a number of reasons: primarily as it makes it so difficult for the end-planner to find and compare data, but also due to resource usage (what happens when we have 3 years' of monthly backups?) In any case, having the live working version and archive versions in the same metrics in the same application is key as a) it would be significantly easier for people to find data (they can look in the same place whether creating a plan or viewing past forecasts/budgets) and b) there would be no support overhead as we can re-use the same metrics and wouldn't have to duplicate applications.
So I've been experimenting in Pigment in an attempt to implement the same approach that I used in TM1 whereby a 'live' working version coexists with static archive versions in each key metric.
My first task was to include IF(Version.ArchiveFlag,BLANK,....) at the start of each formula in each metric that I wanted to archive and to flick the switch to make that metric's formulae overrwriteable. That was the easy bit as, thankfully, all my key metrics already included the Version dimension. Next I had a choice as to how I would copy data - should I use 'Clone Data in Metrics' or a 'Metric to Metric Import' (even though the latter is called 'metric to metric' it can be used to copy data within a single metric too)?
Well, 'Clone Data in Metrics' only copies data that has been INPUT, i.e. it doesn't copy calculated values, so that approach was immediately ruled out.
This left me with 'Metric to Metric Imports'. It was easy enough to create a 'parameterised' import for my first test metric whereby the source and target versions are selected at run-time, and all relevant data was copied successfully for this metric (unlike the Clone Data process, Import copies both input and formula-driven numbers). However, I have discovered that once you add selectable parameters to an Import process then you cannot then add it to a Data Flow so you can't group your Imports into one button-press. Conversely, if you go with hard-coded selections then (while Pigment then allows you to add the Imports to a Data Flow) they are obviously hard-coded for ever to the wrong pair (and Pigment doesn't allow us to set the source/target version selections to read from a separate metric).
All this means that I am only left with the following (unsatisfactory) options to make this approach work for multiple metrics:
Option 1 - set all the Imports as 'select parameters at run-time': place 30 buttons on a Board to call each Import individually and the planner then has to select source and target versions each time they progress to the next button.
Option 2 - hard-code the source and target versions in each Import process: this means they CAN be added to a single Data Flow but, at the start of each planning process, the administrator must change the parameters in every process to reference the correct pair. This is easy from the planner's perspective as they then only need to press a single button and don't have to enter parameters.
The first option is really time-consuming for the planner but the second is a chore for the administrator, and there is scope for error in both approaches (e.g. choosing the wrong source/target version pair for one of the button-presses in Option 1, or neglecting to amend one of the individual Data Flow processes in Option 2).
There must be a better way to undertake this key activity!
Can any of my fellow Pigsies help out here? (Sorry, can't bring myself to say Pigmenaut - oops, I just said it).
