Configure a product refresh integration job

  • Updated

The following walk-through will help you create a Product Refresh integration job. Pulling product data into Optimizely Configured Commerce is required in to support base functionality and it can increase performance having the product data closer to the application.

Many refresh jobs query an ERP database directly, so it is crucial that you know where to find the required data in the ERP database. If you are unfamiliar, you should consult with somebody more familiar with the ERP data structure. Refresh jobs usually make use of the same types of processors:

  1. SqlQuery
  2. SqlQuery, OleDbQuery, or OdbcQuery
  3. FieldMap

The first SqlQuery processor constructs a SQL query statement. The second query processor runs the query to extract data from the ERP database. The FieldMap processor takes the data extracted from the ERP database and inserts it into the Configured Commerce database through a process called "field mapping". For simplicity, the walk-through uses a local SQL Server instance as the ERP database.

Preconditions

Before you start, make sure you've completed the following tasks.

  • Install SQL Server on your local computer
    • This walk-through creates a table, inserts data into that table, and reads data from that table.
  • Install the WIS on your local computer
  • Download and execute the SQL Server scripts (These are found within the attachment at the bottom of this article)
    • These scripts assume you have already created a database named "FakeERP"
    • These scripts create the table and inserts seed data into that table

Create the integration connection

The integration connection specifies the pieces of the connection string used to connect to the SQL Server instance.

  1. Log into the Admin Console.
  2. Go to Administration > Jobs > Connections.
  3. Click Add Integration Connection.
  4. In the Name field, type "FakeERP". This name is important and must be the same in the WIS configuration. The WIS configuration is addressed in a later section.
  5. In the Type Name drop-down, select "SqlServer".
  6. In the Source Server Time Zone drop-down, select the appropriate time zone.
  7. In the SqlServer Name field, type the server name. This example uses SQL Server Express, so the value is ".\sqlexpress".
  8. In the Database field, type "FakeERP".
  9. Use the Integrated Security, Log On, and Password fields to specify the account that will be used for the integration. The account should have read access to the tables in the ERP database. For simplicity, this example uses the default server administrator account (sa). As a best practice, this account should not be used in a production environment.
  10. Click Save.

Below is an image of the connection configuration.

Now that the connection is configured, you need to configure the WIS you installed.

Configure the WIS

Before continuing, you need to configure the WIS with the connection that you just created. This will allow you to select an integration processor for the integration job and also allow the integration job to be executed.

Next, you can create the integration job that actually does the work of refreshing the product data.

Create the integration job

This integration job uses standard processors and a single job step. Other refresh jobs may need to use one or more custom processors and job steps. First, you need to configure the job details.

Configure the job details

  1. In the Admin Console, go to Administration > Jobs > Job Definitions.
  2. Click Add Job Definition.
  3. Select the Details finger tab.
  4. In the Job Name field, type "Product Refresh". This name can be different, but the name "Product Refresh" helps indicate what type of job it is and which Configured Commerce object is being affected.
  5. In the Job Type drop-down, select "Refresh".
  6. In the Connection drop-down, select the "FakeERP" connection you just created. This will allow the integration processor to connect to the database specified in the integration connection.

Below is an image of the job configuration up to this point.

The Preprocessor, Integration Processor, and Post Processor fields are used to indicate what type of work will actually be completed by the integration job. These processors operate in the order the fields appear in the form. First, the preprocessor does any preprocessing required for the job. This can include aggregating data or constructing a query for the integration processor to use. Next, the integration processor builds on the work done by the preprocessor, usually operating on the data or objects retrieved or constructed by the preprocessor. Finally, the post processor processes the response from the integration processor. This can include consuming data retrieved from the ERP by the integration processor. You can read more about the other preprocessors, integration processors, and post processors and what purpose they serve.

  1. In the Preprocessor drop-down, select "SqlQuery". This processor will construct a SQL query. The specific query will be configured in a later section.
  2. In the Integration Processor drop-down, select "SqlQuery". Even though this is the same value as the Preprocessor field, this represents a completely different processor. This processor will take the query constructed by the SqlQuery preprocessor and execute it against the ERP database specified in the integration connection. The query results will be passed to the post processor.
  3. In the Post Processor drop-down, select "FieldMap". This processor will take and execute a field mapping that maps data between the ERP data set and the Configured Commerce database. The ERP data set will be inserted into the Configured Commerce database. The field mapping will be configured in a later section.
  4. Click Save.

Below is an image of the configuration of the three processors. For now, you can ignore the Notifications, Run Options, and Recurrence sections.

Now that the processors and connection have been configured, you can create the job step to specify what data should be extracted from the ERP database and where that data will be inserted into the Configured Commerce database.

Create the job step

Integration job steps are each executed once in order according to the Job Sequence number.

  1. Select the Steps finger tab.
  2. Click Add Job Definition Step.
  3. In the Sequence field, type "1". This field is used to order the execution of the job steps. Job steps are ordered in ascending order. This marks the first (and only) step in this integration job.
  4. In the Step Name field, type "Products". This is a friendly name for the step. It can help others understand the responsibility of each step.
  5. In the Target Object drop-down, select "Product". This is the Configured Commerce object on the receiving end of the refresh. In other words, data from the ERP will be mapped into this object.

Below is an image of the step configuration up to this point.

For the Delete Behavior, select "Ignore".

The Select Clause, From Clause, and Where Clause fields form the SQL query. The values in these fields will be used by the SqlQuery preprocessor to construct a SQL statement, which will be used to query the ERP database for product data. Below is the full SQL query before splitting it apart to fill the separate clause fields in the job step.

SELECT part_id,    part_desc,    product_code,    uom,    ship_weight,    ship_length,    ship_width,    ship_height,    '_MainWebsite:' + cat + ':' + sub_cat as categoryFROM dbo.ProductWHERE status = 'Active'
  1. In the Select Clause field, use the SELECT portion from the above query. You do not need to include the SELECT keyword. You will notice an odd format for the "category" column. This is expected by the field mapping to place products in the correct category or sub-category. The first string in the concatenation is the name of the website for which this product data should be refreshed. You may need to change that name depending on your Configured Commerce websites. 
    part_id,part_desc,product_code,uom,ship_weight,ship_length,ship_width,ship_height,'_MainWebsite:' + cat + ':' + sub_cat as category
  2. In the From Clause field, use the FROM portion from the above query. You do not need to include the FROM keyword.dbo.Product
  3. In the Where Clause field, use the WHERE portion from the above query. You do not need to include the WHERE keyword. 
    status = 'Active'
  4. Click Save.

Below is an image of the clause field configuration.

Now that the Target Object and SQL query have been configured, you can configure the field mappings.

Configure the field mappings

The field mappings tell the FieldMap post processor where the ERP data should be inserted into the Configured Commerce database. Each of the field mappings has a "From Property" and "To Property" property. Usually, the "From" property corresponds to the data set retrieved from the ERP database, but there are exceptions to this statement. The "To" property corresponds to properties on the Target Object specified earlier, Product in this case.

  1. Select the Field Mapping finger tab.
  2. Click Add Job Step Field Mapping.
  3. In the Field Type drop-down, select "Field".
  4. In the From Property drop-down, select "part_id". Remember, this field represents the data returned from the ERP database. Notice that it matches one of the columns specified in the Select Clause field from the earlier query.
  5. In the To Property drop down, select "Product Number". Remember, this field represents the Configured Commerce object property to which the ERP data will mapped. In other words, the "part_id" values from the ERP data will be inserted into the "Product Number" values on the Product object.
  6. For the Can Overwrite toggle, select "No". When this toggle is set to "No", the ERP data value will be inserted in the Configured Commerce database on the first run of this job. However, any subsequent runs of this job will not overwrite that data if it already exists. If this toggle is set to "Yes", the Configured Commerce object will be updated on each run, whether or not the new value is different. A use case for this ability to not overwrite existing values on subsequent runs can be made for product descriptions. Some ERPs may have products with abbreviated descriptions (perhaps due to length limitations). Within the Configured Commerce platform, those abbreviations may not make sense to a user. Setting the Can Overwrite value to "No" would allow the abbreviated descriptions to be inserted into Configured Commerce. Then, when the integration job completes the first run, a marketing department can update the descriptions with a non-abbreviated, user-friendly version and not have it overwritten by subsequent Product Refresh jobs.
  7. For the Is Dataset Key toggle, select "Yes". This indicates that this column in the ERP data set is a unique key. If this value is "Yes", the "To Property" value will be a natural key field of the Configured Commerce object (such as Product Number is a natural key of Product).
  8. Click Save in the top right corner.

  9. Repeat steps 3-11 for the remaining field mappings below in the table, swapping out the appropriate values. The header row in the table corresponds to the fields on the Field Mapping Detail page.

    Field Type
    From Property (such as Static Value)
    To Property
    Lookup Error Handling
    Can Overwrite
    Is Dataset Key
    Comments
    StaticValue 1 ERP Managed n/a No No A StaticValue field type does not use any data from the ERP data set. It uses a static value specified in the field mapping. Every record will use this value to update the Configured Commerce database. In this case, every record will use "1" to populate the "ERP Managed" property. You can read to learn more about StaticValue and other field types.
    ChildCollection category Categories Warning Yes No

    A ChildCollection field type is used when populating a property that is a foreign key to a "child" object of the target object. Categories is a special case of child collection. It requires a formatted value in the "From" property. For this property the earlier query should return a value in the format below.

    {website_name}:{parent_category_name}:{child_category_name}

    This allows the integration job to properly categorize each product. If you run the earlier query in SQL Server, the "category" column will return values such as "_MainWebsite:Apparel:Gloves and Mittens". That specific value will assign the product to the "Gloves and Mittens" sub-category in the "Apparel" parent category of the "_MainWebsite" website. You can read to learn more about ChildCollection and other field types.
    Field part_desc Product Title n/a Yes No  
    Field part_id URL Segment n/a Yes No  
    Field product_code Product Code n/a Yes No  
    Field ship_height Shipping Height n/a Yes No  
    Field ship_length Shipping Length n/a Yes No  
    Field ship_weight Shipping Weight n/a Yes No  
    Field ship_width Shipping Width n/a Yes No  
    Field uom Unit Of Measure n/a Yes No  

Below is an image of all the field mappings displayed in the grid.

After you have added and configured all the field mappings, the integration job should be completely configured and ready to execute.

Run the integration job

Before continuing, you should manually run the product refresh job to make sure it works correctly. If the job fails, you can view the log that was created during the execution to track down any errors.

  1. Go back to the Product Refresh Job Definition.
  2. Select the History finger tab. Here you can see a list of the past executions of this job.
  3. Find the most recent execution and click View. Here you can see the status of the execution, the parameters used by the job, and the job logs.
  4. Select the Job Logs finger tab. Here you can see the various logs that were recorded. If you notice an error log, you can click View again to view the error description.

If the job is still failing, walk back through the steps to make sure you configured the job correctly. If the job is configured correctly, but still fails, you can read about advanced troubleshooting steps to diagnose the problem.

If the job succeeds, go to Catalog > Products to verify the data was imported correctly.

Scheduling the product refresh job

Integration jobs can be configured to run automatically according to a schedule. Most Product Refresh jobs run on a daily basis overnight, during non-peak business hours. This ensures the product data within Configured Commerce is fresh and accurate. You can create a schedule for the Product Refresh job by going to the Product Refresh Job Definition and using the Recurrence section on the Details finger tab.

Next steps

After the products have been imported correctly, you can start importing related data, like product attributes. The next walk-through shows you how to create a product attributes refresh job and how to ensure it runs after the product refresh job.

Resources