Agile ETL….it’s never too late to save a Data Migration Project
Posted by Arbutus Software on Wed, Mar 03, 2010 @ 12:41 PM
"We read to know we're not alone" - C.S. Lewis
As referenced in our last blog entry (67% of all data migration projects are not delivered on time). Last year we worked with a large IT out-sourcing firm that was well on its way to contributing to this unfortunate statistic. They were the lead firm involved in a project for a large government department to capture and convert financial collection data from selected source applications. The requirements were to consolidate and transform them for loading into the new SAP database to create a single collection process. The source data and program logic was spread over multiple applications and government agencies, primarily on IBM mainframes with IMS and DB2 databases. The scope of the project required the data migration work to be split up into two stages. Each stage involved different government departments but, the same types of Mainframe data sources. The conversion included typical data quality and transformation tasks required to move data meaningfully across applications.
The Problem
Typical of many data migration approaches, multiple conversion programs were written in PL1 and COBOL to convert the isolated legacy data into the format required by the SAP data loaders. The initial development of the conversion programs took weeks to months to research and code but were completed on schedule.
The delays started later in the stage one process with the discovery of many data quality issues and the unexpected and additional programming requirements to transform the data. This resulted in significant delays which caused most of the entire project resources and budget being consumed by the initial stage of the conversion while the equally important second stage had yet to be started. The success of the whole project was contingent on completing both stages.
The project team struggled in three areas:
- limited abilities to query and understand the data in the source legacy system(s)
- difficulties separating out the specifically required data from the legacy systems
- the need to write in PL1 and Cobol to convert and cleanse the isolated legacy data into the format required by the SAP data loaders
The Solution
Under the recommendation of one of the client’s sub-contractors working on the conversion effort, the client agreed to look at the use of agile technologies to speed up data conversion activities. This met with some skepticism because there history was with their known methodologies, especially for such a large data conversion project. As well, since the initial stage of the conversion was already well under way, any further interruption caused by changing the approach at such a late date ran the risk of introducing further delays.
The Results
Within a one month period from start to finish, the project team was able to complete the initial conversion stage and complete the critical second stage conversion process. This included acquiring and installing the agile tool, performing data analysis, cleansing and transformation. They were also able to complete all the writing and refining the automated procedures to perform these tasks and export the final data into a tab delimited format to meet the SAP application data loading requirements. In the end, the project was completed within the original timeframe and at a fraction of the cost that was budgeted.
The client acknowledged at the end of the project the main reason they were able to successfully save the project was the decision to adopt the agile technology mid-stream. The technology gave them the power and flexibility to streamline and simplify the conversion process and overcome the issues that were causing the delays.