Getting Started with Planning Analytics Rule and Feeder Optimization
If you are new to IBM Planning Analytics or specifically new to the task of rule and feeder optimization, the following information may help in getting started. Keep in mind that these are some simple “strategies” or “guidelines” outlined here to provide general direction, with some specific prescriptions for action, but not meant to be comprehensive in that not all of the rule and feeder optimizations that may or may not be applicable to a specific model are covered.
Starting Point
To get started, (I assume that you have the appropriate access to make changes to cube rules and feeders) make sure you are working in a development environment! Editing rules and feeders will cause locking and, more importantly, the changes you make most certainly will affect data in the cube (or at least how it is displayed) so you will want to be sure your edits are correct before landing them in a production environment.
Next, be sure to gather some performance baselines for the cube that you want to improve. That is, record all the statistics you can about the cube – total memory used, memory used compared to other cubes in the model, the number of stored calculated cells, number of fed cells, memory used for calculations and memory used for feeders – are some good data point examples.
Finally, create a backup! Even though you are working in a non-production environment, a notepad copy of the cube rule (RUX) file will be a handy reference as you progress with your changes.
Step 1 – Reduction of Calculation Statements
Generally, the more rule (and corresponding feeder) statements you have in a cube, the higher resources (i.e., memory) will be used, so trying to reduce the number of rules is a good place to start. Since historical data does not change in real-time, it is not necessary for calculations based on this data to be performed in real-time. Rules that calculate cube N level values for historical (“actualized”) periods should be removed and reimplemented as TurboIntegrator (batch) processes.
Again, the fewer the number of rules and feeders, the lower the memory and processor requirements will be, which typically results in improved performance. Furthermore, overall sustainability is improved since there is less code to maintain.
Step 2 – Replace all ATTR Functions with DB Statements
Another “easy” optimization– almost a “find and replace” – is to “swap out” less efficient functions within a rule with a more efficient one. For example, using the ATTR function can be much (up to 70 % in some applications) slower than getting the value directly from an }ElementAttributes_ cube using the DB function; therefore, you should replace all ATTR functions with DB functions.
The following rule statements:
ATTRS (‘Cost Center’, !Cost Center, ‘Special Calc’) @= ‘Y’
And:
DB(‘Financial Planning’, !Versions, ATTRS(‘Periods’, !Periods, ‘Prior Reporting Period’), !Company, !Product, !Cost Center, !Region, !Financial Planning Measures)
can be optimized to:
DB (‘}ElementAttributes_Cost Center’, !Cost Center, ‘Special Calc ‘) @= ‘Y’
And:
DB(‘Financial Planning’, !Versions, DB (‘}ElementAttributes_Periods’, !Periods, ‘Prior Reporting Period’), !Company, !Product, !Cost Center, !Region, !Financial Planning Measures)
Step 3 – Streamline and Simplification of Future Period Calculations
After you have addressed the historical (“actualized”) period calculations in the cube, it’s time to look at the future period calculations. If you review these rules and observe any consistently utilized attribute or control-element conditionals being used to invoke or “fire” rules (maybe lines such as these):
IF (ATTRS(‘Versions’, !Versions, ‘Plan Helper Rules’) @= ‘Y’,
IF(!Periods @> DB(‘Period Control’, !Versions, ‘Current Month’),
Then the above conditional statements may be able to be removed (from all the future period calculations that include them) once the following CONTINUE/STET statement is added to the top of the rule (RUX) file:
[ ] = N:
IF ( !Versions @= ‘Working Forecast’
, IF ( !Periods @>DB ( ‘Period Control’ , !Versions , ‘Current Month’ )
, CONTINUE
, STET
)
, CONTINUE
) ;
This statement “turns off” all of the future period calculations UNLESS the current version is “Working Forecast” AND the period is a future period eliminating the need to repeatedly code the previously mentioned attribute and control-element conditionals in each future period calculation. CONTINUE allows evaluation to continue (fire calculations) while STET cancels all calculations beneath the (STET) statement.
The idea here is to “experiment” to see if a single (or a few) CONTINUE/STET statement(s) can be used (obviously more efficiently) than having Planning Analytics interpret each and all of the rules to determine if it should be applied. You’ll need to be sure to thoroughly understand all business logic on this one to make sure that the CONTINUE/STET statement(s) include or exclude the applicable rule(s).
Step 4 – Remove Unnecessary/Obsolete Attributes
Object permanence is “the understanding that objects continue to exist even when they cannot be sensed”; in every Planning Analytics model’s lifespan, new objects are continually added, and typically existing objects are not always decommissioned. This means that the total number of objects increases while some objects continue to consume valuable resources while not adding value (since they are not or seldom used). To that point, in the above example, since the “Plan Helper Rules” attribute reference has been removed from the rule (RUX) file, it can be deleted from the model – again, if you are able to remove any attribute references (and those attributes are not used elsewhere in your model) get rid of them!
Step 5 – Optimization of Feeder Statements
Every rule calculation statement should have a corresponding feeder statement (yes, there are always exceptions) and each feeder statement implemented will have an impact on memory consumption. The more “feeding” being done, the more memory will be consumed. To address “over feeding”, if possible, feeder statements should be first “narrowed” by adding version (or other qualification) to the (left side of the) statement. An original feeder statement such as:
[‘Planning Base Source’, ‘TOTAL_FTE_HEADCOUNT’, ‘Amount’] => [‘999999’];
Could be improved to this:
[‘Working Forecast’, ‘Planning Base Source’, ‘TOTAL_FTE_HEADCOUNT’, ‘Amount’] => [‘999999’];
Additionally, by adding a conditional to the feeder statement, it can be further optimized (from the above) to the following:
[‘Working Forecast’, ‘Planning Base Source’, ‘TOTAL_FTE_HEADCOUNT’, ‘Amount’] =>
DB( IF(!Periods @<= DB(‘Period Control’, !Versions, ‘Current Month’), CONTINUE, ‘Financial Planning’), !System Planning, !Versions, !Periods, !Currencies,
!Company, ‘999999’, !Category, !Product, !Cost Center, !Region, !One Bank, ‘Amount’);
Adding a conditional(s) to a feeder statement makes it a “conditional feeder”. A conditional feeder will contain one (or more) of the following:
- An IF clause to determine an element or cube name,
- A lookup to an attribute or cube value to determine an element or cube to be the fed or
- A feeder to or from a consolidated element
Conditional feeders can generally be very effective in reducing overfeeding but should be used with caution as there are some drawbacks involved. When using conditional feeders, care should be taken to weigh the benefits versus the risks (of using conditional feeders). For example, a conditional feeder will become invalid (stop working) when the conditional(s) in the feeder changes. This is more of an issue if the feeder references an attribute value that changes often. In this case, using a conditional feeder may not be appropriate. You should try to base conditional feeders on data points that are more stable or change on a prescribed schedule, perhaps “period”.
To force Planning Analytics to reevaluate (calculate) a conditional feeder, you can restart the Planning Analytics instance or use the Process Feeders function in a TurboIntegrator process when the underlying condition changes. Keep in mind that feeder processing does cause locking and in larger models may take a significant amount of time.
Conclusion
The optimizations outlined above may be straight forward enough for a beginner to consider making but can also make a material impact on a cube’s performance. Finally, always try to remove obsolete (commented out) code and add some consistent, “best-practice style” formatting and comments to the RUX file for readability.