Data Integration Module (DIM) User Guide

Data Integration Module (DIM) User Guide

Overview

The Field2Base Data Integration Module allows you to automatically download data from your Sent Forms to your backend systems. The DIM provides transformation of your Form Data into a variety of formats including CSV and SQL.
The DIM Sample CSV Project Walkthrough Guide or the DIM SQL Project Walkthrough Guide are a good way to get started using the Data Integration Module.
                                    

DIM Features and Definitions

Basic Features

  1. Automatically downloads Form Data from the Field2Base server
  2. Provides Transformation of Form Data into a variety of formats (including CSV and SQL) to a backend server
  3. Provides an easy to learn Project Manager Utility for creating Projects to transform your Form Data
  4. Provides a comprehensive Administrator Utility for managing the DIM Service and other administrative functions

Advanced Features

  1. Provides an interface to use conditionals with Form Data transformations in the Project Manager Utility
  2. Has the ability to process multiple Projects at the same time
  3. Allows the use of executables and custom code to transform Form Data

DIM Project Manager Utility

The Data Integration Module Project Manager is the Utility used to create and edit Projects for processing Form Data based on Field2Base eForms.  

DIM Administrator Utility

The Data Integration Module Administrator is the Utility used to manage the Jobs of processing Form Data and the Data Integration Module Service itself as well as providing easy access to Logs for troubleshooting.

Definition of a Project

In the Data Integration Module, a Project is the entity that contains the base information from a Field2Base eForm file and the details of how you want Form Data transformed. The Data Integration Module service will use a properly setup Project and process Form Data into the appropriate format for use with a Company’s backend server. 

Definition of a Job

In the Data Integration Module, a Job is the actual Form Data that has been downloaded from the Field2Base server. It is considered a Job until the Form Data has been transformed into the format defined in the Project. You can view and manage your Jobs queue with the Administrator Utility.
The requirements for a job are:
  1. The Data Integration Module is installed on the Company’s backend server
  2. Allows the use of Executables and Custom Code to transform Form Data
  3. Access to the Field2Base Service has been granted
  4. The Company’s backend server has access to the external Internet and can connect to the Field2Base service
  5. A Project has been setup and is enabled on the Company’s backend server
  6. Forms in Field2Base have been sent and processed by the server (No Data on the Field2Base server means no Jobs will be created)
The steps for a job are:
  1. The Data Integration Module will contact the Field2Base Server asking if any Forms have been processed for your Company.
  2. The Field2Base Server will respond and package Form Data up into Jobs for the Data Integration Module to download.
  3. The Data Integration Module will download each Job and place it in the queue.

Data Transformation Process Overview

  1. The first Job in the DIM queue will be opened.
  2. Based on the settings of the Project for the Job, the Data Transformation will begin by doing the following:
    1. For CSV Transformations, a new or existing CSV file will be opened
    2. For SQL Transformations, a connection to the SQL Server will be opened
    3. For Custom Code and Executable Transformations, the respective command will be run with open access to the Job’s data
  3. At this point, CSV and SQL Transformations will continue with the rest of the process described while Custom Code and Executable Transformations will process the data in their own fashion.
  4. For each Region in the Form, the service will take the data in the Job and follow the transformation instructions in the Project and match it up to the proper column in the CSV file or SQL database.
  5. Once all Regions have been processed, the Project will be checked to see if there are multiple Transformation types. For multiple Transformation types, each Region will be processed again using the new mappings.
  6. Once all Transformations have been completed, the connection or file is closed.
  7. Custom Code and Executable Transformations meet back up at this point with CSV and SQL Transforms assuming they were completed successfully.
  8. The currently running Job is then closed and moved to a Processed folder.
  9. The next Job in the DIM queue will then be processed.
Note: Only one Job in queue can be actively transforming Data at a time.


Project Manager Overview

The Data Integration Module Project Manager is the Utility used to create a DIM Project from a Field2Base eForm for use with the processing service to send data to a Company’s back-end system.

The File Menu includes basic functions of the Project Manager.
  1. New - allows you to create New Projects from Forms loaded into the Project Manager. 
  2. Open - Allows you to open an existing Project.
  3. Save - Allows you to save a Project. There is no option to “Save As” because the Project file generation is limited to a single Project per Form and is internal to the Utility. All Project files are saved to C:\Program Files\F2B Data Integration Module\Projects.
  4. Close - Allows you to close any existing Project without exiting the application.
  5. Load Forms - Allows you to load Forms for use within the Project Manager.
  6. Exit - Closes any existing Project and exits the Project Manager.

A.  Page Selection and Zoom Options

Allows you to navigate through the Form pages using the arrow buttons or you can use the Drop Down to go directly to the page selected. The Zoom Options allows you to zoom in or out on the Form currently viewed. 

B.  Form Viewing Window

Allows you to actively work with the Form loaded for this Project. This window gives you the ability to select Regions when mapping Regions to Columns.

 C.  Drag and Drop Mapping Button

This is where Regions are dragged and dropped when mapping Regions to Columns. Enabled means that a Column has been properly selected and Regions can be mapped. Disabled means that no mapping can be done while in this state. 
 

D.  Mapping Window 

This is where all of the actual Mapping of Data is recorded and viewed. Additionally, this is where a Condition can be set on whether or not to output the results of a Data Transformation.
  1. Current Mapping - displays what the mapping value (Region Page and Name) is for the selected column. In addition to Regions, text can be added the Current Mapping section with or without Region values in it. For example, a Column in the Output does not have a related Region in the Form and text needs to be there for import into a Company’s Backend system. Any text entered like this is interpreted as a single string with any Region values mapped.
  2. Mapped Values - displays all of the columns for the Data Transformation Output and the values assigned to each. These values are set by using the Drag and Drop Mapping System. 
  3. Condition Section - this is where a Condition can be set on whether or not a Data Transformation should be outputted. If no conditions are needed, then nothing should be entered here. See the article on Using Conditions in DIM Projects for details how to create a Condition. 

E.  Data Transformations

This section consists of the main window that displays the Data Transformations for the current Project and the various buttons used in conjunction with Data Transformations. 

Types of Data Transformations
There are four different types of Data Transformations available for use with the Data Integration Module. They are CSV, SQL, Custom Code, and Executable represented in the Buttons on the upper right hand part of the Project Manager as CSV, SQL, Custom, and Exec.
CSV - A CSV Data Transformation Type will result in creating an outputted CSV file of Job data processed by the Data Integration Module. The required elements for a CSV Data Transformation Type are:
  1. Output Path – The Folder Location where the CSV Output file will be placed after being processed by Data Integration Module
  2. Output Filename – The Filename that the CSV Output file will have
  3. Number of Columns (if no Column Headers) – The Number of Columns the CSV Output file will have for mapping Regions to
  4. Column Headers – If Column Headers are Enabled, each line entered here represents a Column in the CSV Output file. When Column Headers are Enabled, the Number of Columns is not required and any value entered there previously is disregarded.
The standard method for Output with CSV Data Transformations is to output to a single file and append additional data to that single file. However, the Data Integration Module does allow Projects to be set up to output individual CSV Output files. See the article on Outputting Per Job CSV Files in DIM Projects for details on how to output individual CSV files for every Job processed.
SQL - A SQL Data Transformation Type will result in performing a SQL Insert into a Company’s Backend Database from the Job data processed by the Data Integration Module. The required elements for a SQL Data Transformation Type are:
  1. Database Connection String – The standard Connection String to connect to the SQL Database
  2. Selected Table – The table selected from the Database that will provide the Columns for Mapping
The Database Connection String is derived from the type of SQL Database used on a Company’s Backend Server. See the article on Common Database Connection Strings for the common Database Connection Strings Field2Base has encountered.
The standard type of SQL Data Transformation performs an Insert into the SQL Database. The ability to run an Update on Rows already inserted into the SQL Database is available, but requires editing of the Project file outside of the Project Manager. See the article on Creating SQL Update Data Transformations for details on how to add a SQL Update to a Project.

Custom Code - A Custom Code Data Transformation Type will run externally written code on the Job data processed by the Data Integration Module. The required elements for a Custom Code Data Transformation Type are:
  1. Custom Code Location – The Filename and Folder path of the Custom Code that will be run on the Jobs processed
  2. Selected Class – This the Class that will be used in the Custom Code that contains the proper Interfaces from the Field2Base Data Transformation SDK for use with the Data Integration Module
Executable - An Executable Data Transformation Type will run an external Executable on the Job data processed by the Data Integration Module. The required elements for a Executable Data Transformation Type are:
  1. Exec Location – The Filename and Folder Path of the Executable that will be run on the Jobs processed
  2. The remaining elements are all optional.
Note: It is NOT recommended to try and use the Custom Code or Executable Data Transformation Types without first contacting Field2Base since these types require external development.

Data Transformation Window 

The Project Manager displays all of the current Data Transformations set up in the Project in the window below the Data Transformation Type Buttons. Selecting the Data Transformation or its Child Objects (i.e. CSV Row or SQL Insert) will allow the User to use the various functions that are available by using the Data Transformation Editing Buttons located underneath the window.

There are four Editing functions available for use with Data Transformations in the Project Manager.  These Editing buttons can be used in varying degree with the Data Transformations or their Child Objects.


  1. New - Creates a Blank Row (for CSV) or Insert (for SQL) on the Selected Data Transformation.
  2. Edit - Can be used with CSV and Executable Data Transformations. Re-opens the initial Data Transformation Setup window.

Note: SQL and Custom Code do not have this Option since editing this Type of Transformation is the same as deleting the existing and creating a new one.
  1. Copy - can be used with Rows (CSV) and Inserts (SQL) [Child Objects Only]. Makes a Copy of the Selected Row or Insert and adds it to the end of the Parent Data Transformation
  1. Delete - can be used with ALL Data Transformations and Child Objects

F.  Details Window

This is the area where details of the Data Transformation are displayed for review. This not an editable box; it is essentially a way to display the expected output for the Transformation you have selected. You do have the option to copy text from this area and paste it elsewhere.



Creating a New Project

Loading eForm Files

The first step when creating a project using the DIM Project Manager is to load the eForm that you need to export data from.

  1. Open the File Menu of the Project Manager (folder icon in the upper left corner).
  2. Click on Load Forms. 

  3. A File Browser window will appear and allow you to browse to the folder of your choosing. Select the eForm file(s) you want to load to create DIM projects for.
Note: Alternatively, you can manually copy the eForm file(s) into the Forms folder found in the Program Files folder for the Project Manager Utility instead of loading them through the Project . The location of this folder is:  My Computer > Local (C:) Drive (or other designated Drive Letter for your PC) > Program Files (x86) or Program Files> F2B Data Integration Module > Forms

Creating a New Project 

1. Open the F2B Data Integration Module Project Manager. The Project Manager will open with No Project Loaded.
2. Go to the File menu and select New.
3. The Open Form screen will appear. These are the available eForms that have previously been loaded into your DIM Project Manager as described in the Loading eForms Files section above.

4. Select the .eform for your Project and click Open.
5. The Initial Transform Type Selection screen will open. Select the appropriate Transformation type and click OK.

6. Set the path and filename for your output file.
7. Specify either the number of columns needed in your output file (with no headers) or enable column headers by checking that option. If you enable column headers, you need to list the headers as shown below.

Mapping Values in a Project

Before starting to map values in a Project, you need to plan which Region values should match up with which columns in your output.  It is highly recommended that you work with your Company’s back-end Server Administrator to establish these Mappings prior to starting a Project in the DIM.
 
  1. Click on the Row in the Data Transformation Window (A) to display the column names in the Mapped Values (B).
  2. Select a column in the Mapped Values window (B).
  3. Select the appropriate Region and drag it to the Drag and Drop Regions Here button (C).
  4. You will see the expected output in the Details window (D) for your review.

In addition to using the drag and drop functionality, you can also manually enter the following types of values into the Current Mapping field (E).

  1. Text - For example,  “Form sent by ”
  2. Regions - For example, {Page1@Region1}
  3. Internal Values - See the article on Keywords Available for DIM Projects for a list of available values
Note: Any of the values you enter in the Current Mapping will NOT be processed; they are taken as is in a single string and sent to the output as is. This means that you cannot add two Regions together or change the format of a Region when Mapping. These types of actions need to be done either before the Form is sent to F2B (in Forms Designer) or after the Job is processed (by your Backend Server / Custom Code / Executable). Multiple Regions can be mapped to the same Column and when this is done, they will be interpreted with no spaces in between them so adding spaces or additional text may be needed when this type of Mapping is done.

Enabling a Project

Once all Mappings are complete on a Project, click on the Project Disabled button to enable your Project. The button will now display Project Enabled. This button toggles to disable/enable the Project. To disable a Project, click the Project Enabled button. 

Limiting Job Downloads

Once a Project is enabled, the default setting for the Data Integration Module Service is to download and process the Form’s entire history (all sent Forms) from the Field2Base server.
There is a way to limit the amount of Jobs (Sent Form Data) downloaded by the service based on start date, but it cannot be done in the DIM Project manager and requires an update directly the Project XML file. For more details, see the article on Limiting Job Downloads Start Date in the DIM Service .

Auto-Backup of Projects

The DIM Project Manager will automatically backup each version of a Project to a folder in the Field2Base Program Files folder (C:\Program Files\F2B Data Integration Module\Backup). This backup will occur whenever the Project is saved in the DIM Project Manager. The backed up Project will have a filename based on the Date and Timestamp with the following format: 

[Form Name].[Date of Backup].[Time of Backup].xml
Note: If you edit a Project XML file outside of the Project Manager, backup snapshots will not be taken.
To Restore a Backup Project:
  1. Go to C:\Program Files\F2B Data Integration Module\Backup.
  2. Copy the Project (XML file) you want to restore.
  3. Go to C:\Program Files\F2B Data Integration Module\Projects.
  4. Paste the Project (XML file) to this folder.
  5. Delete the prior existing Project for the same Form in order to avoid conflicts. 


Testing a Project

The Data Integration Module was created to be a flexible piece of software that works in various back-end scenarios. We cannot provide a standard set of Testing Steps due to the fact every Company’s testing scenario will be different. With that being said, we can detail several testing scenarios to consider.

Testing Scenarios

  1. All Testing with the Data Integration Module will be done in a Non-Live Testing Backend Environment.
    1. No additional steps needed for testing
  2. All Testing with the Data Integration Module will be done in a live Backend Environment AND all Test Data can be Deleted (or can be discarded).
    1. No additional steps needed for testing
  3. All Testing with the Data Integration Module will be done in a live Backend Environment BUT all Test Data can NOT be Deleted.
    1. We recommend that a system to identify Test Data be established before testing begins. For example, use a Test Project Code for all Forms sent in while testing.

Company History Scenarios

  1. The Company has previously Sent Data for this Form that can be used for Testing with the Data Integration Module.
    1. Historical Data can be used to test if there are no conflicts with the Testing Environment Scenarios. If there is too much historical data for testing purposes, it is recommended that the Job Downloads are limited. See the article on Limiting Job Downloads Start Date in the DIM Service  for details on how to limit the Job Downloads.
  2. The Company does NOT have previously Sent Data for this Form that can be used for Testing with the Data Integration Module.
    1.   The Form needs to be put into use and be submitted into Field2Base in order for testing to begin.

Creating a Test Version of a Form

If a Company does not feel comfortable with testing the Project with a live Form, a copy of the Form can be created and renamed for testing. This requires access to Forms Designer and adds an additional temporary test Form to the Company’s resources, but it does mean that test data can be easily identified and discarded. Essentially, the Form will be saved with a new unique Form Template ID to create a Test version separate it from the live version. To create a copy of a Form for testing, see the article on How to Clone a Form. We recommend that you add "TEST" to the Form Name in order to differentiate it from the Live version.

  1. Copy the new TEST eForm to the C:\Program Files\F2B Data Integration Module\Forms folder.
  2. Open the C:\Program Files\F2B Data Integration Module\Jobs folder.
  3. Make a copy of the original DIM Project named after the eForm.
  4. Rename this copied Project to match the Test form name.
  5. Open this Project in a text editor.
  6. Paste the new form’s Template ID into the <formTemplateId></formTemplateId> tag, replacing the previous form's ID.
  7. Make sure that the <enabled> tag is set to True (<enabled>True</enabled>).
  8. Save the test DIM Project.
  9. Restart the Data Integration Module Service in the Administrator to begin processing.

Keep in mind, since this is a new Form Template ID, completed TEST forms will need to be submitted in order for the DIM to start processing data.

Note: Remember to delete the Test Form once testing is complete to avoid confusion in the future. If your Company plans on making updates to the Project, it is a good idea to leave the Test Form and Test Project active (in a Testing folder) for easy testing in the future.

Testing Task List

  1. Test the Server Login Info to confirm there are no “Invalid Login” exceptions in the Logs Viewer of the DIM Administrator Utility.
  2. Test the Job Downloads from the Field2Base server to confirm there are Jobs in the Queue Monitor in the DIM Administrator Utility.
  3. Test the Jobs processed by the DIM to confirm the Jobs have been completely processed by using the Processed Job Status Filter of the Queue Monitor in the Administrator Utility.
  4. Test the processed results to confirm data is being outputted. For CSV, this means you need to check the Output folder defined, and for SQL, Executable, and Custom Code, this means you need to check the Database or System where the data was sent.



Using the DIM Administrator

The DIM Administrator allows you to view and manage the various aspects of data being processed by the DIM Service for enabled Projects.

Queue Monitor

The Queue allows you to monitor the current Job queue. You can filter your Queue based on Form (Project), Date Range, and Job Status.


Logs Viewer

The Logs allows you to view and filter the DIM Logs. Logs can be filtered by Form (Project), Date Range, and Job Status.


Service Manager

The Service Manager displays the current status of the DIM service. It also provides the basic administrative functions to Start, Stop, and Restart the DIM Service.  
Note: The Service Manager in the DIM Administrator provides quick interactions with basic service functionality for troubleshooting instead of having to work with the Window Service Control Manager (SCM). If you ever need to directly access the service in Windows Service Control Manager (SCM), the name of the Module’s service is “F2B Data Integration Service”.
You can refresh the contents of each section by using the Refresh button. Clicking the Refresh button will make the Utility refresh the Queue Data, Log Data, or Service Status with the most recent information while still retaining any selected filters.

Note: The Data Integration Module Service does not actively archive or delete any of the Logs, Jobs, or Output files associated with the Data Integration Module. If storage is an issue on the PC running the application, it is recommended that a process is setup to regularly perform maintenance on these files.


    • Related Articles

    • Troubleshooting the Data Integration Module (DIM)

      Troubleshooting Installation Issues User Account Privileges If you encounter any Permission related issues when installing the Data Integration Module, you will need to check and make sure that you have Administrator rights so that the Program AND ...
    • Integration Service Configuration Guide

      How To Configure Integration Services To Allow Read/Write Access on a Network Path All of our Integration Products, including the DIM, DUU, and EDM have respective Windows Services responsible for communicating with our server. Occassionally, ...
    • DIM Reset Guide

      Overview This document will familiarize you with the process behind resetting your installation of the Field2Base Data Integration Module (DIM). You may want to reset the DIM if you wish to recapture all data starting from a specific point in time ...
    • Data Integration Overview

      Our paperless mobile platform drives your digital transformation. A part of how we accomplish this is through our robust integration solutions designed specifically around your business and existing systems. Field2Base offers complete data ...
    • DIM Migration Guide

      Overview This article provides the information necessary to migrate the Field2Base Data Integration Module (DIM) over from one server to another.  Please refer to the DIM Install Guide for the initial installation of the Field2Base DIM.  Once that's ...