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).
11 thoughts on “Guest Post: On Cognos Reports Performance Improvement”
Wow! Very well put..
Excellent tips Rod.
Some other things to look out for:
Make certain the query Cognos generates is doing the aggregation. Generally, databases will do aggregation far quicker than Cognos can. If aggregation is happening in Cognos, you probably are using a function that Cognos supports but the database does not, so look for alternatives that work on the database.
Check on how much data is being returned. Charts and Active Reports can obscure the fact that you may be retrieving millions of rows.
See how many images and charts you are displaying. Typically, these objects take longer to load than text/html.
Thanks Bob! These are great comments.
Love your (Sadly inactive) blog!
I am sorry, I have few questions to get clarified in IBM cognos…. I am not understanding where I need to post my questions…. Will you please help me out? Any LINK?
This is a very nice, systematic approach to performance tuning in which I think people will find great value. Thanks for posting it!
Out of curiosity, what’s your beef with Transformer?
Thanks for the complement.
I don’t have a “beef” with Tranformer as such, it makes it sound so…personal. Transformer is a sub-par OLAP solution which doesn’t allow for many OLAP features, handles security poorly and is restricted severely in terms of data size. It was a wonderful solution some 7 years ago – but it didn’t do much since.
OK, if not Transformer, what do you suggest instead?
If not Transformer that what? What cube builder do you recommend?
David, there are plenty of good cubing solutions. Microsoft’s Analysis Services is very good (And with tabular mode allows for very quick modelling) and relatively cheap if you are already in a Microsoft environment. Essbase is a robust – albeit difficult to develop – solution. Cognos Dynamic Cubes are growing to become very good. For very big data volumes, ActivePivot by QuartetFS is an interesting solution.
I’m also going to chime in with TM1. Depending on the volume of data and what you want to do, different cubing technologies have different strengths. TM1 doesn’t work well against TBs of data, but is great when it comes to scenarioing.
I’ll respectfully disagree. I don’t look at TM1 as a reporting database or a cube, but rather as the engine of a planning software. Using TM1 for the sake of cubes is like using Cognos but only pulling manual SQLs.