IBM Support

What is Optimise2 (ERO / Enhanced Reporting Optimisation), and why should I use it?

Troubleshooting


Problem

Customer would like more information about Optimise2 (also known as "Enhanced Reporting Optimisation" / ERO).

Symptom

Customer would like to speed up their large/complex Excel-based reports.

Cause

'Enhanced Reporting Optimisation' (also known as 'ERO' and 'Optimise2') is a performance-improving feature that has long been part of our Controller product (since version 2.x).
  • For the sake of simplicity, this feature shall be referred to as Optimise2 from now on

Optimise2 works with both of the following types of reports:
  1. 'Excel link' spreadsheet reports (.XLS / .XLSX files)
  2. Reports created in the 'Report Generator' (in other words those stored inside the Controller database itself, which are launched by clicking "Reports - Run" from inside the main Controller application itself.)

Optimise2 will only speed up these reports if they contain many 'cc.fGetVal' formulae inside them.
  • As a general rule-of-thumb, you should use ERO/Optimise2 if the report contains more than approximately 5000 to 10000 fGetVal formulae

To give an idea of the performance boost that Optimise2 can give, some reports can be up to 5 times faster when using Optimise2!

Technical Information
The concept is to use SQL 'bulk insert' to perform multiple SQL inserts directly into the temporary buffer (for MS SQL, this is the 'TEMPDB' database) simultaneously.
  • This is instead of queuing each insert, and running them one-by-one.

By doing this, certain Excel link reports can be sped up massively.

Environment

IMPORTANT NOTE:
Be aware that (from Controller 10.2.1 Fix Pack 2 = 10.2.5120.72) onwards, there is a decreased need to use Optimise2.
  • Specifically, from Controller 10.2.5120.72 onwards, if you are using SQL or DB2 then there has been an improvement to Controller which means that (for many smaller spreadsheets) there is virtually no improvement if you enable Optimise2
  • However, Oracle will still benefit from using Optimise2
Full details are inside separate Technote #1970627.

Resolving The Problem

There are two separate parts that need to be done to implement Optimise2:

(1) Ensure that the Controller I.T. environment is configured to allow Optimise2 to work
  • This step is done by the I.T. administrator.

    This needs to be done for *each* separate database that the end-users will use
    • Therefore you will need to remember to perform this step each time a new database is created


(2) Switch on the ERO 'flag' inside the report itself (done inside the Controller application)
  • This step is performed by the Controller (finance) application superuser.
    • This needs to be done for *each* separate report that they wish to use Optimise2 with.

    IMPORTANT: You must ensure that your Excel worksheet names are named appropriately (see separate IBM Technote #1365263):
    • Excel sheet names must not contain a 'space' character in their name otherwise Optimise2 will not work

Steps

(1) For the I.T. administrator to perform

The following steps are based on Controller 10.2 and SQL 2014.

  • In general these instructions can be used for older versions of Controller & SQL. However, there are very minor differences. For more details on how to configure Controller 10.1 (and older), see attached document "Technote_1347048 - ERO, Optimise2 and Controller10.1 with SQL2008 v2.pdf"
  • If you are using Oracle (not Microsoft SQL) then please refer to separate instructions inside IBM Technote #1498843.
 

If you are unsure about some of the steps referred to below, then more information on each step is given inside the attached document "Technote_1347048 - ERO, Optimise2 and Controller10.1 with SQL2008 v2.pdf".

On the SQL server:


1. Launch ‘SQL Server Management Studio’
2. Expand the <servername> - ‘Security’ – ‘Logins’
3. Locate the Controller SQL login (e.g. ‘fastnet’) that your Controller application server uses to connect to the database (you can check this inside ‘Cognos Controller Configuration’, inside 'Database Connections' section)
  • TIP: this is typically “fastnet”, but you may have a slightly different one (e.g. “controller” or “cognos” etc.)

4. Double-click on the SQL login name (e.g. “fastnet”)
5. Click on the “server roles” tab
6. Ensure that *only* the roles “bulkadmin” and ‘public’ are ticked:


==============================================
NOTE: The following steps (7-11 inclusive) are only necessary when using older versions of Controller
  • If using a modern version of Controller (for example 10.2.1 Fix Pack 4) then these steps (7-11 inclusive) are no longer necessary.
  • 7. Click on the “User Mapping” tab
    8. Select the database ‘tempdb’
    9. Ensure that the following ‘role memberships’ are ticked:
    • db_owner
    • public

    10. Select the database ‘model’
    11. Ensure that the following ‘role memberships’ are ticked:
    • db_owner
    • public
==============================================

12. Choose an Active Directory user (for example "domain\controller_system") which you are going to use to transfer the small files from the application server to the database server
  • Make sure that its password is configured to never expire.

13. Create a new folder in a sensible location (for example: C:\Optimise2)
14. Share this folder (for example as “Optimise2$”)


14. Make sure that your Active Directory user (for example "domain\controller_system") has 'Read/Write' permission on the share:


15. Right-click on the folder, choose “properties”
16. Click on “Security” tab and ensure that the user has “Full Control” ticked:

 

On the Controller application server:


1. Launch Cognos Controller Configuration (from the start menu).
2. Navigate to the section “Enhanced Report Optimisation”
3. Fill in the details, for example:

4. Repeat the above step for each and every database connection listed inside ‘select connection’

NOTE:
  • File mode: For SQL databases, choose “file copy”
  • Server: typically use the NetBIOS name of the SQL server
  • Share: The name of your share (e.g. “Optimise2$”)
  • Server directory:
    • For SQL databases, this should be blank (empty).
    • However, when using Oracle you need to fill this in. See separate IBM Technote #1405638 for details.
  • UserID: Type in the name of the Active Directory user who has read/write permission to the network share (for example DOMAIN\Controller_system)
  • Password: The password of that AD user

 

(2) For the Finance user(s) to perform (on each of the reports):

Inside each and every Excel spreadsheet, the end user should:

  • Highlight cell A1
  • Click "Name Manager"
  • Create a new entry called "Optimise2" which refers to the entire 'Workbook':


In other words, after creating that entry, it will look similar to this:
  • Save changes
  • Repeat for each and every report (every XLSX file)

TIP: For more details, see attached document "Technote_1347048 - How to configure Reports to use Optimise2.pdf".
---------------------------------------------------------------------
TIP: If you want to make a very simple 'test' spreadsheet (simply to trigger ERO and make sure that your configuration is working) see instructions inside separate IBM Technote #1982943.
---------------------------------------------------------------------

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.3;10.2.1;10.2.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

1032417

Document Information

Modified date:
13 February 2019

UID

swg21347048

Page Feedback