Create a Product Attributes Refresh integration job

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 that knows the ERP data structure. Refresh jobs usually 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 Optimizely Configured Commerce database through a process called field mapping. This article uses a local SQL Server instance as the ERP database.

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

Preconditions

Complete the following tasks before you start:

• Install SQL Server on your local computer
  • This walkthrough 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 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 > Jobs > Connections.
3. Click Add Integration Connection.
4. Name the integration FakeERP. This name is important and must be the same in the WIS configuration. The WIS configuration is addressed in a later section.
5. Select SqlServer for the Type Name.
6. Select the appropriate time zone.
7. Type the SqlServer Name. This example uses SQL Server Express, so the value is .\sqlexpress.
8. Name the Database FakeERP.
9. 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 (sa), but this account should not be used in a production environment.
10. Click Save.

Below is an image of the connection configuration.

Configure the WIS

Next, configure the WIS with the connection that you just 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 > Jobs > Job Definitions.

1. Click Add Job Definition.
2. 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.
3. Select Refresh for the Job Type.
4. Select the Connection you created previously. This allows 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.

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

1. Set the Preprocessor to SqlQuery to construct a SQL query. The specific query will be configured in a later section.
2. 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.
3. 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.
4. Click Save.

Below is an image of the processor configurations. For now, you can ignore the Notifications, Run Options, and Recurrence sections.

Create the job steps

Next, create the job steps to specify what data should be extracted from the ERP database and where that data will be inserted into the Configured Commerce database. Integration job steps are each executed once in order 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 is present before creating relationships between that data and the product data.

Create the attribute types job step

1. Select Steps.
2. Click Add Job Definition Step.
3. Set the Sequence to 1 to mark the first integration job. This field orders the execution of the job steps.
4. Name the step Attribute Types. This field help others understand the responsibility of each step.
5. Select Attribute Type for the Target Object. Data from the ERP maps to this object.
6. Set the Delete Behavior to Ignore.

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

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.

1. Enter Name into the Select Clause field.
2. Enter dbo.vw_AttributeTypes into the From Clause field.
3. Click Save.

Below is an image of the clause field configuration.

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

1. Select Field Mapping.
2. Click Add Job Step Field Mapping.
3. Select Field for the Field Type.
4. Select Name for the From Property. Notice that it matches one of the columns specified in the Select Clause field.
5. Select Name for the To Property. The Name values from the ERP data will be inserted into the Name values on the Attribute Type object.
6. Set the Can Overwrite 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. 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 allow the marketing department to update them after the integration jobs completes the first run.
7. 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 will be a natural key field of the Configured Commerce object (such as Name is a natural key of Attribute Type).
8. Click Save.
9. Repeat steps 2-8 for the remaining field mappings below in the table.

After you have added and configured all field mappings, you need to create the step that pulls in the attribute values for each of the attribute types.

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

1. Select Steps.
2. Click Add Job Definition Step.
3. Set the Sequence to 2.
4. Name the step Attribute Values.
5. Set Attribute Value for the Target Object. Data from the ERP will be mapped into this object.
6. Select Ignore for Delete Behavior.
7. Use the clause fields to construct the query. 
   1. Set Select Clause to DISTINCT attr_type AS AttributeType, attr_val AS AttributeValue
   2. Set From Clause to dbo.vw_AttributeValues
   3. Set Where Clause to status = 'Active' AND ISNULL(attr_val, '') <> ''
8. Click Save.

Configure the field mappings

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

After you have added and configured the field mappings, you need to create the step that maps attribute values to products.

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

Create the product attribute values job step

1. Select Steps.
2. Click Add Job Definition Step.
3. Set the Sequence to 3.
4. Name the step Product Attribute Values.
5. Select Product Attribute Value for the Target Object.
6. Set the Delete Behavior to Ignore.
7. Use the clause fields to construct the query. This query includes the part_id so you can map the attribute values to the products.
   1. Set Select Clause to part_id, attr_type AS AttributeType, attr_val AS AttributeValue
   2. Set From Clause to dbo.vw_AttributeValues
   3. Set Where Clause to status = 'Active' AND ISNULL(attr_val, '') <> ''
8. Click Save.

Configure the field mappings

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

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 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 to the Product Attributes Job Definition.
2. Select History to see a list of past executions of this job.
3. Find the most recent execution and click View. You can see the status of the execution, the parameters used by the job, and the job logs.
4. Select Job Logs. If you notice an error log, you can click View to see the error description.

If the job is still failing, go through 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 has been 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. 

1. Go to Administration > Jobs > Job Definitions > Product Refresh.
2. Select Product Attributes Refresh for the Linked Job. 
3. Select Success Only for the Link Condition.
4. Click Save.

The Product Attributes Refresh job will now only run once the Product Refresh job is executed and results in a success. You schedule the Product Refresh job, and the other scheduling will be done automatically.