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:

SoapUI_4_6_4

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.

3

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.

Comments

  1. Atul Jha says:

    I am sure this issue is resolved for other individuals, but for added measure, some points i observed during my testing.

    1) Spaces work in the reportPath tag

    I was successfully able to get the data using the following report path.
    Analysis Name: HCM_Title
    Path: /shared/Custom/Datamodels for Exports/
    Fully Qualified Path in Tag: /shared/Custom/Datamodels for Exports/HCM_Title

    2) I was getting exceptions (Path not found, Unauthorized user, Object not found)
    Resolution for Unauthorized:
    make sure you get the sesison id again

    Resolution for Path Not Found:
    The Path for the report should match what is seen on report properties. That can differ from the UI.
    For instance, the /Shared Folders/ folder’s true path is /shared/
    To get the path, go to the analysis > click more > Properties. there you can find the report path.

    Resolution for Object for found:
    I had a few reports (not analysis) which i was trying to access.
    This approach did not not work for me with reports (.xdo files) and corresponding data models (.xdm files).

    Hope this helps

  2. Hi Richard,

    Even I am not able to test the above solution. Each time it gives me ‘path not found’ error.

    1. Reportpath is correctly mentioned as – /shared/Custom/Financials/Revenue Management/Sale Commission Report

    2. ‘Owner’ of the report and the credentials used in SOAPUI to call the webservice are the same.

    Please suggest, what could be the reason for this issue.

    Thanks
    Shikha

    • I wonder if it’s because you have space characters in your folder names. Perhaps change those to underscore characters (so ‘Revenue_Management’ instead of ‘Revenue Management’). If you can’t – then perhaps put ‘ or ” characters around the whole path (not sure which – I’d try with both)

  3. Amitesh Kumar says:

    This worked like a charm. Thanks for the quick response.

    Cheers
    AK

  4. Amitesh Kumar says:

    Hi Richard,

    I’m trying to test the above solution but constantly getting “Path not found” even though I have double checked the path and was able to logon using my credentials.

    Please help.

    Thanks
    Amitesh Kumar

    • if it’s in ‘Shared Folders’ – then the path you use for that first level is ‘/shared/’

      So for instance ‘Shared Folders/custom/demo_report’ in the OTBI GUI – would become ‘/shared/custom/demo_report’ when you call through web-services.

      Also – make sure the user who is connecting with has rights to that report / path. If you created with one user, but are trying to access the report with another – that might cause this issue too.

      Let me know if this helps. cheers

Add Your Comment