Revive Your Slow Legacy TM1/IBM Planning Analytics Model: Tips and Tricks to Optimize Performance

Suppose you have received an order to optimize a legacy model in #TM1 or IBM #PlanningAnalytics. The model has several hundred or even thousand cubes, rules, and processes. The model is deployed on-premise and has accumulated various add-ons and modifications made by different teams, which often do not correspond to the original design. In addition, the model contains data from 5+ years and user reports that must maintain their appearance and details. As a result, this massive structure starts working slower and slower, which causes frustration among users.

You have a limited time to make the model run faster without completely overhauling the business logic. Where do you start, and what practices will you try to apply?

Below, I describe my experience on a similar project. I’ll start with a disclaimer that this article does not claim to be complete or universally applicable in its methods. On the contrary, I welcome constructive criticism and recommendations and suggest adding them to the article’s comments section. Moreover, by studying the experience of other teams and conducting specific optimization experiments, I have come to the conclusion that some methods are quite universal and will inevitably lead to model acceleration, while others may be effective in one case and almost useless in others.

Before starting the project, our team compiled a list of obvious points to address. It included:

1. Optimizing the configuration settings of the model
2. Reordering dimensions
3. Analysis of the possibility of switching from rule calculations to process calculations in bottleneck areas
4. Elimination of overfeeding
5. Analysis of the impact of conditional feeders on performance
6. Parallelization of long processes
7. Optimization of rule code
8. Optimization of process code
9. Other types of optimization

In addition, the work plan included diagnosing the model to perform specific optimization work, such as analyzing the model for excess calculations (unused cubes, versions, indicators), optimizing allocation algorithms, and several other items.

Below, I will briefly describe each point and the optimization results for each of them.

Optimizing the configuration settings of the model

I selected parameters that affected performance and analyzed their values set in the configuration file. Most of the parameters were set to optimal values, but I noticed that MTCubeLoad and MTFeeders were set to “False”. Further analysis of rules and logs showed the reason for not using the relatively new multi-threaded loading methods for cubes/recalculating feeders. The model used many conditional feeders, and enabling, for example, MTFeeders led to exceptions in feeder calculations during model loading.

Looking ahead, I note that a lot of work was done to optimize and replace conditional feeders, which allowed activating the MTFeeders parameter. This led to the fact that the processes for recalculating feeders for certain cubes run 94-96% faster (recalculation time decreased from several hours to several minutes).

I note that the exception from the chains of recalculation of only conditional feeders was not a sufficient condition, and blocking errors in nested transactions still occurred occasionally. It was only possible to completely get rid of them after setting the parameter ForceReevaluationOfFeedersForFedCellsOnDataChange to “False”.

Dimension reordering

The execution of dimension reordering was carried out not to speed up report generation and processes but to free up additional memory on the test server and deploy another instance of the model nearby, which allowed the team’s work to be parallelized for some tasks. The procedure for dimension reordering is standard. I would like to note only that in this case, the system reordering often did not lead to any noticeable memory optimization, and manual reordering, based on previous experience, along with cube size optimization, could slow down the model’s performance.

Analysis of the possibility of switching from rule-based calculations to process-based calculations in bottleneck areas

One of the standard ways to speed up IBM Planning Analytics Model calculations is to move “heavy” cubes to process-based calculations. Calculating cube measures by processes involves replacing rule-based calculations with process-based calculations. Acceleration is achieved by “breaking” long and resource-intensive chains of “online calculations” from sources to target cubes. An additional optimization is achieved by reducing feeder dependencies. Schematically, the conversion of cube calculations to processes is shown in the figure below.

As I mentioned at the beginning of the article, the optimization task was to optimize a fairly large model with long recalculation chains between cubes. The task was complicated because it was a completely unfamiliar legacy model, and finding possible bottlenecks could become time-consuming.

In order to determine the set of candidate cubes to be converted to static calculations, the following steps were taken:

1. Using the Planning Analytics Explorer, a model map – a tree of cube dependencies – was built.
2. Based on the dependency tree, a matrix of dependencies of target cubes was formed, that is, a selection of source cubes that affect all or most of the “slow” target cubes.
3. An analysis of the potential “load” of the selected cubes was carried out, that is, filtering cubes that have all or several of the following properties simultaneously: complex structure, a large number of rules, “slow” constructions in the rules (for example, multiple and nested conditions for checking), a large number of calculation cells, the long building of typical user views.
4. Static cube replicas were created with a copy of the data.
5. The dynamics of changes in the time to build target user reports were obtained.

The described approach had a certain optimization effect. However, in the end, I decided to postpone its implementation in the productive model due to the natural drawback of this approach – some loss in the online availability of user reports.

Getting rid of overfeeding

A classic method for identifying overfeeding issues is well described by the company #Cubewise, for example, here. However, this time, I decided to try a slightly different approach, also proposed by this remarkable Company. For many cubes, repeating the described settings can be a very tedious task, so I prepared an auxiliary process that automates the visualization settings and rule configurations described in the article. The process can be downloaded here.

The optimization result is a reduction in the model’s memory footprint but not a speed-up in the cube view/report building, as all feeders are already calculated during model load and/or rule compilation.

Transforming conditional feeders

Among the various teams, I have worked with, one of the very disputable approaches when writing rules has been the use of conditional feeders. I know whole teams that refuse to use conditional feeders not only because there are known issues with refeeding target cells but also because modelers believe that conditional feeders have a negative impact on the model’s performance as a whole (even compared to overfeeding). In my experience, this is not always the case, although there are cases where such a statement is entirely justified. Nevertheless, I decided to make a global model refactoring and replaced all conditional feeders with non-conditional ones for another reason. The main reason was the need to refeed a number of very “heavy” cubes, for which feeder reprocessing took several hours. This was a big problem during the budget campaign when data quickly filled and changed. Thus, I planned to either eliminate the need for refeeding altogether or, if such a need remains activate the MTFeeders parameter in the configuration, which should speed up (and, as a result, did speed up!) feeder reprocessing.

The transformation of conditional feeders occurred in the following sequence:

1. Using the Planning Analytics Explorer, a selection of model rules containing conditional feeders (i.e., feeders with the conditional operator IF) was made.
2. All conditional feeders were analyzed and grouped by the source in the condition (cube or dimension).
3. All condition sources were analyzed for data density, sparseness, and the feasibility of using proposed conditions.
4. Approaches to transformation (one or several) were formulated for each group of feeders.
5. Conditional feeders were transformed in the rules of such cubes.
6. Measurements of the time for building target reports and executing target processes were taken.
7. An evaluation of the obtained optimization was made.

The result of transforming conditional feeders was not only a multiple acceleration of cube refeeding, but in general, many calculation processes began to be executed faster. At the same time, the model loading time did not change – apparently, overfeeding leveled the optimization effect from refactoring conditional feeders.

Parallelization of long processes

Analysis of the model showed that all the long-running processes had already been adapted for use with the TM1RunTI utility. Although this is the oldest approach to parallelization, we did not expect to see a significant performance improvement from switching to newer methods (such as using RunProcess), so we decided to leave the already stable processes unchanged.

Process code optimization

Optimizing code is almost always a creative process, but certain patterns work faster than others for the same result. For example, it is known that multiple IF statements significantly slow down processes execution. We found such blocks and replaced repetitive IF statements with more elegant and faster constructions. Subsequent measurements showed that some processes began to run up to 30% faster. Another optimization example was replacing conditions using DIMIX with other structures, which also had an additional positive effect.

Special attention was paid to routine maintenance processes. These processes are run at night and combine long chains of other processes, each of which performs its auxiliary functions and can be run independently.

The analysis of these processes included the following steps:

1. The root processes were sequentially run, and the execution time of basic and nested processes was measured through the }StatsByProcesses cube.
2. The “tree” of running nested processes was built.
3. Processes with a specific execution time of >1% of the total execution time of root processes were selected.
4. The code of the selected processes was analyzed.
5. The chains and conditions for running dependent processes were analyzed.

As a result of the analysis, it was found that in scheduled processes, the same auxiliary processes were often unnecessarily run repeatedly (e.g., the recalculation of feeders for the same cubes). If the nested processes were run independently, such recalculation was really needed, but not as part of the general launch. Eliminating the repetition of running heavy processes significantly reduced the overall execution time of scheduled processes.

Code optimization for rules

To optimize rules, we always use common sense and are inspired by recommendations from the company #Cubewise, described for example here. These are great recommendations, although not all led us to a noticeable effect. The most significant increase in the report building time, based on optimized cubes, was achieved through the following modifications:

1. Common conditions of logical blocks were moved to the beginning of rules and replaced with IF(…, Continue, Stet) statements.
2. The definition area (left-hand side of rules) was additionally specified, where such specification led to a significant reduction in calculated cells with the same target report results.

The reports themselves were also sped up by a more suitable subset selection used on them.

Other types of optimization

Other types of optimization were more specific to the optimized model. Nevertheless, they cannot be they cannot even be mentioned as a general list, as their cumulative effect on the overall model performance was very significant:

1. Data from archive periods and some versions were moved to “static storage.”
2. Indirect cost allocation algorithms were optimized.
3. The number of system locks affecting user operations in the model was reduced.

This article for sure does not describe all possible options for model optimization. However, each project has its deadlines and budget, and the optimizations performed were more than enough to achieve the targets set for us by the Customer.