Controller is integrated with Microsoft Excel through an Add-In application, enabling users to present Controller data via Excel reports #

This is typically achieved via refresh of fGetVal formulae. However, this can result in Excel file growth and development that can result in slow performance.  

The following list includes areas to investigate if your reporting experience is declining. 

Efficient Report Design  #

You can improve your report design by placing Controller formulae, such as fGetVal, on to a front data worksheet. All adjacent report worksheets can then be fed by standard excel formulae against this first worksheet. When the report is refreshed, the Controller application only needs to seek and update formulae content in one location. This is more efficient compared to looking for content randomly placed throughout the file. 

Caution is also advised towards the number of cells populated with Controller formulae. IBM typically advises a limitation of fGetVal formula usage of up to 10,000 occurrences within a single report. However, it is possible for reports to be larger than this and to return results. This does however impact load on the system and run speed of the report. Therefore, if you have very large files, consider whether these can be truncated into smaller templates. 

Audit the Report for Historic Design Impacts  #

It is common for files to accumulate within an organisation. This can result in current report files being copies based on previous report files. This accumulated file build can result in unseen complications. For example: 

• Defined name ranged cells producing errors from worksheets being duplicated and/or edited.  Check the ‘Name Manager’ utility from the Formulas bar. 
• Hidden worksheets containing multiple Controller formulae. These could be irrelevant to the report but being refreshed on each run time execution and slowing performance. 
• Data links to other servers/locations. This broadens the resources needed to be used to update the report. For example, links that may sit on hidden worksheets.

 Enable Enhanced Reporting Optimisation (ERO)  #

The transfer of report run requests between Excel reports for Controller and the underlying database is a very ‘chatty’ communication, as individual data requests are requested and returned. When the Enhanced Reporting Optimisation (ERO) setting is enabled, a bulk transfer of data requests is ran instead. This returns bulk of information, resulting in a run speed improvement. 

The following IBM article details how this configuration can be put in place if you are on premise.   

If your Controller application is managed by IBM SaaS, raise a ticket with IBM support to request ERO.

Once enabled (either on premise, or IBM SaaS) cell A1 of the lead worksheet will need to be defined as “Optimise2” for this enhancement to run. 

Undertake Regular Database Maintenance  #

We would recommend regular optimisation of the Controller database. This can be done as part of your regular month-end reporting timetable, along with any change control works. Secondly, for Controller installations that are integrated with IBM Planning Analytics through FAP publish, it is best practice to regularly truncate data trickle tables. If this is not done. they can generate adverse growth.  

For example, you can truncate tables where FAP publish is generating content older than 30 days. This is then typically executed once a month.  

We would also recommend attention to the XACCLOCK table, as it aggregates and accumulates excess content over time. Retaining a record of local account locking for historic data is typically not needed for retention, particularly where period locks are enabled. 

Other Factors #

There are other areas that can be investigated in addition to the above. This includes server preference settings within the Controller application, to SQL server configuration, server resourcing, and so on.   

This IBM article details some of these other factors.