Before we start, I am assuming that you have some knowledge of Oracle BI and PowerShell. You can use similar concepts to invoke Web Service using Python, Java, cURL. I will be writing another post on invoking the BI report using Python. This post focuses on a report developed in Oracle Cloud.
You may also read my previous blog on loading data from Oracle Cloud to Oracle EPM. In that post I utilized the ReportService web service. It worked pretty well.
However one of the drawbacks was the fact the user name and password were part of the payload, which is not a good practice.
In this post we will be looking at an alternate Web service; ExternalReportWSSService. As you will see later in the post, the advantage of this web service is that you can pass the authentication details as part of the Request Header. So let’s get started.
At a high level below are the steps involved in the process:
- Read BI login credential details
- Construct the SOAP request Header with authentication values
- Invoke BI Report
- Decode the base64 response and Generate CSV File
Read BI Login Credential Details
Rather than hard coding the username and password, I decided to store the username, password and wsdl end point in a file. This way you have the option of encrypting the username and password at rest. See the loginDetails.json file.
I have included default values for the username and password. Make sure you enter the correct username and password instead of the default values.
Construct the SOAP request Header with Authentication Values
To invoke the BI report we need to pass the encrypted username and password as part of the Web Service Request Header. Here is the code snippet that performs the following operations:
- Read credential details from loginDetails.json file
- Encrypts the username and password
- Add the encrypted credentials to the Request Header
Invoking the BI Report
We will be utilizing the Oracle BI Publisher Web services in this post. To be more specific, we will use “ExternalReportWSSService” web service and “runReport” method.
The BI report has a parameter (see lines 6–13) and I will explain it later in the post. The output is in CSV format. Create a SOAP payload file as shown below and save it as EPM_Account_Hierarchy.xml.
There are three important fields that you have to populate:
- reportAbsolutePath : Path to the report / .xdo file (line 14)
- name: Parameter Name (line 8) defined in Oracle BI
- item: Parameter Value (line 10)
Here is the code snippet to invoke the BI Report
Decode the base64 response and Generate CSV File
The response from web service is in base 64 encoded format. The next step is to decode it and save the data to a csv file. Use the below code snippet to decode the response and generate a CSV file.
I hope you find this post to be helpful. Feel free to ask me questions on this process. If you want the complete code, head on over to my GitHub repository.
In the next post, we will take a look at the same process using Unix Shell Script.