Cognos BI Performance

[Ahchay]

Does anyone have any decent tips on how to get the most out of Cognos BI? We are currently upgrading from Cognos 7 - primarily because of performance issues - and while C8 is quicker than the old system, it's not a lot quicker.

We have some reports that perform brilliantly, and others which sometimes run within a few minutes and other times can take hours (I've got one running now which has been going for three hours and is showing no signs of ever finishing). The reports are not prompted and the generated SQL for the report executes in less than two minutes - which is far from perfect but right now I'd be happy with twenty!

I wouldn't mind, but the Cognos server and the db server are, to all intents and purposes idle. There is very little or no server activity at all - the BIBusTkt process tickles into life every few minutes if I navigate through the interface - I am, as far as I know, the only user logged on to the system at the moment (it being 9pm on a Monday and other people effectively having a life)

We have tried increasing the number of processes for the batch and interactive report services to very little effect, and have increased (and decreased) the maximum memory in MB setting in IBM Cognos Configuration to anywhere from 768 to 2000 (which appears to be the limit). We have a 8-core server running with 16gb of RAM and most of that processing power is sitting unused 99% of the time - we might as well be running on dual-core desktop.

What are we doing wrong? IBM support have been next to useless so far.

[IceTea]

Hm, hard to give you a solution :(

Did you try to monitor your database server while one of the wait-long-time-reports is running? Especially within complex reports with many queries, filters and so on it's hard to get the info which sql is REALLY being sent against the database. So this would be my first approach. Monitor your database, which sql is processed and how long you're waiting for your database.

The next approach would be to try around with the report. Delete elements, delete other elements, locate the "thing" that slows down your success. Maybe it's good to try around with a rebuild of the report.

I'm curious about it ;)

[joe123]

You can find some useful tuning tipps here:
http://www.ibm.com/developerworks/data/library/cognos/cognosprovenpractices.html#performance
I rember the maximum size for jvm is 1152mb.

We had some reports causing up to 99% load on our database because some data was sorted on the db server and not local at the cognos server. As suggested by IceTea you should remove element by element from your slow running reports until they speed up. Or rebuild your reports from scratch until they run slow.

Another thing we ran into, for testing we set up the audit level to full and forgot to set back.
This caused some serious report server problems.

To keep config overview the dispatcher diagnostic tool is quite nice.
http://www-01.ibm.com/support/docview.wss?rs=0&uid=swg24021220

You can run stability tests and test plans with apache jmeter, i never used but it looks interesting.
Ibm provide a special jmeter config for the GO-Sales samples: http://www.ibm.com/developerworks/data/library/cognos/page404.html

[Ahchay]

Thanks both - IceTea, that generally describes the approach we're trying from here. A dual attack of database tuning and brute force report rewriting.

Joe - we'll have a look at those performance tips.

Cheers
Chris

[SomeClown]

VM or physical?

[jive]

Hi,
In the project I work now we have a monthly report, around 70 sql was generated from query subject, for 80 graphics and crosstabs. When we start that report we have problem with a specifics crosstab, so we don't include it and leave it to be done later. The monthly report without the problem crosstab take between 8 to 12 minutes, we run it over 100 times to be sure. When we include the problematic crosstab it's slow to never ending the creation of the monthly report. The only solution we could find is to build a query subject in FrameWork Manager based on a sql. That query object have pass rough SQL to be sure they used the right index and the database full capacity.

Now we can run everything in 15-18 minutes.

Just check if the creation of a query subject can reduce the production in our case that do job perfectly.

Thanks