The objective of this article is to share an interesting activity on migrating IBM Cognos (Cognos) reports to Oracle Business Intelligence Enterprise Edition (OBIEE) for a leading financial service client (Client). The client was using Cognos extensively for their financial management reporting areas. As they were not getting the intended results out of Cognos, they decided to move onto OBIEE not only to get a holistic view of their financial reporting pack at enterprise-level but also to align with their corporate reporting strategy.
Just to let you know, these two BI tools (Cognos and OBIEE) uses a different frame-work altogether. So, achieving a 100% automation on migration is not possible. However, with the help of Java APIs and Unique Processes, many of the manual efforts have been automated resulting in almost a 35% saving in terms of cost, efforts and timelines.
Some of the other things to be noted:
- When migrating from one BI-Application to another, the objects in the BI-Application need to be retired and manually re-created.
- Conversion process requires identifying each object, determining its intended functionality, recreating the object and finally to test that it works appropriately.
Some of the high-level migration steps followed:
Now, let’s talk in detail about the migration process which has been divided into three stages – (1) Identification (2) Evaluation (3) Implementation.
This particular step played a crucial role in eliminating un-wanted/duplicate reports and migrate only the reports that are actually being used by Business Users, from Cognos Connection Catalog.
Reviewed the list of identified reports for enabling Security both at Object and Data levels and categorise them into business-area wise.
Based on the list of migrated reports, an evaluation strategy prepared with the help of Business Users to determine:
- Creation of reports and their equivalent meta-data in OBIEE application
- Consolidation and Enhancement
3.1 Manual Process
High-level manual steps for migration:
- Data-lineage document for the final-list of reports
- Gathered report properties like sql/mdx query information, ad-hoc query or frame-work model or cube details
- Report views information and their filters
- Removed duplicates and categorised the reports into different complexity buckets – Simple, Medium and High
3.2 Automation Process
High-level automated steps for migration:
- All of the above manual steps, takes considerable amount of time and effort. The number certainly goes up if the volume of reports to be migrated significantly increases.
- There is more manual activity involved for report assessment for areas like SQL/MDX Query, Prompts, Filters, Aggregations, etc.
As you may be aware, Cognos uses Java programming interfaces for managing its meta-data. A custom Java API has been developed to:
- Parse through the reports in Cognos application
- Process every report and extract all relevant metadata including native SQL and objects information like whether the report is based on Frame work or Model or Package or Cube or on Ad-hoc SQL.
- Assign report complexity based on metadata attributes and its values from program cache
- Create folders like – Simple, Medium and High programmatically based on the complexity
- Write all metadata information into a spreadsheet by adding multiple tabs for – Layout, Prompts, Query, Filter, Report Variables, Report Drills, Charts, Crosstab, etc.
With the help of the above automated steps, a good amount of information on reports has been gathered which not only helped for a realistic estimation but also to migrate the reports to OBIEE in a structure manner.
Another task was to identify a relational DW model requirements by analysing different objects based on the below diagram:
The most challenging task was to migrate Cognos Cubes to relational dimensional models in Oracle DW environment. To support roll-ups and high-performance, a number of aggregates and hierarchies have been built in OBIEE RPD.
This section talks about implementation which has been carried-out in three phases:
- Datawarehouse Customisation and ETL Development
- OBIEE RPD Design and Development
- OBIEE Report and Dashboards Design and Development
4.1 Datawarehouse Customisation and ETL Development
Based on the evaluation processed in (2) above, the immediate task was to develop a relational model requirements using ETL tools and database objects with features like partitions, etc.
4.2 OBIEE RPD Development
Based on the design and development of Oracle Datawarehouse, OBIEE RPD has been developed to support:
- Newly created and converted objects
- New connection pools
- Aliases to physical tables
- Aggregations and hierarchies Setups
- Subject Area Setup
- Security implementation
To summarise, the entire activity has been completed in 24 weeks to migrate 200 Cognos reports along with 4 cubes and with resources as follows:
- 2 OBIEE resource for 24 weeks
- 1 Java resource for 10 weeks
- 1 ETL/DBA for 10 weeks
Migrating from Cognos to Oracle Business Intelligence resulted in a multitude of benefits for a customer including cost savings and increased business value.
To realize these benefits at the lowest possible cost, it is extremely important to follow a best and proven migration method and technical best practices developed through experience.
This blog outlined a proven-process that used automation techniques along with manual processes as well as a case-study of customer that have achieved the success after migration from Cognos to OBIEE.