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

Question 

The only option we now see to finish applying the new canonical value is to do bulk updates to the 3 million records where which are still showing the old values. How can this be performed?

The resolveLookupCode property is set to true and a reindex will not correct the lookup values as expected.

Answer

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.  

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.

mceclip0.png

mceclip1.png

  • 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).

mceclip2.png

  • 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:

mceclip4.png

  •  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

mceclip0.png

mceclip1.png

mceclip2.png

 

 

 

  • 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.

 

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.