Extraction, Transformation and Loading

Extraction, Transformation and Loading is critical component of a Data Integration Tool. More details on ETL tools are covered in the BI core-platform. Here the ETL is covered more in context of data integration in general. Data Integration is not only limited to a data-warehouse or BI. Data Integration can happen for the OLTP or transaction systems as well. One good example of that is when you do data integration for an ODS or for Master Data Management.

• Data transformation library with large number of pre-defined column, record and table level transformations: Data Transformation routines and business rules are quite specific to the context. There are two areas, where these libraries come into play. The first is to have the standard transformations like standardizing names, addresses, pin-codes etc...The other aspect is to create the library of your own custom transformations. For example, if you have to standardize on customer IDs across two systems to a single ID structure. Apart from enhancing productivity, these custom re-usable routines can be useful in providing consistency to your DI transformation across initiatives.

• Wizard for creating the extraction routines: This is covered in wizards page**, and here is more detail. A wizard for creating extraction routines will have the following capabilities:

• Ability to identify the source system structures
• Ability to identify the extraction data structure
• Ability to define the scheduling of extraction
• Ability to define the business rules and filters for extraction

• Wizard for creating transformation routines: Wizard will have following capabilities

• Ability design the transformation routines
• Ability to design the transformation work-flow
• Ability to define the custom re-usable routines
• Ability to define the scheduling for the transformation routines

• Wizard for creating loading routines: You may refer ETL loading design** in Data-Warehouse section** to get some more understanding on Loading. The loading wizard should be able to define the creation of data sets for loading, the sequence for creation of loading data sets, the sequence of loading the datasets into the destination area and finally the validations to be performed post the loading.

• Programmer friendly development language to create complex routines: Wizards may not be able to design all the complexities related to data integration. You may have to supplement it with the development language. The language should be of higher level and programmer friendly.

• Ability to do transformations on various formats and data types: The transformation in an ideal world should be any-to-any in terms of database platform, data structures and data-types (whichever is technically possible).

• Ability to embed the data integration processes in other applications in the callable form: The Data Integration tool should be able to design the routines in a way that they can be called from business applications. Here is where the open standards for application interface come into play.

• Ability to have the detailed activity logs of data integration tasks: This is typical of any processing system. One should bee able to have the activity log (which you can switch of or switch on or keep it for certain activities...) for all what happens in data integration tasks.