Reports unpredictable

[dssd]

Have a couple of reports that run ok at times and at other times are terribly slow. What could be the causes? Server clogging, Database activity? definitley not reports i would think

[AussiePete2011]

Ok this is an issue that will need co-ordination between the report developers, the Cognos Admin's and the DBA's

What I'd suggest is that you start monitoring this so that you capture
1.  Server load where Cognos has been installed.
2.  DB load where the data source for this report exists.
3.  Capture User Audit information for the same period.
4.  Capture schedule Audit information for the same period
Once you have this information, you can correllate this into a load characteristic chart.

We've written an inhouse process that sits on each server and writes to a csv file which captures CPU, RAM, Virtual Memory and PID information every 5 minutes.  The csv file is released at 11 PM and then written to a database which we report from.

This may seem extreme but we have developed a load characteristic of each server and can then work out out tuning requirements.  We also determined that there were issues with the database and this is still being sorted out by the DBA's.

Its not a quick solution but it can assist with writing up part of a root cause analysis for the stakeholders.
We have also purchased MetaManager to show the report and FM model in documentation form to see if we can tune these aspects as well.

Hope this helps
Cheers
Peter B

[dssd]

Thanks Pete. What type of issues could be there on the database side?

[AussiePete2011]

Hi there

In my experience its the number of connections, contention and locks.  If the database is under load, this can affect the performance of the reports and if that's the issue then you need to look at the tables and perform some tuning.

In as far as tuning on the database is concerned, I'm talking about, normalising, summarising, views, lookup tables, indexing etc.  We have Oracle so have purchased Quest Software and the SQL optimizer.  It's made life so much easier as we can run the SQL through the optimiser and it gives you options and timing.

Basically you capture the SQL the report sends to the database and then run this on the database in plan execution mode to see if it uses any indexes.  If it doesn't then it would be advisable to create an index or indexes as the case may be.

Its a big topic this one.

In another topic I explained how to create a performance log which can provide information on the reports of where the likely bottlenecks are.
I'm not sure where it is so I'll have a dig around and get back to you.
See
https://www-304.ibm.com/support/docview.wss?uid=swg21455407

Hope this helps
Cheers
Peter B