Performance tuning is an important part of any Cognos report development project. A report that runs fast provides better user experience and therefore produces happier customers.
There are two main ways to time reports in Cognos, out of the box:
- Run the report and manually time with a stop watch, either physical or virtual
- Schedule the report to run and view the run history, comparing the report start time to the end time
Method #1 has several drawbacks. First, you have to watch the report while it runs so that you can stop the stopwatch when the results are returned. If a report takes a long time to run, which is often the case with reports we want to optimize, this is tedious. Secondly, it’s difficult to be consistent regarding when you initiate and then later stop the stopwatch. Look away from the report for a few seconds and you might miss the actual moment it returns which can lead to inaccurate and less than precise measurement.
Method #2 does provide consistent and precise measurement but is far from convenient. Development of reports within Report Studio is often an exercise in incremental change as you tweak the report and then run it quickly to determine the effectiveness of your modification. Having to schedule the report after each change slows down the development process and adds unnecessary tedium.
There has to be a better way.
Background
I was asked to give a talk on Cognos report performance tuning. I wanted to provide hands-on exercises that gave quick feedback to my students regarding the performance impact of various report design choices. At some point I realized that some modest JavaScript could be used to capture run time of a Report Studio report automatically, displaying the results on the report page with each run.
The advantages of this approach are many. With the report run time being reported automatically for each run the developer gets a natural sense of how the changes they make impact the run time of the report. Over time, they also get a better understanding of what types of changes can have the most severe impact on performance.
Imagine you’ve built a report and it has acceptable performance. You are about to release it but decide to make a last-minute change to the report to accommodate a new requirement. You run the report after making the change and the run time doubles from three seconds to six. You are made aware of this negative consequence because the timer script displays the run time automatically. Without this information, a human running the report interactively might not notice the extra three seconds of run time. Though a whopping 100% increase, the degraded performance might not be enough in real terms to be perceived. With the timer in place you can now take steps to mitigate the performance degradation before the report gets in the customer’s hands.
Introducing: The Cognos BI Timer
The Cognos BI Timer is two pieces of code to be inserted in HTML items within a Report Studio report. The first bit of code attaches a JavaScript event to the ‘Finish’ button of the prompt page. When fired, the event captures the exact date and time the report was initiated, to the millisecond. The second piece of code records the date and time when the report output page is rendered, calculates the time difference, formats the result, and displays the run time.
The Prompt Page Code
This first piece of code should be pasted in an HTML item and placed somewhere after the Finish button:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
<script> //Cognos BI Timer Prompt Page Code var cbit = {}; //Create a new namepace to avoid name collision cbit.initialize = function() { cbit.warningthresh = 30; //First threshold cbit.problemthresh = 60; //Second threshold cbit.startTimer = function() { //Store the start date/time this.runstart = new Date(); this.runtime = 0; }; cbit.stopTimer = function() { //Store the stop date/time, calculate seconds this.runstop = new Date(); this.runtime = Math.ceil((this.runstop - this.runstart)/1000); }; cbit.appendRunTime = function(id) { //Format run-time and append to element with id var runtimediv = document.getElementById(id); var newspan = document.createElement('span'); newspan.id = 'rt'; if (this.runtime < this.warningthresh) { newspan.className = 'rtok'; } else if (this.runtime < this.problemthresh) { newspan.className = 'rtwarning'; } else { newspan.className = 'rtproblem'; } var newtext = document.createTextNode(this.formatTime(this.runtime)); newspan.appendChild(newtext); runtimediv.appendChild(newspan); }; cbit.formatTime = function(seconds) { //Format the time to xh xm xs var message = ''; if (seconds < 60) { message += seconds + 's'; } else if (seconds < 3600) { message += Math.floor(seconds/60) + 'm'; message += ' ' + (seconds % 60) + 's'; } else { message += Math.floor(seconds/3600) + 'h'; message += ' ' + Math.floor((seconds % 3600)/60) + 'm'; message += ' ' + ((seconds % 3600) % 60 % 60) + 's'; } return message; }; //Add click event to finish button var buttons = document.getElementsByTagName('button'); //Get all buttons for (var i=0;i<buttons.length;i++) { if (buttons[i].id.substring(0,6) == 'finish') { //Is button a finish button? if (buttons[i].addEventListener) { //Is not IE? buttons[i].addEventListener('click',this.startTimer.bind(this)); } else { //Use IE attachEvent buttons[i].attachEvent('onclick',this.startTimer.bind(this)); } } } }; cbit.initialize(); </script> |
I’m not going to go over all of the JavaScript this time but it’s worth noting a few things:
|
6 7 |
cbit.warningthresh = 30; //First threshold cbit.problemthresh = 60; //Second threshold |
I’ve built in the ability to set two performance thresholds. This allows you to style the run-time result based on how long the report takes to run. By default, there are three time buckets: 0-29 seconds, 30-59 seconds, and 60+ seconds. These thresholds can be tweaked by changing the assignment here. When the run time output is created a class of ‘rtok’, ‘rtwarning’, or ‘rtproblem’ is applied respectively. This allows for dynamic styling of the run-time output based on which bucket the report run falls in.
|
51 52 53 54 55 56 57 58 59 60 |
var buttons = document.getElementsByTagName('button'); //Get all buttons for (var i=0;i<buttons .length;i++) { if (buttons[i].id.substring(0,6) == 'finish') { //Is button a finish button? if (buttons[i].addEventListener) { //Is not IE? buttons[i].addEventListener('click',this.startTimer.bind(this)); } else { //Use IE attachEvent buttons[i].attachEvent('onclick',this.startTimer.bind(this)); } } } |
This part of the code looks for a ‘Finish’ button using some knowledge of how Cognos creates these buttons and attaches the cbit.startTimer() function to its click event.
This portion of the code should be put in its own ‘HTML Item’ object and placed after the ‘Finish’ button on the prompt page. With Cognos’ default prompt page configuration, this would be located in the footer like this:
The Report Page Code
The second piece of code goes in an HTML item and should be placed somewhere on the report page itself:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<div id="runtime"><span id="rtlabel">Run Time: </span></div> <style> #runtime { } #rtlabel { } #rt { } #rt .rtok { } #rt .rtwarning { } #rt .rtproblem { } </style> <script> if (!cbit.runstop) { //Check to see if stop time doesn't exist cbit.stopTimer(); //Stop the timer } cbit.appendRunTime('runtime'); //Append results to div </script> |
Let’s go through this code.
|
1 |
<div id="runtime"><span id="rtlabel">Run Time: </span></div> |
This is the container that will contain our run time output. Note that the label is enclosed in its own span so it can be styled separately if desired.
|
3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<style> #runtime { } #rtlabel { } #rt { } #rt .rtok { } #rt .rtwarning { } #rt .rtproblem { } </style> |
These are all the styles available. For example, say you wanted the run time label to be orange. The #rtlabel selector would look like this:
|
6 7 8 |
#rtlabel { color: orange; } |
If you wanted to apply a style to just the run time itself, you would use the #rt selector. To make the run time appear bigger while leaving the label alone:
|
8 9 10 |
#rt { size: 150%; } |
Finally, we can style the individual time buckets separately. Remember earlier we established the default thresholds as 0-29, 30-59, and 60+ seconds. We can optionally style the run time depending on the bucket it falls in. Say we wanted a standard green, yellow, and red color scheme:
|
10 11 12 13 14 15 16 17 18 |
#rt .rtok { color: green; } #rt .rtwarning { color: yellow; } #rt .rtproblem { color: red; } |
We can apply any number of styles to distinguish run times that fall within our threshold values.
|
19 20 21 |
if (!cbit.runstop) { //Check to see if stop time doesn't exist cbit.stopTimer(); //Stop the timer } |
We check to make sure that we haven’t already stored the stop time and store the current time if not. This check is done so that we don’t overwrite the initial stop time when you navigate pages in a multi-page report.
|
23 |
cbit.appendRunTime('runtime'); //Append results to div |
Having calculated the run time we can now append it to the div we defined in line 1.
For this code it doesn’t matter where you put it. Wherever it’s located is where the run time of the report will be displayed. A common place you might put it is in the report header:
Result
When we run the report, the precise moment we click the finish button on the prompt page is recorded by the prompt page code. The report page code records the moment when the report page is rendered and the difference is displayed wherever we have placed the report page code object.
Limitations
The report run time is only calculated based on the first page rendered. If you have a multi-page HTML report, time to render pages beyond the first one will be ignored. I tried to code in accumulation of time as you page down but unfortunately due to the way Cognos renders the navigation buttons I cannot attach events to them like I could the finish button on the prompt page.
A workaround is to set the number of rows per page on the data container to a very large number, larger than the maximum number of rows returned by your report. This will force Cognos to render the report all at once and allow you to get an accurate overall run time.
A bit of reverse-engineering was employed in order to reliably locate the finish button so that the timer start event could be attached. It’s possible that in future versions of Cognos BI, Cognos could change the way that finish buttons are identified. In that case this code will no longer work. Since it’s just a development tool I don’t think this is a major problem as if it doesn’t work you can simply pull out the code. Unfortunately, the Cognos JavaScript API provided in Cognos BI versions 10.2 and later does not give full access to Cognos buttons in JavaScript. A Cognos-supported way to detect button events would be very welcome as it could avoid this potential problem.
Note: After publishing this post it was brought to my attention that I didn’t discuss caching. It may be the case that local caching is enabled in your environment. If that is the case then you might see run times drop considerably when Cognos starts retrieving results from the cache instead of from the data source. To prevent this from happening during the development phase you can turn off local caching on a per-query basis by setting the query’s ‘Use Local Cache’ to ‘No’. Of course, it’s probably advisable to set this back to the default before releasing the report.
Thanks Anthony van Muyden for bringing this oversight to my attention. I appreciate it.
Conclusion
A good Cognos developer always has report performance in mind while developing reports. Unfortunately, existing methods to quantify report performance are tedious and often imprecise. By using relatively simple JavaScript code at the beginning of the development process, the user can be monitoring performance throughout development in a consistent and painless way. The result is heightened awareness of performance considerations and increased satisfaction from report stakeholders.
Hi Scott, very informative article. But in my case, the prompts are directly appearing from database, as they are defined in procedures. Is there any way, we can calculate the report time in these kind of reports?
Hi,
There’s a workaround available. Here are the steps:
1. Create a new prompt page
2. Delete all objects except the Finish button.
3. Add the prompt page code in an HTML Item to the right of the Finish button
4. Modify the prompt page code by adding the following lines to the for loop at the end of the initialize function:
buttons[i].style.visibility = ‘hidden’;
window.setTimeout(function(button){button.click();},1,buttons[i]);
So, the entire for loop should now look like this:
When ran, finish button is hidden and a millisecond after the prompt page comes up the button is clicked, which initiates the timer and launches the report.
Let me know if you have any issues.
Scott
Hi,
This a very impressive and helpful blog. Can this script work if we don’t have a prompt page? I have a report which just report page and some prompts with default selection on it.
Thanks & Regards
The only way I could see that working is if you created a blank prompt page. You could add a line of JavaScript to automatically submit the prompt page and store the time allowing the timing code to work. Most likely this would happen so fast the user wouldn’t notice it. Let me know if you want more information on this workaround.
Another person asked this same question and I came up with a workaround. See my response above to cognosdemystifiedAditya’s comment for details.
This method does not take in consideration any caching that the server employs for reports. So the result between first run and second run can vary wildly.
You are correct. I recommend turning off caching on the query during testing. This can be done by setting the ‘Use Local Cache’ to ‘No’ during development. I’ll revise the post to mention this.
Good One!