Within the Sales and Service platform, the import process is unintelligent when it comes to loading data from other SQL-based data sources. The application assumes that all data being imported is managed outside of the data loading process, which means it is up to the source system to tell the Sales and Service platform which data has not been loaded or updated. This thought process is contrary to most standard thinking. The target system needs to be responsible for informing the source system which data has been processed. There is a consistent method using a simple table/view and a small extension to add delta processing to any SQL-based source data.

As an example, let’s assume that we have the following simple table from our source system:

CREATE TABLE src_import_data
(
    ID int NOT NULL PRIMARY KEY,
    strCOL1 nvarchar(100) NULL,
    strCOL2 nvarchar(100) NULL,
	dtUpdatedOn Datetime NOT NULL
)

We will not be covering the basics of building an import map, but for the sake of our discussion, we will assume that we have a bio called TARGET_BIO that has the proper field mappings in place for the source system to reside. We can now do a few small steps to create a DELTA loading process so we can only load data that has not been imported into the Sales and Service platform correctly.

Step #1 — Create Simple Local History Data Management

The first step is to create a table, in the source database, that Sales and Service can manage as the log or history of what data has been loaded. The format should be consistent for each instance, containing only a few key fields. Based on our example above, our history table would be constructed as follows:

CREATE TABLE src_import_data_history
(
    src_import_data_id int NOT NULL PRIMARY_KEY,
	last_update_dt DATETIME NOT NULL
)

The history table uses the same primary key as it’s parent data table and manages the last update date of the row. In essence, this table now represents a log of each row and when it was last imported or updated in the Sales and Service platform. Once the table is in place, the original loading table can be wrapped in a view to join it with the history table to only display rows that are either new or that have been updated since the last successful load cycle. The new loading view would be constructed as follows:

CREATE VIEW src_import_data_view AS
SELECT ID, strCOL1, strCOL2 FROM src_import_data (NOLOCK)
LEFT OUTER JOIN src_import_data_history ON src_import_data_history.src_import_data_id = src_import_data.id
WHERE ((src_import_data.dtUpdatedOn > src_import_data_history.last_update_dt) or (src_import_data_history.last_update_dt IS NULL))

The view above will display all rows where there is either no related history record or where the data row has been updated after the last. The combination of the new history table and the new view provide us an instant method of managing delta data loads. Now we need a method to update the history tables during the loading process.

Step #2 — Use Transformation Maps and Map Extensions to Manage History

For the sake of this discussion, we are assuming that everyone knows how to create the simple import map back to our new view above for the purposes of the standard data load map. But, there are a few additional objects outside of the standard import map that we will need to define in order to properly manage the history entries for each row that is loaded into the system.

First, we will need a recordset and BIO object that point to our new history table. Since we were already planning on using that database as a datasource, we assume that the definition of that datasource is already present in your meta-data. Creating the recordset and BIO in the Sales and Service platform make it easy for us to manage that data even though it resides in a different database.

Next, we need a BIO Transformation map between the source BIO (src_import_data_view) and the new history BIO (src_import_data_history). We use BIO Transformations a great deal to handle the creation/update on a BIO within extensions (see BLOG : Leveraging BIO Transformation Maps To Generate New BIO Instances). They are simple, configurable way to interact with a BIO, removing the varied ways to either create or find an instance. This map is very simple. The only attribute we want it to manage is the ID mapping between the two data sources.

In your transformation, map the src_import_data_view.ID field to the src_import_data_history.src_import_data_id field and we use those attributes as the keys on both parts of the map. This setup allows us to define the map with update capabilities rather than just CREATE ONLY. In instances where we have loaded this record before, but the data is being updated, the history entry can now be UPDATED in the table and will reflect the most recent transaction back to the Sales and Service platform.

The last step is to define our BIO Transformation Extension, which we will call sampleManageHistoryOnXForm, to manage the update and/or creation of the new history records (src_import_data_history). Given that this type of handling could be used on several maps, our extension is going to be generically managed and use parameters to drive some of the key data elements.

package com.sample.extension.integration.bio;

import com.epiphany.shr.data.bio.extensions.BioExtensionBase;
import com.epiphany.shr.data.bio.Bio;
import com.epiphany.shr.data.bio.BioUtil;
import com.epiphany.shr.data.bio.UnitOfWork;
import com.epiphany.shr.data.bioxform.extensions.BioTransformExtensionBase;
import com.epiphany.shr.data.bioxform.BioTransformServiceBean;
import com.epiphany.shr.ui.exceptions.FormException;
import com.epiphany.shr.sf.EpnyServiceContext;
import com.epiphany.shr.util.exceptions.EpiException;
import com.epiphany.shr.util.logging.ILoggerCategory;
import com.epiphany.shr.util.logging.LoggerFactory;

import java.util.Calendar;
import java.util.TimeZone;
import java.text.SimpleDateFormat;


/**
 *
 */
public class sampleManageHistoryOnXForm
        extends
        BioTransformExtensionBase {

    // class level logger
    private static ILoggerCategory log = LoggerFactory.getInstance(sampleManageHistoryOnXForm.class);

    private static String fieldDelimeter = " : ";


    /**
     * @param source
     * @param target
     * @param transformDirection

     * @return int
     * @throws com.epiphany.shr.util.exceptions.EpiException
     *
     *
     */
    protected int bioAfterTransform(
            Object source,
            Object target,
            Object transformDirection)
            throws
            EpiException
    {

        try {
            log.debug("sampleManageHistoryOnXForm::bioBeforeUpdate", "IN", null);

            if (source instanceof Bio)
            {
                process(((Bio) source).getUnitOfWork(), (Bio) source, (Bio) target);
            }
        }
        catch (EpiException e) {
            log.warn("sampleManageHistoryOnXForm::bioBeforeUpdate", "EXP - " + e.getMessage(), null);
            throw e;

        }
        catch (Exception g) {
            log.warn("sampleManageHistoryOnXForm::bioBeforeUpdate", "EXP - " + g.getMessage(), null);

        }
        finally {

            log.debug("sampleManageHistoryOnXForm::bioBeforeUpdate", "OUT", null);
        }

        return RET_CONTINUE;

    }


    protected void process(UnitOfWork localUOW, Bio bioSource, Bio bioTarget)
            throws
            EpiException
    {

        try {
            log.debug("sampleManageHistoryOnXForm::process", "IN", null);

            // Custom Code Starts Here .....
            String paramESBio = getParameterString("History BIO");
            String paramBIOKey = getParameterString("BIO Key");
            int paramTZOffset = getParameterInt("TZ Offset", 0);

            if (paramBIOKey != null)
            {
                // Get a GMT date/time string
                Calendar cNow = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
                cNow.getTime();
				
				// Add TimeZone Offset from GMT for the Source System.  NOTE :  Not all systems use GMT Conversion, so this step
				//   is necessary to get the audit date and your history date aligned
                cNow.add(Calendar.HOUR, paramTZOffset);

                // Get the BIO Key Value from the Source Bio
                Object objSrcID = BioUtil.getUninitializedNull(bioSource,paramBIOKey);

                // Get the BIO Transformation Map Name
               String paramBioXFormationMap = getParameterString("Map Name");

               // Initialze working Objects
               BioTransformServiceBean bioxformer = new BioTransformServiceBean();
               Bio bioESHistory = (Bio) bioxformer.transform(bioTarget,paramBioXFormationMap);
               if (bioESHistory != null)
               {
                        log.debug("sampleManageHistoryOnXForm::process", "Create/Update ES History Item", null);
                        bioESHistory.set("last_updated_dt",cNow);


                        // Save the Change
                        localUOW.save();

                }

            }


        }
        catch (Exception e) {
            log.warn("sampleManageHistoryOnXForm::process()", "EXP - " + e.getMessage(), null);
        }
        finally {
            log.debug("sampleManageHistoryOnXForm::process()", "OUT", null);
            localUOW.close();
        }


    }

}

The code above is defined as a standard extension with an Event Category of “Bio Transformation Events”. The extension also defines a few parameters to allow this extension to be used in all delta load scenarios that use the same paradigm. The following is a list of parameters that are defined to manage this process:

History BIO — The name of the BIO that manages the target history table in the source database
BIO Key — The name of the primary key for the history table
TZ Offset — The number of hours from GMT where the source server is located (i.e. EST = “-5”)
Map Name — The name of the BIO Transformation map that is used to create / update the history data

The basis of the bulk of the code is retrieving and converting the current date/time into a usable version for the source system and either creating or updating the existing history record using the import ma The extension is defined on the bioAfterTransform event to ensure that the data row has been loaded correctly with no exceptions before updating the history record.

The combination of the custom history table and the After Transformation extension allows the target system (Sales and Service) to participate in the data management process for creating proper delta loading processes. The addition of these objects ensure that Sales and Service are only servicing data that is truly updated or new since that last update date. Since it is done in a generic fashion, this process can be carried across all data load procedures that need a delta loading data management.