Matching in the Cool Data Mover

The combination of match attributes should uniquely identify a record in the database. So, in the first example above an aircrafttype is uniquely identified by its iatacode. Meaning that when we query the database for a specific iatacode we get a maximum of 1 record. The second example shows that we can also specify a combination of match attributes; the manufacturer and type combination should also uniquely identify an aircrafttype.

Ideally, we would check for the existence of a record by its identifier. However, most entities have an identifier attribute which is autonumber. This means we have no control over its value and ‘the same’ record is likely to have a different id in different environments.
Following the example above, the aircraft type Boeing 747-400 could have id 10 in Production and have id 15 in Acceptance. This means autonumber identifiers are unfit to use in matching. The match attribute iatacode however, will have the same value (744) in both environments and is therefor fit to use for matching. Of course, the same is true for the manufacturer (‘Boeing’), type (‘747-400’) combination.

Static entities

During export and import static entities needs to be included in the package when they are referred to by other entities. Since the records in the entity are controlled by OutSystems, the Cool Data Mover will never insert or delete static entity records. However, there is the need to uniquely match static entity records because they are referred to and the static entity can have an autonumber identifier.

Luckily, OutSystems keeps an internal administration of static entity records. In that administration there are uniquely identifying attributes which Cool Data Mover uses as matching attributes. Therefor there is no need to specify the match attributes manually.

Lookup entities

By definition, records of lookup entities will have to be matched during import. Therefor it is mandatory to specify match attributes for lookup entities in the import package. The Cool Data Mover will show an error indication (Match attributes!) next to the entity name when no match attributes are specified.

Move entities

Import packages have the ‘Behaviour of move entities’ setting which determines whether matching (and match attributes) is used during import.

This setting can have two values. Either ‘Perform a lookup using specified match attributes, only insert a new record when no match is found’ or ‘Do not perform a lookup, always insert a new record’.

The first option will perform a lookup during import for all records of all move entities. To be able to do this, match attributes need to be specified for all move entities. The Cool Data Mover will show an error indication (Match attributes!) next to the entity name when no match attributes are specified.

The second option will not perform lookups for move entities and all records are inserted as new records in the database. This option is normally only used when the target entities tables are empty.

Finding the correct match attributes

The Cool Data Mover helps you in finding the correct combination of match attributes.

In some cases that Cool Data Mover can automatically determine the correct match attributes. It will then suggest that (combination of) match attributes. However, you are free to change the suggested match attributes.

For instance, when the identifier is not an autonumber attribute, the identifier attribute is marked as a match attribute. A special case of this is when an entity is an extension of another entity without an autonumber identifier.

When you have specified the match attributes there is a link in the attributes section of the entity that allows you the validate the match attributes. By validating the Cool Data Mover determines whether the specified match attributes uniquely identify the records in the database. Although this provides a good indication whether the correct match attributes are specified, you must also keep in mind that the validation has taken place against the current content of the table in that environment. So, the result could potentially be different if the same validation is performed but with a different content of the table or on a different environment. The validation is a handy tool but nothing more than that.

The best way to determine the match attributes is to first search for a functional key. So, a combination of attributes which functionally identifies a record. If such a combination exists, then that must be used as match attributes. In our example the iatacode (or manufacturer, type combination) is such a key and therefor a good candidate for match attributes. Ideally there is a unique index on the entity. The attributes in that index can be used a match attributes.

Tip: The less matching attributes, the better!

Be careful not to ‘just keep adding’ matching attributes to achieve uniqueness. This is normally a sign that a functional key is not present. For example, if you add attributes CreatedBy and CreatedOn then the matching attributes will most likely uniquely identify the records. However, they will probably not match with any record in the destination environment.

No match attributes, now what?

Only when no (correct) combination of matching attributes can be found, you could consider introducing a new attribute especially for matching. A text attribute containing a GUID is ideal for that.

Since it is a new attribute the records in the database will have no value in that new GUID attribute. We recommend creating a timer which populates the GUID attribute by using the GenerateGUID() action. Be sure to

1. Run the timer to populate GUIDs only in the source environment.
2. Export data from the source environment.
3. Delete all records from the destination environment.
4. Import data into the destination environment (NOT using matching).

The export (2) and import (4) will make sure the generated GUIDs (1) are the same in the source and destination environments. After this the GUID can be used as matching attribute.

Data pollution

In some cases, you would expect a certain combination of attributes to uniquely identify the records (and thus being a good candidate for matching attributes) but in reality, they don’t.

Normally, that is a sign that the data has been polluted. Maybe because of ‘old’ records not satisfying new business rules or because of a missing unique index or even because of a bug in the application (it happens;). No matter the cause, it is always a good idea to see if you can correct the data and make the matching attribute a unique identifier before introducing a GUID.

An annoying example of this is the system user entity in the Service Center module. You would expect the tenant_id, username combination to be unique but that is not always the case. When adding users via the users application you will not be able to create those duplicate users, but when creating users programmatically from within your own applications, creating those duplicate users is easily done. In this case the root case if a missing unique index on the combination of tenant_id, username. Since this lies within the Service Center module it is also not easily corrected. We recommend cleaning up your user database in case you encounter this situation.

Matching on an autonumber identifier

Suppose that we want to import a package into the same environment as that we exported it from. In that case the identifier is the ideal candidate to use as a matching attribute. Even if the identifier is autonumber!

For this specific situation only, the Cool Data Mover has got a package setting ‘Allow matching on autonumber id’ which enables the use of autonumber attributes as matching attributes.

What attributes can be used for matching

• Attributes that are anonymized, skipped, or replaced can not be used a match attributes.
• Autonumber attributes can not be used as match attributes.
  The only exception being when the setting ‘allow matching on autonumber id’ is selected because export and import take place in the same environment.
• Attributes of all data types can be selected as match attributes.
  Note: we highly recommend to NOT use binary attributes as matching attributes as we are planning to discontinue support for this.
• Foreign keys and soft keys can be used as matching attributes.