Cognos 10.2 Report Studio report performance issue

[vvasireddy9]

I have a report with 4 fields, no calculations or joins, just straight pull from FM and a filter on date.

The report/tabular data is taking 9 mins to fetch 250,000 records when run in excel/html.

same report takes less than a minute when run in CSV and same time when sql is run against the DB.

There is literally nothing I can think of changing in the report to debug, its a straight pull from FM. and the same fields when tested in FM, I got all 250,000 records in less than a minute.

what could be the issue? I have tried turning off the Auto group & Summarize and setting processing to 'Database Only' and no luck.

[bi4u2]

How are you running this report, from Cognos Connection? Have you tried to use 'Run with Options' and run it in the background to compare how long that is taking to generate? Sometimes I find reports take a fraction of the time when run in the background.

[Lynn]

I have a report with 4 fields, no calculations or joins, just straight pull from FM and a filter on date.

The report/tabular data is taking 9 mins to fetch 250,000 records when run in excel/html.

same report takes less than a minute when run in CSV and same time when sql is run against the DB.

There is literally nothing I can think of changing in the report to debug, its a straight pull from FM. and the same fields when tested in FM, I got all 250,000 records in less than a minute.

what could be the issue? I have tried turning off the Auto group & Summarize and setting processing to 'Database Only' and no luck.

A result set of 250,000 rows is not a report. It is an extraction of data. Cognos is not an ETL tool but if it is necessary for you to use it in that manner then run the content to CSV.

It would generally be recommended that you find out what the user is trying to do with the data and then constructing a report to help them solve that problem rather than dumping data and leaving it up to them using Excel or Access to figure out.

[MFGF]

A result set of 250,000 rows is not a report. It is an extraction of data. Cognos is not an ETL tool but if it is necessary for you to use it in that manner then run the content to CSV.

It would generally be recommended that you find out what the user is trying to do with the data and then constructing a report to help them solve that problem rather than dumping data and leaving it up to them using Excel or Access to figure out.

Hurrah! You wrote almost word-for-word what I was thinking as I read through this thread! :)

I'm with Lynn on this - 100%. Cognos 10 BI is not an ETL tool or a data dump utility. See if you can figure out the real requirement and deliver it in a report that answers the question concisely.

Cheers!

MF.

[cognostechie]

Me too.. agreed 100%

[vvasireddy9]

Though there are summarized interactive report views available, the users also want the ability to view in html/export into excel all the data between a selected range of dates like the example of 250,000 rows that I mentioned earlier and perform their own analysis.

Anyways, it looks like there is one field that is causing the performance lag. It is a descriptive field that can be anywhere from 50 to 100 chars. The report is running much better without this descriptive field. Not sure how to handle this scenario but when run the same with the descriptive field against database it only takes than a minute.

[MFGF]

...the users also want the ability to view in html/export into excel all the data between a selected range of dates like the example of 250,000 rows that I mentioned earlier and perform their own analysis...

Hi,

Your users can perform their own analysis from within Cognos with no need to export data at all. Why are you suggesting the export solution when it is so much more difficult to set up and manage (and control)? Cognos Workspace Advanced is a really powerful analysis environment - using the data you already have in your data source, so it's safe, centrally managed, and (in theory) consistent.

Cheers!

MF.

[vvasireddy9]

Hi MF - I understand and completely agree with you. I dont think we can expect all clients to agree with what a developer suggests. Sometimes their requirements demand to create exactly what users are looking for inspite of explaining and showing proof of concepts on ad-hoc tools like CWA.