Value Mapping artifacts in SAP Cloud Platform Integration
In this blog I will summarize existing options for maintenance of Value Mapping artifacts in SAP Cloud Platform Integration and describe additional possibilities available with recent release (04-2020).
You will learn:
- What is a Value Mapping artifact in SAP Cloud Platform Integration
- How to upload it from CSV and zip files
- How to automate maintenance of Value Mapping with available APIs
Value Mapping artifact is used in SAP Cloud Platform Integration to map values between different representations or formats. Example use cases for Value Mapping are:
- Mapping language codes, units of measures which are represented differently in source and target system
- Mapping product codes, order types, reason codes.
Value Mapping artifacts are created and configured in SAP CPI Web UI:
Value Mapping maintenance screen
Working with large Value Mapping datasets in Web UI can be challenging in terms of manual creation and ongoing maintenance effort.
Recently SAP released an OData API to access Value Mappings:
Source https://help.sap.com/ – CPI Release Notes
I decided to review it and check if it brings any significant changes in this area.
Below I will summarize existing options which can be used to automate the Value Mapping maintenance process and describe the new possibilities which come with API addition.
1. Upload of Value Mapping pairs from CSV
It is possible to upload value mapping mairs from CSV files.
See the blog: https://blogs.sap.com/2018/04/26/sap-cloud-platform-integration-csv-import-in-value-mapping/ from Deepak Govardhanrao Deshpande for details.
This approach doesn’t work very well in case of one to many mappings where you upload multiple target values for the same source value. This issue and workaround is described in SAP Support Note 2866537( Unable to import CSV file while configuring value mapping on CPI tenant ) . The workaround involves manipulation of source file and manual update of target values in CPI web UI after the upload which is not very practical if you have hundreds of values to be mapped.
2. Download artifact as zip and update enclosed value_mapping.xml
The option to download Value Mapping artifact and upload of updated value_mapping.xml was described in blog https://blogs.sap.com/2018/07/23/key-value-mapping-in-cpi-for-huge-data-sets-has-never-been-easier/ from Martin Pankraz .
Dominic Beckbauer further developed this idea to wrap the process in an iflow and described it in https://blogs.sap.com/2018/07/24/key-value-mapping-in-sap-cloud-platform-integration-cpi-with-an-integration-flow/.
This approach is a big improvement but requires manual download and upload of artifacts and working with zip files.
3. (NEW) Use Value Mapping OData V2 API
Value Mapping OData V2 API is available as of 2020-04-11
Following operations are supported by the API:
-
Query All Value Mappings in the tenant
GET https://<account>-tmn.hci.eu2.hana.ondemand.com/api/v1/ValueMappingDesigntimeArtifacts?$format=json
Example response for this package:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
{ "__metadata": { "id": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/api/v1/ValueMappingDesigntimeArtifacts(Id='ValueMapping1',Version='1.0.0')", "uri": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/api/v1/ValueMappingDesigntimeArtifacts(Id='ValueMapping1',Version='1.0.0')", "type": "com.sap.hci.api.ValueMappingDesigntimeArtifact", "content_type": "application/octet-stream", "media_src": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/api/v1/ValueMappingDesigntimeArtifacts(Id='ValueMapping1',Version='1.0.0')/$value", "edit_media": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/api/v1/ValueMappingDesigntimeArtifacts(Id='ValueMapping1',Version='1.0.0')/$value" }, "Id": "ValueMapping1", "Version": "1.0.0", "PackageId": "TMint4ValueMappingExamples", "Name": "ValueMapping1", "Description": " ", "ArtifactContent": null }, { "__metadata": { "id": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/api/v1/ValueMappingDesigntimeArtifacts(Id='ValueMapping1.1',Version='1.0.0')", "uri": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/api/v1/ValueMappingDesigntimeArtifacts(Id='ValueMapping1.1',Version='1.0.0')", "type": "com.sap.hci.api.ValueMappingDesigntimeArtifact", "content_type": "application/octet-stream", "media_src": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/api/v1/ValueMappingDesigntimeArtifacts(Id='ValueMapping1.1',Version='1.0.0')/$value", "edit_media": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/api/v1/ValueMappingDesigntimeArtifacts(Id='ValueMapping1.1',Version='1.0.0')/$value" }, "Id": "ValueMapping1.1", "Version": "1.0.0", "PackageId": "TMint4ValueMappingExamples", "Name": "ValueMapping1.1", "Description": " ", "ArtifactContent": null } |
Filtering by package is not supported.
Following query:
https://<account>-tmn.hci.eu2.hana.ondemand.com/api/v1/ValueMappingDesigntimeArtifacts?$filter=PackageId eq ‘TMint4ValueMappingExamples’
Returns Not Implemented Error:
|
1 2 3 4 5 6 7 |
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"> <code>Not Implemented</code> <message xml:lang="en">Not implemented</message> </error> |
It is however possible to navigate to Value Mappings from Integration Package entity:
https://<account>-tmn.hci.eu2.hana.ondemand.com/api/v1/IntegrationPackages(‘TMint4ValueMappingExamples’)/ValueMappingDesigntimeArtifacts
Returns Value Mappings in selected ‘TMint4ValueMappingExamples’ package.
-
Read details of single Value Mapping
GET https://<account>-tmn.hci.eu2.hana.ondemand.com/api/v1/ValueMappingDesigntimeArtifacts(Id=’ValueMapping1′,Version=’1.0.0′)?$format=json
This request provides only basic metadata information about the mapping:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
{ "d": { "__metadata": { "id": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/api/v1/ValueMappingDesigntimeArtifacts(Id='ValueMapping1',Version='1.0.0')", "uri": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/api/v1/ValueMappingDesigntimeArtifacts(Id='ValueMapping1',Version='1.0.0')", "type": "com.sap.hci.api.ValueMappingDesigntimeArtifact", "content_type": "application/octet-stream", "media_src": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/api/v1/ValueMappingDesigntimeArtifacts(Id='ValueMapping1',Version='1.0.0')/$value", "edit_media": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/api/v1/ValueMappingDesigntimeArtifacts(Id='ValueMapping1',Version='1.0.0')/$value" }, "Id": "ValueMapping1", "Version": "1.0.0", "PackageId": "TMint4ValueMappingExamples", "Name": "ValueMapping1", "Description": " ", "ArtifactContent": null } } |
-
Download Value Mapping
GET https://<account>-tmn.hci.eu2.hana.ondemand.com/api/v1/ValueMappingDesigntimeArtifacts(Id=’ValueMapping1′,Version=’1.0.0′)/$value
This request downloads Value mapping artifact as zip file. It is the same format as provided by download option in Web UI:
-
Upload Value Mapping
This request uploads Value Mapping artifact. Zip content has to be base64 encoded and provided in ArtifactContent property. X-CSRF-Token has to be provided in request header.
POST /ValueMappingDesigntimeArtifacts:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
{ "Name" : "ValueMapping1.2", "Id" : "ValueMapping1.2", "PackageId" : "TMint4ValueMappingExamples", "Description" : "Uploaded Mapping", "ArtifactContent" : "base64 encoded zip file" } |
Unfortunately there is no option to update or overwrite existing mapping with this API.
Sending the request again for the same Value Mapping results in:
|
1 2 3 4 5 6 7 8 9 |
<?xml version='1.0' encoding='UTF-8'?> <error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"> <code>Bad Request</code> <message xml:lang="en">"Create of Value Mapping failed as Id: ValueMapping1.2 already exists"</message> </error> |
Adding Overwrite=true url parameter which is available for IntegrationPackages entity set results in:
|
1 2 3 4 5 6 7 8 9 |
<?xml version='1.0' encoding='UTF-8'?> <error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"> <code>Not Implemented</code> <message xml:lang="en">Not implemented</message> </error> |
-
Deploy Value Mapping
POST https://<account>-tmn.hci.eu2.hana.ondemand.com/api/v1/DeployIntegrationDesigntimeArtifact?Id=’ValueMapping1.1’&Version=’1.0.0′ with empty body deploys the Value Mapping:
Summary: New OData V2 API for Value Mapping enables automation of mapping maintenance and could be used to further enhance already described methods of value_mapping.xml update.
I see two biggest limitations of this APi:
- Lack of direct access to mapping values – you still have to work with zip files
- No support of updating/overwriting existing mapping. Only new mappings can be uploaded.
Official API documentation
-
(BONUS) Unofficial API for Value Mappings
OData V2 API for Value Mapping is a step in the right direction but it still lacks some important pieces to provide a way to conveniently manipulate large Value Mapping data sets.
I decided to explore other options and look under the hood of CPI Web UI.
CPI Web UI uses workspace services available under:
- https://<account>-tmn.hci.eu2.hana.ondemand.com//itspaces/odata/1.0/workspace.svc
- https://<account>-tmn.hci.eu2.hana.ondemand.com/itspaces/api/1.0/workspace urls.
Workspace service provides convenient access to Value Mapping data sets and an easy way to update them.
Let’s take following mapping as an example:
First we use OData service to get the package and value mapping Id
GET https://<account>-tmn.hci.eu2.hana.ondemand.com/itspaces/odata/1.0/workspace.svc/ContentEntities.ContentPackages(‘TMint4ValueMappingExamples’)?$format=json
Returns details about the package including internal ID which will be used later.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
{ "d": { "__metadata": { "id": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/itspaces/odata/1.0/workspace.svc/ContentPackages('TMint4ValueMappingExamples')", "uri": "https://<account>-tmn.hci.eu2.hana.ondemand.com:443/itspaces/odata/1.0/workspace.svc/ContentPackages('TMint4ValueMappingExamples')", "type": "ContentPackageModel.ContentPackage" }, "TechnicalName": "TMint4ValueMappingExamples", "DisplayName": "TM int4 Value Mapping Examples", "ShortText": "TMint4ValueMappingExamples", "reg_id": "a1d66f708bf24c73b9b74f6fc4361a05" }, } } } |
GET
https://<account>-tmn.hci.eu2.hana.ondemand.com/itspaces/odata/1.0/workspace.svc/ContentEntities.ContentPackages(‘TMint4ValueMappingExamples’)/Artifacts?$format=json
Return list of all artifacts in the package including Value Mappings.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
{ "Name": "ValueMapping1.1", "Type": "ValueMapping", "DisplayName": "ValueMapping1.1", "SubType": null, "reg_id": "ea1b310d2b8f4854be4d00e1243015d7", "Version": "Draft", "Description": " ", "CreatedAt": "/Date(1588837254653)/", "CreatedBy": "S0017420637", "ModifiedAt": "/Date(1588847951233)/", "ModifiedBy": "", "ContentType": null, "URI": null, "State": null, } |
Then we switch to the second REST API to read the actual mapping:
GET
https://<account>-tmn.hci.eu2.hana.ondemand.com/itspaces/api/1.0/workspace/<package id>/artifacts/<mapping id>/entities/<mapping id>/valuemappings/<mapping id>
https://<account>tmn.hci.eu2.hana.ondemand.com/itspaces/api/1.0/workspace/a1d66f708bf24c73b9b74f6fc4361a05/artifacts/ea1b310d2b8f4854be4d00e1243015d7/entities/ea1b310d2b8f4854be4d00e1243015d7/valuemappings/ea1b310d2b8f4854be4d00e1243015d7
Returns Value Mapping entries:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 |
{ "version": "2.0", "valueMappingCLMS": [ { "id": "e18c2684643c421695efbe6b0d016062", "isConfigured": false, "cvmList": [ { "id": "265fdd94a6ffd2a154b30bbd73dcb9bc", "parentId": "e18c2684643c421695efbe6b0d016062", "valueMappingEntryList": [ { "value": "S1100", "isDefault": false }, { "value": "FIELD", "isDefault": false } ] }, { "id": "40173dd75b1471e51d8a5ec4aad79647", "parentId": "e18c2684643c421695efbe6b0d016062", "valueMappingEntryList": [ { "value": "S1200", "isDefault": true }, { "value": "FIELD", "isDefault": false } ] }, { "id": "5a5dc32b068f6a226f65410c820f5ee8", "parentId": "e18c2684643c421695efbe6b0d016062", "valueMappingEntryList": [ { "value": "S4200", "isDefault": false }, { "value": "SP", "isDefault": false } ] } ], "source": { "agency": "C4C", "schema": "ServiceCat" }, "target": { "agency": "S4", "schema": "DocType" } } ] } |
Same endpoint can be used to update the value mapping. You can add new entries to the JSON file with the same parent id and new GUID as id.
PUT
https://<account>tmn.hci.eu2.hana.ondemand.com/itspaces/api/1.0/workspace/a1d66f708bf24c73b9b74f6fc4361a05/artifacts/ea1b310d2b8f4854be4d00e1243015d7/entities/ea1b310d2b8f4854be4d00e1243015d7/valuemappings/ea1b310d2b8f4854be4d00e1243015d7 with modified JSON as body will update the mapping.
There are two important steps for this update to work:
- X-CSRF-Token header has to be provided or you will get 403 Forbidden error
- Value mapping has to first locked for editing in current session or you will get 500 Resource not locked error
Value mapping can be locked with:
PUT
https://<account>tmn.hci.eu2.hana.ondemand.com/itspaces/api/1.0/workspace/a1d66f708bf24c73b9b74f6fc4361a05/artifacts/ea1b310d2b8f4854be4d00e1243015d7/entities/ea1b310d2b8f4854be4d00e1243015d7/valuemappings/ea1b310d2b8f4854be4d00e1243015d7?webdav=LOCK.
Summary
Workspace API is not officially released but it provides very convenient methods to read and update Value Mappings. It enables you to fully automate maintenance of large mapping data sets. Direct access to mapping values and update of existing sets including one to many scenarios is supported.
Value Mapping JSON format required for the update request is straightforward and payload can be generated for example using Groovy script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 |
package com.tm import groovy.text.SimpleTemplateEngine import groovy.json.* class ValueMAppingJSONExampleTM { static String getId() { return UUID.randomUUID().toString().replaceAll('-','') } static String createMappingJSON() { def mappingMetadata = [id: getId(), sourceAgency:"C4C", sourceIdentifier:"ServiceCat", targetAgency:"S4", targetIdentifier:"DocType"] def mappings = [ [id: getId(), parentId:mappingMetadata.id, sourceValue: "C4C1", targetValue: "S41", targetDefault: true], [id: getId(), parentId:mappingMetadata.id, sourceValue: "C4C2", targetValue: "S442"] ] JsonBuilder builder = new JsonBuilder() builder { version "2.0" valueMappingCLMS ([ { id mappingMetadata.id isConfigured false cvmList mappings.collect { mapping -> ["id": mapping.id, "parentId" :mapping.parentId, "valueMappingEntryList": [ { value mapping.sourceValue isDefault mapping.sourceDefault }, { value mapping.targetValue isDefault mapping.targetDefault } ]] } source { agency mappingMetadata.sourceAgency schema mappingMetadata.sourceIdentifier } target { agency mappingMetadata.targetAgency schema mappingMetadata.targetIdentifier } } ]) } return JsonOutput.prettyPrint(builder.toString()) } static void main(def args){ println createMappingJSON() } } |
