Advanced Database Mapping

To complement its database mapping and integration capabilities, MapForce includes a Database Query tab for performing direct database queries. When you connect to a database using the Database Query tab, MapForce displays its tables in a hierarchical tree in the browser pane.

MapForce Database Query

You can then use the SQL editor tab to display, edit, and execute SQL or SQL/XML statements, either by opening existing SQL files or creating SQL statements from scratch using drag and drop and auto-complete functionality.

You can execute your SQL script and view the results in tabular form and save both the retrieved data and the SQL script individually to separate files.

Database Key Settings

The MapForce database key settings allow you to customize how primary and foreign key values will be added to a database that is a data mapping target. You can either provide values for keys from within MapForce, or you can let the database system handle the generation of auto-values.

In situations where primary and/or foreign key relationships are not explicitly defined in your database tables, MapForce allows you to define these relationships inline, without any effect on the source data.

Define Database Table Actions

When you are mapping to a database, MapForce allows you to select database table actions to control how data is written to the database. This gives you full flexibility to automate the most advanced data management tasks.

The easy-to-use Database Table Actions dialog box allows you to define the columns within the selected table to be used to determine what action (INSERT, UPDATE, DELETE) should be executed in the database.

This provides unprecedented flexibility in manipulating database rows in response to XML, PDF, database, EDI, XBRL, flat file, Excel, JSON, JSON5, Protocol Buffers, Web services, or other database data through MapForce.

Support for SQL Stored Procedures

MapForce includes robust support for stored procedures as input components (procedures that provide results) or as output components (procedures that insert or update data). Or, stored procedures may be inserted as a function-like call allowing users to provide input data, execute the stored procedure, and read/map the output data to other components.

This screenshot shows the mapping of a stored procedure in SQL Server to create an XML file. The procedure returns a table of data showing all the managers in the chain of command above the specified employee ID, supplied as an input parameter – in this example the constant 67.

The parameter could also be supplied as a calculated value or a data element retrieved from elsewhere in the database.

MapForce provides a context menu that lets users run the stored procedure to reveal the data structure for mapping. Executing the illustrated mapping generates the XML output.

MapForce Stored Procedure

Map XML in Database Fields

In addition to extensive XML mapping functionality, MapForce also allows you to connect to and map XML data stored in relational database fields (currently supported for SQL Server & IBM DB2). You simply assign an XML Schema – either one registered in the database or one from your local file system – to the field, and MapForce renders the schema as a sub-tree of the database field for mapping purposes.

Sort Database Input Components

SQL queries that operate inside the database are not always sufficient for complex data mapping tasks. MapForce provides additional database sorting functionality through the SQL-WHERE/ORDER component for database input that requires additional processing, or when other data or conditions in the mapping impact the sorted order of data rows.

Support for NULL Values in Database Table Actions

The Database Table Actions dialog supports NULL value comparison. NULL-aware comparisons provide an improved way to handle databases that contains null values. MapForce users can configure a database mapping so that data comparison is done in a NULL-aware manner, according to rules applicable to the database kind involved in the mapping.

The data mapping shown at right is intended to update the target table without inserting duplicate entries. Both tables are defined allowing the email field to be NULL, so matching name entries with NULL email fields could exist in each table.

Clicking the Actions icon next to the TARGET database table opens the Database Actions Dialog. The NULL equal checkbox next to the email field allows MapForce to treat NULL values in the source and target as equal for data mapping purposes, even though they are not considered equal by database rules.

Null-aware data mapping in MapForce

Enterprise ETL

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, databases, PDF, flat files, EDI, etc.), MapForce is an extremely effective, lightweight, and scalable ETL tool. MapForce offers a straightforward, visual user interface that lets you easily load any supported mapping structures and then use drag and drop functionality to connect nodes and add data processing functions and filters, or use the visual function builder for more complex ETL projects.

MapForce ETL

In addition to its visual interface, MapForce is also accessible through a flexible Java or COM-based API, providing an integrated automation layer to your ETL implementations.

ETL Data Processing

MapForce allows you to easily associate your ETL data structures using drag and drop 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.

Direct File Input and Output (Data Streaming)

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.

Data Mapping Web Services

MapForce supports calling SOAP and REST Web services directly from within a mapping. You can insert a Web service call into a mapping and supply input parameters and username/password or other authentication. Connect output components to the Web service to pass the result to another component or write it to a file.

MapForce also supports generic HTTP Web services that typically carry custom request or response structures in the message body. MapForce supports both JSON or XML data in the request or response body, allowing you to call virtually any HTTP Web service that requires or returns XML or JSON structures.

MapForce Web Service

Manual definition of settings in the Web Service Call definition dialog allows developers to define settings based on a template URL. This is a convenient step when developers test and refine Web service calls in a Web browser window, since they can copy the URL from the browser to become the template.

Looking for a real world example? Here is a detailed tutorial on Web Services Data Integration.

Support for Web Services Security

MapForce supports authentication based on the WS-Security (Web Services Security) standard via client certificates and calling Web services via HTTPS. The parameters needed to establish secure communication are defined as part of the data mapping so execution is automated.

Building Web Services Visually

Altova MapForce provides powerful capabilities for building Web services based on existing WSDL 1.1/2.0 files and XML, PDF, database, EDI, XBRL, flat file, Excel and/or JSON data sources.

Web Services Description Language (WSDL) is an XML-based language used to describe and locate Web services that use XML-based messages to exchange data between applications. MapForce supports visual, drag and drop WSDL mapping based on WSDL 1.1 or 2.0. WSDL works hand-in-hand with SOAP, where WSDL defines a Web service and the functionality it provides, and SOAP is the transport protocol used by the client application to actually call the functions listed in the WSDL description. A WSDL file is made up of multiple transactions, each of which includes a SOAP request schema and a SOAP response schema.

Implementing a Web service based on a WSDL normally requires writing the code to access the data required for each transaction. MapForce automates this process by allowing you to connect data sources and operations visually. Once your visual mapping is complete, MapForce autogenerates the required Java or C# program code required to implement the service on a server.

XBRL Data Mapping

Extensible Business Reporting Language (XBRL) is an XML-based markup language for electronic transmission of business and financial data.

MapForce supports the use of XBRL taxonomies as the source or target of any mapping, enabling you to graphically transform backend accounting data into a compliant format without any risk to its semantic or structural integrity and/or integrate reporting data for financial analysis.

MapForce XBRL Mapping

Support for mapping execution files, code generation in Java, C#, or C++, and automation via MapForce Server means that you can also automate the conversion of financial data based on the graphical mapping design. This makes public financial data submission a repeatable and highly manageable process, allowing you to produce valid XBRL reports as required based on the variable data stored in accounting system fields.

Developing XBRL Mappings

To develop an XBRL mapping based on an existing taxonomy, simply insert your taxonomy structure in the MapForce design pane. MapForce will display a graphical representation of the file structure, depicting taxonomy concepts, hypercubes, abstract elements, periods, identifiers, etc. with intuitive icons.

The XBRL component takes structural and semantic information from the associated linkbases and is presented in a hierarchy that can be easily interpreted, expanded or contracted as needed.

Filtering and Processing XBRL Data

MapForce provides flexible support for filtering and processing data via advanced processing functions based on Boolean conditions or manipulating data as it’s converted from source to target format.

For example, because XBRL data is essentially flat, it is often necessary to apply grouping functions that combine source data into groups and then further process those groups to transform the data into a hierarchical structure. This is facilitated by grouping functions in the MapForce function library.

Any-to-Many and Chained Data Mappings

MapForce is an extremely flexible data mapping tool for complex data integration and conversion projects. The input and destination of a data mapping can be mixed formats, including any combination of XML, databases, PDF, flat files, EDI, Excel, XBRL, and Web services. A data mapping can have one input mapped to one output, one input to many outputs, many sources to one destination, or many sources to many destinations, all in different data formats.

MapForce Chained Mapping

MapForce lets you integrate data from multiple files and data sources or split data from one source into many outputs. Multiple files can be specified through support for wildcard characters (e.g., ? or *) in filenames, a database table, auto-number sequences, or other methods. This feature is very useful in a wide variety of data integration scenarios; for example, it may be necessary to integrate data from a file collection or to generate individual XML files for each main table record in a large database.

MapForce also allows you to use file names as parameters in your data integration projects. This lets you create dynamic mappings in which specific file names are defined at run-time.

Chained Data Mapping

Chained transformations empower you to create complex mappings in which the output of one mapping becomes the input of another. This powerful feature adds to MapForce's ability to execute fully automated transformations. Each chained component becomes a modular entity in an interdependent transformation sequence, allowing for conversions to be made on-the-fly.

Chained data transformations can be created using any number of mapping components connected to a final target component. Preview and code generation features can be displayed/generated for intermediate components, as well as for the final mapping result.

Output Preview

When a data mapping produces dynamic output of multiple files, each file has its own numbered pane in the Output preview window. A convenient menu option lets you save all the output files in a single step.

For chained mappings and mappings producing multiple distinct output components, an icon on the component lets you assign it to the output preview window.

Data Mapping Documentation

MapForce is often used in team environments by developers and non-technical subject matter experts to map wide varieties of complex data to and from XML, databases, flat files, EDI, Excel, XBRL, and Web services. MapForce lets you generate detailed documentation of your data mappings, enabling designers and data integration specialists to share and confirm the accuracy of their mappings with other departments and/or customers.

MapForce Generate Documentation

The generated documentation gives an overview of all mapping inputs and outputs, including details on connections made and functions and filters that have been applied.

This is an invaluable feature for complex data integration and conversion projects, where the intricacies of all of the formats being mapped are often not known to quality assurance teams, subject matter experts, and other stakeholders.

To further improve data mapping documentation, MapForce lets you add annotations to any mapping connection(s) in the connection Properties dialog.

Text entered in the Annotation Description field appears in the mapping design to help you retrace your steps and adds clarification for other members of the project team.

Integration with StyleVision

Integration with StyleVision lets MapForce users automatically view output from their XML and XBRL data mapping projects in HTML, RTF, PDF, and Word by associating them with a template design. This powerful feature allows you to automate report generation by combining the data mapping functionality of MapForce with the sophisticated rendering engine of StyleVision.

MapForce StyleVision Integration

When a stylesheet is assigned to your data mapping, simply click the relevant format tab (i.e., HTML, RTF, PDF, or Word 2007+) in the Output preview window to view your report. (StyleVision must be installed on your workstation.)

Data Conversion Source Code

After a project is designed and tested in MapForce, it can be executed by MapForce Server to automate business processes that require repetitive data transformations.

As an alternative, MapForce can automate data integration and repeatable business processes by generating program code for complex recurring data mappings.

MapForce includes a built-in code generator that can automatically generate Java, C++ or C# class files from XML Schema definitions, databases, flat files, EDI configuration files, Excel workbooks, and XBRL taxonomies.

MapForce Code Generation

The result of the code generation is a full-featured and complete application that performs the mapping operations. You can run the application directly as generated, insert the generated code into your own application, or extend it with your own functionality.

You can edit your generated code directly to complete such operations as defining your own source or target files, using an XML input stream as an XML data source, or adding extra error handling code.

MapForce Source Code Generation Features:

  • Royalty-free Java, C++, and C# source code from data mappings
  • Source code implements data processing functions included in the mapping
  • Built-in MapForce engine previews source code execution result
  • Easily update code by modifying the underlying data mapping

The ability to generate royalty-free code in various programming languages can provide tremendous performance benefits in your mission-critical data mappings and conversions by enabling you to implement lightning-fast data integration operations in source code that can be compiled into your own applications.

Customizing Code Generation

Generated output code is completely customizable via a simple yet powerful template language.

You can easily replace the underlying parsing and validating engine, customize code according to your own writing conventions, or use different base libraries such as the Microsoft Foundation Classes (MFC) and the Standard Template Library (STL). You can even configure your code with data streams for input and output.