HFM User:
Do you have any rule optimization tips?
Chris Barbieri:
Recently, one of our clients asked us how to reduce consolidation times by increasing the efficiency of one specific rule. The rule in question copies data from “C4#DefaultRate” to various other Custom 4 dimension members using a trigger. The copied data is then translated using various foreign exchange rates, but to begin, I will focus solely on the efficiency of copying local currency data for base entities.
I tested three different variations of the rule in the attached .RLE files. For illustration purposes, each of these files include only a single subroutine. The durations in bold text are the cumulative times for the respective Sub Calculate routines for one period’s consolidation.
- Approach #1 (Copy Account Loop): This approach selectively copies the data for three specific account lists to avoid copying any accounts that are not necessary for FX analysis. 34.1 seconds
- Approach #2 (Copy All Accounts): Instead of using account lists, this approach copies data for all of the accounts. 9.6 seconds, a 72% reduction in time over Approach #1
- Approach #3 (Copy Selected Accounts): Similar to Approach #1, this rule copies data only for those accounts in three specific lists. However, this approach leverages an Open Data Unit at the top of the Intercompany and Custom Dimensions, so it only runs on those accounts with data. 12.6 seconds, a 63% reduction in time over Approach #1
Although the second method is faster than the third one based on the data copy rule performance, it is actually slower overall: Due to the increased volume of data that HFM is translating, the consolidation times are actually slower for Approach #2.
Total consolidation times using each approach:
- Approach #1: 9.1 minutes
- Approach #2: 10.1 minutes, an 11% increase in consolidation time due to increase data volume
- Approach #3: 8.3 minutes, an 8% reduction in consolidation time with the same amount of data!
Upon reviewing this analysis, our client’s FP&A team chose to implement Approach #3. Not only did it decrease consolidation times, but it also allowed them to double the number of Custom 4 members, allowing them to perform much more detailed FX Analysis.
I hope that you enjoyed this description of the various approaches and their respective consolidation performance impacts.
Please email insights@finit.com with any questions or feedback on this topic.
Do you have an issue or topic that you’d like Chris to discuss in his next blog post? Please e-mail us at insights@finit.com and we will “Ask our ACE”!
Attached Files:
CopyAccountLoop.rle
C4DATACOPY_SelectedAccounts.rle