Configuring Reference Mappings to Optimise Performance

This article relates to ServiceNow->ServiceNow migrations only

Do you need a Reference Mapping?

You only need a reference mapping if the referenced records on the source and target server have (or may have) different sys-id values. For many system tables, or if the reference table has been migrated with Precision Bridge using the Maintain Sys-ID option, the records will have the same ID on both source and target. In this case, a simple mapping will suffice and this is very efficient.

Identifying the Best Strategy for Mapping Reference Fields:

Performance of Reference Mappings depends on:

1. Unique Source ID Count – The number of unique source ID’s to be reference mapped.
2. Reference Mapping Source Count – The number of references that need to be mapped in the project with the given reference mapping.
3. Referenced Data Set Size – The number of records in the referenced table. (or a subset of these defined by a source filter in the reference mapping)

Using a Form mapping that was Previously used to Migrate Data

This is the most efficient use of reference mappings and where you are migrating ‘child’ records for a previously migrated ’parent’ you should always use this type of mapping since all the migrated parent records will already have their id’s mapped in the id cache so no source/target queries will be needed at all.

Best Practice:

Always use this method if:

• All the references being mapped refer to records already migrated in the earlier form mapping
• Over 50% of the references being mapped refer to records already migrated in the earlier form mapping and the key mappings used include only simple, assignment, value match or reference mappings (where the id being mapped will already have been cached in the id mapping cache during an earlier form mapping)

You should always consider using this method if:

The key mappings used include only simple, assignment, value match or reference mappings (where the id being mapped will already have been cached in the id mapping cache during an earlier form mapping)

AND

The ratio Reference Mapping Calculation Count  : Unique Source ID Count is likely to be over 100:1

 

Using a Disabled Reference Mapping

This mapping can be created as follows

1. Create a form mapping for the reference table, mapping only the key fields. Do NOT use Lookups, ID List Mappings, ID Replacement Mappings or Temporary Variables in the mappings.
2. Disable the Reference Mapping so that it does not migrate any data.
3. Move the form mapping to a point in the project where it is above any form mappings that contain references to this table that need to be mapped.
4. Map the reference field using a reference mapping that references the form mapping created above

This is very efficient where in the reference values the number of unique source id’s to be mapped is small, but the number of records being migrated is large. For example:

• Setting the same domain for all records
• Setting the same company for all records
• Setting a category value for 1000 records where there are only 10 categories in existence.

Remember that if the referenced key mapping contains any of the following, it will NEVER return a match (even if one exists)

• LOOKUPS
• VARIABLE REFERENCES
• ID LIST Mappings
• ID REPLACEMENT Mappings

Also remember that if the key mapping contains other reference mappings, it will only return a value if the referenced id is already cached.

Best Practice:

Only use disabled key mappings where:

The Key Mappings consists of Simple, Assignment, Value Match and Reference Mappings where the id mapping will already be cached when executing the reference form mapping.

AND

the ratio Reference Mapping Calculation Count : Unique Source ID Count is likely to be over 100:1

OR

Reference Mapping Calculation Count<10

 

Using an Enabled Reference Mapping where all Records are Skipped

This method again just maps the key fields. Unlike the Disabled Reference Mapping option, all mapping types can be used in the key mapping.

1. Create a form mapping for the reference table, mapping only the key fields.
2. To prevent records actually getting migrated, on the Options tab for the form mapping, set the options Entry Exists on Target Action and No Entry Exists on Target Action to ‘Skip Entry’
3. Move the form mapping to a point in the project where it is above any form mappings that contain references to this table that need to be mapped.
4. Map the reference field using a reference mapping that references the form mapping created above
5. Optionally, add a source filter to limit the number of source records that will be processed, as long as you are sure that the set of records defined by the filter includes ALL the records that may be referenced in source records getting processed later in the project.

Best Practice:

Use this option if:

The Disabled Mapping Method will not work, or will be inefficient due to the number of unique references being calculated.

The number of records to be retrieved (and therefore cached) is less than 50,000

The ratio Reference Mapping Calculation Count  : Referenced Data Set Size is likely to be over 5:1

 

Use a Source and Target Lookup Instead

This method should be used if none of the other methods fit. It is best suited to a situation where one field’s value uniquely identifies the source and target record. For example ‘Number’ on Task Records. It can however be adapted to much more complex situations.

The example below assumes we want to map a reference to the Incident table. Incidents on source and target can be identified by their Number, which is unique and the same on both servers.

1. Define a Temporary Variable ‘Incident Number’ of type String
2. Define a Lookup on the Source Incident table to obtain the Incident with the sys_id given in by the source reference value
3. Map the Temporary Variable using the Lookup defined in (1) to store the Number of the source incident
4. Create a second lookup query on the target Incident form. This should use the Temporary Variable value to obtain the target record of the same Number
5. Map the Incident Reference field using the lookup created in (4) to set the value to the sys_id of the looked up target record.

This is usually fairly efficient, since the source and target lookups are batched with 100 lookups being performed per server call. To improve performance in large tables, look at the Indexing for the field.

If multiple fields are used to map the key, you will need additional temporary variables to store the additional values from the source query, and the target query will need to have multiple conditions.

Best Practice:

Use this method where no other options fit.