ayrus

 Profile Answers by ayrus 

• Apr 15th, 2010

 

To analyse the performance of reports, first the place where we lack is to be identified. Follow the order below,

1 - Database :

Check the configurations of the database. Check whether the DB parameters set is appropriate and good enough for the existing/ continously increasing data size in the database.

Ensure all indexes are present. Run a simple query joining the tables and columns being often used in the report. Check the time taken and view the auto trace plan.

During the peak hours when the reports will be accessed, get the AWR reports from the DB team and analyse the parameters.

2 - Model :

Check the governor settings set. Change it as per the scenario.

Try to have the query subject from the database without any modification. Any modifcation in the creation of DB query subject will restrict the Cognos from generating minimized SQL for those dimensions.

Use Model Query subjects for getting from different query subjects / computations wherever required.

Ensure that all joins and cardinalities are proper for table relationships. Check the joins by viewing the query for a sample data using 'Test' option.

Use dimensional modelling if and only if it is required for reports. Else use relational modelling. If dimensional is required, buil it on top of relational model.

3 - Report Design (Cognos 8) :

Check the main query generated (Generated SQL/MDX option) for report. The query should not be very lengthy and more or less to be similar with the normal DB query.

Order the data items in the query based on the grouping required. Check the query generated simultaneously to note the difference. Remove the unwanted data items and change the aggregation funtions of the data items with constant values to 'None'.

Check the normal properties of the Query like 'Processing' etc and set it based on the implementation scenario.

If Singleton or Repeater is used for display purposes , which again points to the main query, confirm that the main query is not running twice.

Check the query generated in report studio and the query that is hitting the database by taking the query from v$session.

Thanks,
Ayrus

itsanu.rules

 Profile Answers by itsanu.rules 

• Apr 17th, 2010

 

Anayse the SQL generated by cognos and check its query plan.
Check the amount of data involved.
Check whether the tables involved are analysed recently.
Check if any indexes can be made on the columns involved in the joins, that can hugely improve the performance of the report.
Else check if the query of the report can be split into a union of two queries which would run in parallel.

Rankapur

 Profile Answers by Rankapur 

• Apr 17th, 2010

 

Data not in correct NF
Loops in retriving data
Too many parameters
Pulling data from muliple sources
There can be many reasons otherwise the question needs to be more specific.

Kind Regards,
Randeep Kapur

bonniewade1

 Profile Answers by bonniewade1 

• Jan 14th, 2011

 

1) Use the native SQL from the Cognos report to do an explain plan
2) Check the sql of the prompt page queries and use static values for prompts whenever possible.
3) Validate the joins in Framework manager.
4) Limit the number of rows displayed in an HTML report.
5) Add filters to limit the amount of data returned.

Chandra Kiran

• Aug 17th, 2011

 

Agree with all of the above answers.

In addition to those,
1. The report performance also can be impacted based on the Calculated columns and the aggregations defined at the Report level.

2. It even impacted by the data it pulls with the Master-Detail relationships. For example, I want to see the Trend chart of Sales by City wise. I created a Chart with Master-Detail relationship which gives me Trend-charts for around 200 Cities across the world. The query may execute in little time at database but the Cognos iterates the query for 200 times which takes huge amount of time for Report generation.

Thanks,
CK :)

Iamrr

• Oct 18th, 2011

 

It depends also on Query that has been written to build the package, Analyse your query try to Optimise it and see the difference.