Cognos Report Migration

What I inherited

When I joined the project, a German insurance company was already partway through moving its KPI reports from Excel to IBM Cognos, a platform for building and distributing reports.

The plan was to show four KPIs across five organisational levels, for a total of twenty report variants. The filters and aggregation differed from one variant to another.

Users started on a central landing page and selected which variant they wanted to view within the consolidated report. Once inside a report, they were supposed to move between variants through a two-level tab menu. The upper row selected the organisational level, while the lower row selected the report type.

One variant already existed and served as the template. The other nineteen still had to be built. A custom tab component also existed, but it was buggy and its original design did not include all twenty variants. Most of the architecture had already been decided, so I had to make that setup work.

Why the report stopped scaling

I fixed the tab component, made it configurable, and used the existing template to build the missing report variants. This worked, but it left us with one enormous report. Each variant needed roughly ten queries, so the finished report contained more than two hundred of them. Load times sometimes exceeded three hours.

At the same time, the report was still changing constantly. There were bugs, small inconsistencies, and unanswered design questions. Each fix or decision had to be carried across all twenty variants.

The report had become so large that even routine actions in the Cognos interface were sluggish. Making changes by hand meant waiting on the interface and then repeating the same work twenty times.

Working around Cognos

Cognos stores report definitions as XML and lets you export them. That gave me a way around the manual editor, so I wrote a Python toolkit that could extract, change, or delete specific parts of the XML.

Once that worked, I could apply the same change across all report variants instead of clicking through them one by one. Changes that had taken hours now took minutes, and the toolkit reduced the risk of inconsistent manual changes.

Fixing the runtime

The toolkit made the reports manageable to change. It did nothing for their load times, though, so that was what I tackled next.

I used database and Cognos logs to find bottlenecks in the generated SQL. I merged similar queries, filtered large tables earlier, and rewrote inefficient joins. That brought the load time down from sometimes more than three hours to about twenty minutes.

Twenty minutes was still too long, so I changed the structure of the report itself. I used the Python toolkit to split it into twenty smaller reports, then changed the landing page to open the corresponding standalone report for the user’s selection. I also adapted the tab component so that switching tabs opened the corresponding report. This halved the load time again, to about ten minutes.

Result

By the end, the system consisted of twenty standalone reports covering all four KPIs and five organisational levels. The tab interface still let users move between them. They loaded in about ten minutes. That was still slow, but far better than a worst case of more than three hours.

The Python toolkit also made later changes easier, because they no longer had to be repeated manually across every report.