Profitability Tech Tips – Optimizing Performance with NonEmptyTuple
One of my favorite features that were introduced into Oracle EPM Profitability was the ability to utilize the NonEmptyTuple property in our Custom Calculations. By utilizing it correctly we can dramatically improve performance on calculations that have a large number of possible target intersections and/or have complicated formulas by ensuring that our calculations only execute on intersections that have data.
This may not sound like a big deal, but let us look at a simple example:
In the below Custom Calculation Rule we are multiplying a Labor Rate (1) by Time Spent (2). Pretty simple but you can see that the number of target intersections is 232,232,000!
Now by Essbase ASO standards that is not an enormous amount of intersections, but it does mean that as of right now, PCMCS would execute the Labor Rate x Time Spent formula against 232 million intersections and that is A LOT of work. The waste of calculation effort becomes very apparent when we look at the Execution Report and realize that this specific calculation only impacts 12 intersections!
So, how do we stop the application from executing the formula against 232 million intersections and only focus on the 12 (in this case) that matter? The answer is the NonEmptyTuple property. By introducing the following NonEmptyTuple property, the Labor Rate x Time Spent formula will only be executed where there is a Labor Rate, and all intersections that do not have a Labor Rate (empty) are ignored:
Now that we understand the value of the NonEmptyTuple property, let us focus on some tips when using it:
1. Use it to Improve Performance:
- This is obviously a no-brainer but it's difficult to overstate how much of an impact this can make on performance. I have personally seen calculations go down from hours to seconds.
- This performance improvement becomes very visible when comparing the performance of on-premises HPCM applications (where we cannot use the NonEmptyTuple property) to PCMCS applications (where we can).
- In addition, we can also look at replacing long-running Allocation Rules with Custom Calculations using the NonEmptyTuple. We typically only advise this in extreme situations but it can significantly improve the overall run time of applications.
2. Be Careful How You Use It:
- The NonEmptyTuple helps improve performance by ignoring intersections that should not be impacted by the calculation. However, if we configure the property incorrectly we can be ignoring valid intersections which would result in incorrect calculation results.
- The NonEmptyTuple property works best in the following types of Custom Calculations:
- A = B
- A = B*C
- A = B/C
- In any of the above scenarios creating a NonEmptyTuple property on the “B” intersection would improve performance. However, if we have the following type of formula inserting a NonEmptyTuple on the “B” intersection could create incorrect results by ignoring valid “D” intersections:
- A = (B*C) + D
3. Be Specific:
- The more specific you can make your NonEmptyTuple, the faster the calculation will run. However, similar to the above point be sure that your NonEmptyTuple doesn’t result in your calculation missing valid intersections.
- Creating a NonEmptyTuple that is too generic, or contains too many parent members will result in a MAXFORMULACACHESIZE error while executing the calculation.
4. Avoid References to Members with a Dynamic Formula:
- In some circumstances, we could be tempted to include a member with a dynamic formula in our NonEmptyTuple. If possible this should be avoided as it can negate the performance gains from the usage of the property.
- When we place a member with a dynamic formula in our NonEmptyTuple the formula within the member will execute against all the target intersections, which often makes the NonEmptyTuple irrelevant.
NonEmptyTuple is a fantastic feature in Oracle EPM Profitability but it should be used correctly otherwise it can be a source of real frustration. The above Tech Tips should help you in knowing when to use the NonEmptyTuple property but also know how to use it correctly.
For comments, questions, or suggestions for future topics, please reach out to us at firstname.lastname@example.org. Visit our blog regularly for new posts about Cloud updates and other Oracle Cloud Services such as Planning and Budgeting, Financial Consolidation, Account Reconciliation, and Enterprise Data Management. Follow Alithya on social media for the latest information about EPM, ERP, and Analytics solutions to meet your business needs.