Improve Report Performance
This article contains information on how report design affects performance and suggestions that may improve your report performance. It includes recommendations from IBM: https://www.ibm.com/support/pages/how-improve-report-performance
If you would like Changepoint to make report changes for you, please open a Report Request, which is fee based or available for customers with Reporting Assurance.
Local processing
When a report is run, Cognos looks at the requirements of the data container in the report, the query in the report that the data container uses and the metadata in the report package. From that it generates a query in Cognos SQL. That query is then converted to the native SQL of the data source and sent to the database. Sometimes there are functions used in a report that cannot be converted to native SQL and sent to the database. In that case the native SQL is formed without those elements, and after Cognos receives the results it applies the functions that could not be sent down.
This has the potential of creating large performance issues. Say for example the function that can’t be sent to the database is inside a filter on a query. The native SQL then will not have that filter. It could result in a much larger set of rows being returned than needed, and Cognos will have to consume much time and system resources to process this large result set down to what is actually needed for the report.
An easy way to test if this is affecting a particular report is to set the Processing property on every query in the report to Database Only, then run the report. If the query requires local processing this change will result in the error:
UDA-SQL-0458 PREPARE failed because the query requires local processing of the data
Along with that error will be additional messages that will detail what function in the report requires local processing. Usually it is possible to change the report expression in a way that will allow it to be sent to avoid local processing.
How to use local cache:
1- Under the Navigation Menu, open the Queries folder:
2- Select the Query where you want to utilize the local cache:
3- In the properties panel, change ‘Use local cache’ to Yes:
Output format
The process Cognos follows in generating report output depends in part on the output format being used. With an HTML report Cognos will fetch the data required to display the first page of the report in the browser window. When the user clicks on Page Down it will query more data and generate the next page. With file based formats like PDF and Excel Cognos must fetch all the data and build the entire output file before anything can be presented to the user. In some case this creates the appearance that the report runs much faster when it is run to HTML.
To get a better idea of the time required for a report when run to HTML, run the report to HTML in the background and save the output to the content store, then check the run history to establish how long it takes. There will likely still be a difference between running the same report to HTML and to other formats. If the report is displaying a large result set the difference could be significant. This is due in part to inherent differences between the different formats.
How to change output format:
There are various ways that a user can run a report in a specific format.
From the Reports tab you can click the Run As drop-down arrow next to the report name and then you are presented with the available formats for you to select from:
Option 1: Run As from PPM (Reports tab)
The other option you have from the Reports tab is to click on the Actions arrow and select ‘Run Report With Options’.
Option 2: Run Report With Options (Reports tab)
In the pop-up window, you have the options of when you’d like to send it (now or later) and more importantly you can select the format that you’d like it to run in.
From Analytics, you will have similar options to the ones that are available in the PPM UI.
While viewing your reports in the Team Content folder, hover your mouse over the report you’d like to run and if you click the three dots to the right of the report name you’ll have the option to ‘Run As’. If you select this option you’ll be presented a page that allows you to select the format you’d like to run the report in (along with other run options).
Option 3: Run As from Analytics
If you’ve already run a report and are viewing it in Analytics, you have the option to run the report again but in a different format. Once the report has run and you’re viewing it, you can click the drop-down arrow next to the ‘Play’ (Run) button and select a different format that you’d like to use.
Option 4: Rerun report in a different format (from Analytics)
Outer joins
Outer joins and cross products can arise either from the model or from the report design. In either case then tend to cause poor performance and should be avoided.
Crosstabs with nested rows and charts
When report contains a crosstab or chart, Cognos will get the data from the query then build a cube in memory to use for the data container. If the amount of data being presented is large, or if there are many nested items in the data container, the size and complexity of the cube increases and in some cases can affect performance. It may work better to use drill up/down or drill through to move from higher level data to more focused details on a smaller segment.
Concurrent Query Execution
Using concurrent query execution might speed up a report, but It is also possible that it could slow it down. There is no way to tell in advance what the result of using concurrent query will be in a specific case. You would have to test it both ways to find out if it will be worth it or not.
One reason for this is that using Concurrent Query Execution places a higher demand on the system resource both on the Cognos server and the database server. If the demand it creates is greater than the capacity of either server, the end result is likely to harm performance rather than help it.
Even when there are sufficient system resources, using concurrent query mode does not mean every query will run in parallel. There are a number of situation where queries must be run sequentially no matter what. For example when you have a master-detail relationship, the master query must be run before the detail query. It is not possible to run them in parallel because the results of the master query are needed to form the SQL for the detail query.
When using concurrent query mode, Cognos will parse through the report spec at runtime to determine which (if any) of the queries flagged for concurrent query can be run concurrently and this adds slightly to the run time of the report. If enough queries can be run concurrently then that can offset the time taken by this step.
Master-Detail Optimization (DQM only)
In CQM, when there is a master-detail relationship, Cognos will run the detail query for each row returned by the master query. This can result in the detail query being run a large number of time.
In DQM there is a governor in FM named ‘(DQM) Master-Detail Optimization’. When this governor is set to ‘Cache Relational Detail Query’ then the DQM engine is able to process master-detail relationship more efficiently than they can be in CQM.
Parameter Info
When a report is run, Cognos will first parse the entire report spec to determine if there are required parameters that do not have a prompt created for them on a prompt page. If it finds any, Cognos will generate a prompt page for them and present that to the user first.
If the report spec is large and complex, it may take Cognos a few seconds to parse through it, causing a delay before anything is displayed to the user. If you set the ‘Use parameter info’ property on a report query to No, then that query will not be parsed. If you set all queries like that, Cognos will not be delayed from presenting the first prompt page in the report. The risk is that if there is a required parameter not covered on the prompt pages it will cause the report to fail rather than present a Cognos generated prompt page.
Changepoint Solutions
Report scheduling
Setting up a report schedule will automate when reports are run ahead of time.
How to create a report schedule:
1- From the Daptiv Reports global tab, select Report Schedule from the report you’d like to create a schedule for.
2- Enter the required fields
3- Enable Schedule and Save
Timesheet Reporting
When creating timesheet reports, use the Timesheet.TotalMinutesWorked field instead of aggregating using a rollup on the fields [Relational View].[Work Entries].[Hours Worked] or [Relational Field].[Work Entries].[Minutes Worked] to report on Hours Worked or Minutes Worked.
This will improve the performance of your query and report run time.
Project Name in prompts and joins
Use WorkspaceID (Projects > IDs bucket) instead of Project Name in the project prompt, as this may dramatically increase run time of your report. This is because with WorkspaceID, Cognos does not need to run any information about the uniqueness of the value like with the Project Name field.
Under the Value Prompt properties you can update the 'Use Value' to WorkspaceID (you'll need to add it to your prompt query if you don't have it already) and 'Display Value to Project Name to have the report keep the same prompt display. Lastly updating in your report queries the filter [Project Name] in (?YourParameterName?) to [WorkspaceID] in (?YourParameterName?) will ensure the filter works properly with the updated changes.
Similarly, when you need to join queries on projects, using the WorkspaceID instead of the project name will increase performances.