Oracle Service Cloud (henceforth referred to as OSvC) provides a complete customer service solution that offers cross-channel service delivery, web customer service, self-service, knowledge management, and a host of other features.
Besides its functional capabilities, OSvC provides a robust, API based integration and extension framework.
In this and a few upcoming blogs we’ll focus on the Integration Framework and explore the Connect Web Services (CWSS) APIs in detail. This blog in particular is about bulk data inserts/updates using CWSS.
A basic knowledge of OSvC is assumed; if not, the tutorials available here are a great place to start.
The CWSS APIs provide SOAP based services that can create, modify or get OSvC data. The API is described by the WSDL https://{your_site}/cgi-bin/{your_interface}.cfg/services/soap?wsdl=typed
, where ‘your_site’ is the name of the OSvC site and ‘your_interface’ is the name of the specific interface. The WSDL has two ports, viz. RightNowSyncBinding which allows WS-UsernamePassword Token based authentication, and RightNowFedAuthSyncBinding which allows SAML based authentication. Both ports expose the same set of operations.
Further CWSS documentation is available here.
The WSDL operation that enables bulk functionality —which is the topic of this blog— is ‘Batch’. The Batch operation supports sending bulk, heterogeneous requests to the server. Following is a sample input payload (only key XML tags shown) :
<Batch> <BatchRequestItem> <CreateMsg> <RNObjects xsi:type="{Incident,Contact,etc.}"/> <RNObjects/> <!--n times--> <ProcessingOptions> <SuppressExternalEvents/> <SuppressExternalRules/> </ProcessingOptions> </CreateMsg> <!--17 such 'operations' exist. Only one allowed per BatchRequestItem--> <CommitAfter>{true/false}-defaults to false</CommitAfter> </BatchRequestItem> <BatchRequestItem> <GetMsg> <RNObjects/> <RNObjects/> <!--n times--> <ProcessingOptions/> </GetMsg> <CommitAfter/> </BatchRequestItem> <BatchRequestItem> <UpdateMsg> <RNObjects/> <RNObjects/> <!--n times--> </UpdateMsg> <CommitAfter/> </BatchRequestItem> <BatchRequestItem> <DestroyMsg> <RNObjects/> <RNObjects/> <!--n times--> </DestroyMsg> <CommitAfter/> </BatchRequestItem> <BatchRequestItem/> <BatchRequestItem/> <!--max 100 such BatchRequestItems--> <!--max 10,000 total RNObjects in a given SOAP request--> </Batch>
Let's dive in.
true
. Transactions can span across BatchRequestItems as well if the CommitAfter flag is not set. The default value of the flag is false
.A sample successful Batch invocation response is provided below (only relevant XML tags shown):
<BatchResponse> <BatchResponseItem> <CreateResponseMsg> <RNObjectsResult> <RNObjects xsi:type="Incident"> <ID id="sample_id_1"/> </RNObjects> <RNObjects xsi:type="Incident"> <ID id="sample_id_2"/> </RNObjects> </RNObjectsResult> </CreateResponseMsg> </BatchResponseItem> <BatchResponseItem> <UpdateResponseMsg/> </BatchResponseItem> <BatchResponseItem> <GetResponseMsg> <RNObjectsResult> <RNObjects xsi:type="Contact"> <!--details--> </RNObjects> </RNObjectsResult> </GetResponseMsg> </BatchResponseItem> </BatchResponse>
As explained above, the CommitAfter flag can be used to separate a given Batch payload into multiple transactions.
Given below are two examples(BatchRequestItem is abbreviated as BRI).
It should be obvious by now that a given SOAP payload is processed sequentially, in the XML-order it is received. This allows for a clean separation of transaction boundaries.
Following upper limits are imposed on a given input payload for the Batch operation:
It must be noted that these are only numerical limits, and the server can reject lower sized messages as well if the memory/cpu consumption is high.
Errors can happen for a number of reasons :
Checks for ill-formed XML and numerical upper limits are performed first, before the payload is processed. For such errors a SOAPFault is returned. For example :
<soapenv:Fault> <faultcode>soapenv:Sender</faultcode> <faultstring>Data element in the Message is NULL</faultstring> <detail> <fault>NULL returned from the Batch deserializer due to missing or invalid XML</fault> </detail> </soapenv:Fault> <soapenv:Fault> <faultcode>soapenv:Sender</faultcode> <faultstring>Too many items in Batch - exceeds internal limit of 100 items per Batch.</faultstring> <detail> <n0:RequestErrorFault xmlns:n0="urn:faults.ws.rightnow.com/v1_2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <n0:exceptionCode>INVALID_REQUEST</n0:exceptionCode> <n0:exceptionMessage>Too many items in Batch - exceeds internal limit of 100 items per Batch.</n0:exceptionMessage> </n0:RequestErrorFault> </detail> </soapenv:Fault>
Things get interesting for bad data within RNObjects. Whenever such an error is encountered :
The following example demonstrates a batch with a bad RNObject(shown in red) and the corresponding output (You can open the image in a new tab if needed).
OSvC manages the CWSS timeout settings in the front-end Apache Server. There isn't a Web Service timeout per se, but there's an idle timeout value of 300 seconds (by default). That is, if the TCP connection with the Server remains idle for more than 300 seconds, a timeout error occurs.
For example, if a large and DB intensive payload is sent, it is possible that the invocation will time out. When that happens, the current transaction being processed is rolled back, previously committed transactions remain committed, and pending transactions are not processed.
The response message only contains the timeout exception, and it is upon the caller to ensure committed transactions are rolled back.
Following factors should be taken into account when developing bulk load interfaces using CWSS Batch:
Custom fields and attributes associated with an OSvC object have a direct impact on the object’s Create/ Update throughput time. I.e. , as more and more attributes are added, the API throughput time increases. Hence, it’s a good idea to keep the number of custom fields and attributes as less as possible.
CWSS scales very well with multiple parallel threads invoking the API. Hence, bulk data-load processes should be designed to use concurrent threads, and the degree of concurrency should be controllable.
We ran a series of tests, using an Oracle SOA 12c instance as client, increasing the number of parallel threads exponentially from 2 all the way up to 128, and observed the net API throughput to remain consistent. We'll publish a separate blog detailing the tests and the results, but for now it should suffice to say that the bulk data-load process should be concurrent.
From a design/error-handling perspective, it's a good practice to limit transactions( discussed above ) to one per SOAP message.
That way, irrespective of whether the the transaction succeeds within OSvC or errors out(due to business faults, timeouts, and other systems errors), the client is rightly intimated and the client's transaction and OSvC's transaction remain in sync.
Otherwise, the client would have to code logic to figure out which transactions succeeded, which failed, and which weren't processed.
Also, transactions in a single batch payload are processed sequentially. It's a lot faster if the same transactions are sent in parallel using multiple threads, as described above.
As seen above, the Batch Size for a given SOAP payload can vary from 1 RNObject all the way up to 10,000. So the question arises, what’s the ideal batch size ? The answer isn’t as simple as one magic number. It depends on the specific object, number of attributes being created/updated, and if the client is invoking CWSS in parallel threads.
For example, for creating the Contact object with about 25 Custom Attributes(along with all the standard attributes), with 4-32 parallel threads, we found 100 RNObjects in a batch in a single transaction to be a good number, when compared against 1, 500 and 1000 RNObjects (More details on the testing methodology in a separate blog). This DOES NOT imply that 100 is the ideal number. Some testing may be necessary to find the optimal batch size.
Also, it is almost always a bad idea to program one commit per RNObject, for example sending only one RNObject in a payload, or having CommitAfter set to true for each RNObject. This is because commits are a highly costly database operation, and the last thing we need in bulk load scenarios is multiple parallel hits on the DB.
In this blog we discussed the CWSS Batch API in some detail, a few do's and dont's, and throughput considerations while performing bulk data-loads.
Other A-Team blogs in the series on OSvC Integration APIs :
Implementing Upsert for OSvC APIs
Previous Post