Effective Report Automation with IBM Cognos

Published 31st July 2014

Lindsey O’Grady – Practice Lead Report & Analyse

As your business grows and you move along the evolutionary curve, the complexity increases and, what started as a simple report to a few people, can evolve into an onerous task that spirals out of control. With so much technology out there, you’re probably thinking, surely there’s an easy-to-understand solution out there where the software can do all the hard work for us and all we need to do is push a button?

That sort of wishful thinking should not be laughed out. I’ve seen a report distribution process which took 1 person 15 days every month reduced to 15 minutes with the click of the button. The words “I’ve got my life back, I might actually enjoy coming into work now” immediately sprung up! IBM Cognos software can turn your manually compiled, complex report distribution processes into an automated, simplified reporting solution and is able to cope with internal re-structuring with little or no effort.

The immediate benefits are: reduced costs, better utilisation of employees’ time, increased staff motivation & retention and more agility with changing circumstances. The report frequency is not limited to how busy your team in compiling the information, but can be as frequent as makes sense to effectively manage your business. Lastly, the process continues when someone takes a holiday! It doesn’t require handover.

Many organisations are extracting data out of the source systems via an SQL query or large data dump from basic in-built reporting from the source system and moulding the data into another form (pivots, excel formulae etc.) and sending it out manually. Others have a simple reporting tool already in place that’s supposed to make report distribution easier but either the software can’t cope with the complexity of the task or the software isn’t being used to its full potential.

IBM Cognos software is specially designed to connect directly to the data from multiple source systems, or manually stored data in spreadsheets. Data is brought together in one place into a format which is easily accessible for non-technical people to extract, analyse and report. This information can be pushed into a report in whichever format you require (PDF, Excel, HTML to name a few) and can cope with complex layouts, logos, interactivity, drop down lists, drill-down, drill-up, drill through etc. The data in the report will automatically update as new data is entered into the source systems reducing the need to manually re-extract new data and recompile into the same layout.

Best of all you can run a report once and have the results distribute into separate files, one file for each recipient’s slice of the data. It can be stored in a share drive, central web repository, sent straight to a printer, pushed out to a mobile device or automatically emailed directly out to each individual or distribution lists.

Evolution of Reporting

My experience of the ‘Traditional’ method

When I started my career as an analyst, in a university internship within a blue-chip organisation, I thought it would be the height of technology and everything would be super-efficient. But when looking under the covers I unearthed a deep entrenchment of inefficient reporting processes. My predecessor handed over a report which he compiled by fetching 10 data extracts out of reporting software and dumping into Excel. After pivoting it, we would manually type in each figure from the pivot table into a prettier looking presentation sheet. Sometimes the data hadn’t updated on time and we only discovered this half way through compiling the report when the figures looked incomplete. It took all of Monday and I also accidently mistyped one or two figures most weeks and it had to be reissued. Safe to say, the email recipients didn’t trust the data that much and I felt pretty useless!

Evolution to the ‘High-Performing’ method

A couple of months later I went on an advanced Excel course, and learnt all about Excel macros and advanced formulae including VLOOKUPs. I re-engineered the reporting process by getting the data dumps sent to me via email automatically. I pasted the data into my reporting template and Excel macros did the rest. Off I went for 10 minutes, a coffee and a pretzel, and came back to a nicely compiled report which was ready for me to email out. I thought that was the height of sophistication, until I realised that my process fell down when new regions needed adding to the reporting template or old regions were to be removed. The automated emails were only as up to date as the system was. I would have to re-run them again individually once it was up-to-date. It still took up part of Monday morning and was pretty tedious.

Step change to the ‘Best in Class’ method

My career progressed nicely after graduation and I was head-hunted for a job in Business Intelligence as a developer using IBM Cognos. “Business, Intelli-what?” I thought. Never heard of it! This job completely flipped my perception of reporting on its head. Now reporting was all about automation. Data extracts from source systems were scheduled overnight, and reports were automatically triggered to run as soon as the data was available. Sales directors were able to see the previous day’s sales late in the evening rather than waiting for analysts to distribute the figures in the morning. If one report was to go to 50 people with each person having just their own slice of the last month’s data, I set it up so that the report dynamically reflected last month’s figures without any intervention, it would compile and distribute 50 different files automatically in minutes, not days. If new regions where added or removed, the report dynamically reflected the latest set of regions and went out to the correct people automatically without changing the report layout or list of recipients. If regions were renamed, no reports would break or need changing. There was one version of the truth because all reports would come from a data model I had designed within Cognos. Any transformation of the data would be agreed in advance and I would be pre-build this data transformation into the model. This reduced the chance of people having different interpretations of compiling the same thing due to lack of knowledge sharing.

In effect, the processes would be set up once, and forever report reliable data. It would run efficiently and I could leave it behind with no maintenance or administration. It allowed me to continue with high value activities like developing new reporting capabilities rather than administration. Likewise, it allowed analysts to ditch the copious hours of spreadsheet manipulation and spend more time analysing the figures.

Summary Matrix – Evolution of Report Automation
Click here for stages of automation – Which stage are you at?

Call us for a chat about which stage you are at and how report automation can make your life easier – 01473 276159

–          How Aramar can help with your Cognos BI Solution

–          Learn more about IBM Cognos BI

–          Click here for a demo