Key-Value Mapping in CPI for huge data sets has never been easier
I am very fond of SAP’s undertaking to bring all Cloud Platform Integration (CPI) functions to WebUI to be edited in the cloud. However creating Key-Value Mapping artifacts with more than twenty entries can be a real pain.
SAP offers a CSV upload function, which is primarily targeted at former SAP Process Integration (PI) customers who want to reuse their mappings on CPI now.
You can have look here to learn more about the CSV structure here: https://blogs.sap.com/2018/04/26/sap-cloud-platform-integration-csv-import-in-value-mapping/
This works well if you have unique values and cleansed data sets but what if you want maintain a country mapping and you have something like this:
Frankreich -> FR
FRANKREICH -> FR
France -> FR
In this case you need to assign a default mapping. The import function described above does not take care of that. It even breaks if you want to upload values, which need default mapping. In addition to that WebUI gets very slow or even goes through several timeout cycles in case you upload several hundred values. Worst case would be, you can’t even open the artifact anymore due to its size.
Long story short, I decided to create a script that creates fully blown key value mapping artifacts with default mapping and all those nice things.
To do so I downloaded an existing artifact to learn about its structure.
There is some meta-data describing the artifact, a project file and the actual mapping structured as xml.
<?xml version="1.0" encoding="UTF-8"?> <vm version="2.0"> <group id='eaeb8f32-b90d-4529-b145-ce667ba5a02f'> <entry isDefault='true'> <agency>id</agency> <schema>country</schema> <value>Frankreich</value> </entry> <entry> <agency>sap</agency> <schema>code</schema> <value>FR</value> </entry> </group> … </vm>
To get a valid mapping we need to generate UUIDs for the group tag, add an xml attribute for the default entry and generate the actual entries. The script always chooses the first entry as a default for simplicity reasons.
In addition to that some basic data cleansing would be nice. God knows what kind of crappy CSV files come your way that need uploading to the cloud. I started with some basic trimming, empty string and null value handling. You can find my groovy script here.
I did it in groovy because it is likely most SAP integration developers are familiar with it and have the required dev-environment running.
So here is the whole process in a nutshell:
- Create a new key value mapping artefact in CPI and download it as an empty structure for you to fill. I find this the easiest and most failure proof way of doing it.
- Change the filename on line 8 and the agency/schema names on the loop according to your needs and execute the script
- Take the xml output and merge it with the existing one in the downloaded structure (value_mapping.xml file)
Upload the new artifact to CPI. You can also import into existing ones by clicking “View Metadata” -> Edit and selecting the file chooser
Have fun creating huge key-value maps in no time and feel free to build upon the script to make it even more useful.
as I told you yesterday, I used your Skript and put it in an integration Flow to build a small service that provides the xml.
Key-Value Mapping in SAP Cloud Platform Integration (CPI) with an integration Flow
Thanks for the input.
Great idea! Thanks for sharing