Create a Product Attributes Refresh integration job

13 min

You can create a Product Attributes Refresh integration job with Optimizely Configured Commerce. Because many refresh jobs query an ERP database directly, you should know where to find the required data in the ERP database. If you are unfamiliar, consult with somebody who knows the ERP data structure. Refresh jobs usually use the same types of processors:

SqlQuery

SqlQuery, OleDbQuery, or OdbcQuery

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

This job uses multiple steps to sequence importing data for separate Configured Commerce objects. This ensures all appropriate data exists before forming the necessary relationships between the objects.

Note

If you have completed the Product Refresh walk-through, go to Create the Integration Job.

Prerequisites

Complete the following tasks before you start:

Install SQL Server on your local computer.

Install the WIS on your local computer.

Download and execute the SQL Server scripts.

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.

Go to Administration > Connections.

Click Add Integration Connection.

Name the integration FakeERP. This name must be the same in the WIS configuration.

Select SqlServer for the Type Name.

Select the appropriate time zone.

Enter the SqlServer Name. This example uses SQL Server Express, so the value is .\\sqlexpress.

Name the Database FakeERP.

Use the Integrated Security, Log On, 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, which should not be used in a production environment.

Click Save.

Configure the WIS

Configure the WIS with the connection you created to select an integration processor for the integration job and allow the job to be executed.

Create the integration job

This integration job uses standard processors and multiple job steps responsible for refreshing certain pieces of the product-attributes relationship.

Specify the job details

Go to Administration > Job Definitions.

Click Add Job Definition.

Enter a Job Name. This example uses Product Attributes Refresh to indicate what type of job it is and what Configured Commerce object it affects.

Select Refresh for the Job Type.

Select the Connection you created previously. This lets the integration processor connect to the database specified in the integration connection.

Preprocessor, Integration Processor, and Post Processor indicate what type of work the integration job completes and operates in the order they display in the form. First, the preprocessor does any preprocessing required for the job, including aggregating data or constructing a query for the integration processor. The integration processor then adds to the work, 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.

Set the Preprocessor to SqlQuery to construct an SQL query. The specific query will be configured in a later section.

Set the Integration Processor to SqlQuery. Even though this is the same value, this processor executes the query constructed by the SqlQuery preprocessor against the ERP database specified in the integration connection. The post processor receives the query results.

Set the Post Processor to FieldMap to map data between the ERP data set and the Configured Commerce database. The ERP data set will be inserted into the Configured Commerce database. You will configure the field mapping later.

Click Save.

Create the job steps

Create the job steps to specify what data should be extracted from the ERP database and where that data is inserted into the Configured Commerce database. Integration job steps are each executed once according to the Job Sequence number. The Product Attributes Refresh job uses three steps to pull in the attribute types, attribute values, and product-to-attribute value relationships to ensure the attribute types and values data are present before creating relationships between that data and the product data.

Create the attribute types job step

Select Steps.

Click Add Job Definition Step.

Set the Sequence to 1. This field orders the execution of the job steps.

Name the step Attribute Types.

Select Attribute Type for the Target Object. Data from the ERP maps to this object.

Set the Delete Behavior to Ignore.

The Select Clause, From Clause, and Where Clause fields form the SQL query. The preprocesser uses these fields to construct a SQL statement to query the ERP database for product data.

Enter Name into the Select Clause field.

Enter dbo.vw_AttributeTypes into the From Clause field.

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 field mapping has a From Property and To Property. The From Property often corresponds to the data set retrieved from the ERP database, and the To Property corresponds to properties on the Target Object specified earlier.

Select Field Mapping.

Click Add Job Step Field Mapping.

Select Field for the Field Type.

Select Name for the From Property. Note that it matches one of the columns specified in the Select Clause field.

Select Name for the To Property. The Name values from the ERP data are inserted into the Name values on the Attribute Type object.

Set the Can Overwrite to No. The ERP data value is inserted in the Configured Commerce database on the first run of this job. However, any subsequent runs of this job do not overwrite that data if it already exists. If this toggle is set to Yes, the Configured Commerce object is updated on each run, whether or not the new value is different. For example, some ERPs may have products with abbreviated descriptions that would not make sense to a user. Setting the Can Overwrite value to No would insert the abbreviations into Configured Commerce and let the marketing department update them after the integration jobs completes the first run.

Set the Is Dataset Key to Yes to indicate that this column in the ERP data set is a unique key. The To Property value is a natural key field of the Configured Commerce object (such as Name is a natural key of Attribute Type).

Click Save.

Repeat steps 2-8 for the remaining field mappings below in the table.

Field Type	From Property	To Property	Lookup Error Handling	Can Overwrite	Is Dataset?Key	Comments
StaticValue	1	Active	n/a	No	No	You can read tolearn more about StaticValue and other field types.
Field	Name	Label	n/a	Yes	No	?

After you add the field mappings, create the step that pulls in the attribute values for each attribute type.

Before continuing, you should run the Product Attributes Refresh job manually to verify it is working correctly. At this point, the job should be importing only the attribute types.

Create the attribute values job step

Select Steps.

Click Add Job Definition Step.

Set the Sequence to 2.

Name the step Attribute Values.

Set Attribute Value for the Target Object. Data from the ERP is mapped into this object.

Select Ignore for Delete Behavior.

Use the clause fields to construct the query.

Set Select Clause to DISTINCT attr_type AS AttributeType, attr_val AS AttributeValue.

Set From Clause to dbo.vw_AttributeValues.

Set Where Clause to status = 'Active' AND ISNULL(attr_val, '') <> ''.

Click Save.

Configure the field mappings

Select Field Mapping, and use the following table to configure them.

Field Type	From Property	To Property	Lookup Error Handling	Can Overwrite	Is Dataset Key	Comments
StaticValue	1	Active	n/a	No	No
Field	AttributeValue	Value	n/a	No	Yes
Lookup	AttributeType	Attribute Type	Warning	No	Yes	A Lookup field type handles mapping a many-to-one relationship, where the Target Object is on the many sides of the relationship and the Lookup object is on the one side. In this case, an Attribute Value has an Attribute Type. The Attribute Type name is returned in this property, so the mapping can occur based on name.

After you add the field mappings, create the step that maps attribute values to products.

Before continuing, you should run the Product Attributes Refresh job manually to verify that it is working correctly. The job should import the attribute types and values and assign the attribute values to the appropriate attribute types.

Create the product attribute values job step

Select Steps.

Click Add Job Definition Step.

Set the Sequence to 3.

Name the step Product Attribute Values.

Select Product Attribute Value for the Target Object.

Set the Delete Behavior to Ignore.

Use the clause fields to construct the query. This query includes the part_id to map the attribute values to the products.

Set Select Clause to part_id, attr_type AS AttributeType, attr_val AS AttributeValue.

Set From Clause to dbo.vw_AttributeValues.

Set Where Clause to status = 'Active' AND ISNULL(attr_val, '') <> ''.

Click Save.

Configure the field mappings

Select Field Mapping, and use the following table to configure them.

Field Type	From Property	To Property	Lookup Error Handling	Can Overwrite	Is Dataset Key	Comments
LookUp	part_id	Product	Warning	Yes	Yes	A LookUp field type is used when populating a property that is a foreign key to a "child" object of the target object. A child collection requires a "From Property" with a natural key value of the Target Object in the "To Property". In this case, "part_id" is the natural key of Product, which is the relationship defined here (Attribute Value to Product).
Lookup	AttributeType,Attribute Value	Attribute Value	Warning	No	Yes

Now that you have added and configured all the job steps, the integration job should be completely configured and ready to execute.

Run the integration job

You should manually run the Product Attributes RefreshProduct Attributes Refresh job to ensure it works correctly. If the job fails, you can view the log that was created during the execution to track down any errors.

Go to the Product Attributes Job Definition.

Select History to see a list of past executions of this job.

Find the most recent execution and click View. You can see the execution's status, the job's parameters, and the job logs.

Select Job Logs. If you notice an error log, you can click View to see the error description.

If the job still fails, follow the steps to ensure 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, you should verify that the product attribute data was imported correctly.

Go to Catalog > Attribute Types to verify attribute types and values.

Go to Catalog > Products to verify attribute value relationships. Each product has an Attributes tab.

Link to the product refresh job

This product attributes refresh job should only execute if the products refresh job was already successfully executed. You can ensure this and the execution order by linking the two jobs. You can also stagger the workload for external systems. Linked jobs only run one at a time and under the conditions you specify.

Go to Administration > Jobs > Job Definitions > Product Refresh.

Select Product Attributes Refresh for the Linked Job.

Select Success Only for the Link Condition.

Click Save.

The Product Attributes Refresh job now only runs when the Product Refresh job is executed and results in success. You schedule the Product Refresh job, and the other scheduling is done automatically.