What is Optimise2 (ERO / Enhanced Reporting Optimisation), and why should I use it?
- 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:
- 'Excel link' spreadsheet reports (.XLS / .XLSX files)
- 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!
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.
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
Resolving The Problem
(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
(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:
10. Select the database ‘model’
11. Ensure that the following ‘role memberships’ are ticked:
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’
- 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".
Related information1347569 - Controller SQL 2000 database optimisation and
Technote 1365263 - ERO not working when sheet names are
1396973 - Controller SQL 2005 database optimisation an
1370796 - Error 'FetchValuesBulk# - File not found Serv
1498843 - How to configure servers to use Optimise2 (ER
1970627 - Decreased need of ERO (Enhanced Reporting Opt
1405638 - "ORA-29280: invalid directory path ... ORA-06
1982943 - How to create a test Optimise2 (ERO) spreadsheet