We did an interesting experiment the other day about copying data from one TM1 cube to another. We tested to see how much faster it is to use an extract and load via a turbo integrator process vs using a direct link in a TI process.
Background Information for the Cube Transfer
A bit of background information though. We have an Operating Plan cube at a client with a driver based forecast out for 10 years. The granularity is less detailed than the GL cube, in that we are using Planning Accounts, which are aggregations of GL accounts. Drivers are then used to calculate most of the values above Gross Profit as these (mostly) relate to volume. We needed a process to copy GL values from the GL cube to the Operating Plan cube at the Planning Account level. To make this work, we needed to rollup Planning Accounts in the GL cube before transferring values to the Operating Plan cube.
Options to Copy Data between Cubes
We knew that we did not need to have this as a dynamic link as the data changes once a day when the GL cube is reloaded. Thus a rule was not required.
That left using a Turbo Integrator process to manage the transfer of data between the cubes. There are two options for doing copying data from one cube to another via a TI process. They are:
- Set up a view in a TI and use a CellPutN and CellPutS to load values directly into the target cube.
- Extract values from the source cube to a text file and then load the text file values into the target cube.
Results – Is a Direct TI or Extract and Load Faster to Copy Data between TM1 Cubes?
Option 1 – View and Direct Load
When we used option 1 above – setting up a view and using that as the source for CellPutS or CellPutN, the process took 368 seconds to complete. Not bad, and if it was in the middle of the night, probably acceptable.
Option 2 – Extract and Load
We then flipped it to option 2 – extract to text file and then load – and it ws competed in just 37 seconds.
Limited Test
Our test involved a small dataset – only 40,000 records for a single cost centre. We then did an extract from the same data for all cost centres. This was 357,000 records and that extract and load took 575 seconds to complete. So it wasn’t a straight line extrapolation, but it was still a good result.
Conclusion
So our test concluded that it is much, much faster to use the Extract and Load method rather than the View and Direct load method – by, in our example, about 10 times as fast.
