X

Best Practices from Oracle Development's A‑Team

Using Oracle BI Answers to Extract Data from HCM via Web Services

Introduction

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.

Main 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

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.

Windows7_x64

Ad-Hoc Reports

To create an Ad-Hoc report, a user login with the minimum of BI Author rights is required.

a. Select 'New' and then 'Analysis'

Windows7_x64

b. Select the appropriate HCM Subject Area to create a report.

Windows7_x64

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.

/Shared Folders/Custom/demo

This path will be referenced later in the WSDL.

Edit_Post_‹_ATeam_Chronicles_—_WordPress

Building Web Service Request

To create and test the Web Service, this post will use the opensource tool SoapUI.  This is free and can be downloaded here:

https://www.soapui.org

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′

For example:

https://cloudlocation.oracle.com/analytics-ws/saw.dll/wsdl/v7

or

https://my-on-premise-server.com/analytics-ws/saw.dll/wsdl/v7

This will list the available WSDLs

 

Calling the BI Answers report is a 2 step process

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.

Summary

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.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Recent Content