ETL Data Mapping, Integration and Migration

ETL ( Extract, Transform, Load ) is a data mappingand migration product designed to provide a fast and flexible solution to meet the requirement of transferring data between two separate systems.

Key Features:

  • Ease of Installation

The application and its web based configuration client can be installed very quickly with dependancies on just Apache Derby database and Tomcat web server being required however, these are also supplied as part of the package.

  • Modular construction:

This modular architecture provides a very flexible platform enabling new components and transformers to be added very readily

  • Complex XML format capabilities

XML Standards such as FpML require complex nested data structures to represent financial trades eg. Interest Rate Cross Currency Swap which has a collection of Cashflow Payments within a collection of Swap Streams, the DEX ETL application is designed to cater for these data structures using a very simple data definition technique.

  • Mode of operation:

An individual instance of Source to Target transformation is referred to as a Server eg. a CSV file source system loading into a database target system.

A server can operate as either a batch process or as a service.

An example of a batch process would be that mentioned above where it terminates on completion of the load.

An example of a Service would be a Message Queue source system transforming messages to another Message Queue target system where the server runs continually operating as a transformation bridge between source and target.

The ETL installation can support as many Server configurations as required bounded only by the memory resources of the host computer.

  • Data Structures

Both flat eg. Fixed Field Width and hierarchical eg. XML data structures are supported. The internal data format for all message types is XML allowing the Transformer classes to operate on any data structure provided.

  • Source and Target System Adaptors:

The implementation ships with a selection of interfaces to support a variety of technolgies eg.

  1. JMS ( Java Message Service )
  2. MQ
  3. Socket
  4. JDBC ( Java Database Connectivity ) compliant databases eg. Oracle, DB2, SQL Server etc.
  5. Spreadsheets
  6. Delimited files
  7. Fixed field width files
  • Transformers.

Transformer classes sit between the source and target system adaptors and are used to enhance the message before it is sent to the target system. The implementation ships with 40+ transformer classes including for example:

  1. Database lookup
  2. Convert a value to its Absolute Value
  3. Apply a ceiling or floor value.
  4. Decode, this sets a conditional value akin to an if, else if, else if, else struture.

A transformer can be as simple or as complex as necessary for instance, the LoanInterest transformer will generate a series of messages similar to a repayment schedule from a single message containing the data necessary to perform the calculation.

  • Failed Message Mechanic:

In addition to the usual message logging facility, XML format messages include the ability to log failures to the configuration database, these messages are then available via the Configuration client where they can be modified and re-submitted to the target system.

  • Multi-threading:

The three fuctional components Extractor, Transformer and Loader run in separate execution threads to provide the best possible throughput available.

  • ETL Configuration:

The configuration is managed using a web based client using some of the latest technologies running in the Apache Tomcat web server.

DEX Software Limited 2006