Product refresh integration job
The following walk-through will help you create a Product Refresh integration job. Pulling product data into Optimizely {user["product-name"]} 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:
- 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 {user["product-name"]} 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.
- Log into the Admin Console.
- Go to Administration > Jobs > Connections.
- Click Add Integration Connection.
- In Name, type "FakeERP". This name is important and must be the same in the WIS configuration. The WIS configuration is addressed in a later section.
- In Type Name, select "SqlServer".
- In Source Server Time Zone, select the appropriate time zone.
- In SqlServer Name, type the server name. This example uses SQL Server Express, so the value is ".\sqlexpress".
- In the Database field, type "FakeERP".
- 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.
- Click Save in the top right corner.
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
- In the Admin Console, go to Administration > Jobs > Job Definitions.
- Click Add Job Definition.
- Select the Details finger tab.
- In Job Name, type "Product Refresh". This name can be different, but the name "Product Refresh" helps indicate what type of job it is and which {user["product-name"]} object is being affected.
- In Job Type, select "Refresh".
- In Connection, 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.
- In Preprocessor, select "SqlQuery". This processor will construct a SQL query. The specific query will be configured in a later section.
- In Integration Processor, 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.
- In Post Processor, select "FieldMap". This processor will take and execute a field mapping that maps data between the ERP data set and the {user["product-name"]} database. The ERP data set will be inserted into the {user["product-name"]} database. The field mapping will be configured in a later section.
- Click Save in the top right corner.
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 {user["product-name"]} database.
Create the job step
Integration job steps are each executed once in order according to the Job Sequence number.
- Select the Steps finger tab.
- Click Add Job Definition Step.
- 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.
- In Step Name, type "Products". This is a friendly name for the step. It can help others understand the responsibility of each step.
- In Target Object, select "Product". This is the {user["product-name"]} 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.
- In Select Clause, use the SELECT portion from the above query. You don't need to include the SELECT keyword. You will notice an odd format for the "category" column. This is expected by the field mapping in order 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 {user["product-name"]} websites.
part_id,
part_desc,
product_code,
uom,
ship_weight,
ship_length,
ship_width,
ship_height,
'_MainWebsite:' + cat + ':' + sub_cat as category- In From Clause, use the FROM portion from the above query. You don't need to include the FROM keyword. dbo.Product
- In Where Clause, use the WHERE portion from the above query. You don't need to include the WHERE keyword.
status = 'Active'- Click Save in the top right corner.
SELECT part_id,
part_desc,
product_code,
uom,
ship_weight,
ship_length,
ship_width,
ship_height,
'_MainWebsite:' + cat + ':' + sub_cat as category
FROM dbo.Product
WHERE status = 'Active'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 {user["product-name"]} 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.
- Select the Field Mapping finger tab.
- Click Add Job Step Field Mapping.
- In Field Type, select "Field".
- In From Property, 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.
- In To Property, select "Product Number". Remember, this field represents the {user["product-name"]} 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.
- For Can Overwrite, select "No". When this toggle is set to "No", the ERP data value will be inserted in the {user["product-name"]} 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 {user["product-name"]} 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 {user["product-name"]} 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 {user["product-name"]}. 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.
- For Is Dataset Key, 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 {user["product-name"]} object (such as Product Number is a natural key of Product).
- Click Save in the top right corner.
- 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 `<>` 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. 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 further 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.
- Go back to the Product Refresh Job Definition.
- Select the History finger tab. Here you can see a list of the past executions of this job.
- Find the most recent execution and click the View button. Here you can see the status of the execution, the parameters used by the job, and the job logs.
- 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 the View button 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 {user["product-name"]} 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 are correctly imported, 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 jobhow to create a product attributes refresh job and how to ensure it runs after the product refresh job.