Below is the list of solutions for Cognos report performance tuning. Each topic will be enabled as soon as the corresponded article is published.
From Cognos developer point of view, there are 4 tiers where we can enhance performance. They are report requirement tier, database tier, framework manager tier and report tier.
2 Database tier - Perform general database performance tuning
Obviously, report performance can be improved by tuning in your database. This document is intended to guide us to capture SQL statement from report, then further identify which data container has performance problem.
3 Database tier - Separate summary table from detail table – “vertical partition”
Summary tables are used to reduce number of records by either aggregating measures for duplicated transactions or reducing number of dimensions.
Breaking a single fact table into multiple small fact tables with their corresponded measures could dramatically improve performance, All these small fact tables shares same conformed dimensions. This approach does not request to change report specification.
5 Database tier – Create summary table for cumulative relative measure
There are many cases that you need relative measures, such as YTD, 13 Rolling Months, or Prior YTD. Summary tables can be built to store such cumulative measures to improve performance.
6 Framework tier - Maximize the benefit of “stitched query” with conformed dimensions
Stitched query is applied for multiple facts with conformed dimensions in Cognos query engine. This document provides a way to a single logical fact table at business tier with multiple physical fact tables behind.
Turn relative time as measure yields much better performance and therefore scalable. This approach is from IBM best practice (http://www.ibm.com/developerworks/data/library/cognos/page90.html
8 Framework tier - Set processing property to Database Only in framework manager
Local process is one of major problem with report performance. This document is intended to identify what causes local processing and how to avoid it.
9 Framework tier - Integrate prompt Marco in framework manager query.
In case when the fact query is complicated query, it is possible to embed all parameters directly into query with prompt macro. This way provides an opportunity to optimize query and therefore result in a better performance.
Instead of dynamically determining date range in all reports, it is the best solution to use query based parameter maps to pre-calculate all date related parameters. This approach can dramatically improve performance as the query sent to databased is with “static” value as where clause.
11 Report tier - Use shared sub query efficiently
Overuse subquery could impair your performance. There is balance between reuse the same subquery and separated query. This document explores the limit of using sub query.
12 Report tier - Avoid using master detail relationship if possible
Sometimes, it is possible to join subquery to the main query, even when main query and subquery data from different logic. Combined query with join will reduce the number of query significantly sent to database.
In case when detail report is based on query from another base query, it is good idea to make filter data happened at the base query level. this approach will filtered out data from the basic one rather than getting all data and then filter it.
This approach is from Cognos best practice, (http://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html) to improve master detail report performance. The whole idea is to send a single query to database instead of sending 1 (master query) + number of master query records (detail query).
15 Report tier - Enhance prompt page performance by applying prompt cache, tabbed prompt page, and prompt reconciliation
There are three methods to enhance prompt performance by 1) cache prompt; 2) use tabbed page when the prompt page has too many items; and 3) apply different ways to reduce process of prompt reconciliation.
16 Report tier – Avoid query calculation to be used for baseline and similar function in chart
when using query calculation, Cognos engine generates each query for each query item. Meaning that 10 queries will be sent to database if 5 baselines and 5 markers are used. This document is intended to provide a way to use layout calculation.
17 Report tier – Cache Cognos parameters
Cognos report studio does not provide a way to initialize parameter. This document provides a few ways to initialize the parameters. These parameters can be directly used in report rather than building logic into individual query for the report.
The idea is to save date expressions in parameters, then use #Prompt()# in query items or filters. When change date logic, you will change it in single place. At the same time, you can get good performance as well, because the filters are built into query.The idea is to save date expressions in parameters, then use prompt token in query items or filters.
This article introduces a solution that union query is used to achieve the same functionality of "stitch query” at report level. It will achieve a very good performance, as data only needs to stitch at summary level.
20 Report tier – Transform nested Crosstab to single-edge Crosstab with much better performance (relational only)
When the left node is the lowest granularity, then you can turn nested Crosstab to single-edge Crosstab with much better performance, for relational model only
No comments:
Post a Comment