Best Practices from Oracle Development's A‑Team
-
Data Management
May 30, 2020
BI Cloud Connector – Further Automation Options
Introduction into existing BICC Automation
BI Cloud Connector offers a broad bandwidth of options to automate the extraction of data. In various other blogs we’ve shared information how to download extraction files, log information or to delete extracts. Those scripts can be scheduled on the client side via the tools of choice. The built-in capabilities in BICC provide more and more automation as the product evolves. Scheduling frequent extraction runs was a feature that could be enabled via UI since the first release. The intention of this blog is to provide an update how an extraction run can be managed remotely via Web Services. This capability adds more options for further automation on a programmatic level. As we can see below the existing web services will allow to run activities like scheduling, monitoring or cancelling an extraction. All existing SOAP actions won’t be explained here as it would be too much content for a single blog. Instead this article will focus on the aspects of submitting requests, checking request status and details. Furthermore, on the parameter usage and the security header information being required to authenticate for a WS usage.
Triggering a BICC extraction run via Web Service
The existing Web Service supports SOAP in the current release. It can be reached via the following URL:
https://
A successful request submission will result in a regular run of a data extraction job like shown in the screenshot below.
The list of available operations can be determined by opening this URL:
https://<SaaS host>/bi/ess/esswebservice?WSDL
It will return the following SOAP actions:
- submitRequest
- submit a single request
- immediate or scheduled execution
- submitRecurringRequest
- submit a request that will run as a new batch with scheduled occurrences
- first scheduled run defined as a parameter
- submitInstantRecurringRequest
- like above, first run will start immediately at execution of WS
- getCompletionStatus
- check the completion status of a request
- getRequestState
- get the state of request
- cancelRequest
- cancel a request given by a request ID as parameter
- holdRequest
- put a request on hold
- checkInCancelling
- getRequestDetail
- getRequestExecutionContext
- setAsyncRequestStatus
- deleteRequest
- purgeRequest
- setSubmitArgs
- setStepsArgs
- addPPActions
- setNLSOptions
All these SOAP Actions appear in SoapUI as shown in screenshot below and the tool provides a good coverage for testing the various functions. Alternately, as being a regular web service, it can be used from Oracle Integration Cloud or any other PaaS offering that supports processing SOAP requests. However, for a real automation these calls should be made from command line scripts or orchestrated by OIC or SOACS or embedded into custom code (Java etc) that will execute these calls. In many cases a combination of coding, orchestrating, scripting and scheduling will make most sense.
Webservice Authentication
The authentication to execute this Web Service must be defined in the SOAP Header. As shown in screenshot below these values are dynamic in a sense that a timestamp and an expiration time will keep the authentication limited for a certain period in time.
A WSSE Security entry must look like the snippet below that shows how these dynamics are set at runtime. If intended to be used in scripting the security header must be obtained dynamically via a programmatic interface. Especially the wsse.Nonce tag stands for an encoded value that is used for a validation of the basic authentication here. In essence the Nonce is a Base64 encoded entry using the username, password and creation timestamp as encoding values. There are various resources in the web that will provide code snippets in multiple programming languages to create such an entry.
...
<wsse:Security soapenv:mustUnderstand="1"
xmlns:wsse= "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<wsu:Timestamp wsu:Id="TS-DFF81D849D4367D0EE15898027916232">
<wsu:Created>2020-05-18T11:53:11.623Z</wsu:Created>
<wsu:Expires>2020-05-18T12:53:11.623Z</wsu:Expires>
</wsu:Timestamp>
<wsse:UsernameToken wsu:Id="UsernameToken-6CAD3CE6C0666692”>
<wsse:Username>biadmin</wsse:Username>
<wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">
ABC123
</wsse:Password>
<wsu:Created>2020-05-18T11:54:01.623Z</wsu:Created>
<wsse:Nonce EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">
yfqfeZd2hA2sEQChqhliPw==
</wsse:Nonce>
</wsse:UsernameToken>
</wsse:Security>
...
When testing and evaluating this request in SoapUI these security settings can be created via the context menu in payload window. On right mouse-click a dialog box will appear that creates and embeds the correct entry for WSS Username Token in the SOAP Header. As a pre-requirement its necessary to add the basic authentication with username and password to this SOAP Action in the Authorization window before.
Once such a basic authentication entry exists for this dedicated action in SoapUI, it can be picked from the Password Type dialog box as shown in screenshot below.
Another dynamic value for the WSSE Security is the timestamp to determine the validity of a request. This tag has a creation date and an end date. Via SoapUI such an entry can be created by using the context menu in payload wind again and to choose the Add WS-Timestamp item as shown in screenshot below.
In another step the duration of the request validity has to be defined. The value represents the number of seconds and as shown in screenshot below the timestamp is valid for one hour.
Once this header information has been created in SoapUI a fully equipped Security Header should look like shown in the screenshot below.
Before this request can be successfully invoked, one more SOAP Header entry must be set: the WSA Header entries for MessageID and Action as shown in sample below. The value for Action must reflect the name of soapAction being executed while MessageID is a custom value identifying a message for further referencing like callback.
... <wsa:MessageID>ujanke4711</wsa:MessageID> <wsa:Action>submitRequest</wsa:Action> ...
In SoapUI this entry for Action can also be generated via the context menu like shown in screenshot below
An entry for the MessageID must be created manually though. As mentioned before the MessageID naming is a user’s choice:
These steps above are mandatory for a successful authentication of a request when invoking within SoapUI. Just to emphasize again: filling in these dynamic SOAP Header values is required to be done programmatically when calling this WS API within an automated approach.
Initiating the submitRequest
The sample of a generated SOAP payload for the submitRequest action could look like the snippet below. Even after filling in the SOAP header as shown above would not be sufficient to execute this WS successfully.
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sch="http://xmlns.oracle.com/scheduler" xmlns:typ="http://xmlns.oracle.com/scheduler/types">
<soapenv:Header/>
<soapenv:Body>
<sch:submitRequest>
<sch:description>?</sch:description>
<sch:jobDefinitionId>
<!--Optional:-->
<typ:name>?</typ:name>
<!--Optional:-->
<typ:packageName>?</typ:packageName>
<!--Optional:-->
<typ:type>?</typ:type>
</sch:jobDefinitionId>
<sch:application>?</sch:application>
<sch:requestedStartTime>?</sch:requestedStartTime>
<sch:requestParameters>
<!--0 to 1000 repetitions:-->
<typ:parameter>
<!--Optional:-->
<typ:dataType>?</typ:dataType>
<!--Optional:-->
<typ:name>?</typ:name>
<!--Optional:-->
<typ:scope>?</typ:scope>
<!--Optional:-->
<typ:value>?</typ:value>
</typ:parameter>
</sch:requestParameters>
</sch:submitRequest>
</soapenv:Body>
</soapenv:Envelope>
Some more mandatory fields within the SOAP body are missing and a complete payload must be added in order to create a message that can be invoked. The screenshot below shows such a message containing all necessary fields being required to invoke a request.
It is now to explain the payload structures being required to fulfil the minimal standards. First of all: the mandatory values for a jobDefinitionId and application must be added as listed below:
| Name | XML tag | Value |
|
Job Description |
description |
<Custom string visible in BICC UI> |
|
Job Name |
name |
BICloudConnectorJobDefinition |
|
Package |
packageName |
oracle.apps.ess.biccc |
|
Type |
type |
JOB_DEFINITION |
|
Start Time |
requestedStartTime |
Provide start time under sch:requestedStartTime. (If left null, will start immediately) |
|
Application |
application |
oracle.biacm (hosting application) |
The screenshot below shows the sample of a fully equipped payload containing the required entries as mentioned above.
The entries above are mandatory for all available options. In a next step we will have a look at the various parameters that will control the details of a submit request.
Overview of various other request options
A SOAP payload generated via SoapUI will define an optional tag called requestParameters as we shared further up this article. We will now have a look how we can control various runtime parameters when filling in various values.
The screenshot below shows a fully equipped messages with according request parameters:
The requestParameters structure contains a list of types with the element parameter having the following fields:
- <typ:dataType>
- <typ:name>
- <typ:scope>
- <typ:value>
These entries will control the runtime behavior of a BICC ESS job. Some samples below give a better understanding how a submitRequest action can use different scopes to run a data extraction job. It will also explain various other parameters being used for output file handling etc. The definition of the job as described above remains the same among all these variations. The value for dataType above is consistently the same: “STRING” if not instructed otherwise. The entries for name, scope and value are explained according to the various scenarios below.
SOAP request to submit Jobs for Cloud Adaptor job - Global data stores
For an extraction run of the Global Data Store the entries for the job definition must be used as explained above. A sample parameter list would look like this XML payload snippet:
…
<sch:requestParameters>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>SYS_className</typ:name>
<typ:scope/>
<typ:value>oracle.esshost.impl.CloudAdaptorJobImpl</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>SYS_application</typ:name>
<typ:scope/>
<typ:value>BI Cloud Adaptor</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>EXTRACT_JOB_TYPE</typ:name>
<typ:scope/>
<typ:value>VO_EXTRACT</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>EXTERNAL_STORAGE_LIST</typ:name>
<typ:scope/>
<typ:value>storage1</typ:value>
</typ:parameter>
</sch:requestParameters>
…
Request Parameters
|
Parameter |
Description |
|
SYS_className |
oracle.esshost.impl.CloudAdaptorJobImpl (Classname in the hosting app running the job) |
|
EXTRACT_JOB_TYPE |
Job Type of the extract. Valid values are:
|
|
EXTERNAL_STORAGE_LIST |
External Storages to upload the files to. The external storage names can be obtained from the BICC UI: Configure External Storage. Files can be uploaded to a maximum of 2 external storages. |
|
Sys_application |
BI Cloud Adaptor |
Optional Request Parameters:
Users can specify the following optional preferences. Its recommended to specify dataType as STRING for all the parameters.
|
Parameter |
Description |
Value |
|
STARTED_NOTIFICATION |
Should notification email be sent when the extract is started? |
true/false |
|
SUCCESS_NOTIFICATION |
Should notification email be sent when the extract succeeded? |
true/false |
|
FAILURE_NOTIFICATION |
Should notification email be sent when the extract failed? |
true/false |
|
MAIL_TO_ADDRESSES |
Comma separated list of email addresses to whom the notification should be sent. |
<List of Email Addresses> |
|
FILE_EXPIRY_DAYS |
Number of days after which the uploaded files will be expired. |
<number of days> |
|
FILE_SPLIT_SIZE |
Split size (MB) for the extract files. |
<number of MB> |
|
EXTRACT_COMPRESS_TYPE |
Compression type for the files |
zip/gzip |
|
EXTERNAL_STORAGE_FILE_NAME_PREFIX |
Uploaded file name prefix support for OCI Object Storage |
<prefix string> |
The response of such a successful Web Service call will look like the sample below:
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">
<env:Header>
<wsa:Action>submitRequest</wsa:Action>
<wsa:MessageID>abc1234</wsa:MessageID>
<wsa:RelatesTo>def5678</wsa:RelatesTo>
</env:Header>
<env:Body>
<ns0:submitRequestResponse
xmlns:ns0="http://xmlns.oracle.com/scheduler">
<requestId xmlns:ns2="http://xmlns.oracle.com/scheduler/types">49318</requestId>
</ns0:submitRequestResponse>
</env:Body>
</env:Envelope>
SOAP request to submit Jobs for Cloud Adaptor job - Given list of VOs
Another extraction option is to put the scope on a list of VO’s that should be extracted. As mentioned above, the job definition can be used as described earlier in this article. A sample parameter list would look like this XML payload snippet:
…
<sch:requestParameters>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>SYS_className</typ:name>
<typ:scope/>
<typ:value>oracle.esshost.impl.CloudAdaptorJobImpl</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>SYS_application</typ:name>
<typ:scope/>
<typ:value>BI Cloud Adaptor</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>EXTRACT_JOB_TYPE</typ:name>
<typ:scope/>
<typ:value>VO_EXTRACT</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>;STRING</typ:dataType>
<typ:name>EXTERNAL_STORAGE_LIST</typ:name>
<typ:scope/>
<typ:value/>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>DATA_STORE_LIST</typ:name>
<typ:scope/>
<typ:value>CrmAnalyticsAM.PartiesAnalyticsAM.Location,
CrmAnalyticsAM.PartiesAnalyticsAM.Organization,
CrmAnalyticsAM.PartiesAnalyticsAM.PartyContactEmail
</typ:value>
</typ:parameter>
</sch:requestParameters>
…
Request Parameters
|
Parameter |
Description |
|
SYS_className |
oracle.esshost.impl.CloudAdaptorJobImpl (Classname in the hosting app running the job) |
|
EXTRACT_JOB_TYPE |
Job Type of the extract. Valid values are:
|
|
EXTERNAL_STORAGE_LIST |
External Storages to upload the files to. The external storage names can be obtained from the BICC UI: Configure External Storage. Files can be uploaded to a maximum of 2 external storages. |
|
Sys_application |
BI Cloud Adaptor |
|
DATA_STORE_LIST |
<Comma separated list of Data Stores> |
Optional Request Parameters:
Users can specify the following optional preferences. Its recommended to specify dataType as STRING for all the parameters.
|
Parameter |
Description |
Value |
|
STARTED_NOTIFICATION |
Should notification email be sent when the extract is started? |
true/false |
|
SUCCESS_NOTIFICATION |
Should notification email be sent when the extract succeeded? |
true/false |
|
FAILURE_NOTIFICATION |
Should notification email be sent when the extract failed? |
true/false |
|
MAIL_TO_ADDRESSES |
Comma separated list of email addresses to whom the notification should be sent. |
<List of Email Addresses> |
|
FILE_EXPIRY_DAYS |
Number of days after which the uploaded files will be expired. |
<number of days> |
|
FILE_SPLIT_SIZE |
Split size (MB) for the extract files. |
<number of MB> |
|
EXTRACT_COMPRESS_TYPE |
Compression type for the files |
zip/gzip |
|
EXTERNAL_STORAGE_FILE_NAME_PREFIX |
Uploaded file name prefix support for OCI Object Storage |
<prefix string> |
The response of this Web Service call has the same content as the one for the other submit request above.
SOAP request to submit Jobs for Cloud Adaptor job - Given list of Offerings
A third extraction option is to put the scope on a list of offerings that should be extracted. As shown for the samples above the job definition can be used as described earlier in this article. A sample parameter list would look like this XML payload snippet:
…
<sch:requestParameters>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>SYS_className</typ:name>
<typ:scope/>
<typ:value>oracle.esshost.impl.CloudAdaptorJobImpl</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>SYS_application</typ:name>
<typ:scope/>
<typ:value>BI Cloud Adaptor</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>EXTRACT_JOB_TYPE</typ:name>
<typ:scope/>
<typ:value>VO_EXTRACT</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>EXTERNAL_STORAGE_LIST</typ:name>
<typ:scope/>
<typ:value/>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>OFFERING_LIST</typ:name>
<typ:scope/>
<typ:value>PUBLICSECTOR_AN_OFRNG,HR_AN_OFRNG</typ:value>
</typ:parameter>
</sch:requestParameters>
…
Request Parameters
|
Parameter |
Description |
|
SYS_className |
oracle.esshost.impl.CloudAdaptorJobImpl (Classname in the hosting app running the job) |
|
EXTRACT_JOB_TYPE |
Job Type of the extract. Valid values are:
|
|
EXTERNAL_STORAGE_LIST |
External Storages to upload the files to. The external storage names can be obtained from the BICC UI: Configure External Storage. Files can be uploaded to a maximum of 2 external storages. |
|
Sys_application |
BI Cloud Adaptor |
|
OFFERING_LIST |
<Comma separated list of Offerings to be extracted> |
Optional Request Parameters:
Users can specify the following optional preferences. Its recommended to specify dataType as STRING for all the parameters.
|
Parameter |
Description |
Value |
|
STARTED_NOTIFICATION |
Should notification email be sent when the extract is started? |
true/false |
|
SUCCESS_NOTIFICATION |
Should notification email be sent when the extract succeeded? |
true/false |
|
FAILURE_NOTIFICATION |
Should notification email be sent when the extract failed? |
true/false |
|
MAIL_TO_ADDRESSES |
Comma separated list of email addresses to whom the notification should be sent. |
<List of Email Addresses> |
|
FILE_EXPIRY_DAYS |
Number of days after which the uploaded files will be expired. |
<number of days> |
|
FILE_SPLIT_SIZE |
Split size (MB) for the extract files. |
<number of MB> |
|
EXTRACT_COMPRESS_TYPE |
Compression type for the files |
zip/gzip |
|
EXTERNAL_STORAGE_FILE_NAME_PREFIX |
Uploaded file name prefix support for OCI Object Storage |
<prefix string> |
The response of this Web Service call has the same content as the one for the other submit request above.
Schedule a Job based on the definition of a previous Job
A last sample for an extraction option is to schedule a job based on another job that has been run previously. As shown for the samples above the job definition can be used as described earlier in this article. A sample parameter list would look like this XML payload snippet:
…
<sch:requestParameters>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>SYS_className</typ:name>
<typ:scope/>
<typ:value>oracle.esshost.impl.CloudAdaptorJobImpl</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>SYS_requestCategory</typ:name>
<typ:scope/>
<typ:value>JobSchedule</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>SYS_application</typ:name>
<typ:scope/>
<typ:value>BI Cloud Adaptor</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>EXTRACT_JOB_TYPE</typ:name>
<typ:scope/>
<typ:value>VO_EXTRACT</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>LONG</typ:dataType>
<typ:name>JOB_ID</typ:name>
<typ:scope/>
<typ:value>142</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>EXTERNAL_STORAGE_LIST</typ:name>
<typ:scope/>
<typ:value>UCM</typ:value>
</typ:parameter>
</sch:requestParameters>
…
Request Parameters
|
Parameter |
Description |
|
SYS_className |
oracle.esshost.impl.CloudAdaptorJobImpl (Classname in the hosting app running the job) |
|
EXTRACT_JOB_TYPE |
Job Type of the extract. Valid values are:
|
|
EXTERNAL_STORAGE_LIST |
External Storages to upload the files to. The external storage names can be obtained from the BICC UI: Configure External Storage. Files can be uploaded to a maximum of 2 external storages. |
|
Sys_application |
BI Cloud Adaptor |
|
JOB_ID |
<Job Id> for which the schedule is being created. |
Optional Request Parameters:
Users can specify the following optional preferences. Its recommended to specify dataType as STRING for all the parameters.
|
Parameter |
Description |
Value |
|
STARTED_NOTIFICATION |
Should notification email be sent when the extract is started? |
true/false |
|
SUCCESS_NOTIFICATION |
Should notification email be sent when the extract succeeded? |
true/false |
|
FAILURE_NOTIFICATION |
Should notification email be sent when the extract failed? |
true/false |
|
MAIL_TO_ADDRESSES |
Comma separated list of email addresses to whom the notification should be sent. |
<List of Email Addresses> |
|
FILE_EXPIRY_DAYS |
Number of days after which the uploaded files will be expired. |
<number of days> |
|
FILE_SPLIT_SIZE |
Split size (MB) for the extract files. |
<number of MB> |
|
EXTRACT_COMPRESS_TYPE |
Compression type for the files |
zip/gzip |
|
EXTERNAL_STORAGE_FILE_NAME_PREFIX |
Uploaded file name prefix support for OCI Object Storage |
<prefix string> |
The response of this Web Service call has the same content as the one for the other submit request above.
Summarizing Submit Request Web Service
After showing multiple variances of a Submit Request call here is a complete sample of a SOAP payload to trigger an extraction of a set of various View Objects in BICC. It shows all elements from SOAP Header to SOAP Body that have been explained in detail above.
<soapenv:Envelope xmlns:sch="http://xmlns.oracle.com/scheduler" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/scheduler/types">
<soapenv:Header xmlns:wsa="http://www.w3.org/2005/08/addressing">
<wsse:Security soapenv:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<wsu:Timestamp wsu:Id="TS-DFF81D849D4367D0EE15898027916232">
<wsu:Created>2020-05-18T11:53:11.623Z</wsu:Created>
<wsu:Expires>2020-05-18T12:53:11.623Z</wsu:Expires>
</wsu:Timestamp>
<wsse:UsernameToken wsu:Id="UsernameToken-DFF81D849D4367D0EE15898027242921">
<wsse:Username>biadmin</wsse:Username>
<wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">ABCD123</wsse:Password>
<wsse:Nonce EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">yfqfeZd2hA2sEQChqhliPw==</wsse:Nonce>
<wsu:Created>2020-05-18T11:52:03.987Z</wsu:Created>
</wsse:UsernameToken>
</wsse:Security>
<wsa:MessageID>ujanke4711</wsa:MessageID>
<wsa:Action>submitRequest</wsa:Action>
</soapenv:Header>
<soapenv:Body>
<sch:submitRequest>
<sch:description>UJ Test ESS Web Service</sch:description>
<sch:jobDefinitionId>
<typ:name>BICloudConnectorJobDefinition</typ:name>
<typ:packageName>oracle.apps.ess.biccc</typ:packageName>
<typ:type>JOB_DEFINITION</typ:type>
</sch:jobDefinitionId>
<sch:application>oracle.biacm</sch:application>
<sch:requestedStartTime/>
<sch:requestParameters>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>SYS_className</typ:name>
<typ:scope/>
<typ:value>oracle.esshost.impl.CloudAdaptorJobImpl</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>SYS_application</typ:name>
<typ:scope/>
<typ:value>BI Cloud Adaptor</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>EXTRACT_JOB_TYPE</typ:name>
<typ:scope/>
<typ:value>VO_EXTRACT</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>DATA_STORE_LIST</typ:name>
<typ:scope/>
<typ:value>CrmAnalyticsAM.PartiesAnalyticsAM.Organization,CrmAnalyticsAM.PartiesAnalyticsAM.PartyContactEmail</typ:value>
</typ:parameter>
<typ:parameter>
<typ:dataType>STRING</typ:dataType>
<typ:name>EXTERNAL_STORAGE_LIST</typ:name>
<typ:scope/>
<typ:value/>
</typ:parameter>
</sch:requestParameters>
</sch:submitRequest>
</soapenv:Body>
</soapenv:Envelope>
If invoking such a request from SoapUI, the successful run of a test request will look like shown in screenshot below.
How to trigger BICC Web Services via programmatic interfaces
The real benefit of such a Web Service for controlling BICC jobs is the ability to run it via scripts or programs in an automated mode. One of the simplest way to run it would be the command line program curl:
curl -v --header "Content-Type: text/xml;charset=UTF-8" --data @submitMsg.xml https://<SaaS URL>:443/bi/ess/esswebservice
In this use case we have put the SOAP message into a file called submitMsg.xml. With the parameter -v we receive a full output of the program execution as shown in screenshot below:
As all the details about message authentication are part of the SOAP message below it is not required to pass any user credentials as a parameter to curl. The parameter --header "Content-Type: text/xml;charset=UTF-8" declares the message type of the SOAP request. As stated on top of this article the usage of scripts itself is not an ultimate choice as the message payload contains dynamic elements. If intended to productize such an automated approach it will be necessary to create a programmatic interface in Java or any other language to construct this payload before running the Web Service.
Other SOAP Actions available via Web Service
Beside the previously explained options how the Web Service API can be used to submit the request for a new BICC extraction, here are two other samples about further features being provided by those interfaces: the request to get the job state and the request to obtain job details.
SOAP Request to get the Job State
The previous samples were demonstrating the fact that a successful invocation of a submit request are returning the ESS Job ID. This value must be used now as a parameter to request the Job State via the soap action getRequestState. A message payload should look like this
<soapenv:Envelope xmlns:sch="http://xmlns.oracle.com/scheduler" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header>
<wsse:Security soapenv:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<wsse:UsernameToken wsu:Id="UsernameToken-096D9A2FA5EEE63DC514744911304045">
<wsse:Username>faadmin</wsse:Username>
<wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">xxxxxx</wsse:Password>
<wsse:Nonce EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">96M4RBdl+afFA6MUufbbAQ==</wsse:Nonce>
<wsu:Created>2016-09-21T20:52:10.404Z</wsu:Created>
</wsse:UsernameToken>
</wsse:Security>
</soapenv:Header>
<soapenv:Body>
<sch:getRequestState>
<sch:requestId>49318</sch:requestId>
</sch:getRequestState>
</soapenv:Body>
</soapenv:Envelope>
The response of a successful invocation will deliver the actual run state:
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">
<env:Header>
<wsa:Action>getRequestState</wsa:Action>
<wsa:MessageID>urn:uuid:b44f932b-2b21-41c0-b748-1ec5007fe05f</wsa:MessageID>
<wsa:RelatesTo>uuid:374006ab-6fc5-4073-950a-dd6018d1308c</wsa:RelatesTo>
</env:Header>
<env:Body>
<ns0:getRequestStateResponse xmlns:ns0="http://xmlns.oracle.com/scheduler">
<state xmlns:ns2="http://xmlns.oracle.com/scheduler/types">RUNNING</state>
</ns0:getRequestStateResponse>
</env:Body>
</env:Envelope>
Request Detail
Another samples is the request to obtain the Request Details via the soap action getRequestDetail. A message payload should look like this
<soapenv:Envelope xmlns:sch="http://xmlns.oracle.com/scheduler" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header>
<wsse:Security soapenv:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<wsse:UsernameToken wsu:Id="UsernameToken-096D9A2FA5EEE63DC514744918990386">
<wsse:Username>faadmin</wsse:Username>
<wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">xxxxxx</wsse:Password>
<wsse:Nonce EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">5WwwW1fii5/6DKpgBafreQ==</wsse:Nonce>
<wsu:Created>2016-09-21T21:04:59.038Z</wsu:Created>
</wsse:UsernameToken>
</wsse:Security>
</soapenv:Header>
<soapenv:Body>
<sch:getRequestDetail>
<sch:requestId>49318</sch:requestId>
</sch:getRequestDetail>
</soapenv:Body>
</soapenv:Envelope>
The response of a successful invocation will return the actual run details of the requested job:
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">
<env:Header>
<wsa:Action>getRequestDetail</wsa:Action>
<wsa:MessageID>urn:uuid:bc779307-1819-4667-8cec-b246405011a1</wsa:MessageID>
<wsa:RelatesTo>uuid:055a802b-ab9e-46f5-9a87-b54bcde19a4a</wsa:RelatesTo>
</env:Header>
<env:Body>
<ns0:getRequestDetailResponse xmlns:ns0="http://xmlns.oracle.com/scheduler">
<requestDetail xmlns:ns2="http://xmlns.oracle.com/scheduler/types">
<ns2:requestId>49318</ns2:requestId>
<ns2:description>Test_ESS</ns2:description>
<ns2:jobDefinitionId>
<ns2:type>JOB_DEFINITION</ns2:type>
<ns2:packageName>/oracle.apps.ess.biccc</ns2:packageName>
<ns2:name>BICloudConnectorJobDefinition</ns2:name>
</ns2:jobDefinitionId>
<ns2:dispatcherNode>bi_server1</ns2:dispatcherNode>
<ns2:processorNode>bi_server1</ns2:processorNode>
<ns2:workAssignmentId>
<ns2:type>WORK_ASSIGNMENT</ns2:type>
<ns2:packageName>/oracle/as/ess/essapp/internal</ns2:packageName>
<ns2:name>defaultWA</ns2:name>
</ns2:workAssignmentId>
<ns2:workshiftId>
<ns2:type>WORKSHIFT</ns2:type>
<ns2:packageName>/oracle/as/ess/essapp/internal</ns2:packageName>
<ns2:name>defaultWS</ns2:name>
</ns2:workshiftId>
<ns2:isCancellable>true</ns2:isCancellable>
<ns2:isHoldable>false</ns2:isHoldable>
<ns2:retried>0</ns2:retried>
<ns2:executionAttempt>1</ns2:executionAttempt>
<ns2:submitter>FAAdmin</ns2:submitter>
<ns2:runAsUser>FAAdmin</ns2:runAsUser>
<ns2:submissionTime>2016-09-21T21:00:38.563Z</ns2:submissionTime>
<ns2:requestedStartTime>2016-09-21T21:00:38.563Z</ns2:requestedStartTime>
<ns2:scheduledTime>2016-09-21T21:00:38.563Z</ns2:scheduledTime>
<ns2:actualStartTime>2016-09-21T21:00:53.362Z</ns2:actualStartTime>
<ns2:state>RUNNING</ns2:state>
<ns2:priority>4</ns2:priority>
<ns2:cause>Request in non-terminal state</ns2:cause>
<ns2:parentRequestId>0</ns2:parentRequestId>
<ns2:stepId>0</ns2:stepId>
<ns2:requestType>SINGLETON</ns2:requestType>
<ns2:requestParameters>
<ns2:parameter>
<ns2:dataType>INTEGER</ns2:dataType>
<ns2:name>SYS_priority</ns2:name>
<ns2:scope/>
<ns2:value>4</ns2:value>
</ns2:parameter>
<ns2:parameter>
<ns2:dataType>STRING</ns2:dataType>
<ns2:name>EXTRACT_JOB_TYPE</ns2:name>
<ns2:scope/>
<ns2:value>VO_EXTRACT</ns2:value>
</ns2:parameter>
<ns2:parameter>
<ns2:dataType>STRING</ns2:dataType>
<ns2:name>SYS_userName</ns2:name>
<ns2:scope/>
<ns2:value>FAAdmin</ns2:value>
</ns2:parameter>
<ns2:parameter>
<ns2:dataType>INTEGER</ns2:dataType>
<ns2:name>SYS_retries</ns2:name>
<ns2:scope/>
<ns2:value>0</ns2:value>
</ns2:parameter>
<ns2:parameter>
<ns2:dataType>STRING</ns2:dataType>
<ns2:name>SYS_className</ns2:name>
<ns2:scope/>
<ns2:value>oracle.esshost.impl.CloudAdaptorJobImpl</ns2:value>
</ns2:parameter>
<ns2:parameter>
<ns2:dataType>LONG</ns2:dataType>
<ns2:name>SYS_request_timeout</ns2:name>
<ns2:scope/>
<ns2:value>0</ns2:value>
</ns2:parameter>
<ns2:parameter>
<ns2:dataType>STRING</ns2:dataType>
<ns2:name>SYS_groupName</ns2:name>
<ns2:scope/>
<ns2:value>ESS_DefaultIsolationGroup</ns2:value>
</ns2:parameter>
<ns2:parameter>
<ns2:dataType>STRING</ns2:dataType>
<ns2:name>SYS_submittingApplication</ns2:name>
<ns2:scope/>
<ns2:value>oracle.biacm</ns2:value>
</ns2:parameter>
<ns2:parameter>
<ns2:dataType>BOOLEAN</ns2:dataType>
<ns2:name>SYS_EXT_userFileDirShared</ns2:name>
<ns2:scope/>
<ns2:value>false</ns2:value>
</ns2:parameter>
<ns2:parameter>
<ns2:dataType>INTEGER</ns2:dataType>
<ns2:name>SYS_requestExpiration</ns2:name>
<ns2:scope/>
<ns2:value>0</ns2:value>
</ns2:parameter>
<ns2:parameter>
<ns2:dataType>STRING</ns2:dataType>
<ns2:name>SYS_application</ns2:name>
<ns2:scope/>
<ns2:value>oracle.biacm</ns2:value>
</ns2:parameter>
<ns2:parameter>
<ns2:dataType>STRING</ns2:dataType>
<ns2:name>SYS_userFileDir</ns2:name>
<ns2:scope/>
<ns2:value>${ESS_ENV:jrfServerLogPath}/ess_request</ns2:value>
</ns2:parameter>
<ns2:parameter>
<ns2:dataType>INTEGER</ns2:dataType>
<ns2:name>SYS_reprocessDelay</ns2:name>
<ns2:scope/>
<ns2:value>5</ns2:value>
</ns2:parameter>
<ns2:parameter>
<ns2:dataType>BOOLEAN</ns2:dataType>
<ns2:name>SYS_executePast</ns2:name>
<ns2:scope/>
<ns2:value>true</ns2:value>
</ns2:parameter>
</ns2:requestParameters>
<ns2:ecid>5aa4a1152ec38837:-422f90c2:157393de3be:-8000-000000000006e4bc</ns2:ecid>
<ns2:processPhase>ExecuteFinalize</ns2:processPhase>
</requestDetail>
</ns0:getRequestDetailResponse>
</env:Body>
</env:Envelope>
Conclusion
With the proposed capabilities to use a Web Services interface for controlling and monitoring BICC jobs we have one more option for implementing an automation here. The intention of this article is to introduce into the general features and to provide some ideas how this WS interface can be validated via SoapUI. For an implementation of a job automation the use of scripting and programmatic components is mandatory. The explanation of these capabilities was not in scope of this article, but such might follow in future if this is a matter of common interest.
Last but not least: many thanks to Sireesha for her incredible support!