Oracle BI Answers, also known as 'Analyses' or 'Analysis Editor', is a reporting tool that is part of the Oracle Transactional Business Intelligence (OTBI), and available within the Oracle Human Capital Management (HCM) product suite.
This article will outline an approach in which a BI Answers report will be used to extract data from HCM via web services. This provides an alternative method to the file based loader process (details of which can be found here)
This can be used for both Cloud and On-Premise versions of Oracle Fusion HCM.
Please note - the HCM team recommends the HCM extract process as the preferred approach to extract data from HCM. That method is outlined here in this article.
Another recent method where the 'Data Sync' tool is used, which is similar to that described below, but helps to automate the process and adds true incremental load capability is covered in this article.
During regular product updates to Oracle HCM, underlying data objects may be changed. As part of the upgrade process, these changes will automatically be updated in the pre-packaged reports that come with Oracle HCM, and also in the OTBI 'Subject Areas' - a semantic layer used to aid report writers by removing the need to write SQL directly against the underlying database.
As a result it is highly recommended to use either a pre-packaged report, or to create a new report based on one of the many OTBI Subject Areas, to prevent extracts subsequently breaking due to the changing data structures.
Pre-packaged reports can be found by selecting 'Catalog', expanding 'Shared Folders' and looking in the 'Human Capital Management' sub-folder. If a pre-packaged report is used, make a note of the full path of the report shown in the 'Location' box below. This path, and the report name, will be required for the WSDL.
To create an Ad-Hoc report, a user login with the minimum of BI Author rights is required.
a. Select 'New' and then 'Analysis'
b. Select the appropriate HCM Subject Area to create a report.
c. Expand the folders and drag the required elements into the report.
d. Save the report into a shared location. In this example this is being called 'Answers_demo_report' and saved into this location.
This path will be referenced later in the WSDL.
To create and test the Web Service, this post will use the opensource tool SoapUI. This is free and can be downloaded here:
Within SoapUI, create a new SOAP project. For the Initial WSDL address, use the Cloud or On-Premise URL, appending ‘/analytics-ws/saw.dll/wsdl/v7′
This will list the available WSDLs
1. Within SoapUI, expand out the ‘SAWSessionService’ and then ‘logon’. Make a copy of the example ‘Request’ WSDL, then update it to add the username and password for a user with credentials to run the BI Answers report.
Run that WSDL and a sessionID is returned:
2. In SoapUI expand ‘XmlViewService’ / ‘executeXMLQuery’. Make a copy of the example ‘Request’ WSDL. Edit that, insert the BI Answers report name and path into the <v7:reportPath> variable, and the SessionID from the first step into the <v7:sessionID> variable.
Note that while in the GUI the top level in the path was called 'Shared Reports', in the WSDL that is replaced with 'shared'. The rest of the path will match the format from the GUI.
You will notice a number of other options available. For this example we are going to ignore those.
You can then execute the web service request. The report returns the data as an XML stream, which can then be parsed by your code.
This post demonstrated a simple method to leverage BI Answers and the underlying OTBI Subject Areas within Oracle HCM, to create and call a report via web service to extract data for a down stream process.