Need for Speed: Automated ETL Tool Migration
The key to a successful organization lies in the fact how organized is their data management service. Many Data-driven organizations today rely on their decision support systems and data warehouses to provide real-time access and analysis of business critical information, in order to provide the most comprehensive data management services.
They utilize multiple data integration processes, SQL based or ETL applications -that require specific support resources and pocket friendly budgeted costs. They typically invest in COTS ETL software to address this need. However, these investments become prohibitive very quickly, due to high (ongoing and increasing) licensing cost and those highly skilled resources required for development, support and maintenance of the software.
Migrating from one ETL tool to another presents significant challenges to an IT organization. How the team plans for and approaches that could make this migration process a successful outcome.
So many questions arise while this migration process is on, like:
- What elements define the best migration methodology?
- Can any aspects of the migration process be automated?
- Are there ways to increase efficiency by grouping conversion tasks?
Often seen , organizations typically rely on 100% manual conversion that consumes significant time and labor costs when migrating from their legacy ETL tool to another new platform of choice ETL tool. It could be an open source tool or from one COTS solution to another.
The ETL Conversion Challenge:
If you are planning to migrate your enterprise from one ETL platform to another, there are several questions that become essential to ask yourself.
Your migration is one of those questions that is essential to any large scale program effort that needs to be assured to be error free. These questions should be considered essential that will not only help in avoiding the common pitfalls in execution but also define your migration strategy.
Converting ETL Jobs between ETL platforms is neither quick nor easy. Few challenges that are faced and make this job a little cumbersome are:
- ETL jobs built using a particular ETL tool cannot be easily ported/migrated to another ETL tool.
- Organizations embarking on an ETL conversion project will require an ETL expert for both tools (the legacy ETL tool and the tool to which the jobs are being migrated) to analyze the legacy ETL tool jobs, document the “job mappings” using Microsoft Excel templates
- Bring in new ETL developers to create the ETL job for the target ETL tool.
The typical conversion project using a resource-driven approach looks something like this:
- Bring in legacy ETL developers for analysis and documentation of legacy ETL mappings.
- Document the ETL jobs using MS Excel templates.
- Identify opportunities for improvement.
- Build phased conversion plan
- Bring in new ETL developers and begin “rewriting” the ETL Jobs
Questions need to be considered:
There are several important reasons to migrate ETL tools, among these are:
- The proliferation of different tools throughout the organization has led to multiple licenses and exploding costs for IT.
- Merging organizations or acquisitions bring multiple data warehouses and ETL tools. Most M&A strategies call for IT vendor consolidation. As ETL tools reach a level of functional parity, it makes sense for IT organizations to select an ETL vendor of choice.
- When you feel the need to re-evaluate the existing tool sets to determine the impact it creates on departmental budget and productivity. The reason for doing this could be when you find the existing vendor’s licensing strategy and associated features less adequate over the course of time.
- Migrating your existing ETL platform manually to another is like running a marathon. In short term, renewing the license agreement for another year with the existing vendor will be much effective that the cost incurred on migrating the existing tool to another when converting those ETL jobs manually.
- Each ETL tool requires specialized knowledge—and that can require specialized and expensive IT skills. By migrating to fewer ETL platforms, the organization can consolidate staff requirements.
- In some cases the ETL tool you invested in 10 years ago is no longer marketed or it has been acquired and then acquired again and is no longer supported by the new, new mega-software vendor
- Licensing costs are not the only feature that needs to be considered while you pick a new ETL platform but
- The Developer learning curve on new platform.
- Importance of highly essential features like Data Lineage, Data Quality, Data profiling, Source control, Scheduling, connectors to Big Data platforms etc.
- Factors such as from an administration standpoint, vendor support, ease of maintainability and ease of administration.
- The ability of the tool to scale up in terms of supporting the increasing demands in quantum of processing without any performance degrades.
- By migrating to one ETL platform of choice, IT departments can solve the too many tools, too much work, too little time dilemma.
Last but not the least, a good debate with your architects on Open Source vs. Proprietary would also be handy.
2.Derive highest Value
Very often, licensing agreement renewals that run till the end of calendar year coincides with the reporting and real time information needs of business. The business folks cannot be dependent on their predictive analysis and risk assessment of sales or marketing strategies, if the technology and operation people cannot guarantee the up-time of the data warehouse and the relevance of its data.
Co-operation between technical and business teams is critical to time the migration and ensure its success. Migrating manually from one ETL platform to another is a time consuming process which needs thorough planning and smooth execution.
There have been projects that ranges from 18 man months to 280 man months. There are very few automated solutions in the market that help reduce migration efforts, one such technology is from Pragmatic Works that helps migrating DTS packages to SSIS. This is good but is limited to only Microsoft SSIS. Here comes a highly recommended solution for automated migration from Analytix DS. Its ETL tool agnostic Mapping Manager’s ETL conversion capability, integrates with major ETL vendors like Informatica, Datastage, SSIS, Oracle Warehouse Builder, Oracle Data Integrator etc.
Scaling down versions of your enterprise applications in a Proof-of-concept is essential before you decide to jump on any new ETL platform.
Finding the right balance between risk and cost should ensure that business users’ decision support systems will remain available during the migration. To ensure the success of the migration project, data validation—the ultimate test of data quality—must be performed to ensure data integrity. This will ensure confidence and acceptance among the user communities
However, for managing the execution of the product in your environment or from an administrative perspective, these scripts do not port easily and it is recommended to be redone from scratch
One of the key elements for any enterprise Data Integration/ETL platform is Metadata Management and Data Lineage tracking and this should be included as the focused offering and the most important and appropriate platform for any enterprise. While setting up a test data for your migration team, having an effective data lineage in place reduces time and greatly help the analysts.
As is with every critical system, budget for extensive testing and data validation while also allowing parallel execution of the old and the new systems with the same input data sets for Production validation.
For an efficient ETL migration, it may be a good idea to assess various tools/services that can not only automate such migration but also validate the associated testing from ST (source to target) platforms.
To reduce the turnaround time for the UAT, it is great to have a Data Validation suite in place that can be used by both the migration team and as well as the end business users. This will not only save your efforts on a manual conversion but also bring in efficiencies form cost and time to market perspective.
AnalytiX DS’ ALC is a good candidate for such automated migration/conversion service offering which can serve as your starting point.
Here is a video and screenshot to demonstrate the ETL migration capabilities through Mapping Manager:
Return on Investment: An Example :
Doing the Math:
At its core, conversion of an ETL job of medium complexity can take up to 16hours of labor, conservatively. Put that with an $88 average hourly rate card and you are looking at $1,408 per job. LiteSpeed price per ETL job, averages between $200-$400 (depending on volume)- saving more than $1,000 per job, and taking a fraction of the time.
That is a savings of $3M on a 3,000 job conversion!
As organizations begin to retire or phase out ETL platforms and migrate to other platforms, an automated, disciplined and predictable way of managing the conversion is required. The conversion process can always add value to your enterprise if assessed, planned and executed .
To make any data migration project successful all you need is a team armed with a methodology, a proven ETL conversion automation tool/service with conversion test automation.
Do not let the challenges act as an impediment to the migration process and implement a strategy that could smooth your migration journey.
So have you ever planned or executed any ETL migration project?
Please feel free to share your experiences, best practices and solutions you may have implemented.