Financial Consolidation and Close Performance Tuning

Thank you to Rich Wilkie, Rick McIntyre, Dheeraj Anand, Shailesh Kumar, part of the EPM Development team, for the presentation on this topic.  You can also watch the recorded webcast.

The presentation covers the following topics:

  • Periodic Consolidations  
  • Operations Guide
    • Troubleshooting Process
    • Troubleshooting Activities
    • Change Management
    • Options
    • Performance Improvement Variables
  • Custom Calculations Best Practices – refer to the presentation for detailed examples
    • Calculation Concepts
    • Performance
    • Customer Case Studies
    • Extended Dimensionality (Hybrid) Scripting Techniques

 

Periodic Consolidations  

Controlling ‘To date view storage’

  • Starting 21.06, there will be a feature available that gives flexibility to store/not store ‘To Date’ View members – QTD, HYTD and YTD
  • Once enabled, users will see new flavor of Consolidate/Translate rules: Consolidate – by selected View/Translate – by selected View
  • View dimension is now a parameter in these rules where user can select the required view members (Periodic is default).
  • Choosing not to compute and store View members helps in increased performance of consolidation routine, reduces the application size, reduces restructure timing
  • To see the data in required View members, users should use the associated _RULE member
  • The member formulas for the _RULE members have been significantly tuned to provide the best performance
  • Please review the documentation that will be published along with 21.06 release for more details

Consolidate By View

  • Enablement
    • Not enabled by default, to enable
    • From Navigator, select Configuration from the Application column.
    • On the Configure: Consolidation screen, click on the Enable Features button on right
    • On the Consolidation-Enable Features dialog, click on the Enable button on the right
    • Close when the “consolidation features selected have been enabled” message returns.
  • Confirmation
    • From Navigator, select Rules
    • Confirm that the following new Rules are available
    • Consolidate – by selected View
    • ForceConsolidate – by selected View
    • Translate – by selected View
    • ForceTranslate – by selected View
    • Update View Calculations
  • How to Implement
    • From Rules, Run Consolidate – by selected View on all months loaded to the application.

The process will delete all QTD, HYTD and YTD data currently stored in the application

  • From Rules, Run Clear Empty Blocks
  • From Navigator, select Jobs from the Application column. Click on Schedule Jobs and run a  Restructure Cube, Now
  • Run a database refresh, then test consolidation times using the Consolidate – by Selected View rule.
  • Retrievals Considerations
    • Build spreadsheets using adhoc
    • HSGetValue formulas are supported, but use them only where necessary
    • Enable Ignore Constants functionality included in 20.11 update to improve performance
    • Generally most small, medium and even larger reports will perform at acceptable times
    • EPM Performance Engineering and FCCS Development can help optimize problematic
    • spreadsheets
    • Use the Update View Calculations Rule on slow spreadsheets to store that specific slice of data  as Periodic, QTD, HYTD, and YTD to perform.

 

Operations Guide and Troubleshooting Information

Troubleshooting Process

  • Backups (UDR)
    • Important to have a snapshot for troubleshooting
  • Provide Feedback
    • Useful when logging SR’s
    • Helps save time for troubleshooting activities
  • Best Practices
    • Maintenance Mode
    • Limit Service Admin role
  • Client Compatibility Issues
    • Activity Report tracks client access

 

Troubleshooting Activities

  • Business Rule Errors
  • Database Refresh Issues
  • Performance Issues
  • Imports and Exports
  • EPM Automate
  • Reporting Errors
  • Smart View
  • Consolidation Errors
  • Resolving Data Issues


Change Management

  • Current Process
    • Monthly Update
    • Weekly Patch
    • One-Off Patch
    • Emergency
  • Regressions found in Test Update
    • Fixed in Weekly or One-off before Production update
  • Regressions found in Production Update
    • One-off or emergency
  • Change Migration Process


Options

  • Upgrade delays -Available via EPM Automate
  • Patching – Follow steps to ensure smooth processing
  • Loaner Pods -Available via SR process for testing
  • Implementation Success Program -Ensure project success via Development assistance
  • Performance Validations -Available before Go-Live
  • Regression Testing -Development created automation for monthly regression testing
  • Governors -Process for increasing dimension governor limits
  • RCA -Requesting Root Cause Analysis for resolved issues
  • Health Checks -Ensure environment is healthy and free of issues

 

Performance improvement variables

Below performance variables that are currently available:

  • OptimizeConcurrency = True
    • Some of the will calculations are reorganized to execute at the very beginning or at the end. There is no guarantee that these will improve performance in all applications. The degree of  improvement depends on the entity structure of a given customer. Customers with deeper entity hierarchies would benefit most. The idea is to try them out and use them if they work for your application
  • OLURatesLoad = True
    • This substitution variable improves refresh database performance. The more scenarios, currencies and rate accounts in an application, the more noticeable the improvement will be.
  • DeltaDBRefresh = True
    • This Substitution variable improves database refresh performance by doing only necessary actions based on metadata change. Not applicable for Legacy Non “Extended  Dimensionality” applications. With this variable set, if a database refresh is performed without changing entity dimension, users will not be required to do a re-compute of ownership. (if enabled).

=====================================================================================================

References: