Data spread and User Defined Consolidations
- Feb. 24, 2012
Introduction
Data spreading is a useful piece of functionality in TM1. In this article, we will take the example of Relative Proportional Spread. This is the kind of trick lazy business unit managers use to set up their annual budget for next year: take last year actuals, multiply with 1.05 and we are done! (mind the pun) Seriously though, this option is VERY useful in drafting initial budgets, copying data to a different slice of the cube, working with several versions of data, and so on. Important is that if you initiate a Relative Proportional Spread on a consolidated cell (could be consolidated in many dimensions), all underlying data will be affected too and will copy across. Percentage changes or additions or subtractions will occur at any level below the consolidated cell that takes the data spread operation.
Let us narrow down the functionality to what I needed recently: a copy/paste of several chunks of data to other slices in the cube, whereby metadata elements in multiple dimensions change. It is not a simple copy- paste from one element to another. A view and/or TI approach would cost me quite some time since I had many non-related slices of data to do.
User Defined Consolidations (UDC)
Relative Proportional Spread operations cannot be fired starting from a range of cells and I needed just that! But this is where I used a trick: a rollup or so-called User Defined Consolidation (UDC). Please have a look at the screenshot below.
I needed to spread data (copy in fact) from version 1 to version 2, but on selected elements in 3 other dimensions. Given the fact that only single cells can be used for a Relative Proportional Spread operation, I grouped the dimension elements (consolidations or leaf level members does not make a difference) in a UDC. Hence, I have 3 UDC’s in 3 dimensions and the grid contracts to 2 columns for a SINGLE row:
As you can see now only 1 cell is left and we are ready in a nick of time.
Conclusion
In the discussion above, I did not use a cube view that I store on the database, no TI code, no wizard TI code, … Obviously, if regular consolidated elements already group the needed elements for the spread, you should prefer these to the UDC’s. But in their absence, a UDC will prove very useful. An additional benefit of this approach is that users can do this on their own in a cube viewer and do not need a TI process, TM1 administrator, text file export and import and the like.
