Manmohan Mishra: Cross Platform ETL Migration through AMM

Hello Friends, in today’s era where technologies are upgrading in every single minute, everyone is interested in upgrading for one either from one version to another or one ETL tool to another.

Let’s have a chat on Datawarehousing Domain, The most used tools for transformations are Datastage, Informatica, Ab-Initio and Pentaho.

It’s quite easy when we talk about migration from one version of ETL tool to another upgraded version because this new version is capable to handle all the issues which were there in previous version and loaded with new feature as well as all fixes. This same tool allows the same format and extensions which has been used in the earlier version. So this would generally an EXPORT and IMPORT of code from older version to newer version with a small effort of sanity check.

The actual pain of migration comes into the picture we have requirement of migrating from one tool to another because here we have to deal with different syntaxes, different components as well as overall differences in Design and Development Environments.

What is the panic to migrate from one ETL tool to another??

When we try to migrate code from one ETL tool to another we have deal with so many of things, few of them are as below:

Understanding and Expertise in both the Environments.
Re-creation of same logic in target tool (Efforts required is more the fresh development)
Differences in the functionalities of components in both the tools.
Most of the Components are not re-usable.
Efforts to Analyze, Develop and Test

There are many more challenges which we have to face apart from the above.

So what’s the efficient SOLUTION??

Now when we are aware of all the possible issues which we could face then we can understand the significance of the solution which I want to discuss here. Here’s I want to introduce one tool named as “AMM” which is capable enough to do the cross migration from “ETL Datastage to ETL Informatica” for us. This is an automated tool which needs a very small intervention from human.

What are the other ways to do it??

There are several ways to do the cross migration in-fact there are many methods such as PCaaS, Cloud Deployment where either you don’t even need to migrate the code in new environment or you just need to deploy the code once in the cloud and use its services, Cloud computing a costly operation for the industry. Let’s talk about the PCaaS (PC as Service), it is also a costly operation but better then cloud but I would suggest AMM.

Let’s not go much deep on other ways to do this, I’ll share another blog which will have more descriptive information regarding this.

Why AMM??

Now that’s the genuine question, it is FREE Tool by Informatica Corp.

Well its first quality is enough for us to give a try to this tool but let’s go in some more details about the tool from the below paragraphs. This tool is capable to do Data Lineage, Impact Analysis and Version control. It is capable to create jobs just by taking the mapping specification as source. We can directly feed the mapping specifications to this tool and get the job design.

There is one IBM tool which is capable to perform similar operation named “Fast Track” but this tools have its own additional cost as well as this tool is not capable to handle complex operations such as multiple joins and other complex transformation. It’s good for small transformations as well as look-ups.

AMM is capable to handle complex operation and joins. We can feed thousands of mapping documents as input to this tool and this tool can handle that as well.

This tool can be used to perform the reverse engineering,

Is Reverse Engineering, Possible?

As I told we can directly feed mapping document as source to this tool, same way if we feed already designed jobs as input to this tool then AMM is capable enough to generate the mapping specifications on the basis of that job. We can say this is an accelerator of for development of our jobs.

What makes AMM to perform Cross Platform Operations??

A robust XML parser, this tool is capable to generate jobs through XML feed, we can take export from any ETL tool in XML format and feed that XML to AMM and get the job generated. AMM is robust enough to generate the jobs from XML’s. So there is no any platform dependent code export required for it. This makes this tool to work efficiently for codes from multiple environments.

Deep dive in AMM

AnalytiX Mapping Manager (AMM) is a robust solution for Solving all your data mapping needs. It is a part of the “Agile Tools Movement” for the data integration industry, enabling customers to accelerate delivery by automating manual “pre- ETL” Source to Target Mapping (STM) processes of data integration projects. AMM enables organizations to “break free” of managing hundreds to thousands of MS Excel based data mappings across the organization.

AMM enables organizations to quickly consolidate metadata using its built-in scanners, manage data dictionaries and automates the STM process by allowing mappings to be created using “dragging -and-dropping” techniques and storing mappings in central data mapping repository.

AnalytiX Mapping Manager (AMM) is a complement to existing data integration products and allows the data integration and management professionals to manage enterprise data dictionaries and build source to target mapping specifications using drag and drop techniques to accelerate the mapping process.

The mapping specifications are then versionable, trackable, auditable, and repeatable throughout lifecycle of data integration and Master Data Management (MDM) projects. AMM auto-generates ETL Jobs for Informatica.

It enables forward / reverse Engineering as well.
Scans and consolidates metadata and manages enterprise data dictionaries (metadata repository)
Automates source to target mapping (pre-etl) process using drag n drop. You can then version them through the change process and manage them in our data mapping repository.
Once the mapping are ready for development they can then be printed or exported as coding requirements or exported in XML to auto-generate ETL jobs for leading ETL tool providers like Informatica, datastage and SQL Server SSIS Pkgs.
The code generation is available out of the box, however if you want “multiple code generation templates” to be created we can generate these templates based on your own best practices and standards for “different job types.
Advanced analysis features include: Impact Analysis. Data Lineage Analyzer, Gap Analysis and Mapping Status Reporting.

Feature described by Informatica Corporation (AMM Developers)

Customizable Export of Mapping Specifications to Excel.
Multiple Exports of Legacy Excel Mappings.
Project Specific Transformations (Business rules).
Mass update of source/target data across Mapping Specification.
Multi Browser Compatibility.
Full Enterprise Metadata Repository with built-in data preview capabilities.
Promotes Excellence in STM Mapping. Standards (Metadata & Standards Based).
Accelerate Delivery & Improve Quality.
Governance. Best Practices. Standards. Versioning. Traceability. Audit-ability. Impact Analysis. Data Lineage.
Collaboration & Programmatic Excellence.
Cross Team Collaboration & Planning.
Canned Reporting & Open-Source DBMS for mining the repositories.
New improved web user interface with performance improvements.
Extended the Integration with ETL Tools (Informatica, DataStage and SQL Server SSIS).
Certified on internal repository Oracle DBMS and new versions of SQL Server.
Certified to run on Linux based environments (Ubuntu and Red Hat).
Enhanced change log tracking other features.
ETL Tool Conversion Capabilities. Ability to quickly convert legacy ETL Jobs from other vendors to Informatica.