What happens when you run a Cognos HTML report in compatible query mode on a relational data source?
Rather a lot actually. The ability to create reports in IBM Cognos that excel in performance and design, requires a good understanding of the different phases a report goes through in a report run.
The picture shows you wat happens when you run an HTML report with prompts in IBM Cognos.
- Lets start in the upleft corner at number 1. You sit behind your computer and you hit “run the report”.
- What Cognos will do, this in number 2 in the center top, is get the report definition out of the Content Store database and start evaluating the report definition. Cognos will search for all the queries in the report definition and parse them to check if no errors are thrown, second Cognos will also look for prompt parameters in the report definition. It is obvious that the longer your report definition, the more complex the report, the more queries you have and the more complex these queries are, the longer this whole process will take.
- The 3rd step is to send the prompt queries to the database for execution. Again the more complex the queries are or the more data they have to process the longer this will take.
- In step 4, back in the center top of the slide, Cognos may or may not perform additional processing on the database query resultset.
- Next Cognos will take the resulting data, render the prompt page after which the prompt page will show in your browser. This is step 5 in the left upmost corner of the slide. If you experience that steps 1 to 5 take a long time and you do not succeed in tuning them any further, then a handy trick is to split the prompt page into multiple smaller prompt pages. This usually has a positive impact on performance, as you avoid rendering the entire prompt page over and over again after each selection, and the user will perceive better performance as it are smaller waiting times with user interaction in between instead of a single longer waiting time.
- When the prompt page is rendered, you will make a number of selections to assign to the prompt parameters and you submit the prompt page, this is number 6 on the bottom right.
- When the prompt page is submitted the queries from the report definition are sent for execution to the database, number 7 on your bottom right. The resultset will be returned to Cognos for possibly additional processing and the rendered report will show in your browser.
(Click the picture for u full size view)
Remark: If you have a report with on-page prompts or you have multiple prompt pages then the sequence and number of steps will vary, but the key steps will remain the same.
Now, it is important that you understand this flow, as this flow shows you the breakdown of all the steps that take place. E.g. if your are having a 500 pages list report that is rendered in PDF, you might want to start looking at the network speed between the Cognos server and the client, as you are potentially sending a sizable chunk of data over the network. If you have a 60 query report and it takes ages to show the prompt page, then your first choice might be the query complexity. And so on.