ABSTRACT (by Federico Levis ) Category: performance
Performance Tip: Improve Cognos Performance using 1 DataSource insted of 2
We will describe a real case of an international telecommunication customer, who had critical Cognos Report performance issue. After our modification to the DataSource architecure, the Report time excution has reduced from 5-10 minutes to less than 30 seconds:
1. Problem Abstract
International Telecommunication Customer with:
Cognos server HW: Sparc M 8 core 64 G RAM
Cognos 10.2.1 Reports built with ReportStudio and QueryStudio
Oracle 11 DB: fact table with about 100 Million Records. 2 DB Schemas, one for Fact tables, the other for configuration tables
Cognos Metadada Interface: 2 DataSources (one per each DB schema). Each Report Query uses both DataSources
Performance Problems:
Very low performance: report execution time > 10 minutes
Critical resource usage at Cognos Server (Memory, Disk)
Sometimes, in case of burst of OnDemand requests, all available Cognos Processes become busy end all the Cognos Report requests are enqueed instead of being executed
Some batch report scheduled each day cannot be finished in one day: after some days it takes to Cognos hangup or crash.
Hundreds of Reports are involved in these issues
2. Problem Analysis
We enabled Cognos Query trace, that underlined that performance issue was caused by the Cognos Architecture with 2 DataSources in the same query; in fact Cognos uses 2 different algorithms depending on the number of DataSources involved in the relative QuerySubject:
3.“Not intrusive” Solution: DB synonyms to use only 1 DataSource
Original architecture with the performance Problem: QuerySubject involving the 2 different DataSources FACT_DataSource and CONF_DataSource
We implemented following “not intrusive” solution:
Oracle: Create synonyms for CONF object in FACT_Schema, “pointing” to CONF_schema. So now all DB objects used by Cognos are visible in FACT_SChema
Cognos FM model: Remove CONF_DataSource. Then (using a notepad editor) replace “CONF_DataSource” with “FACT_DataSource”
Our Solution: only the DataSource FACT_DataSource is used
NOTE:
No modification has been done to the Queries structure of to FM Objects Relation (Joins,..).
No modification to Report Studio or QueryStudio.
With very simple modification centralized at Oracle and FM, we obtained that all the hundreds of Reports has changed their query behavior and their performances has been drastically increased.
4. Performance Improvement Results
Follow all the performence improvement measured in the custumer Cognos Server:
Cognos Total Report Reduction: from 5-10 minutes to less than 30 sec
Both DB and Cognos Elaboration Time have been drastically reduced with 1 DataSource Solution:
Oracle DB resource usage is reduced
Cognos Resource usage (CPU, RAM, Disk) is drastically reduced