The automatic IBM Cognos BI

I've been working with IBM Cognos BI (Business Intelligence) system for some years. Our application area for the BI system differs from typical application areas: we use it to provide public internet reporting and OLAP analysis tools. These are based on data extracted from our registers and other statistical data sources.

One of the biggest challenges with IBM Cognos BI has been making it as automatic as possible. You can define and update Power Play cubes by using Transformer. We felt that doing this manually is a waste of time and so we wanted to make this automatic. You can desing and maintain your reports with Report Studio. Instead of working for numerous hours creating reports with Report Studio we wanted to make this automatic too. And we made it!

First step for our automatic BI system was defining an organization wide data format called Hydra. To be able to process all our data to cubes and reports automatically the data and its metadata must be put into a defined Hydra data format. The Hydra data format is quite simple but extendable. It can be stored in a database or in a zip file consisting of serveral CSV files. The format includes a simple XML-based definition language for defining reports. The organization wide Hydra format works like a distributed data warehouse: people gather data from various sources without coordination and use the tools they like to process the data. But at the end there is a common result: all our data in the Hydra format.

The data and metadata in Hydra format is fed into a in-house made software system called Amor (automatic reporting). Amor is controlled and commanded via email commands making working with it completely independent from time and location. When Amor receives a command to process some Hydra data, it

1. extracts the data
2. verifies the correctness of the data (and provides feedback)
3. creates Cognos Transformer model (MDL) from the data
4. processes the data into a format understood by Cognos Transformer
5. runs Cognos Transformer to generate Power Play cubes
6. ships the cubes to server and publishes them into Cognos BI (using Cognos BI Software Develoment Kit)
7. reads the report definitions and based on them generates Report Studio reports
8. ships the reports to server and publishes them into Cognos BI (using Cognos BI Software Develoment Kit)

So what we now have is a system that automates our BI. All we need is to put our data into Hydra format and write simple report definitions. The rest is automatic. When Amor is done it returns a log that contains possible data quality warnings and of course URLs to open the cubes in Power Play Studio and URLs to open the reports in Viewer. It also manages the development, testing and production versions of cubes and reports so that these are properly isolated.

The business benefits of making IBM Cognos BI automatic with in-house developed Hydra and Amor are

• defining and updating cubes no longer needs manual work
• defining and updating reports requires a minimal amount of manual work compared to working with Report Studio
• creating and updating cubes and reports is now complelety time and location independent
• it is relatively easy to add new channels of data distribution: in addition or instead of cubes and reprots we could for example provide D3.js visualizations or an API for public data access (often called “open data”)
• it is possible to replace Cognos BI with other BI system or other means for OLAP and reporting by changing only the last modules of our Amor system (we are expecting that there will never be a need to rebuild our reporting system from scratch ever again)

The Amor software is written in Perl and Java programming languages and we are looking forward in releasing the source code as an open source. The system has been in production for a while now and we are currenlty working on making it known in our organization. We are also considering an integration to our CMS in order to make our website and Amor generated content appear as a single service.