Did you know one of the most popular uses of TurboIntegrator is to load data into a cube? There are many different commands that can be used to load data depending on whether the data consists of numbers, strings or attributes.
When loading numeric data, there are two approaches that can be followed.
- One approach is to load the value from the record into Planning Analytics using the CellPutN command. This approach will overwrite any existing data in the cube.
- One approach is to accumulate the value from the record with any existing data in the cube.
The purpose of this write up is to discuss how to accumulate data in a Planning Analytics cube.
There are two approaches to accumulating data. The first approach is to get the data that is already in the cube, add that value to the value from the record, and then reload the total into TM1. Here is an example of this using a cube called ‘Finance’:
|numExisting = CELLGETN ( ‘Finance’, vVersion, vPeriod, ‘Amount’);|
numTotal = numExisting + vValue;
CELLPUTN ( numTotal, ‘Finance’, vVersion, vPeriod, ‘Amount’);
This approach creates temporary variables to tell you the existing amount and the new total. The benefit to this approach is that you can easily do error checks if the results don’t reconcile to your expected results. However, the disadvantage of this approach is that you had to write multiple lines of code to perform one function.
You can consolidate these lines by using a single command called CellIncrementN. The increment command increases the existing value by the value from the record. Here is an example of this using the same cube:
|CELLINCREMENTN( vValue, ‘Finance’, vVersion, vPeriod, ‘Amount’);|
If the data loads properly then we have shortened our script by only having to create one line of code. However, if the data did not load properly then we have done this at the expense of not being able to easily reconcile the pieces.
There are multiple commands that can be used to load data into a cube, each of which has its own pros and cons.
IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at firstname.lastname@example.org. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!