Skip to Content
Technical Articles
Author's profile photo Jacob Binu

SAP Integration Suite: Merge two multi map data sets on common fields

Introduction:

We had a requirement to fetch learners’ history from LinkedIn learning API and covert this message to the format to be uploaded as learning history in SuccessFactors (SF).

I would like to share my experience is going about building this integration flow using the Integration Suite. The below fields needed to be mapped.

  1. email -> UserID.
  2. LinkedIn course ID -> SuccessFactors item ID

In this blog post I wish to share the options I reviewed to map the first case above.

The dataset containing email is fetched from LinkedIn Learning ‘Learner detail report’  API and the dataset containing the email and user ID from the SF oData service for the User entity.

 

The input xml formats:

Input: Dataset 1:

<root>
<activities>
<assetType>COURSE</assetType>
<engagementMetricQualifier>TOTAL</engagementMetricQualifier>
<engagementType>COMPLETIONS</engagementType>
<engagementValue>1</engagementValue>
<firstEngagedAt>1627639372554</firstEngagedAt>
<lastEngagedAt>1627641467848</lastEngagedAt>
<contentProviderName>LinkedIn</contentProviderName>
<contentUrn>urn:li:XXXXXXX5</contentUrn>
<name>Learning Design Thinking</name>
<email>FATxxxxxxx.om</email>
</activities>
<activities>
<assetType>COURSE</assetType>
<engagementMetricQualifier>TOTAL</engagementMetricQualifier>
<engagementType>COMPLETIONS</engagementType>
<engagementValue>1</engagementValue>
<firstEngagedAt>1627888342882</firstEngagedAt>
<lastEngagedAt>1627898079120</lastEngagedAt>
<contentProviderName>LinkedIn</contentProviderName>
<contentUrn>urn:lixxxxxxxxxxx9</contentUrn>
<name>Scrum: The Basics</name>
<email>Axxxxxxxxxxxx.COM</email>
</activities>
</root>

 

Input: Dataset 2:

<User>
<User>
<userId>Mxxxx9</userId>
<email>Fxxxxxxxxxx.COM</email>
</User>
<User>
<userId>Mxxxxxx6</userId>
<email>Axxxxxxxxxxxxxxxx.COM</email>
</User>
</User>

 

Based on the matched email ID the dataset were to be merged to add the userId.

 

Option-1: Use of Content Enricher:

The integration flow is below.

The option ‘Enrich’ is used with the key element being ’email’ where the records are matched based on the ‘Paths’ from the 2 datasets

 

Output:

The outputs with the merged data on email fields with the UserId from the 2nd dataset is below.

 

 

 

 

 

 

 

 

 

 

 

 

We subsequently used message mapping to get a flat structure as below.

 

Option-2: Using groovy script:

This option was using a combination of Content Enricher with Groovy scripts

 The Content Enricher is set for the ‘Combine’ Algorithm.

The output from the above Content Enricher is below combing the two datasets. This is a multimap file in xml

The above was merged using the groovy script below:

 

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
import groovy.xml.XmlUtil;
import groovy.util.*;

def Message processData(Message message) {
def body = message.getBody(java.lang.String);
def xml = new XmlParser().parseText(body)

def ns = new groovy.xml.Namespace('http://sap.com/xi/XI/SplitAndMerge')

//for fast search let map PERNR value to a node that contains it
def pernrMap=xml[ns.Message2][0].User[0].children().collectEntries{ [it.email.text(), it] }

//itearte msg1 -> find entry in pernrMap -> add node
xml[ns.Message1][0].root[0].activities.each{activities->
def emp = activities.email[0]

def pernr = pernrMap[emp.text()]
if(pernr) activities.appendNode('userId', pernr.userId.text() )
}

message.setBody(XmlUtil.serialize(xml));

return message

}

 

The output:

 

 

Option-3: Using XSLT:

This option was using a combination of Content Enricher like the above with XSLT mapping:

The xsl file used is below:

Note: that in the below all fields for the output are not shown to avoid repeating. Only 2 from the first dataset and the other from the second for illustration only.

 

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:multimap="http://sap.com/xi/XI/SplitAndMerge"
xmlns:exsl="http://exslt.org/common"
extension-element-prefixes="exsl">

<xsl:output method="xml" encoding="utf-8" indent="yes"/>
<xsl:key name = "email-search" match = "//User" use = "email"/>

<xsl:template match="/">

<xsl:variable name="User">
<xsl:for-each select="//User/User">
<emails>
<email><xsl:copy-of select="email"/></email>
</emails>
</xsl:for-each>
</xsl:variable>

<root>
<xsl:for-each select="multimap:Messages/multimap:Message1/root/activities">
<activities>
<assetType>
<xsl:value-of select="assetType"/>
</assetType>
<email>
<xsl:value-of select="email"/>
</email>
<xsl:if test="email = exsl:node-set($User)/emails/email">
<xsl:for-each select = "key('email-search', email)">
<userId><xsl:value-of select = "userId"/></userId>
</xsl:for-each>
</xsl:if>
</activities>
</xsl:for-each>
</root>
</xsl:template>

</xsl:stylesheet>


The Output is as below: (illustrative only)

 

Of course all the outputs from the 3 options were further processed in other steps in the integration flow to get the format to load the learning history to SuccessFactors.

 

The purpose of this blog post was to provide a glimpse to the wide functionality provided to prepare integrations flows in SAP Integration Suite.

 

A few handy tools out there to help check your xsl statements and groovy script:

xsl: https://xsltfiddle.liberty-development.net/

groovy script:  Groovy IDE

 

Hope this blog post was helpful.

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.