AR Upsert step

The AR Upsert step uses the Checksum mechanism to compare the source record and the target record to determine whether or not to import the source record. The AR Upsert step also performs the tasks mentioned below:

Connects to the AR System server using the connection input parameters. A connection is created once and reused.
Substitutes the batch commit size and qualifications with real values, if variables are used.
Checks whether to start a new batch based on the flag, if the commit size is provided. This flag is true by default. After a transaction is started, the flag changes to false. Once the transaction is committed, the flag is reset to true.
Retrieves the input row from the previous step using the Atrium Integrator get row API function.
Converts the Atrium Integrator input row to an AR System form entry using the Mapping Field list. The Atrium Integrator row data type is converted to the AR System data type. After the value is converted to the corresponding AR System data type, a second-level conversion is required if the field's data type does not match the converted value's data type. For example, when the field has selection or enum data type, the Atrium Integrator performs second level of conversion if the mapped field has a string value. The data type conversions are listed here.
Parses the matching qualifications and substitutes the values of stream fields from the input row in the qualification object.
Uses the qualification object to retrieve IDs for the matching entries.
Checks whether it is time to commit the transaction based on the record number, if a bulk transaction is started. If it is, then calls the end bulk entry transaction API and sets the start bulk entry transaction flag to true.

The following screenshot illustrates the AR Upsert step in the Spoon client:

ARUpsert on Spoon.png

Overview of Checksum mechanism

You can configure a Checksum value for a job transformation in AR Upsert step. This is used in a job transformation with other Input step, which obtains data from the external source.

The AR Upsert step uses the Secure Hash Algorithm (SHA) logic to calculate a Checksum value for all the fields you have mapped in the Filed Mapping tab of AR Upsert step. The AR Upsert step does not perform field-by-field comparison while updating records in a BMC Remedy AR System form. It refers to the Checksum value and decides whether or not to update the record. This steps improves the performance by eliminating unnecessary calls to the AR System server to insert or update records in a form.

You must add a checksum field with the field ID 304412001 to your form to use the checksum feature.

The Checksum computation is based on the following conditions:

Result	Condition
Entry in a form is updated	When the Checksum value of the input row does not match with the Checksum value present on the staging or target form. When the Checksum value is not present on the staging or target form.
A new entry is added in a form	When the Checksum flag is checked and an entry does not exist in the staging or target form.
No operation is performed	When the Checksum flag is checked and when an entry exists in the staging or target form. The Checksum value of the input row matches with the Checksum value present on the staging or target form.

General tab

The General tab in the AR Upsert step allows you set connection and values for available fields. The following screenshot illustrates the General tab.

ARUpsert Configurations.png

The General tab has the following fields:

Field	Description
Connection	Click the New button to create a new AR System server connection. Click the Edit button to edit an existing connection. Select an existing connection from the drop down menu.Click Wizard for a step-by-step guided procedure to create a new connection.
Form Name	Name of the AR System form where you output the data. This also works as a staging form when you provide the Alternate Target Form Name.
Use Checksum Checkbox	Performs checksum comparison in update mode.
Alternate Target Form Name	Specify when the target form is different from the staging form. When you provide the Alternate Target Form Name in an AR Upsert step, the operations are performed on the staging form. When you update any record, an entry is inserted into the staging form. The Alternate Target form is used for Checksum comparison. Example: When you push data into the staging form and that data is validated and promoted to the target from using a mechanism chosen by the Data Management administration. For example, when you push the data into the CTM: LoadPeople form, where the data is validated before moving to target form CTM: People form. The data in the CTM: LoadPeople form is purged over the period of time so that the last Checksum value is lost and not available for the next Data Load activity. In such scenarios you can specify CTM: People as an Alternate Target Form. When AR Upsert gets the same record for processing, it does comparison based on last Checksum value from CTM: People against the input record Checksum. If checksum is matched, then the record is not imported to CTM: LoadPeople, if the record does not match, then it creates a new record in CTM: LoadPeople form.
Batch Commit Size	(Optional) When you specify a Batch Commit Size, the bulk API import the data. You can specify the Batch Commit Size as variable. When you specify the Batch Commit Size as a variable, the AR Upsert step uses the actual value provided. Best practice When the source data has large number of duplicate records, we recommend either of the following actions: Use step that has the filtering capability, such as Filtering unique records, which filters the duplicate records before calling AR Upsert step or AROutput step. Keep the batch size smaller that can be compared with the unique records.
Fallback to single row commit if batch commit fails	Use this option to switch to a single-row commit if batch-commit fails.
Insert Only	Creates a new record if an entry does not exist in AR System form. Skips the record, if an entry already exists in an AR System form. The Insert Only mode ignores the Use Checksum flag and adds a new entry.
Update Record	Performs an update as well as insert operation, based on the Checksum conditions or Checksum computation if Checksum is enabled.
Match By Request ID	Select to match output rows by Request ID. When you select the Match by Request ID checkbox, a new entry is created when the Request ID is not provided for the input row.
Multi Match Option	Select when more than one record can matche the given qualification.
Configure Matching Qualification	Click to configure a matching qualification in the qualification helper dialog.

Field Mapping tab

You can map the output fields from previous steps, or stream fields, to their respective AR System form fields, using the Field Mapping tab. You can also exclude a particular field from computing checksum. By default all fields are included in the checksum calculation.

Best practice
We recommend that you exclude the fields that participates in the qualification and the fields that are known to have a unique value for the Checksum computation.

The following screenshot illustrates the Field Mapping tab in the AR Upsert step:

The Edit Mapping button provides a helper dialog to map the fields.

The following screenshot illustrates the Enter Mapping dialog from the Field Mapping tab:

The following table shows AR Upsert data type conversions:

Atrium Integrator adapter data type	AR System data type
None	Null
Number	Real
String	Char
Date	Date
Boolean	Enum
Integer	Ulong
Bignumber	Decimal
Binary	Attach

AR Upsert step

Overview of Checksum mechanism

General tab

Field Mapping tab

On this page