Complex Queries Resulting from Rollup/Aggregation?

[tsarken]

Hello All,

I'm hoping that someone here can help me.  I'm working with a report created by a former consultant who is no longer available to us.  The report actually crashes our entire database server when run to PDF.  It appears that the SQL being generated by the final page of the report when run to PDF is so large and complex that it is triggering a memory error on our SQL server.  My best guess is that this is due to the multiple levels of groupings and rollups, as I see in the generated SQL basically the same query being joined to itself seven times, each with different GROUP BY clauses and summary column selections.

So, my two questions are:
1.) Has anyone else ever seen this behaviour with massively complex queries caused by aggregate/rollup settings? 
and
2.) Is there any way to get Cognos to run the query once, and perform the rollup/aggregation on the Cognos server, and not on the database side?

Any help would be greatly appreciated.

Thanks,
-Tim

[blom0344]

Check out the tips on 'Rollup processing'. Setting this to local should take load from database to the C8 server. Should very much depend on the exact nature of the query whether Cognos can do it more efficiently..

[CognosPaul]

If you're having problems with the generated SQL, you should probably also go over the framework model.

Ensure the queries in the Business and Presentation layers aren't joined. Adding joins at these levels prevents Cognos from minimizing the SQL. (This may be intentional in some cases).

Check the joins. Are there any duplicated joins? Are there any traps?

Check the aggregation and determinates.

On the report level:
How is the final page set up? Is it a big crosstab? A list? A crosstab inside a list inside a repeater?-