Configure a product refresh integration job

You can create a product refresh integration job in Optimizely Configured Commerce. Pulling product data into Configured Commerce is required to support base functionality and can increase performance.

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. Talk to your developer or partner for help.

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 an 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 field mapping.

Prerequisites

• Install SQL Server on your local computer.
• Install the WIS on your local computer.
• Download and execute the SQL Server scripts found within the attachment in this article.
  • These scripts assume you have already created a database named FakeERP.
  • These scripts create the table and insert 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 in to the Admin Console.
2. Go to Administration > Connections.
3. Click Add Integration Connection.
4. Enter a Name, such as FakeERP. This name must be the same in the WIS configuration.
5. Select SqlServer as the Type Name.
6. Select the appropriate time zone for the Source Server Time Zone.
7. Enter the SqlServer Name. This example uses SQL Server Express, so the value is .\sqlexpress.
8. Enter the Database, such as FakeERP.
9. Use the Integrated Security, User Name, and Password fields to specify the account for the integration. The account should have read access to the tables in the ERP database. This example uses the default server administrator account (sa), which should not be used in a production environment.
10. Click Save.

Configure the WIS

Before continuing, you need to configure the WIS with the connection you created. This lets you select an integration processor for the integration job and execute it.

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.

Configure the job details

1. Go to Administration > Job Definitions.
2. Click Add Job Definition.
3. Select the Details tab.
4. Enter the Job Name, such as Product Refresh. This name helps indicate what type of job it is and which Configured Commerce object it affects.
5. Select Refresh for the Job Type.
6. Select the FakeERP connection for the Connection. This lets the integration processor connect to the database specified in the integration connection.

The Preprocessor, Integration Processor, and Post Processor fields indicate what type of work is completed by the integration job. These processors operate in the order the fields display in the form. The preprocessor does any preprocessing required for the job, including aggregating data or constructing a query for the integration processor. The integration processor builds on the work done by the preprocessor, usually operating on the data or objects retrieved or constructed by the preprocessor. The post processor processes the response from the integration processor. This can include consuming data retrieved from the ERP by the integration processor. Read more about the other preprocessors, integration processors, and post processors and their purpose.

1. Select SqlQuery for the Preprocessor. This processor constructs an SQL query.
2. Select SqlQuery for the Integration Processor. Although this is the same value as the Preprocessor field, this represents a different processor. This processor takes the query constructed by the SqlQuery preprocessor and executes it against the ERP database in the integration connection. The query results are passed to the post processor.
3. Select FieldMap for the Post Processor. This processor takes and executes a field mapping that maps data between the ERP data set and the Configured Commerce database. The ERP data set is inserted into the Configured Commerce database.
4. Click Save.

Create the job step

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

1. Select the Steps tab.
2. Click Add Job Definition Step.
3. Select 1 for the Sequence. This is the first and only integration job step in this job.
4. Enter a Step Name, such as Products. This helps others understand the step's purpose.
5. Select Product for the Target Object. This is the Configured Commerce object on the receiving end of the refresh. Data from the ERP is mapped into this object.
6. Select Ignore for the Delete Behavior.

The Select Clause, From Clause, and Where Clause fields form the SQL query. The values in these fields are used by the SqlQuery preprocessor to construct an SQL statement, which is 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. Use the SELECT portion from the previous code sample in the Select Clause field. You do not need to include the SELECT keyword. The odd format for the "category" column is expected by the field mapping to place products in the correct category or sub-category. The first string in the concatenation is the website name 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. Use the FROM portion from the previous query for the From Clause. You do not need to include the FROM keyword.dbo.Product.
3. Use the WHERE portion from the previous query for the Where Clause. You do not need to include the WHERE keyword.

   status = 'Active'

4. Click Save.

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

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

• Install the WIS
• Configure the WIS
• Other preprocessors, integration processors, and post processors
• Learn more about field types (like StaticValue).
• Manually run an integration job.
• Advanced troubleshooting for integration job failures.