How to convert OTBI report into XML output

The purpose of the document is to provide the steps to invoke OTBI report through Oracle Integration Cloud Service (ICS/OIC) and convert the response into XML format.

High-Level Installation Steps: 

1. Create a sample OTBI report in the ERP Cloud
2. Invoke OTBI report through Oracle Integration Cloud Service (ICS/OIC)
3. Download the IAR file of the ICS Orchestration.
4. Edit the XSL file in the downloaded IAR file.
5. Upload the IAR file back to ICS 
6. Submit the process

1. Creating sample OTBI Report in ERP Cloud

Detailed steps for creating a sample report in ERP Cloud.

1. Navigate to Reports and Analytics and click on New -> Analysis.

2. Select a Subject Area from the pop up

3. Drag some of the columns from subject areas to selected columns and click on Save.

4. Give a name to the report (OTBI Sample) and note down the path in which you are saving the report.

For Example the path here is ( /Shared Folders/OTBI Report/OTBI_Sample )

2. Creating the connection in the ICS.

Service WSDL URL: https://(FADomain,FSCMServices)/analytics-ws/saw.dll/wsdl/v7

1. Open the ICS Instance and navigate to ‘Connections’ -> ‘Create’
2. Select the ‘SOAP’ Adapter and then give a name to the connection. Ex: AnalyticsWebService

                Clicking on create will open the newly created connection

WSDL: – https://ERPInstanceURL/analytics-ws/saw.dll/wsdl/v7

3. In the connection Properties, click on ‘Configure Connectivity’ and add the Service WSDL URL.

4. In the Security panel, click on ‘Configure Security’ and add the ERP UI Username, Password of the ERP user.

5. Click on ‘Test’ and then click on ‘Validate and Test’.

3. Creating Orchestration type Integration to run the OTBI report.

Navigate to Integrations and click on ‘Create’. Select the Orchestration style in the pop up.

1. Select the ‘Schedule’ type trigger for the integration. Enter the Name of the integration and click on ‘Create’.

2. Drag and drop the ‘AnalyticsWebService’ connection created in the previous steps after the ‘Schedule’.

3. Enter the name of the activity and click ‘Next’. To run the OTBI report we first need to get the Session ID from a ‘SAWSessionService’ Operation in the same web service.

4. In the Operations page select ‘SAWSessionService’ under Service dropdown and select ‘Logon’ under the Operations dropdown.

Keep clicking ‘Next’ until the summary and then click ‘Done’.

5. Once you click ‘Done’, the SOAP service is displayed under ‘Schedule’ along with mapping activity. Click on the Map activity and click on the ‘Edit’ symbol.

6. In the Map activity enter the ERP UI username and password. Click Validate and then ‘Close’.

7. Drag the same connection as in ‘Step 2’. Select ‘XMLViewService’ under Service dropdown and select ‘executeXMLQuery’ under the Operations dropdown. 

Follow the similar steps mentioned in Step 4, 5 and open the map activity.

8. In the Map activity under ‘Report Path’ enter the OTBI report path. Map the SessionID from source with SessionID in Target and click ‘Validate’.

Enter ( “” ) in the Output Format, async, maxRowsPerPage, refresh, PresentationInfo, type. This indicates that we are passing null in that field. Please note that you can even pass values as per your requirement. 

9. Click ‘Validate’ and then ‘Close’ in the Map Activity. Click on ‘Save’ and then ‘Close’.

 10. Now, in the integrations page locate ‘OTBI_Report_Integration’ and click on ‘Switch to Activate’ to activate the integration.

11. In the pop-up check both ‘Enable Tracing’ and ‘Include Payload’. Click on Activate once you check both the check boxes.

12. Click on the ‘Menu’ and click on ‘Submit Now’. In the Monitoring section, select the integration which we have submitted and click on it.

 13. Once the integration is opened, click on the ‘menu’ and click on ‘Activity Stream’.

14. Expand the ‘ExcuteXMLQuery’ response payload. You can see that the data fetched is ‘String’ and you will find the ‘&lt’ character in between data.

4. Write the fetched string into proper XML.

Navigate to Integrations and select ‘OTBI_Report_Integration’. Deactivate the integration and select ‘Edit’ from the ‘Menu’.

1 . Click on ‘Actions’ and drag ‘Stage File’ under the ‘Run OTBI report’ SOAP activity.

2. Specify the name of the activity in the first step and click on ‘Next’.

3. Select ‘Write File’ in the Stage File Operation field. Now specify the name and directory of the file where we want to write the OTBI response in ‘.xml’ format

Note: This file and directory is temporary and is not accessible.

4. In the Schema Options check the ‘Select an existing schema from the file system’ option and click next.

5. Now upload the ‘Opaque.xsd’ file from your local. This file has format such that it converts the received data into proper string. Please find xsd file below.

Click on ‘Next’ and ‘Done’. Open the mapper which was created for the ‘Stage Write’ activity.

6. Map the ‘Rowset’ from source to ‘OpaqueElement’ in the target and click on ‘Validate’.

7. Export the integration from to ICS to local system. Rename the downloaded ‘.iar’ file to ‘.zip’.

8. Unzip the renamed file and search for ‘.xsl’ files. There are 3 files as there are 3 mapper activities in our integration. 

9. Open these files and one of these files will contain the source and target of the ‘Stage Write’ activity.

Replace the ‘Value-of’ function to ‘Copy-of’ function and add ‘oraext:parseXML’ before the ‘Source’ mapping as below and save the file.

10. Save the ‘.xsl’ file after making the changes and rename the file back to ‘.iar’.

11. Import this file to ICS instance. Activate the integration and submit it with both ‘Trace’ and ‘Payload’.

12. Navigate to monitoring and navigate to the submitted Run and open the activity stream like step 13 in section-3.

13. We can notice that the ‘&lt’ text is not present under the ‘Stage Write’ activity payload and the data is now in proper ‘xml’.