## Performance Improvement Tips in IBM ICM

IBM ICM (aka Varicent) is one of the most popular and most efficient ICM systems used by mid-large enterprise class companies. However, there are always ways to improve the system and optimize efficiency, be it the architecture of a model, or the structure of a calculation. Here are a few best practices, which if implemented, may boost system performance:

- Breaking up Complex Calculations
- Optimizing Structural Tables
- End Dating Calculations

** **

**Breaking up Complex Calculations**

One of the most essential components of the IBM ICM system model is the Calculation. The Calculation allows a user to assimilate and join data together from various sources (tables, other calculations, etc.) to create desired result sets that will be used for specific purposes. Calculations can be simplistic or complex in nature and consist of five steps:

**1. Naming and selecting the type of calculation**

-The name of a Calculation can be defined; the type of Calculation (i.e., User Defined, Sort) can be defined; a description for the Calculation can be assigned.

**2. Defining data sources for the calculation**

-Tables or other calculations can be brought in and can serve as sources for the new Calculation.

**3. Defining the calculation formula (to attain a value)**

-One or more values from data sources can be utilized, and basic or complex math operations can be performed in order to achieve a desired value.

**4. Defining the partition (grouping the data) for the calculation**

-Desired fields from the sources can be chosen to achieve a desired result set.

**5. Defining restrictions (filters) for the calculation**

-The Calculation can be modified to only include records based on a criterion or set of criteria.

Like any other task, application or process, calculations use computing power and resources to operate. During Steps 2, 3, and 5, it is possible to create a level of complexity that dominates system resources and decreases system efficiency. Examples of issues that decrease efficiency include:

**--Improper Data Source Joins **(Figure 1)

**--Excessive Data Source Joins** (Figure 2)

**--Complex Formulas**

SUM(IF(Calc 1.Value >= tblPlanGoals.Value, (ROUND(SUM(MAX(10000, (GoalValue1.Value * ((GoalValue2.Value * GoalValue3.Value) + (Goal PercentValue.Value * Goal Percent Value2.Value))))) / 1000, 0) * 1000) + 2500,ROUND(SUM(MAX(10000, (GoalValue1.Value * ((GoalValue2.Value * GoalValue3.Value) + (Goal Percent Value.Value * Goal Percent Value2.Value))))) / 1000, 0) * 1000)

**--Excessive Restriction****s (Figure 3)**

These issues not only reduce the efficiency of the system, but also cause a lag in performance. The good news is that these issues can be resolved.

**Improper Data Source Joins**

When joining data sources in a calculation, the primary goal is to return the most accurate set of records. To achieve this, best practice is to join those sources on the primary key. When the join is not correct, there are a few things that can happen:

- An excessive number of records are returned
- An excessive amount of processing time is taken to return records
- An excessive amount of resources are used, causing a database overflow error

Using proper joins practices can help circumvent these issues when building calculations.

However, even when proper joins are performed, efficiency issues can still occur if an excessive number of data sources in a single calculation are used. Requesting data from so many resources causes a spike in processing time and system resources. This situation highlights the need to avoid using an extreme amount of data sources in a single calculation. Instead, it is better practice to split the source joins into dual or multiple calculations.

**Complex Formulas**

Calculation formulas generate a value based on a set of operations which can be simple or complex. Simple formulas can quickly generate a value, while complex formulas take a little more computing power. Some of those complex formulas include:

**Nested Operations:**

Note: Nested operations are operations that exist within another to achieve a desired value or answer to a question, i.e., IF(x=1, IF(y=2, ‘Yes’, ‘No’), ‘No’

**LongFormulas:**

SUM(IF(Calc 1.Value >= tblPlanGoals.Value, (ROUND(SUM(MAX(10000, (GoalValue1.Value * ((GoalValue2.Value* GoalValue3.Value) + (Goal Percent Value.Value * Goal Percent Value2.Value))))) / 1000, 0) * 1000) + 2500,ROUND(SUM(MAX(10000, (GoalValue1.Value * ((GoalValue2.Value * GoalValue3.Value) + (Goal Percent Value.Value * Goal Percent Value2.Value))))) / 1000, 0) * 1000)

**Complex/Excessive Operations:**

ROUND(SUM(IF(OR(CALC2.value=7, AND(CALC1.value=CALC3.vrate, r=9)), ROUND(SUM(MAX(y, CALC1.value)))))

Simplifying the formula by splitting the calculation can help avoid a catastrophic drop in performance.

**Excessive Data Source Joins**

Although Complex Formulas can cause system performance issues, a more common reason for these issues is Excessive Data Source Joins. Formulas depend on the sources being introduced to the Calculation setup. Whether the formula is simple or complex in nature, its effect can be masked because of its dependency on the sources. If there is an issue in the source joins, it will manifest itself before the formula. Alternatively, the join setup may be just fine, but the issue is in the formula. Identifying where the issue lies can be a little difficult at the beginning of implementing a Model and its Calculations, mainly because the Data Set is smaller. However, once the Data Set grows, it is easier to test where the performance latency exists-- the source joins or the formula. Using a simple formula such as Sum(Calc1) can help to expose where the issue resides.

**Excessive Restrictions**

Best practice recommends that, if possible, restrictions be stated while defining data sources in Step 2. Doing this can improve performance because the processes are taking place while the results are being retrieved from the sources. This means that the database isn’t being “double-tapped” by later-defined restrictions. There may be instances where restrictions need to be defined in Step 5; however, too many defined restrictions can have a negative impact on performance. Although performing restriction definitions in Step 2 may be the most effective method for restriction placement, there may be a need to split a calculation and define restrictions within the set of calculations (Figures 4 & 5).

Figure 4: Example (Before – a high number of Calculation restrictions in Step 5)

Figure 5: Example (After – a Calculation split into two, and restrictions reduced)

Improper data source joins, excessive data source joins, complex formulas and excessive restrictions are all components of complex calculations that can impede system performance and efficiency. Addressing these issues by separating the calculation into parts can help minimize the restraints of system drag.

**Optimizing Structural Tables**

Structural tables are an essential part of the system model because the data within these tables is what provides values to other custom tables. Since other tables have dependencies to structural tables, it is vital that the data is accurate. The following methods are various ways to import data to structural tables:

- Manually import data from an Excel spreadsheet, text document, etc.
- Manually run a scheduled task that imports data from said sources
- Schedule an import task to run automatically

Validating that the table data sources are accurate prior to import ensures that the structural table will feed correct data to the dependent tables. To learn more about properly importing structural table data and avoiding errors, please refer to our technical article:

**“How To Avoid Data Import Errors - IBM ICM” **(http://spectrumbiztech.com/index.php?option=com_content&view=article&id=161).

**End Dating**

Active Compensation Plans within will experience Plan Changes periodically. These changes can range from rate updates to revising the calculation methodologies. Changes to the structures of the calculations and methodologies are necessary when making Plan Changes; however, the original calculations can only be modified to a certain point.

When changes to a plan require a calculation modification, best practice is to end date the old calculation and create a new version of the calculation. There are two types of calculations that will be modified using the method previously mentioned.

- Locked calculation
- Unlocked calculation

With both calculation types, end dating the calculations restricts result sets to displayup to the ending date specified. To that end, there are two methods of end dating that can be used:

- End Dating by Partition
- End Dating by Restriction

Both methods effectively restrict the result sets of a calculation; however, there is a slight difference in the way each goes about it. You can learn more about end dating calculation options in our technical article:

**“End Dating Calculations in IBM ICM: Options and Benefits” **(http://www.spectrumbiztech.com/index.php/end-dating-calculations-options-and-benefits).

**Conclusion**

No system is perfect. The best case scenario is that the system of choice performs at the highest levels when used to handle daily tasks and major operations. IBM ICM is very good at that. A well-tuned system model will always yield optimal performance. For calculations, improper data source joins, excessive data source joins, complex formulas, and excessive restrictions will impede the efficiency and performance of a model. When these issues exist within a number of calculations, the chances of a catastrophic breakdown are all too real. The good news is that there are ways to avoid such a disaster.

When creating calculations, the following actions can ensure that a model performs optimally:

- Break up Complex Calculations
- Optimize Structural Tables
- Apply End Datingto Calculations

Optimizing the data source joins, and simplifying the formulas and restrictions when breaking up complex calculations are key tasks to improving the process. Optimizing structural tables can safeguard against duplicate records and errors. End dating calculations will restrict the result sets of calculations and cut down the processing time.

All these steps will assist in the ultimate goal of maintaining your system in the best performance shape possible.

**_________________________________**

**About Author:**

This article is authored by a team of Sr. SPM Analysts at Spectrum Technologies LLC. For further information on this topic, please reach us at This email address is being protected from spambots. You need JavaScript enabled to view it. , or visit us at www.spectrumbiztech.com