Load Data from Oracle ERP Cloud to Oracle EPM SaaS using PowerShell

In this post, we’ll look at how to fetch data from Oracle ERP (SaaS) and load it into Oracle EPM (SaaS). 

*This post assumes that the reader has some knowledge of PowerShell and EPM Automate commands.  It also assumes that EPM Automate is already installed and that the import jobs are set up in the EPM instance to import the data file.

The high-level steps involved in the process are:

• Invoke BI Report
• Parse and Decode Base64 Response
• Upload File to EPM
• Invoke Import Job in EPM

Here, we take a look at using a combination of PowerShell and EPM Automate utility commands to accomplish the task at hand.

Invoking the BI Report

In this scenario, we are using Oracle BI Publisher Web Services.  More specifically, we’re using the “ExternalReportWSSService” web service and the “runReport” method.

In an effort to simplify this example, the BI report does not have any parameters.  The output is in CSV format.  

Create a SOAP payload file, as shown below, and save it as saopRequest.xml.

Make sure the path to the BI report is populated correctly. Look for reportAbsolutePath in the above code snippet.  Be sure to include “.xdo” at the end of the report name.  It is assumed that the report is available in the shared folders.

Now, let’s write the PowerShell script to invoke the BI report.  Make sure to use the correct Oracle Cloud URL.  You need the following information to invoke the web service:

• User Name to Access BI Report
• Password of the BI User
• WSDL

Store all of this information in a json file.  Let’s name it biDetails.json.

Be sure to provide the correct username, password, and URL to the BI server.

Moving on to the PowerShell Script, let’s read the data file to get the details that are required to invoke the web service. We will also need to pass the authorization details as part of the request header.

In the above code snippet, we created the encoded credential string, which is a base64 encoded string of user name and password. The encoded string is then added to the request header.

The next code snippet invokes the BI report.

Make sure the following are set correctly:

• ContentType to “application/soap+xml;charset=utf-8”
• Method as “Post”
• InFile as soapRequest.xml (the SOAP Payload)
• OutFile as outFile.xml (response will be saved)

outFile.xml is the response from the web service. The report data is base64 encoded and is available in the xml response. The specific xml tag is reportBytes.

Parse and Decode the Report Data

The response from the web service is in base64 encoded format. We have to decode it to save the data to a csv file. Use the below code snippet to decode the response.

In the above code snippet, we are reading the output.xml file and generating the data file. The data is saved as “Positions.csv” in the current working directory.

Upload File to EPM

Once the data file is generated from the BI report, we can use EPM Automate utility commands to upload the file to the planning instance. The pre-requisite for this is to have EPM Automate installed on your system.

Log in to the Planning instance and upload the file. To learn more about EPM Automate commands, click here.  You may have to give the full path to the file in the upload command.

Invoke the Import Job in EPM

The final step is to invoke the job in Planning to import the data. The pre-requisite to this step is to define the Import Job in EPM. Use the below command to invoke the import job and log out of the Planning instance.

Import_Position_Data is the import job defined in EPM.

Conclusion

This approach allows you to run the job on-demand. You are not dependent on the ERP team or any scheduled jobs in ERP to get the data file. There are other ways of doing this, but this is the most convenient, from an EPM user’s perspective.  

As always, if you have any questions or comments, or if you want the complete code, please leave a note below and one of our experts will get back to you.

In future posts, we’ll take a look at the same process, but using Unix Shell Scripts.  Stay tuned!