Call Business Intelligence API in Oracle Integration Cloud

This is a frequent requirement to call Business Intelligence (BI) service in Oracle Integration Cloud (OIC). This article focuses on how to call BI SOAP API in the OIC scheduler process and extract data.

See useful links

How to create a SOAP connection
How to create a Scheduled Integration

First, have a look at the sample request and response of BI SOAP API

BI SOPA Endpoint: https://ef-dev1.af.em1.oraclecloud.com/xmlpserver/services/v2/ReportService?wsdl

Method: runReport

Request

<x:Envelope xmlns:x="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v="http://xmlns.oracle.com/oxp/service/v2">
    <x:Header/>
    <x:Body>
        <v:runReport>
            <v:reportRequest>
                <v:reportAbsolutePath>/Custom/Integration/XX_sales.xdo</v:reportAbsolutePath>
                <v:sizeOfDataChunkDownload>-1</v:sizeOfDataChunkDownload>
            </v:reportRequest>
            <v:userID>ankur</v:userID>
            <v:password>#####</v:password>
        </v:runReport>
    </x:Body>
</x:Envelope>

Response:

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <soapenv:Body>
        <runReportResponse xmlns="http://xmlns.oracle.com/oxp/service/v2">
            <runReportReturn>
                <metaDataList xsi:nil="true"/>                <reportBytes>PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPCEtLUdlbmVyYXRlZCBieSBPcmFjbGUgQkkgUHVibGlzaGVyIC1EYXRhZW5naW5lLCBkYXRhbW9kZWw6X0N1c3RvbV9JbnRlZ3JhdGlvbl9YWE1</reportBytes>
                <reportContentType>text/xml</reportContentType>
                <reportFileID xsi:nil="true"/>
                <reportLocale xsi:nil="true"/>
            </runReportReturn>
        </runReportResponse>
    </soapenv:Body>
</soapenv:Envelope>

Notice the reportBytes field, BI provides the data into Base64. If we decode the same, it will provide the data in XML format as below:

<?xml version="1.0" encoding="UTF-8"?>
<DATA_DS>
   <P_REASON_CODE />
   <P_CUSTOMER_NUMBER />
   <G_1>
      <OU>OU Unit</OU>
      <WNAME>WNAME</WNAME>
      <ORDER_NUMBER>11</ORDER_NUMBER>
      <HEADER_ID>123</HEADER_ID>
      <ORDER_DATE>2018-010-04T00:00:00.000+00:00</ORDER_DATE>
      <CUSTOMER_NUMBER>1</CUSTOMER_NUMBER>
      <CUSTOMER_ID>11</CUSTOMER_ID>
      <AMOUNT>11</AMOUNT>
      <CURRENCY_CODE>INR</CURRENCY_CODE>
      <CUSTOMER_NAME>ab1</CUSTOMER_NAME>
   </G_1>
   <G_1>
      <OU>OU Unit</OU>
      <WNAME>WNAME</WNAME>
      <ORDER_NUMBER>11</ORDER_NUMBER>
      <HEADER_ID>123</HEADER_ID>
      <ORDER_DATE>2019-10-10T00:00:00.000+00:00</ORDER_DATE>
      <CUSTOMER_NUMBER>1</CUSTOMER_NUMBER>
      <CUSTOMER_ID>11</CUSTOMER_ID>
      <AMOUNT>11</AMOUNT>
      <CURRENCY_CODE>INR</CURRENCY_CODE>
      <CUSTOMER_NAME>ab1</CUSTOMER_NAME>
   </G_1>
   <G_3>
      <INSTANCE_ID>11</INSTANCE_ID>
      <ORCHESTRATION>10008</ORCHESTRATION>
      <OBJECT>1</OBJECT>
      <HOLD>22</HOLD>
      <User>AOO</User>
      <USERID>10</USERID>
   </G_3>
</DATA_DS>

Let’s go ahead and consume the BI API in OIC scheduled process.

Consume BI Service in OIC

Create a scheduled orchestration and drop the BI soap connection just below the Scheduled activity. From the wizard enter the endpoint name, select Next. From the Next screen select Operation Name “runReport” and finish the wizard

Open the SOAP mapper and map the request parameter as below:

• reportAbsolutePath
• sizeOfDataChunkDownload
• userID
• password

Drop a Stage activity below the SOAP Adapter and name as “WriteDataOnStage and click on the Next button. On the next screen, Select Operation as “Write File“, enter the file name as “test.xml“, Specify Output directory as “/temp“

On the next screen, select “XML Schema (XSD) document” from the drop-down

On the next screen select the opaque.xsd and Finish the wizard. Below is opaque.xsd

<?xml version = '1.0' encoding = 'UTF-8'?>  
<schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/"
            xmlns="http://www.w3.org/2001/XMLSchema" >
      <element name="opaqueElement" type="base64Binary" />
</schema>

Edit WriteDataOnStage mapper and map runBytes to opaqueElement.

Drop another Stage activity and name it as “ReadFileFromStage” and click on the Next button. Configure operation and select the Next button

• Choose Stage File Operation: Select “Read Entire File” operation
• Choose File Reference: No
• Specify the File Name: $WriteDataOnStage/nsmpr5:WriteResponse/nsmpr7:WriteResponse/nsmpr1:ICSFile/nsmpr1:Properties/nsmpr1:filename
• Specify the Directory to read From: $WriteDataOnStage/nsmpr5:WriteResponse/nsmpr7:WriteResponse/nsmpr1:ICSFile/nsmpr1:Properties/nsmpr1:directory

On the next screen, select “XML Schema (XSD) document” from the drop-down and click the Next button. From the next screen, choose the XSD file. The XSD file can be created online using XML. Use XML which came from reportBytes decoded value as I did above. Below will be the corresponding schema file.

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="DATA_DS">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:string" name="P_REASON_CODE"/>
        <xs:element type="xs:string" name="P_CUSTOMER_NUMBER"/>
        <xs:element name="G_1" maxOccurs="unbounded" minOccurs="0">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:string" name="OU"/>
              <xs:element type="xs:string" name="WNAME"/>
              <xs:element type="xs:byte" name="ORDER_NUMBER"/>
              <xs:element type="xs:byte" name="HEADER_ID"/>
              <xs:element type="xs:string" name="ORDER_DATE"/>
              <xs:element type="xs:byte" name="CUSTOMER_NUMBER"/>
              <xs:element type="xs:byte" name="CUSTOMER_ID"/>
              <xs:element type="xs:byte" name="AMOUNT"/>
              <xs:element type="xs:string" name="CURRENCY_CODE"/>
              <xs:element type="xs:string" name="CUSTOMER_NAME"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="G_3">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:byte" name="INSTANCE_ID"/>
              <xs:element type="xs:short" name="ORCHESTRATION"/>
              <xs:element type="xs:byte" name="OBJECT"/>
              <xs:element type="xs:byte" name="HOLD"/>
              <xs:element type="xs:string" name="User"/>
              <xs:element type="xs:byte" name="USERID"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Drop a forEach loop and configure options as below:

• Name: Data
• Repeating Element: $ReadFileFromStage/nsmpr12:ReadResponse/nsmpr1:DATA_DS/nsmpr1:G_1
• Current Element Name: Current

Drop a logger inside the for loop and print the OU name.

Integration is completed now. Let’s try to execute the Integration

Run the Integration

Activate the Integration. Click on the Action menu and click on Submit Now button. Go to the Monitoring tab and see the OU should get printed into the logger