Turbo Integrator: report on progress
- Dec. 27, 2018
 | Example files with this article: |
Introduction
I was deleting big chunks of data from TM1 cubes just the other day, but you already knew that. To sum up, in an Excel file I stored the combinations of data (slices) that should be deleted in all cubes of a TM1 model. Below you can find a trimmed down version of the input Excel / text file:
Comparing with the original article, I also show the Track in column A. The TM1 model consists of 4 tracks, and every clear action should target the cubes within the given track.
Now, running the mother TI process took quite some time, even if we only clear leaf-level data (input cells). Therefore, it would be good if we could report on the progress of the exercise and follow up. At the same time, we don't want to delay the process so the solution should have a minimal footprint.
Text files come into the picture then. With the AsciiOutput function, this can be pretty simple. And yes, keep it simple ! But then again, I wanted to increase the information contained in the text files, to quickly follow up where we are in the chain of data clear operations. Another option is updating a table in a database, Planning Analytics has the commands to do so.
Desired output
Record after record, we receive 1 text file in the model's data directory. Here the process runs instantly, since I deleted all code pertaining to clearing data. Basically, we have 2 counters:
- A counter for all records, for all tracks
- A counter for all records of the track that we execute. The mother TI process contains a parameter pTrack.
The parameter pTrack can be either 'ALL', either Track_1, Track_2, Track_3, Track_4. This is self-explanatory. I executed the process for Track_2, so (chronologically) we get 4 records for Track_1 in yellow (Itemskip in the code), then 8 records for the desired Track_2 in orange, then 9 records for Track_3 in pink (Itemskip) and Track_4 in green (Itemskip). Please note that the (single) header record is not counted along, as you would expect.
We need 4 counters/variables:
- The number of records for all tracks
- The number of records for the desired track only
- How many records have passed for all tracks
- How many records have passed for the desired track only
Counters 3 and 4 should be assessed in the Advanced > Data tab of the process. Counters 1 and 2, on the other hand, should be assessed in the Advanced > METADATA tab of the process. If not you will not be able to have a count of records. This implies that we process the text file twice, but that is very very fast in Planning Analytics. Clearing the data is only done in the Advanced > Data tab of the process.
I added some nifty natural logarithm calculation to be able to pad the numbers with leading zeroes, for example: "CLEAR MEMORY - 01 of 21.txt" instead of "CLEAR MEMORY - 1 of 21.txt". Mind the 0. The advantage is that sorting on file names is possible ! If we have records that run into thousands, the numbers will be formatted with more leading zeroes.
All relevant code fits within 1 Planning Analytics Workspace screen, so here is the image:
Interestingly, Windows did not create the files in chronological order, so keep an eye for that (if the process executes really fast).
Happy waiting ! And Season's Greetings too !
