How to improve report run time performance?

[suhas_bhat]

Hi all,
   I am working on Cognos 8.4 and using Oracle database. I have a problem where my reports are taking too long to run.It is a simple list report using Final_Qry
I am joining Query A (uses Fact A and Dimension S joined at FM level) with
Query T (uses Fact T and Dimension C joined at FM level) at the report level to get Final_Qry. The join condition is just that
Each "Query A" has one and only one "Query T" while each "Query T"has one or more "QueryA".

When i run tabular data for Query A and Query T it runs within seconds. Final_Qry however takes ages to run.

I have set the following properties of Final_Qry  to improve performance
Auto Group and Summarize = Yes
Override Dimension Info = No
Define member sets = No
Auto Sort = none
Processing = Database only
Rollup Processing = Database
Execution Optimization = All Rows
Use SQL with clause = Yes
Execution Method - Concurrent

After setting above properties report runs in 40-50 sec for HTML o/p but on clicking the next page link throws the following error.
RQP-DEF-0371
Operation unsupported in Forward only cursor mode was used
PDF o/p again takes for ages.

If i generate sql for Final_Qry in cognos and run that query on the database i get the result in seconds.Also indexing has been done on database cols.
Can someone please suggest any other technique to imrove report performance when facts having large amount of data are being joined..
Please help..

[bvk.cognoise]

Hi suhas-bhat,
please follow this link.hope it helps you.

https://www-304.ibm.com/support/docview.wss?uid=swg21341765

thanks
bvk

[suhas_bhat]

Hi bvk,
   The error is only a fraction of the problem am facing.
Even if i undo the property Execution Optimization = All Rows and go back to default value my report still takes a long time to run.
Infact Execution Optimization = All Rows property was the only reason why i got an o/p in HTML.
What am looking for is if adjusting query props is the soln when joining facts with large volume of data or is ther a better way of going about this.  :-\

[Lynn]

I suspect that the local processing required to join Query A and Query T might be the culprit.

Cognos can do multi-fact querying around conformed dimensions quite well, so you might want to revisit the Framework Manager model to see if it has been properly set up to accommodate multiple facts. I'm not entirely sure what your situation is since I wouldn't have expected a 1:n relationship between your two queries if they both contain facts.

Otherwise you might compare the native sql that gets generated with the cognos sql that gets generated to find clues on what is being done locally and attempt to push that back to the database. For example, is Query A native sql very different from Query A cognos sql? That can indicate load on the Cognos server.

Any filtering ought to be done in Query A and T rather than in the final query.

Good luck!

[suhas_bhat]

Hi Lynn,
thanks for your reply, I guess ther is no workaround for this problem. There is so much data in the fact tables that joiing them is going to take an insane amount of time!!
Now we have set the option to fetch 1000 rows in html o/p and mostly going to schedule the reports (so that it runs in the background) for anything larger than that.

I would still like to underastand why the generated cognos query for Final_Qry runs in no time but the report itself takes a long time to run.. Any ideas??

[blom0344]

Try to persuade the DBA to allow for running a trace file. You will probably find that the queries are executed 1 by 1 on the database and that Cognos will be responsible for rejoining the sets. For large sets this could be the real performance killer.
The solution lies in getting the model to handle all data in a single query executed against the database..

[bloggerman]

Hi

I got the part where if you have two queries in the report then each is run separately and then the results are combined by the cognos server.

But, isnt that the case with multi fact queries with confirmed dimensions as well. Even if we have data from two facts in one query, cognos engine would make two separate queries for each fact and stitch them together at the local server level. Which would mean local processing.

[BigMikeMike]

I've seen a situation where Cognos used local processing even though  the query could be executed by the database.

It doesn't make sense, but I was able to get this to push all the SQL to the database, by setting "Use Local Cache" to "No"

Give that a try.

Good Luck

[Lynn]

Hi

I got the part where if you have two queries in the report then each is run separately and then the results are combined by the cognos server.

But, isnt that the case with multi fact queries with confirmed dimensions as well. Even if we have data from two facts in one query, cognos engine would make two separate queries for each fact and stitch them together at the local server level. Which would mean local processing.

Multi-fact queries are stitched together locally, but they seem to perform quite efficiently compared to a comparable approach with two separate queries in the report joined explicitly. This is based on my observations, so FWIW.....

[blom0344]

My guess would be that the size of the sets matters the most. Say,aggregating data to year level for the period 2006-2010 would yield 60 rows at most. If we rejoin this with another set of yearly aggregated data I would not expect any performance issues. If we do the same for a set of some thousands of customers performance may suffer very badly.

I run a lot of trace files on SQL server and I am surprised how often a multifact query is handled by the database itself (i.e. where the server does not 'stitch'), but where the DB handles both the full outer join and coalesce part

[bloggerman]

Does this hold true for other operations as well like union, intersect or only for joins?