How can I perform a bulk update to change lookup code and lookup value in the MDM for relations?

gloria.faley

Updated January 04, 2023 22:27

Question

How can we finish applying the new canonical value for a relations? The resolveLookupCode property is set to true and a reindex did not correct the lookup values as expected.

Answer

You need to perform a bulk update to change lookup code and lookup value in the MDM for relations.

Relations Lookup Change/Relations Update

"attributes": [
                        {
                            "label": "Code",
                            "name": "Code",
                            "type": "String",
                            "hidden": false,
                            "important": false,
                            "system": false,
                            "required": false,
                            "attributeOrdering": {
                                "orderingStrategy": "LUD"
                            },
                            "uri": "configuration/relationTypes/OrgToAccount/attributes/Codes/attributes/Code",
                            "lookupCode": "rdm/lookupTypes/CODE",
                            "skipInDataAccess": false
                        },

Option 1 - Update with POST /relation API

Perform a search of relations with a specific lookup code.

GET https://<environment>.reltio.com/reltio/api/<tenantId>/relations?filter=equals(attributes.Roles.RoleCode,'EMPE')

Use POST /relations (limited to 50 profiles per batch).

Execute the following

POST https://<environment>.reltio.com/reltio/api/<tenantId>/relations

Request body:

[{
       
        "type": "configuration/relationTypes/IndividualToAccount",
        "attributes": {
            "Roles": [
                {
                    "label": "Employee",
                    "value": {
                        "Code": [
                            {
                                "type": "configuration/relationTypes/IndividualToAccount/attributes/Roles/attributes/RoleCode",
                                "ov": true,
                                "value": "Annuitant",
                                "lookupCode": "ANN",
                                "lookupValue": "Annuitant",
                                "uri": "relations/0002B7c/attributes/Roles/LWxSPMi/Code/LWxSTcy"
                            }
                        ]
                }
            ]
        },
        "crosswalks": [
            {

                "type": "configuration/sources/2",
                "value": "5658118|V000444731|PAR"
            }
        ],
        "startObject": {
            "objectURI": "entities/0rCGSlo"
       
        },
        "endObject": {
            "objectURI": "entities/07rfiXU"
        }
    }
]

Option 2: Using the ROC data loader facility for a bulk update.

Here you can upload the entire revised text file. The ROCS will slice relations and will process them in 50 records per batch.
Download code from https://bitbucket.org/reltio-ondemand/util-rdm-dataloader/src/master/.
You should use the following configuration property file.

RECORDS_PER_POST=50
THREAD_COUNT=10
FAILED_RECORD_FILE_PATH=c:\\localhost\Failed.json
JSON_FILE_TYPE=ARRAY 
DATALOAD_SERVER_HOST= test-dataload.reltio.com
TENANT_ID=<tenant>
DATALOAD_TYPE=Relations
TYPE_OF_DATA=configuration/relationTypes/IndividualToAccount
USERNAME=<Reltio Username>
PASSWORD=<Reltio Account password>
MAX_QUEUE_SIZE=300000
AUTH_URL= https://auth.reltio.com/oauth/token
IS_PARTIAL_OVERRIDE=FALSE

It is important that the input JSON being used for the ROCS utility, each relations payload must be on the same line.

[{"type": "configuration/relationTypes/IndividualToAccount","attributes": {"Roles": [{"label": "Employee","value": {"Code": [{"type": "configuration/relationTypes/IndividualToAccount/attributes/Roles/attributes/RoleCode","ov": true,"value": "Annuitant","lookupCode": "ANN","lookupValue": "Annuitant","uri": "relations/0002B7c/attributes/Roles/LWxSPMi/Code/LWxSTcy"}]}]},"crosswalks": [{"type": "configuration/sources/2","value": "5658118|V000444731|PAR"}],"startObject": {"objectURI": "entities/0rCGSlo"},"endObject": {"objectURI": "entities/07rfiXU"}}]

Option 3: Using the out-of-box data loader in the UI (recommended for large volume).

RDM looks like before changes are made.

This is what the RDM mapping looks like after the update. The same value from Vantage - “Head(H)” is now mapped to a new canonical value/code = “HD” ( instead of the old value of HOD).

We will reload the same file for the Org Account relationship without any change. Note that Vantage is still sending value as “Head(H)”.
After the data reload, the MDM hub looks like this:

The role code value has been changed to HD after reload as per the new RDM mapping even though the source data was the same.

How to create this file to upload

We can export the relationship (for a specific type of relation and specific attribute value) using the following API

curl --location --request POST 'https://Environment.reltio.com/jobs/export/<tenant>/relations
?filter=equals(attributes.<attribute type>, '<attribute value>')&exploded=true
&distributed=true&fileFormat=JSON' \
--header 'Authorization: Bearer e7708cd2-6218-40e1-9997-9ffc5e4ef85d' \
--header 'Content-Type: application/json' \
--data-raw '{
  "ovOnly": true,
  "includeType": [
      "configuration/relationTypes/<relationType>"
  ],
  "fileFormat": "JSON"
}'

Note: Other options can be applied (see https://docs.reltio.com/export/exportrelations.html).

This is what the export file looks like. In this case, we are change Owner to Payee in the JSON file.

{
  "uri": "relations/kvCLRAV",
  "type": "configuration/relationTypes/IndividualToAccount",

  "createdTime": 1596021549392,

  "updatedTime": 1596021549392,
  "startRefPinned": false,
  "startRefIgnored": false,
  "endRefPinned": false,
  "endRefIgnored": false,
  "attributes": {
    "Roles": [
      {
        "label": "Owner",
        "value": {
          "RoleCode": [
            {
              "type": "configuration/relationTypes/IndividualToAccount/attributes/Roles/attributes/RoleCode",
              "ov": true,
              "value": "Owner",
              "lookupCode": "OWN",
              "lookupValue": "Owner",
              "uri": "relations/kvCLRAV/attributes/Roles/2W714EZ1r/RoleCode/2W714EdI7"
            }
          ]
        },
        "ov": true,
        "uri": "relations/kvCLRAV/attributes/Roles/2W714EZ1r"
      }
    ]
  },
  "crosswalks": [
    {
      "uri": "relations/kvCLRAV/crosswalks/2W714EhYN",
      "type": "configuration/sources/13",
      "value": "2809|SC10000114|O",
      "reltioLoadDate": "2020-07-29T11:19:09.392Z",
      "createDate": "2020-07-29T11:19:09.392Z",
      "updateDate": "2020-07-29T11:19:09.392Z",
      "attributes": [
        "relations/kvCLRAV/attributes/Roles/2W714EZ1r/RoleCode/2W714EdI7",
        "relations/kvCLRAV/attributes/Roles/2W714EZ1r"
      ],
      "singleAttributeUpdateDates": {}
    }
  ],
  "startObject": {
    "type": "configuration/entityTypes/Individual",
    "objectURI": "entities/1a6AKHIr",
    "label": "",
    "directionalLabel": "Has Policy",
    "crosswalks": [
      {
        "uri": "entities/1a6AKHIr/crosswalks/3Xb7QkydZ",
        "type": "configuration/sources/13",
        "value": "2809",
        "reltioLoadDate": "2020-07-29T10:45:39.281Z",
        "createDate": "2020-07-29T08:13:00.496Z",
        "updateDate": "2020-07-29T08:13:00.496Z",
        "attributes": [
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu/PostalCode/2ncJverJg",
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu/AddressID/2ncJvf46S",
          "entities/1a6AKHIr/attributes/Identifiers/2W6zc6mhr/Type/2W6zc6qy7",
          "entities/1a6AKHIr/attributes/LastName/3Xb7QkdKH",
          "entities/1a6AKHIr/attributes/MiddleName/3Xb7QkhaX",
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu/StateProvince/2ncJven3Q",
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu/AddressType/2ncJvezqC",
          "entities/1a6AKHIr/attributes/Identifiers/2W6zc6mhr",
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu",
          "entities/1a6AKHIr/attributes/DoB/3Xb7QkZ41",
          "entities/1a6AKHIr/attributes/FirstName/3Xb7QkUnl",
          "entities/1a6AKHIr/attributes/Identifiers/2W6zc6mhr/ID/2W6zc6vEN",
          "entities/1a6AKHIr/attributes/Name/3Xb7QkuNJ",
          "entities/1a6AKHIr/attributes/PartySince/3Xb7QkQXV",
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu/AddressLine1/2ncJveinA",
          "entities/1a6AKHIr/attributes/PartyLifeCycleStatus/3Xb7Qkq73",
          "entities/1a6AKHIr/attributes/Status/3Xb7QkMHF",
          "entities/1a6AKHIr/attributes/SourceSystem/3Xb7Qklqn",
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu/City/2ncJvevZw",
          "entities/1a6AKHIr/attributes/Identifiers/2W6zc6mhr/IdentifierID/2W6zc6zUd"
        ],
        "singleAttributeUpdateDates": {
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu/PostalCode/2ncJverJg": "2020-07-29T08:42:38.932Z",
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu/AddressID/2ncJvf46S": "2020-07-29T08:42:38.932Z",
          "entities/1a6AKHIr/attributes/Identifiers/2W6zc6mhr/Type/2W6zc6qy7": "2020-07-29T10:45:39.281Z",
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu/StateProvince/2ncJven3Q": "2020-07-29T08:42:38.932Z",
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu/AddressType/2ncJvezqC": "2020-07-29T08:42:38.932Z",
          "entities/1a6AKHIr/attributes/Identifiers/2W6zc6mhr": "2020-07-29T10:45:39.281Z",
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu": "2020-07-29T08:42:38.932Z",
          "entities/1a6AKHIr/attributes/Identifiers/2W6zc6mhr/ID/2W6zc6vEN": "2020-07-29T10:45:39.281Z",
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu/AddressLine1/2ncJveinA": "2020-07-29T08:42:38.932Z",
          "entities/1a6AKHIr/attributes/SourceSystem/3Xb7Qklqn": "2020-07-29T10:45:39.281Z",
          "entities/1a6AKHIr/attributes/Addresses/2ncJveeWu/City/2ncJvevZw": "2020-07-29T08:42:38.932Z",
          "entities/1a6AKHIr/attributes/Identifiers/2W6zc6mhr/IdentifierID/2W6zc6zUd": "2020-07-29T10:45:39.281Z"
        }
      }
    ]
  },
  "endObject": {
    "type": "configuration/entityTypes/Account",
    "objectURI": "entities/1FKzZyP4",
    "label": "SC10000114 - VPS",
    "directionalLabel": "Policy",
    "crosswalks": [
      {
        "uri": "entities/1FKzZyP4/crosswalks/2S5RccBcA",
        "type": "configuration/sources/13",
        "value": "SC10000114",
        "reltioLoadDate": "2020-07-29T08:23:48.155Z",
        "createDate": "2020-07-29T08:23:48.155Z",
        "updateDate": "2020-07-29T08:23:48.155Z",
        "attributes": [
          "entities/1FKzZyP4/attributes/AccountType/2S5Rcc35e",
          "entities/1FKzZyP4/attributes/EffectiveStartDate/2S5RcbypO",
          "entities/1FKzZyP4/attributes/AccountNumber/2S5Rcc7Lu",
          "entities/1FKzZyP4/attributes/AccountAdminSourceSystem/2S5RcbqIs",
          "entities/1FKzZyP4/attributes/AccountStatus/2S5Rcbm2c",
          "entities/1FKzZyP4/attributes/SourceSystem/2S5RcbuZ8"
        ],
        "singleAttributeUpdateDates": {}
      }
    ]
  }
}

The export contains canonical “HD” as role code because of resolvelookupcode=’True’
Mapping definition

We can save the mapping to execute this test again and we can change the value to ANN.

Points to be noted:

We have not considered match-merge/survivorship for the relationship attribute. If a customer tenant has that complexity this needs to be considered (refer to https://docs.reltio.com/matchmerge/merginginreltio.html).
If we are using an export file to upload data - we have to remember that the file will have a crosswalk for a relationship, start entity, and end entity but it does not have source information and contains a canonical form for lookup. We would need to make these updates while loading the data.

Disclaimer: The execution of this data load needs technical understanding.