Database Mapping & ETL

Database Mapping Tools

MapForce is a data integration and ETL Tool with powerful support for database conversion. You can convert between any combination of database data and XML, JSON, PDF, CSV and other flat files, EDI, Excel (OOXML), 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. You can then see the database structure visually.

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

Supported relational databases:

Supported NoSQL Databases:

MapForce supports all major relational databases, as well as 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. This allows support for common database migration scenarios, such as MySQL to PostgreSQL, as well as countless other possibilities including one-to-many and chained data transformations.

Filtering and Processing Database Data

Many database transformations 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.

Database output logging (tracing)

Database output tracing makes it easy to log all the changes made by a mapping or ETL project to the database when the mapping runs.

When tracing is on, events such as database insert or update actions, or errors, are logged in an XML file that you can later analyze or process further in an automated way.

Database tracing can be enabled at the database component, table, stored procedure, or database field level. You can choose to trace all messages or only errors, or you can disable tracing completely.

Once you have selected the desired tracing level, the affected database component in the mapping project is extended with an XML tree showing the log structure. If you prefer the log file to be in a format other than XML, you can map data from it to some other data format supported by MapForce (for example, a text file or even another database).

When clicking the Output button to preview the result of the mapping, the developer can also see an example of what the trace log will look like when the mapping is executed.

Advanced Database Mapping and ETL

For users working with SQL, 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.

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.

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.

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, database, EDI, XBRL, flat file, Excel, JSON, JSON5, Web services, or other database data through MapForce.

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.

MapForce also allows you to connect to and map database XML 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.

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.

JSON Database Mapping

MapForce includes support for defining and executing database mappings based on JSON (JavaScript Object Notation) models. You can add JSON instance or JSON schema files as source or target components of a database mapping. MapForce reads and writes JSON files based on the JSON Draft 04 Schema.

As shown below, JSON components are displayed with appropriate element syntax and their data types are clearly indicated.

Data processing functions from the MapForce Function Library can be applied to transform JSON data, exactly as they are used with other components.

When you add a JSON or JSON5 file to a database mapping, MapForce detects automatically whether it is a schema or instance file. For JSON or JSON5 instance files, MapForce prompts you to browse for a schema or generate one automatically. MapForce uses the JSON or JSON5 schema to build the structure of the component.

MapForce Server Supports Bulk Insert for Databases

Bulk Insert is an operation available for certain databases that allows a large volume of data to be inserted into a database table in a single SQL statement, as opposed to the typical method of using individual Insert statements for each row. Since processing overhead by the database engine is greatly reduced, performance is much faster. Testing MapForce Server with some examples has shown Bulk Insert can be more than 10 times faster than individual Insert statements.

Bulk Insert is also advantageous in multi-user environments. A long series of Insert commands transmitted to the database by one user could potentially be interrupted by another user sending a Select request to the same table, then the Insert sequence would continue. In a situation like this the Select operation would result in incomplete or invalid data.