Guest Post: On Cognos Reports Performance Improvement

One of the tasks I’m hired to do quite often is to analyse why a report (or a group of reports) is loading slowly, and how could this be improved. The good news are, when a report runs for a very long time, there is almost always something we can do to improve it. But first, we must analyse what causes the report to run long.

The first thing we want to do is to rule out the query as the culprit. That is, we want to see how long it takes to get the report data from the database. This is best done by getting someone who is well versed in SQL or MDX to write a query that will pull the relevant data from the database. Many BI developers have sufficient knowledge of SQL, but in case you don’t, get a DBA to help.

One might ask – why not just take the SQL Cognos generates, and run that against the database. The answer is that we are trying to figure out what Cognos does wrong, and that includes the query generation. We need to compare what Cognos does with a well structured query.

If your manual query takes a long time to give results, it means that the underlying database has a hard time supporting your query. There could be many reasons for that. First of all, check that your tables are properly indexed and, if necessary, partitioned. Again, developers with gaps in knowledge in terms of DB tuning will do well to consult a DBA. If you are aggregating large amount of rows, or creating many joins, maybe an aggregated table or a materialised (Indexed) view will solve the problem. If you are doing multiple aggregates based on a few million rows, perhaps a Dynamic Cube would be a good bet, or you could try building an OLAP cube to support your reporting needs (Almost any cubing solution would be better than Transformer, but if push comes to shove, Transformer could be better than nothing).

If your data source is OLAP, and a query takes long to come back, it probably means you might want to design your cube differently. The most important tip I can give you here is to avoid nesting in as much as possible – try to hierarchise the nested columns under one single hierarchy, it will also help with aggregates. A rugged hierarchy is often preferable to a full cross join between different hierarchies, even when nonempty is defined in the query.

If your manual query returns within a reasonable time, it’s time to compare it with the Cognos generated query (Obtain it from tools->Show generated SQL/MDX). Just skim through the Cognos query and see that there aren’t any major differences. If there are, it could be your framework model is problematic.  Run the Cognos generated query against the DB – how long does it take to come back? If much longer, then your model is probably at fault. The most usual culprit in cases like these are ill defined relations.

If the query Cognos generates is fine, we’re probably looking at some processing done on the result set. There are several of those, the common ones are master-detail relationships, chart generation and crosstab generation. There are ways to improve master-detail relationships over CQM (Follow this link, and also make sure that you are sectioning by a single key field), but ultimately, a DQM model would optimise master-detail relationships better than any wizardry. Crosstabs and charts over relational are rendered by creating mini-cubes on the server side, these may take some processing – again, the best cure is DQM, or creating a dimensional data source. If you are rendering many charts, the charting engine will queue some of them – not a lot to be done here other than increasing number of connections to the Graphics Service, minimising the amount of charts by using matrix rows/columns where appropriate or by loading the serially, or creating a RAVE based chart that spans multiple charts together.

These are the basics. There are obviously many cases which aren’t covered here, but that should give you the first-aid steps. If you tried all this and your report still slugs, then it is time to call the cavalry in.

 

Nimrod (Rod) Avissar is a Cognos expert, with a penchant for specialized UX solutions. Feel free to drop me a line! (LinkedIn).

 

Hiding Cognos Connection Elements

Hiding Cognos Connection Elements

By default Cognos offers a ride range of UI options. Based on the group or role you can programmatically decide who can see which UI Elements. A simple modification of the system.xml file is all that is needed to take care of this.

The Admin and Security guide, which can be found in the \webcontents\documentation\en\ug_cra.pdf, has an excellent article on how to accomplish this. You can also find the article online here.

The full list of elements that you can hide can also be found in the guide, or online here.

While those links are for 8.4, they are essentially identical for 8.4.1 and 10.1.

But what happens when you have a requirement to remove elements that don’t appear in the list? This is possible by modifying one of the template files that controls the structure of the portal. These files are saved as XSL files, and are easily modified using any text editor. I strongly recommend using a text editor with XML support, such as Notepad++.

Before I continue, it’s worth mentioning the following. Changing these files may be risky. A misplaced semicolon will prevent Cognos from loading, and IBM has a tendancy not to support installations that have modified the Cognos internals. These changes will also be overridden by any patches or upgrades.

Always make a backup of any files you modify. When trouble does occur and you need IBM’s help, simply switch the active files with the backup, and IBM will be none the wiser (and if the problem is fixed, you know where to look).
You should also maintain a change log of everything you do. Upgrades will replace the template files, and there may be differences between the versions. Instead of simply overwriting the upgraded version with your modified version, you should make all the changes again manually. Fortunately (unfortunately?) patches and version upgrades tend to be rare occurrences.

A brief explanation of the files to modify:

There are two primary XSL files which control the portal.
1. \templates\ps\logicsheets\presentation\controls\presentation.xsl
2. \templates\ps\logicsheets\presentation\main\presentation.xsl

The \controls\presentation.xsl renders, among other things, the HTML behind tabs, and the rows and columns in the Public Folders/My Folders table.

The \main\presentation.xsl renders the links to the correct style sheets, the page headers, and the individual links in the Public Folders/My Folders table.

Between the two, they control the HTML between most of the objects you see on the page.

The system.xml file that contains the UI black list can be found in \templates\ps\portal\system.xml

And now the problem. The client has decided that no user, except administrators and report authors, should be able to see the Properties or More… links for any reports or folders.

First, hiding the properties.

In the \main\presentation.xsl do a search for action_properties.gif. There should be four instances of that string. Each of these instances controls the properties for a different type of object. Series 7 object, CRN object, Job and… well, I’m not entirely sure what the fourth one is for, but I’m sure it’s very important. 5 points to whoever enlightens me.

In each of these four cases the action_properties.gif is part of a xsl:call-template reference.

This statement is calling the renderActions template with values for the onclick, icon and tooltip parameters.

In order to prevent this segment from being rendered we need to add it to the “Elements you can hide” list. The code that controls items on the list is as follows:

<out:if test="not(contains($ui_black_list, 'NAME'))">			
</out:if>

This will check the system.xml for an appearance of NAME in the ui_hide list. Should it appear, it will not render anything inside the code block except for users or groups listed in the show parameter. Knowing this, all that is needed to hide the properties is to add the black list code to all four appearances of the renderAction call.

		<out:if test="not(contains($ui_black_list, ' PROPERTIES '))">			
			<xsl:call-template name="renderAction">
				<xsl:with-param name="onclick">actions('{xtsext:javascriptencode(string($obj-name))}', '{$obj-class}', '{xtsext:javascriptencode(string($obj-path))}','properties_general.xts');</xsl:with-param>
				<xsl:with-param name="icon" select="'action_properties.gif'"/>
				<xsl:with-param name="tooltip" select="'IDS_PROPERTIES'"/>
			</xsl:call-template>
		</out:if>

In this case, I set the black list name to PROPERTIES. Now, in the system.xml simply add the following to the ui_hide param:

	<PROPERTIES show ="Administrators RSUsers"/>

When you restart, only administrators and authors will be able to see the properties for any object.

Now for the More…

The technique to hide the “More…” link is almost identical.

Simply find all occurences of IDS_ACT_MORE in the same file. In my modified file all occurences appear between lines 4640 and 4722. Thanks to Notepad++’s XML support, I can see that all of these are held inside a single group.

Instead of black listing each individual appearance of the More… link, let’s try black listing the entire group.

Now to add MORE to the system.xml black list

As before, only administrators and authors will be able to see the More… link.

The following shows how folders look to normal users:

And this is what reports look like:

It’s very important to do extensive tests to ensure this meets the client needs. You could probably also reverse the black list by getting rid of the “not()” from the code. Remember that the idea behind this article is not to simply show how to hide specific elements from the Cognos Connection. It’s to show how easily it is to shape Cognos to meet your needs. The XSL sheets are there to be modified.

Use Design Locale for Reference ID in 8.4.1

Sorry, no screenshots for this one. Blame InfoSec.

Cognos 10 has a nice little checkbox that you can set to have authors use only the design language when creating reports. Without this option any changes made to any of the non-design languages may break existing reports. Report authors will have to always use the design language when building reports.

Sadly that checkbox doesn’t exist in previous versions. You can, however, manually change the model.xml and add the same tag.

First, back up your model.xml. You don’t want to mess this up.

Place the following code between the </defaultLocale> and <namespace> tags.

 <fixIdsToDefaultLocale>true</fixIdsToDefaultLocale> 

Publish your model, create a new report, drag in a data item into a query. While the data item caption should appear as your active language, the reference should appear in the design language. Remember to close your browser completely before creating the new report, as you may inadvertently use a cached version of the model.

I have only tested this in 8.4.1, but it may work in previous versions. I don’t know and don’t have access to FM of previous versions, so I’d welcome feedback indicating which versions it works.