Introduction

It is possible to setup a pipeline so that data loaders can resolve failed lookups in an interactive manner by either creating synonms or by adding new rows to lookup tables. This can happen during data loading (preview mode) or after data loading (committed batch).

By “failed lookups” we mean any issues reported by these pipeline commands: MartLookup, HierarchyLookup and SysIDLookup.

For example, in the issue below “South Sudans” (with an extra “s” at the end) was not found in the REF_COUNTRY table and the UI proposes to the user to either add a synonym to “South Sudan” or to add a new country to the country table.

south_sudans

How to setup

How can you do this in a pipeline?

Add rows to lookup tables UI

To allow a user to add rows to lookup tables:

  • Use either the MartLookup, HierarchyLookup and SysIDLookup commands.
  • Give the user DATA_EDIT rights on the lookup table.

In the example Xml below, the source data column DESTINATION_COUNTRY must have country names that exactly match the values in the NAME_SHORT_EN column of the REF_COUNTRY table in order to successfully lookup the ISO 3 value. If a value in DESTINATION_COUNTRY does not match any values in the NAME_SHORT_EN column and if the user has DATA_EDIT rights on REF_COUNTRY, they will be prompted to add a new record to the REF_COUNTRY table.

Note that no special configuration is needed other than using the MartLookup command and DATA_EDIT rights on REF_COUNTRY.

<MartLookup LookupTable="REF_COUNTRY"
            SourceColumns="DESTINATION_COUNTRY"
            LookupColumns="NAME_SHORT_EN"
            LookupResultColumn="CODE_ISO_3"
            SourceResultColumn="ISO_3">
</MartLookup>

To prevent a user from being able to edit REF_COUNTRY, remove DATA_EDIT rights from REF_COUNTRY for that user.

Add rows to a synonym table UI

More typically, data loaders should create synonyms for new alternative spellings to existing lookup data, rather than extend the lookup data itself.

To do that, first locate or setup a synonym table. A synonym table is just a normal table with 2 columns that maps raw/incoming values to standardized values. You can read more about synonym tables.

Here is an example synonym table named REF_SYNONYMS which maps alternate spellings of country names in the INPUT column to standardized country names in the OUTPUT column.

ref_synonyms

Then add a SynonymTable command to our example MartLookup command.

<MartLookup LookupTable="REF_COUNTRY"
            SourceColumns="DESTINATION_COUNTRY"
            LookupColumns="NAME_SHORT_EN"
            LookupResultColumn="CODE_ISO_3"
            SourceResultColumn="ISO_3">
    <SynonymTable Code="REF_SYNONYMS" FromColumn="INPUT" ToColumn="OUTPUT" />
</MartLookup>

The freetext values in the source column DESTINATION_COUNTRY which do not directly match values in REF_COUNTRY’s NAME_SHORT_EN column will attempt to be matched to values in the INPUT column of the synonym table. If a match is found, the OUTPUT values will be returned from the synonym table and used to match values in the REF_COUNTRY’s NAME_SHORT_EN column.

If a match is not found and if the data loader has DATA_EDIT rights on REF_SYNONYMS, the data loader will be prompted in the UI to create a synonym for the unmatched value.

If the data loader does not have DATA_EDIT rights on REF_SYNONYMS, the option to create a synonym in the data loading UI is not available.