Performance tuning of cognos reports

[madhancog]

HI GURUS,
  let me know how we can tune the cognos reports in report studio.
  what all the things we need to do as a best practice.

Thanks in advance
K.M

[cognostechie]

It's a very wide question. It depends on whether the report is using a Relational Package or a Cube, the queries, filters , expressions used , modelling, servers, dispatchers and 16.987632525 million other possibilties to look into.. ;)

As an example, having a Prompt and setting a filter on a Dimension query subject is an unfavoubale idea as compared to retrieving the value of the code and using it to filter the Fact table which will avoid an unneccecary join between the Fact and the Dimension .

Having MDX expressions in the columns/rows, using Slicers instead of Detail filters when using an OLAP package will result in much faster query ..

[ShwetalM]

yep..this is an extremely broad question ...but here are a few things to tune at the report level:
1. Query properties - query processing, use local cache, use for parameter info (this helps in faster prompt page display), parallel execution. Depending on your report configuration, you'll need to find the best combination to expedite report run
2. Filter definitions - depending on db platform (relational), "if-then-else" vs "case" statements, resolve filters with fields that are in the primary key or primary index
3. If possible, avoid queries that could result in full-table scans...if you have a large fact table, this could degrade performance in a big way...
4. If you are dealing with OLAP or DMR use MDX functions not relational SQL style functions as much as possible
e.g "set" function instead of "in" to filter for multiple values...

These are just a few suggestions.....really depends on how your report needs to be structured and how your metadata and database layer supports it....ofcourse how Cognos itself is configured/load-balanced,etc will have a bearing on performance...