To make reports run faster/ better

[phoenixfire]

Hello everyone,
If I have to work on reports to make them run better, where can I look to fix the problem? These reports are based on cubes and FM packages. Where should I look - in the reports, cubes, packages? Is there anything else I should look for?
Any help is greatly appreciated.

Regards,

[BrianInderwies]

Hi there,

I cannot speak directly to cubes as we are not using them, but for FM packages built from relational databases, the largest impediment to report performance will be the quality of the SQL generated by ReportNet.  The "queries" that are created by Report Studio to support your report output will be translated into your database's native SQL (using UDA.)  The SQL that is created is greatly impacted by how your framework is established.

We had pretty major performance issues while building reporting in C8 from a pretty vanilla star-schema warehouse in SQL Server 2005.  With help from some experts at Cognos, here are some of the things we did:

1.  Ensure that you do not have extra "queries" for your reports, and unnecessary query items that your spec is not using.  Typically Report Studio will do a good job of removing unused queries, be sure to check for yourself. 

2.  Most importantly, examine the "Generated SQL" for each of your report queries.  A good practice is to copy that SQL and run it directly against your database (using Query Analyzer for SQL Server or a comparable tool.)  Generate the query plan if possible and look at the cost of each part of your query.  More often than not, if there are performance bottlenecks, they will become very apparent here. 

If you find your RS-generated queries to be inefficient or slow run directly against the database, you can
   a. look at the FM model to make sure the relationships between your data tables make sense
   b. look at FM to remove unnecessary determinants on query subjects (according to someone from Cognos, FM will sometimes "guess" determinants that are not necessary.)
   c. build composite indexes on tables in your db for your queries to eliminate bottlenecks.  The largest performance gains for us were gained here.

Your best bet is to simplify the amount of query processing done by ReportNet as much as possible.  Often, queries will run sequentially, and your report will not appear until they are finished.

If query performance is not the issue, take a look at the report specifications closely.  Many layers of nested conditional blocks might slow things down as ReportNet will evaluate the SQL for each of the conditions, in some cases before the report appears.

Best of luck!

[phoenixfire]

Thank you very much....Really informative...

[jguevin]

First, what version of are you on?  If you are on 8.1, upgrade to 8.2. Performace for cubes use has increased significantly from 8.1 to 8.2.  Also make sure the Powercubes are optimized when built in transformer.  There is a document Cognos 8 BI Troubleshooting Guide which outlines some tips as well.  hope this helps
Jeff