Case Study 2 - Simplification

Program/Project overview:

Conversion of PL-SQL based ETL code to Informatica ETL tool and building a dashboard to help in better monitoring and reduce complexity

Size in FTE / $$:

18 FTEs (5 onsite, 13 offshore)

Scope:

About 472 feeds from various transaction systems including mainframes, Oracle ERP, Siebel, local data marts of sub businesses to be converted from PL-SQL code to Informatica using a Multi Generation Project Plan (MGPP)

Challenges:

· Hybrid approach (converting in phases) created problems in interlinked systems

· Absence of business rules documentation - difficulty in developing correct Informatica mappings

· Scripts to load data were written long back with no documentation

· Testing – users did not want to re test new system

· Small development team – internally funded IT project

· Team expertise in tool

Our Solutions/Value Propositions:

The EIS team member led the project as the COE Leader of the BI COE. Given the challenges and the newness of the technology, a phased wise approach was proposed with a pilot project

Execution Strategies:

. A relatively new BI application was chosen with medium complexity and low business impact was decided as the pilot project. A team was formed with PL-SQL & Informatica developers and Subject Matter Experts (SMEs). The existing code was reverse engineered to develop business specification.

The Informatica team used the business specifications to forward engineer the code. Since functional users would not test it, a creative test strategy was formed. A mirror database was created and loads were executed with the same source data – both with PL-SQL code and Informatica code. The results were correlated to check for data inaccuracies and code was suitably modified. This was done until the entire application was completed and put into production.

The learning and approach were used for the rest of the applications and the organization moved into a single ETL tool at the end of the project. To the users, the effort was seamless and they were quite impressed by the background work. An important positive side effect was the loads were done faster using parallel load techniques as well better job scheduling.

Due diligence was also taken care to ensure that there was a constant review of error logs and this make sure that the improvements did not fall through the cracks.

An alerting mechanism was also set up to ensure that mails were sent to the monitoring team, in case of errors, to ensure faster follow up.

Benefits:

· Single ETL tool for the organization – leading to simplification

· Improved productivity of the monitoring team

· Faster response to rejects because of alerting mechanism