CX Works

CX Works brings the most relevant leading practices to you.
It is a single portal of curated, field-tested and SAP-verified expertise for SAP Customer Experience solutions

Extract SAP Marketing Cloud Data with CDS-based Export Services

24 min read

Overview

Extract SAP Marketing Cloud data with CDS-based export services

In many use cases, SAP Marketing Cloud is the recipient of data for building a 360° view of your customers, collecting contacts and interaction data from multiple data sources and combining them in one Best Record. The collected data is used by SAP Marketing Cloud and connected systems to build connected customer journeys and improve customer experience.
However, that data can also be useful for other systems and might need to be replicated in large volumes. A typical example would be external analytics tools that are not necessarily connected to your customer journey from a marketing perspective.

In this blog post we are having a closer look on how to extract large amounts of data from SAP Marketing Cloud to be provided to external systems using SAP Cloud Integration.

SAP Marketing Cloud provides a service to export more substantial amounts of data for external analytics tools. This service has a much lower resource consumption footprint compared to using the other APIs available on SAP Marketing Cloud. The public APIs, such as the Contact or Interaction API, are not recommended to be used for mass data extracts. Here, we give a practical example of how to use the API for Core Data Services (CDS)-based data export and how to build an integration with SAP Integration Suite to extract and transform the pulled data from SAP Marketing Cloud.

Note

Please be advised that the content of this blog post is developed custom and not delivered as a standard integration scenario with SAP Cloud Integration.
The integration content described is built custom and will require adjustment for use cases other than the ones described in this blog post.

The CDI API supports SAP Data Intelligence (Data Hub) and SAP Smart Data Integration (SDI).

Before using the CDS-based Extraction, please make yourself familiar with the official documentation Core Data Services-Based Extraction from SAP Marketing Cloud to Other SAP and Non-SAP Systems and Cloud Data Integration API.

SAP Cloud Integration is is an process integration service and not a traditional ETL tool. Consider message throughput and size for the active integrations, as processing lager amounts of data through the CDI Service can affect the SCI tenant's performances when scheduling large batch loads.
Please review the documentation to help designing IFlows.

For further information, please see the Guidelines to Design Enterprise-Grade Integration Flows.

Table of Contents

Scenario Overview

In this blog post we are building an integration using SAP Integration Suite to extract a large amount of Contact and Interaction Data from SAP Marketing Cloud. The extracted data is then send to an external system for further processing.

Prerequisites:

  • SAP Marketing Cloud
  • Cloud Integration capability within SAP Integration Suite with SAP Enterprise Messaging enabled
    • Alternative options without message queueing is described but not implemented in this build
  • HTTP Client (e.g. Postman or Insomnia)

Service Overview

The OData APIs used in the integration are made available with the Communication Arrangement SAP Data Hub - ABAP CDS OData Integration (SAP_COM_0531). The Communication Arrangement needs to be configured on SAP Marketing Cloud to use the APIs.

  • Administrative Service

  • Data Provider Service

Communication Protocol
The Cloud Data Integration (CDI) API uses OData v4 as protocol for the communication between server and client.

Entity Sets

As of now (Release 2005), one Namespace is available and all Providers are assigned to the Namespace 'ABAP_CDS'. Both, the Namespace and Provider are read only and can be displayed through the Administrative Service.

Each provider can have none, one, or multiple subscriptions, those subscriptions are created, read, and deleted through the Data Provider Service.


  • Namespaces

    Available namespace: ABAP_CDS
    The client can navigate from the namespace to the list of its data providers:
    https://.../Namespaces(NamespaceID=...)/Providers

  • Providers
    The provider's entity set is read-only. Each provider has its own OData service for data access. The entitySet of providers includes the URL of the services. Each provider has one entitySet.

The Data Provider includes custom fields that are enabled for analytics. Custom fields can be enabled for analytics use in the “Custom Fields and Logic” Application.

Contacts: Enable “Marketing: Contact Facet Data” in the tab “UIs and Reports”
Interactions: Enable “Interaction Basic View” in the tab “UIs and Reports”

Enabled/disabled/deleted custom fields are updated in the Provider metadata and will be included/excluded in the data export.

  • Subscriptions

A client can request data through the subscription and track changes between each completed request.
To track changes, the Prefer header is set in the request.

Prefer: odata.track-changes

When supported, the service includes a Preference-Applied header in the response with the track-changes preference as well as the delta link on the last result page. With using explicit subscribers, the delta link is updated after a successful request.

When the Prefer header with value odata.track-changes is added to the request, an implicit subscription is created.

Explicit Subscriptions can be created with a POST request on the Subscription entity set.
Subscriptions can be deleted with a DELETE request on the Subscription entity set.

In this blog post, we describe the usage of explicit subscription.


Pagination

Different options are provided for paging through the data response set.

  • $top & $skip
    You can use $top and $skip to page through the results

  • Prefer header & $skiptoken
    when setting the Prefer header Prefer: odata.maxpagesize=n, a skiptoken is provided in the response.

Data Access

SAP Marketing Cloud Configuration

To enable access to the SAP Marketing Cloud data, the scenario 'SAP Data Hub - ABAP CDS OData Integration' needs to be enabled. To do so the Communication Arrangement needs to be created and communication to the APIs enabled.
The communication with SAP Marketing Cloud is configured using the following applications.

  • Communication User
  • Communication System
  • Communication Arrangement

Please refer to the SAP Marketing Cloud Help documentation for additional information.
Core Data Services-Based Data Extraction from SAP Marketing Cloud to Other SAP and Non-SAP Systems

Communication User

It is recommended to use a dedicated Communication User or client-certificate based authentication for this integration. In SAP Marketing Cloud, open the application Maintain Communication User and create a new communication user. Define User and Password or use client-certificate authentication.
In this example we are using User and Password authentication. It is recommended to use a more secure way for productive systems (e.g. client-certificate authentication).

Communication System

Next, create a new or reuse an existing Communication System for this integration. Select the created Communication User from the previous step.


Communication Arrangement

Open the Communication Arrangements Application and create a new Communication Arrangement. Assign a Communication System from the previous step and Communication User.
When saving the Communication Arrangement, the associates services to this Communication Arrangements are enabled and can be accessed with the authentication method defined.

Scenario ID: SAP_COM_0531
Scenario: SAP Data Hub - ABAP CDS OData Integration

Test Connectivity

You can test the connectivity to the APIs from any HTTP Client. Use the HTTP or CURL request to import to your HTTP Client.

Response format:

{
    "@odata.context": "$metadata#Providers",
    "@odata.metadataEtag": "W/\"20200208031804\"",
    "value": [
        {
            "NamespaceID": "ABAP_CDS",
            "ProviderID": "<provider ID>",
            "Description": "<description>",
            "ServiceURL": "<service URL>"
        }
    ]
}

Description

Request

Get Providers

URL: https://{{SMC_HOST}}/sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Providers
HTTP

GET /sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Providers? HTTP/1.1
Host: {{SMC_HOST}}
Authorization: Basic <Base64 encoded username:password>

CURL

curl --location --request GET 'https://{{SMC_HOST}}/sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Providers' \
--header 'Authorization: Basic <Base64 encoded username:password>


Sample Requests for managing subscriptions and collecting data

Before extracting data from SAP Marketing Cloud we create an explicit subscriber to the data provider we are requesting data from. You can also request data from the API without creating an explicit subscription. The explicit provider created an entry in the subscriber list that can be called from the API to retrieve the subscriber information. It is recommended to use explicit subscriptions for productive scenario where a delta load is needed.

The data is collected through the subscriber and each subscriber is assigned to a specific provider ID. You can create multiple subscribers for the same or different providers.

Additional information can be found in the Cloud Data Integration (CDI) API documentation.

Sample Requests

The following sample request can be used for testing the API for managing subscriptions and collect data from the subscribed data providers.
Some HTTP Clients support importing request code from the UI. (Screenshot: Import CURL in Postman)

Manage Subscriptions

Sample Requests for creating and deleting subscriptions

Add the property $count with keyValue true to the URI to get a count of the collection of entities in the response.

GET entitySet?$count=true
"@odata.count": 85,

Description

Request

Get Token

HTTP

GET /sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Subscriptions? HTTP/1.1
Host: {{SMC_HOST}}
x-csrf-token: fetch

CURL

curl --location --request GET 'https://{{SMC_HOST}}/sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Subscriptions'
--header 'x-csrf-token: fetch'

Create Contact Subscription

Adapt request properties.

HTTP

POST /sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Subscriptions? HTTP/1.1
Host: {{SMC_HOST}}
Content-Type: application/json
x-csrf-token: <csrf-token>
Authorization: Basic <Base64 encoded username:password>

{
"NamespaceID": "ABAP_CDS",
"ProviderID": "I_MKT_CONTACTFACETDATA_2",
"Filter": "",
"Selection": "",
"Description": "Contact Export",
"EntitySetName": "MasterData",
"ExternalID": "Contact_Export"
}

CURL

curl --location --request POST 'https://{{SMC_HOST}}/sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Subscriptions'
--header 'Content-Type: application/json'
--header 'x-csrf-token: <csrf-token>'
--header 'Authorization: Basic <Base64 encoded username:password>'
--data-raw '{
"NamespaceID": "ABAP_CDS",
"ProviderID": "I_MKT_CONTACTFACETDATA_2",
"Filter": "",
"Selection": "",
"Description": "Contact Export",
"EntitySetName": "MasterData",
"ExternalID": "Contact_Export"
}'

Create Interaction Subscription

Adapt request properties.

HTTP

POST /sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Subscriptions? HTTP/1.1
Host: {{SMC_HOST}}
Content-Type: application/json
x-csrf-token: <csrf-token>
Authorization: <Base64 encoded username:password>

{
"NamespaceID": "ABAP_CDS",
"ProviderID": "I_MKT_INTERACTION",
"Filter": "",
"Selection": "",
"Description": "Interaction Export",
"EntitySetName": "Facts",
"ExternalID": "Interaction_Export"
}

CURL

curl --location --request POST 'https://{{SMC_HOST}}/sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Subscriptions'
--header 'Content-Type: application/json'
--header 'x-csrf-token: <csrf-token>'
--header 'Authorization: Basic <Base64 encoded username:password>'
--data-raw '{
"NamespaceID": "ABAP_CDS",
"ProviderID": "I_MKT_INTERACTION",
"Filter": "",
"Selection": "",
"Description": "Interaction Export",
"EntitySetName": "Facts",
"ExternalID": "Interaction_Export"
}'

Delete Subscription

HTTP

DELETE /sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Subscriptions(NamespaceID='ABAP_CDS',ProviderID='<Provider ID>',SubscriptionID='<Subscription ID>')? HTTP/1.1
Host: {{SMC_HOST}}
x-csrf-token: <csrf-token>

CURL

curl --location --request DELETE 'https://{{SMC_HOST}}/sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Subscriptions(NamespaceID='ABAP_CDS',ProviderID='<Provider ID>',SubscriptionID='Subscription ID>')'
--header 'x-csrf-token: <csrf-token>'

It is recommended to define the filter conditions in the data provider request (in the IFlow). Filter conditions defined in the subscriptions can't be changed and changes require re-creating the explicit subscription.


Collect Data from the Data Providers

The following requests are samples only and should give an idea how requests are formatted and used.
The sample requests can be used for testing.

Action

Description

HTTP Request

Get all Providers

get a list of all providers

GET /sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Providers HTTP/1.1
Host: {{SMC_HOST}}
Authorization: *

Get Provider Metadata

The provider ServiceURL is displayed in the providers list.

Append $metadata

GET /sap/opu/odata4/sap/cdi_cds/cdi_cds/sap/i_mkt_interaction/0001/$metadata HTTP/1.1
Host: {{SMC_HOST}}
Authorization: *

Get Interactions without DeltaToken

Read interactions from SAP Marketing Cloud with $filter, $select and $top parameters.

This is only a sample request.

GET /sap/opu/odata4/sap/cdi_cds/cdi_cds/sap/i_mkt_interaction/0001/Facts?$filter=InteractionContactOrigin eq 'SAP_ERP_CONTACT' and InteractionTimeStampUTC gt 20200101000000&$select=InteractionContactId,InteractionType,InteractionTimeStampUTC,InteractionContent&$top=10 HTTP/1.1
Host: {{SMC_HOST}}
Authorization: *

Get Interactions with DeltaToken

Read interactions from SAP Marketing Cloud with delta token from explicit subscriptions.

GET /sap/opu/odata4/sap/cdi_cds/cdi_cds/sap/i_mkt_interaction/0001/Facts?$deltatoken=<CurrentDeltaToken>&$select=InteractionContactId,InteractionType,InteractionTimeStampUTC,InteractionContent HTTP/1.1
Host: {{SMC_HOST}}
Prefer: odata.maxpagesize=10
Authorization: *


Get Contacts without DeltaToken

Read contacts from SAP Marketing Cloud with $filter, $select and $top parameters.

This is only a sample request

GET /sap/opu/odata4/sap/cdi_cds/cdi_cds/sap/i_mkt_contactfacetdata_2/0001/MasterData?$select=InteractionContactOrigin,InteractionContactId,EmailAddress,LastChangeDateTime,LanguageCode&$filter=InteractionContactOrigin eq 'SAP_ERP_CONTACT' and LastChangeDateTime gt 20200101000000&$top=10 HTTP/1.1
Host: {{SMC_HOST}}
Authorization: *

Get Contacts with DeltaToken Read contacts from SAP Marketing Cloud with delta token from explicit subscriptions.

GET /sap/opu/odata4/sap/cdi_cds/cdi_cds/sap/i_mkt_contactfacetdata_2/0001/MasterData?$deltatoken=<CurrentDeltaToken>&$select=nteractionContactOrigin,InteractionContactId,EmailAddress,LastChangeDateTime,LanguageCode HTTP/1.1
Host: {{SMC_HOST}}
Prefer: odata.maxpagesize=10
Authorization: *

Get all Subscribers

read all created subscribers

/sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Subscriptions
Host: {{SMC_HOST}}
Authorization: *

Get Subscriber

get a specific subscriber

/sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Subscriptions(NamespaceID='ABAP_CDS',ProviderID='<ProviderID>',SubscriptionID='<SubscriptionID>')
Host: {{SMC_HOST}}
Authorization: *

Create new Subscriber

Create an explicit subscriber.
Filter parameter is set when reading delta entries from the ODQ.

This sample creates a new subscriber for contact facet data.

POST /sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Subscriptions? HTTP/1.1
Host: {{SMC_HOST}}
Authorization: *
Content-Type: application/json
x-csrf-token: {{AccessToken}}

{
"NamespaceID": "ABAP_CDS",
"ProviderID": "I_MKT_CONTACTFACETDATA_2",
"Filter": "<Filter>",
"Selection": <Select>",
"Description": "Contact Export Sample",
"EntitySetName": "MasterData",
"ExternalID": "Contact_Export_Sample"
}

Delete Subscriber

To stop a subscription, the subscriber is deleted from the active subscribers.

DELETE /sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Subscriptions(NamespaceID='ABAP_CDS',ProviderID='I_MKT_INTERACTION',SubscriptionID='7ILD4CZETMPOVEGY6GGDCBLBXM')? HTTP/1.1
Host: {{SMC_HOST}}
Authorization: *
x-csrf-token: <csrf-token>

Get Access Token

The access token is valid across APIs and valid for the user that was used to retrieve the access token.

GET /sap/opu/odata4/sap/cdi/default/sap/cdi/0001/Subscriptions? HTTP/1.1
Host: {{SMC_HOST}}
Authorization: *
x-csrf-token: fetch


SAP Integration Suite

Custom IFlows are configured to collect data from SAP Marketing Cloud, transform, and submit the data to the Receiver.

The shown IFlow is an example and is not part of a standard integration on SAP Marketing Cloud or pre-packaged integration content on Cloud Integration. The IFlows shown and described in this blog post can be used as examples to build a custom integration on Cloud Integration.

Design

The Integration on SAP Integration Suite consists of three components.

  • Data Collector IFlow
    • This IFlow collects data from SAP Marketing Cloud and saves the collected data in a Message Queue.
  • Message Queue
    • A SAP Enterprise Messaging license in required to use this functionality on Cloud Integration.
      As alternative options, a Process direct or Data Store can be used. Those can be alternative options but do not have the same functionality as the Message Queues enabled with SAP Enterprise Messaging.
  • Data Processor IFlow
    • This IFlow transforms the collected data and sends the transformed data to the receiver.

When implementing this scenario, it is recommended to implement a backup/retry mechanism in case the delta request to SAP Marketing Cloud fails. The response provides a CurrentDeltaLink and a PreviousDeltaLink in the response payload. The PreviousDeltaLink can be used to recollect data in case the call with the CurrentDeltaLink failed. A simple retry can be useful when for example a 500 or 503 error is thrown. For 4xx errors or message processing errors on Cloud Integration it is recommended to investigate the error first and then trigger a retry as those errors are usually not solved by doing a simple retry (e.g. mapping errors).

A backup/retry mechanism is not part of this blog post.

In this example we are using SAP Enterprise Messaging embedded for JMS queueing on Cloud Integration. This is not mandatory but enables asynchronous decoupling of processesI.
If you wish to add SAP Enterprise Messaging to you Cloud Integration tenant, please refer to the listed documentation and SAP Enterprise Messaging Product Page.


Data Collector IFlow

This IFlow collects the data from SAP Marketing Cloud and pushes the data to a message queue for further processing. This IFlow manages initial and delta data extracts though the provided delta tokens provided in the created subscriber.

  1. A Timer Event defines the start of the process

  2. Get the subscription information
    The Content Modifier defines the Subscription information to be used in the following request. See Get Subscriber sample request.
    The response contains the Subscription information including the CurrentDeltaLink The script getCurrentDeltaLink persists the CurrentDeltaLink as a property.

  3. The loop process call is continuing looping through records until the exit condition is met.
    Exit condition: ${property.lastMessage} = ‘false'
    The property lastMessage is set after reading the last page of the data set.
    The maximum number of iterations need to be adjusted depending on the max. expected record to be retrieved from the data provider.The parameter Max. Numbers of Iterations can’t be externalized and need to be set in the Iflow.

  4. The first call is set by the CurrentDeltaLink in the subscription. All following requests are set from the @odata.nextLink value in the response payload.

  5. Empty messages are discarded and indicate the last page of the result set.

  6. The value from @odata.nextLink is saved in a parameter for the next call.

  7. Each page of the result set is sent to a JMS where the message is collected for further processing.
    The page size is configured by setting the Prefer header in the request message.
    Prefer: odata.maxpagesize=10000
    The header can be defined in an externalized property in the IFlow configuration.

  8. A simple error handling is added to write error messages is a message queue.


Loop and page size

A maximum number of iterations must be configured in the Loop Process Call.
It is recommended to set the odata.maxpagesize header to limit the number of records of each response.

The Maximum number of iterations multiplied with the Page Size should be equal or larger than the maximum number of expected records to be extracted. Those parameters can be adjusted after the initial data extract.


Script

The script contains multiple script functions called during message processing.
This script is an example and might need to be adjusted for other implementations.

import com.sap.gateway.ip.core.customdev.util.Message
import java.util.HashMap
import groovy.json.JsonSlurper
 
Message getCurrentDeltaLink(Message message) {
     
def jsonResponse = new JsonSlurper().parseText(message.getBody(java.lang.String))
 
def CurrentDeltaLink = jsonResponse.CurrentDeltaLink
message.setProperty("CurrentDeltaLink", CurrentDeltaLink)
     
return message
}
 
Message defineNextRequest(Message message) {
 
def jsonResponse = message.getBody(java.lang.String)
def newJsonResponse = jsonResponse.replaceAll("@odata.nextLink","nextLink")
     
def oJsonResponse = new JsonSlurper().parseText(newJsonResponse)
 
     
if(oJsonResponse.nextLink)
    {
        def nextLink = oJsonResponse.nextLink
        message.setProperty("nextLink", nextLink)
    }
else
    {
        message.setProperty("nextLink", "null")
        message.setProperty("lastMessage", "true")
    }
     
return message
}
 
Message validateResponse(Message message) {
     
def jsonResponse = new JsonSlurper().parseText(message.getBody(java.lang.String))
def isEmpty = jsonResponse.value.isEmpty()
 
if(isEmpty == true)
    {
        message.setProperty("nextLink", "null")
        message.setProperty("lastMessage", "true")
    }
     
message.setProperty("isEmpty", isEmpty)
     
return message
}


Data Processor IFlow

The Data Processor IFlow is transforming the extracted data from SAP Marketing Cloud according to the requirements to the receiver. This IFlow will look different for every implementation since the requirements for each receiver can differ. Here, we are providing a template for an IFlow with possible transformation steps.

  1. Collect messages from the message queue
    The number of Concurrent Processes is defaulted to 1. The throughput can be increased by increasing the number of concurrent processes.

  2. Steps in this box are recommended and generic for all copies of this IFlow (all Data Processor IFlows)
    Message transformations that apply to all Data Processor IFlows are described here.
    For example an outbound payload would require a specific property that apply to all IFlows of this integration (e.g. Source System Identifier).
    In this example, the receiver expects no root element in the message payload. The root element in the json message is deleted in script function getResponseValues.

  3. Steps in this box are optional message transformations and specific to this type of IFlow (all Data Collector IFlows of a specific type)
    Message transformations that apply to a specific type of Data collector IFlows such as transforming Contact Creation Date to another DateTime format or value mapping of Interaction Types.
    In this example the Receiver expects the DateTime format in a different format than extracted from SAP Marketing Cloud.

  4. Steps in this box are optional and specific to a specific IFlow (one specific Data Collector IFlows - usually depended on the receiver)
    Message transformations that are specific to this individual IFlow can be added here.
    You can have copies of the same IFlows but each IFlow can have their own queue and receiver.
    In this example the receiver URL is modified.

    Note: Steps 2 to 4 are can consist of multiple steps and should help understanding different levels of the customized build of this Data Processor IFlow.
    This can help keep Your IFlow structured and understandable. For more complex IFlows this can be split out in different Local Integration Processes.

  5. The message is send to the receiver. In this example a HTTP Adapter is used.

  6. The re-processing step is called when an error occurs.
    First the maximum number of retries is checked. This is a functionality of Message Queueing the and JMS Adapter.
    The error message is saved in a separate Message Queue.

  7. The exception process has a retry counter to limit the number of retries. The max. number of retries is a configurable property in the IFlow configuration.
    In this design, the retry is triggered by the message queue through the Cloud Integration JMS Adapter.


Message Processing Script

The script contains multiple script functions called during message processing.
This script is an example and might need to be adjusted for other implementations.


import com.sap.gateway.ip.core.customdev.util.Message
import java.util.HashMap
import groovy.json.JsonSlurper
import groovy.json.JsonOutput
import java.text.SimpleDateFormat


def Message getResponseValues(Message message) {
    def jsonBody = new JsonSlurper().parseText(message.getBody(java.lang.String))
    //def aValue = jsonBody.value
    def aValue = JsonOutput.toJson(jsonBody["value"])
    message.setBody(aValue)
return message
}


def Message urlencode(Message message) {
    //Body 
       //def body = message.getBody().toString();
       def body = message.getBody(java.lang.String);

    //Encode payload
    def encodedmessage = URLEncoder.encode(body, "UTF-8")
    message.setProperty("message", encodedmessage)

return message
}


def Message encodeBase64(Message message) {
    //http://docs.groovy-lang.org/2.4.3/html/api/org/codehaus/groovy/runtime/EncodingGroovyMethods.html
    map = message.getProperties()
    
    def InbMessage = map.get("error_InbMessage")
    def OutbMessage = map.get("error_OutbMessage")
    
    def InbMessageEncoded = InbMessage.bytes.encodeBase64().toString()
    def OutbMessageEncoded = OutbMessage.bytes.encodeBase64().toString()
    
    message.setProperty("error_InbMessage", InbMessageEncoded)
    message.setProperty("error_OutbMessage", OutbMessageEncoded)
    
    return message
}

//TRANSFORM AND REPLACE TIMESTAMP FOR INTERACTIONS
Message transformInteractionTimestamp(Message message) {
    def jsonBody = new JsonSlurper().parseText(message.getBody(java.lang.String))

    jsonBody.eachWithIndex{
                it, i -> 
                        //transform InteractionTimeStampUTC
                        def IaTimestamp = JsonOutput.toJson(it.InteractionTimeStampUTC)
                        IaTimestamp = IaTimestamp.substring(0, IaTimestamp.lastIndexOf("."))
                        def vIaTimestamp = new SimpleDateFormat("yyyyMMddHHmmss").parse(IaTimestamp)
                        def oIaTimestamp = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS").format(vIaTimestamp)

                        //transform InteractionCreationUTCDateTime
                        def IaCreateTimestamp = JsonOutput.toJson(it.InteractionCreationUTCDateTime)
                        IaCreateTimestamp = IaCreateTimestamp.substring(0, IaCreateTimestamp.lastIndexOf("."))
                        def vIaCreateTimestamp = new SimpleDateFormat("yyyyMMddHHmmss").parse(IaCreateTimestamp)
                        def oIaCreateTimestamp = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS").format(vIaCreateTimestamp)
                        
                        //transform InteractionLastChangedDateTime
                        def IaChangeTimestamp = JsonOutput.toJson(it.InteractionLastChangedDateTime)
                        IaChangeTimestamp = IaChangeTimestamp.substring(0, IaChangeTimestamp.lastIndexOf("."))
                        def vIaChangeTimestamp = new SimpleDateFormat("yyyyMMddHHmmss").parse(IaChangeTimestamp)
                        def oIaChangeTimestamp = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS").format(vIaChangeTimestamp)

                //Set new timestamps in json
                it.InteractionTimeStampUTC = oIaTimestamp
                it.InteractionCreationUTCDateTime = oIaCreateTimestamp
                it.InteractionLastChangedDateTime = oIaChangeTimestamp
                }

message.setBody(JsonOutput.toJson(jsonBody))
return message
}

//TRANSFORM AND REPLACE TIMESTAMP FOR CONTACTS
Message transformContactTimestamp(Message message) {
    def jsonBody = new JsonSlurper().parseText(message.getBody(java.lang.String))

    jsonBody.eachWithIndex{
                it, i -> 
                        //transform CreationDateTime
                        def IcCreateTimestamp = JsonOutput.toJson(it.CreationDateTime)
                        IcCreateTimestamp = IcCreateTimestamp.substring(0, IcCreateTimestamp.lastIndexOf("."))
                        def vIcCreateTimestamp = new SimpleDateFormat("yyyyMMddHHmmss").parse(IcCreateTimestamp)
                        def oIcCreateTimestamp = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS").format(vIcCreateTimestamp)
                        
                        //transform LastChangeDateTime
                        def IcChangeTimestamp = JsonOutput.toJson(it.LastChangeDateTime)
                        IcChangeTimestamp = IcChangeTimestamp.substring(0, IcChangeTimestamp.lastIndexOf("."))
                        def vIcChangeTimestamp = new SimpleDateFormat("yyyyMMddHHmmss").parse(IcChangeTimestamp)
                        def oIcChangeTimestamp = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS").format(vIcChangeTimestamp)

                        //transform CreationDate
                        def IcCreationDate = JsonOutput.toJson(it.CreationDate)
                        IcCreationDate = IcCreationDate.replace('"','')
                        def vIcCreationDate = new SimpleDateFormat("yyyyMMdd").parse(IcCreationDate)
                        def oIcCreationDate = new SimpleDateFormat("yyyy-MM-dd").format(vIcCreationDate)
                        
                        //transform LastChangeDate
                        def IcLastChangeDate = JsonOutput.toJson(it.LastChangeDate)
                        IcLastChangeDate = IcLastChangeDate.replace('"', '')
                        def vIcLastChangeDate = new SimpleDateFormat("yyyyMMdd").parse(IcLastChangeDate)
                        def oIcLastChangeDate = new SimpleDateFormat("yyyy-MM-dd").format(vIcLastChangeDate)
                        
                //Set new timestamps in json
                it.CreationDateTime = oIcCreateTimestamp
                it.LastChangeDateTime = oIcChangeTimestamp
                it.CreationDate = oIcCreationDate
                it.LastChangeDate = oIcLastChangeDate

                }

message.setBody(JsonOutput.toJson(jsonBody))
return message
}

def Message transformContactValues(Message message) {
    def jsonBody = new JsonSlurper().parseText(message.getBody(java.lang.String))

    jsonBody.eachWithIndex{
                it, i ->
                        def sLatitude = JsonOutput.toJson(it.Latitude)
                        if(sLatitude == '0E-10'){
                            sLatitude = sLatitude.replace('0E-10', '0.0000000000')
                            def fLatitude = Float.valueOf(sLatitude)
                            it.Latitude = fLatitude
                        }

                        def sLongitude = JsonOutput.toJson(it.Longitude)
                        if(sLongitude == '0E-10'){
                            sLongitude = sLongitude.replace('0E-10', '0.0000000000')
                            def fLongitude = Float.valueOf(sLongitude)
                            it.Longitude = fLongitude
                        }
                        
                }
    message.setBody(JsonOutput.toJson(jsonBody))
    return message
}

def Message transformInteractionValues(Message message) {
    def jsonBody = new JsonSlurper().parseText(message.getBody(java.lang.String))

    jsonBody.eachWithIndex{
                it, i ->
                        def sInteractionLatitude = JsonOutput.toJson(it.InteractionLatitude)
                        if(sInteractionLatitude == '0E-10'){
                            sInteractionLatitude = sInteractionLatitude.replace('0E-10', '0.0000000000')
                            def fInteractionLatitude = Float.valueOf(sInteractionLatitude)
                            it.InteractionLatitude = fInteractionLatitude
                        }

                        def sInteractionLongitude = JsonOutput.toJson(it.InteractionLongitude)
                        if(sInteractionLongitude == '0E-10'){
                            sInteractionLongitude = sInteractionLongitude.replace('0E-10', '0.0000000000')
                            def fInteractionLongitude = Float.valueOf(sInteractionLongitude)
                            it.InteractionLongitude = fInteractionLongitude
                        }

                        def sInteractionSourceTimeStampUTC = JsonOutput.toJson(it.InteractionSourceTimeStampUTC)
                        if(sInteractionSourceTimeStampUTC == '0E-7'){
                            def fInteractionSourceTimeStampUTC = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS")
                            fInteractionSourceTimeStampUTC = null
                            it.InteractionSourceTimeStampUTC = fInteractionSourceTimeStampUTC
                        }
                        
                }
    message.setBody(JsonOutput.toJson(jsonBody))
    return message
}

Performance Tests

It is not recommended using the SAP Marketing Cloud Integration APIs (Contact API, Interaction API, Campaign API, etc.) for mass data exports. If you need to extract larger amounts of data it is recommended to use the CDS based export described in this blog post.
The numbers are not official performance metrics and can vary on different tenants depending on workload, assigned resources, network connectivity, etc.. The numbers shown here are a guideline for an initial estimate but do not guarantee processing time in that range.

Tests are conducted on two different systems with multiple exports ranging from 100k to 10MM Contact and 100k to 200MM Interaction records per data set.

Average Processing for Contacts and Interaction extracts: 28-55 seconds per 100k records (average: 40 seconds per 100k records)

The average per 100k records varies depending on of the total volume of the data to be retrieved from SMC. We observed that retrieving larger data sets have faster throughputs.

 

Chart shows average processing time per 100k records in seconds.

Performance tests need to be carried out for each system individually. The numbers provided here merely provide an estimate and no definite metric.

Conclusion

CDS Based Exports provide a fast and reliable option for mass data extracts from SAP Marketing Cloud to external systems. The intend of this functionality is to export data for external analytics tools, but it can provide a good option to export data where the SAP Marketing Cloud Integration APIs reach their limits.

This service provides delta extracts, eliminating the need for implementing mechanisms to persist delta tokens (e.g. timestamp or other identifiers) externally.

When building an integration like the one described in this blog post, it is recommended to decouple the Data Collector and Data Processor IFlow.

  • The Data Collector IFlow is generic and can be copied and reconfigured with minimal effort when using multiple subscriptions.
  • The Data Processor IFlow usually requires message processing steps that are specific to the data collected or receiver.
  • JMS is a functionality that come only with specific SAP Integration Suite editions or needs to be acquired as a separate functionality on Cloud Integration.
    A Process Direct connection or Data Store can be used as an alternative, but do not provide the save capabilities that SAP Enterprise Messaging.


Overlay