Using Open Connectors: Formula, FAAR & Bulk API to harmonize high volume data across systems.

Open connectors comes loaded with lots of useful features – like Open Connectors Query Language (OCNQL), in built pagination support, virtual data repository for cross system data plumbing and the general ability to harvest existing API. There is another feature which can be widely used in a number of common situations – adding the BULK feature to existing API – to pull and push high volume data asynchronously.

This is particularly useful when there is a high volume of master/transactional data consolidation that is required to take place between 2 systems. E.g. employee master data synchronization or product catalog synchronization with some web-shop.

Let’s go!

We need to achieve the following goals:

  • Upload high volume data to a system. I have taken shopify as an example.
  • The data upload should occur in a non blocking manner.
  • Statistical data must be available after the data upload. How many were successful? how many are in errors?
  • A post processing must be launched after the job completion. Mail notification – or launching a CPI-PI IFLOW/ODTATA API for even more complex functions.

All of the above must be accomplished only using OPEN CONNECTORS.

This image will provide a high level understanding of the various task that are involved.

Create an instance of the SHOPIFY element:

Please follow the steps mentioned in establishing the connectivity to the shopify API. Shopify | SAP Open Connectors (ondemand.com). Shopify uses OAUTH2.0 to establish the connectivity. In order to configure this in the shopify – we need to provide the redirect URL which will carry the OAUTH tokens to open connector. Here is the redirect URL: https://auth.cloudelements.io/oauth.

Create a custom API in the SHOPIFY element to perform existence check of a customer.

Before a new customer is being created in shopify – we need to check if it exists or not, we create the customer only if it is a new one. This API must receive the email to query as Base64 encoded, it must decode it back to String format before performing the existence check. Base64 conversion is required to make the data URL safe.

I have created a Pre-Request hook to perform the conversion from the Base64 to String.

let email = CE.decode64(request_vendor_parameters['query']);
request_vendor_parameters['query'] = email;
done({ "request_vendor_parameters": request_vendor_parameters, 'continue': true
});

I have also created a Post-Request hook to check if there is any result available or not. If the result set is initial – I will throw an error. This is required to be done as the shopify API returns HTTP status code 200 even when nothing is found for the queried data. This becomes useful later in the solution when we will be building the formulas.

Post-Request hook

if((response_body_raw_map['customers']).length === 0){ done({ 'response_status_code': 404, 'continue': true });
}
else{ done({ 'continue': true })
}

Create formula which will eventually perform the POST in shopify using the shopify API.

This is the formula template. We have created only one element instance corresponding to shopify.

this formula will be eventually exposed as a FAAR – thereby making it synchronous. This formula wraps the shopify API created before and uses them to create customers. Mark the check it performs before creating the customer.

Code for reference:

Read email.

let email = CE.b64(trigger.args.email);
let response = { 'query': email
};
done(response);

Mark how the CE library is used to convert the string to Base 64 before invoking the API created above. Open connectors comes loaded with many inbuilt JS library. They can be found here: Javascript in Formulas | SAP Open Connectors (ondemand.com)

Pass the email in Base64 format to the custom API created above.

If customer exist, this will be the response from the formula.

let response = { 'status': 200, 'message': `Customer ${trigger.args.email} exist.`
};
done(response);

If there are issues with customer creation.

let response = { 'status': 500, 'message': `Customer ${steps.CreateCustomer.response.body.customer.email} was not created.`
};
done(response);

If the customer was created successfully.

let response = { 'status': 200, 'message': `Customer ${steps.CreateCustomer.response.body.customer.email} created.`
};
done(response);

We can invoke the formula and get the response.

Create a formula to launch advanced post processing actions on the completion of the Job.

This formula will receive the notification after the job has been completed. The notification will contain important information regarding the job as well as statistical data. Using these data we can launch advanced post processing functions.

Notify step.

notify.email('deepankar.bhowmick@gmail.com', 'Job execution', trigger.args);
done(true);

Again, open connectors comes loaded with inbuilt JS library for email triggers. We do not need to establish connectivity with any email servers to achieve this.

Activate bulk feature for this formula.

How does the bulk feature work in Open Connectors?

There are many dedicated BULK API which will be activated once we enable bulk mode for a certain resource for an element. Using the BULK API we can perform both pull and push data to the intended resource.  Data can be provided to these BULK enabled API in file format (CSV) or in JSON format. Once this data is submitted to the BULK API, Open connector creates a file internally in its staging area – then it calls the intended resource for every record of this file. This operation takes place asynchronously. Once all the records are processed – the configured notification callback URL is used to transmit the results for consumption.

Create a private instance for the formula that creates customer.

These configuration properties are created so that he authentication can be performed with the Open connector formula. We will create a global pre-request hook to configure it properly.

//add authentication details.
let user = configuration['oc.user.id'];
let organization = configuration['oc.organization.id'];
let formula = configuration['oc.formula.instance']; request_vendor_headers['Elements-Formula-Instance-Id'] = formula;
request_vendor_headers['Authorization'] = `User ${user}, Organization ${organization}`;
done({ 'request_vendor_headers': request_vendor_headers, 'continue': true
});

Now we can authenticate this element successfully.

Create a wrapper API for the formula.

Activate the BULK mode for this wrapper API.

this is an important step as without these the BULK mode will not be activated.

Create a JSON file containing the customer.

Remember – this is just for testing – in reality we can submit data over payload as well in JSON format.

[ { "last_name": "twelve", "first_name": "customer", "email": "customer.twelve@gmail.com" }, { "last_name": "eleven", "first_name": "customer", "email": "customer.elevenn@gmail.com" }, { "last_name": "two", "first_name": "customer", "email": "customer.two@gmail.com" }
]

Schedule the job:

we get the following response

{ "instanceId": 4109587, "id": "16483647", "status": "CREATED"
}

Note that the job id is returned to us.

Check the status of the Job.

we get the following response.

{ "recordsCount": 3, "metadata": "{\"format\":\"json\"}", "recordsFailedCount": 0, "instanceId": 4109587, "canonical_object": "createShopifyCustomer", "object_name": "createShopifyCustomer", "job_direction": "UPLOAD", "id": "16483647", "bulk_start_time": "2021-12-31T22:59:32.55Z", "bulk_finish_time": "2021-12-31T22:59:38.236Z", "fileFormat": "json", "status": "COMPLETED"
}

Note the statistical information available in this payload.

Email notification also sent.

Shopify uploaded with the data.

We can use this function to implement high performance data consolidation jobs. Even though open connectors does not have the ability to interact with the on premise system, but we can always create a proxy (ODATA, REST, SOAP based) in CPI-PI and provide the BULK feature to it.