MapForce ETL Tools

Enterprise ETL Software

ETL (Extract-Transform-Load) tools provide a mechanism for extracting data from external sources, transforming it to a normalized data format, and then loading it into an end target or repository.

With support for prevalent enterprise data formats (XML, JSON, PDF, databases, flat files, EDI, Protobuf, etc.), MapForce is an extremely effective, lightweight, and scalable ETL tool. MapForce offers a straightforward, visual ETL mapping interface that lets you easily load any supported structures and then use drag and drop functionality to connect nodes and add data transformation functions and filters, or use the visual function builder for more complex ETL projects.

To complement its visual interface, MapForce ETL tools can be seamlessly automated via the high-performance MapForce Server.

MapForce allows you to easily associate data structures using drag and drop ETL data mapping functionality.

Advanced data processing filters and functions can be added via a built-in function library, and you can use the visual function builder to combine multiple inline and/or recursive operations in more complex ETL or data integration projects, and even save functions for use in other mapping projects.

MapForce also supports advanced ETL scenarios involving multiple input and output schemas, multiple source and/or target files, or advanced multi-pass data transformations.

Support for data streaming gives your ETL projects a huge performance boost with the ability to stream input from arbitrarily large XML, CSV, and FLF files and relational databases, and stream output to equally large XML, CSV, and FLF files or insert it into a database.

This built-in functionality means that MapForce can easily process massive data sets and ETL projects, limited only by the amount of disk space available on your local machine or accessible on a network.

In order to activate this feature, simply select the BUILTIN icon from the toolbar in the MapForce design pane.

With support for bulk database insert as well as direct data streaming, MapForce Server is also ideally suited for execution of ETL data mappings.

Convert Data Formats

In ETL applications, new information intended for import often arrives in a data format incompatible with the existing repository. MapForce offers unparalleled power and flexibility for advanced conversion and transformation, making it an efficient ETL tool for global and intra-enterprise data integration projects. You can easily integrate data from multiple files in different formats.

With support for automating data integration, MapForce is an ideal middleware product for connecting distributed applications in any local enterprise, Web-based workflow, or even Cloud architecture.

Three data formats are included in the ETL mapping illustrated below. The main source file is an Excel spreadsheet and the final output repository is a database. However, records in the SharesAndLeaves database column are stored as a JSON object. The MapForce ETL tool constructs the JSON object on the fly and includes it in the generated database insert statement, as highlighted in the output preview at the lower right of the screenshot.

Whether it is an XML, JSON, or database schema; EDI configuration file; PDF template; or XBRL taxonomy and beyond, MapForce integrates data based on data structures, regardless of underlying particular instance of content. This means that you can re-use data mappings as business data changes.

MapForce can generate MapForce execution files for execution by MapForce Server, or royalty-free data integration code in Java, C#, or C++ for automated reprocessing of ETL projects. This lets you implement scheduled or event-triggered data integration/migration operations for inclusion in any reporting, e-commerce, or SOA-based applications. MapForce data integration operations can also be automated via a data integration API, or ActiveX control.

ETL projects require data manipulation to transform input data before it is consumed by the target system. MapForce provides an intuitive visual function builder, fully scalable data processing functions with built-in libraries, filters and conditions, and more, to empower you to easily manipulate data and integrate disparate formats.

MapForce does not depend on assembling a collection of adaptors or other extra-cost add-ons. Users have complete control over design and implementation of data integration and transformations of all datatypes, including any combination of XML, JSON, database, text file, EDI, Excel, XBRL, and/or Web services data.

Database ETL Tools

MapForce database ETL tools include powerful support for database mapping, including mapping database data between any combination of XML, JSON, PDF, CSV, EDI, Excel, Protobuf, XBRL, Web services, and even other database formats.

When you load a database structure in the design window, MapForce automatically interprets the database schema, lets you pick available database tables and views, and recognizes table relationships.

Once you have loaded all of the content models required for your database ETL mapping, complete the mapping by simply dragging connecting lines between the source and target structures.

Database Mapping Features:

MapForce supports all major relational databases and popular NoSQL databases, empowering you to create graphical database mapping designs between database source data, data processing functions and filters, and other data structures of various types.

Supported Relational Databases for ETL:

Supported NoSQL Databases for ETL:

Many database ETL mappings require manipulation of data between the source and target based on Boolean conditions or SQL and SQL/XML statements. You may need to perform logical comparisons, mathematical computations, or string operations, check for database data of a particular value, and make other modifications to the data. In screenshot above, data processing functions appear as the boxes between the lines joining the source and the target data model.

Data processing functions enable you perform advanced database mappings on-the-fly for a multitude of real-world transformation requirements. You can, for example, construct database mappings that use XML or EDI messages to extract database rows based on filter criteria from the XML or EDI elements.

Once your mapping is defined, the built-in MapForce Engine allows you to view and save the results with one click.

Mappings to a database produce output in the form of SQL scripts (e.g., SELECT, INSERT, UPDATE, and DELETE statements) that are run against your target database directly from within MapForce or via MapForc Server.

Text and Flat File ETL Tool

Legacy data contained in existing text files can be extracted and updated to be consistent with an existing ETL repository. MapForce includes the unique FlexText utility for parsing and converting text files such as mainframe text reports, text-based log files, and other legacy text file types in mapping designs. With its visual interface, FlexText lets you insert an existing text file and extract the portions you want to convert in the MapForce mapping interface.

FlexText produces a template that is then loaded into MapForce, where individual text nodes can be converted to any combination of XML, database, PDF, EDI, XBRL, flat file, Excel, JSON, Protocol Buffers, and Web services data. By saving the configuration you create in FlexText, you can reuse the same template to convert multiple text files in multiple mappings.

FlexText allows you to create rules for text file conversion templates for ETL mapping. When you open a text file in the FlexText interface, the file is displayed in two blocks. The root block represents the original file, while the operation block (to its right) displays the data of the file in real-time as you extract the data you need.

The result of every operation you make is visible in real-time, so you can immediately see if you’ve achieved the desired result.

Legacy text files may contain useful data in CSV (comma-separated values) or FLF (fixed length field) formats inside a more complex flat file. FlexText allows you to directly extract such data using the CSV and FLF operations. After applying Split and other operations, you can store remaining CSV- or FLF-formatted fields by configuring the field names, lengths, etc.

FlexText allows you to isolate the data you need to access by removing non-relevant text, characters, and whitespace using split commands. Each split presents your data in two new blocks: one that contains the data you have split out, and another displays the modified view of your converted file. You can immediately see the result of each operation you perform.

FlexText supports Node and Ignore operations for further flexibility in constructing the information tree. An Ignore operation marks a block of text as irrelevant for conversion purposes and instructs MapForce to ignore it. The Node operation creates a new node in the information tree in MapForce so that you can properly represent the hierarchical nature of your text data when needed.

The Switch operation allows you to define multiple conditions for a single block of text. Data in the text file is passed to the associated container for use in your MapForce conversion only if it meets a defined condition.

FlexText also supports for regular expressions. For instance, an input file could be a system-generated report with numbers and letter codes in the left margin that indicate record types where a sequence of any five digits followed “O” indicates the beginning of a new section for one office location.