Best Practices on Report Development to improve Performance

[master27]

Hi All,

Any recommendations on how to design a report or model a DMR that achieves optimal performance? Currently, we had designed a report that originally has a runtime of 4 minutes. We were able to reduce this down to a minute, but is still unacceptable. All data was not yet loaded into the testing environment, only a small scale for development purposes. Another thing that makes it unacceptable is that this report was not yet deployed to the users, so if in the testing environment it already requires 1 minute to up, what more would it require if concurrent users were already using it? The database was already designed as star schema, primary keys and indexes were all set there so that Framework Manager would just inherit everything from it. Use of local cache was of great help but only after the initial running of the report. Other options that would still reduce the 1 minute report runtime? Documentations would be of great help.

Thanks!

[MFGF]

Where is the time being taken - in the processing of the SQL query by the database, or in the rendering of the report by Cognos 8?

MF.

[master27]

It's in the rendering of the report by Cognos 8.

[deva.adi]

One thing I have found with all this performance tuning is a way to speed up all the initial report pre-processing.
 
If you have a report with several queries Cognos reads each query and attempts to satisfy any Parameters or Macros. 
 
One way to reduce this time is to create a dummy query “Q_Param_Dummy” and put all the filters in this query.  You can also copy any column that uses parameters, parameter maps or Macros into the objects panel.  This query should not be ties to any visualization and will not be executed when the report runs, it’s only purpose is to satisfy all the pre-processing.
 
After this is completed you then need to set a Query property on every query in the report.
 
“Use for Parameter  Info” – Set this to “Yes” for Dummy query and “No” for all others.
 
Other things that have seemed to have helped is setting the “Execution Method” to “Concurrent” on queries that can run without information from others.
 
On the Dummy Query, I also move this to the top of the list and set it to “Sequential” in the “Execution Method” property.
 
 

[nmcdermaid]

To troubleshoot the rendering part, you can press F12 in IE, and go to Profiler. It will tell you how long each individual element took to render. There is a lot of information there but it might shed some light on it for you.