Load Data From Oracle Cloud ERP to Oracle EPM using PowerShell

Before we start, I am assuming that you have some knowledge of PowerShell and EPM Automate commands. I am also assuming that EPM Automate is installed on your system and that you have setup the import jobs in your EPM instance to import the data file. In this post we are looking at how to fetch data from Oracle ERP (SaaS ) and load into Oracle EPM (SaaS).

At a high level below are the steps involved in the process:

1. Invoke BI Report
2. Decode the base64 response
3. Upload the File to EPM
4. Invoke Import job in EPM

In this post we will take a look at using a combination of PowerShell and EPM Automate utility commands to accomplish the task at hand.

Invoking the BI Report

We will be utilizing the Oracle BI Publisher Web services in this post. You can read more about it here. To be more specific we will use “ReportService” web service and “runReport” method.

To keep it simple 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 request.xml.

• For the request header, set SOAPAction to “”
• ContentType to “text/xml; charset=utf-8”
• Method as “Post”
• InFile as request.xml (the SOAP Payload)
• OutFile as output.xml (response will be saved)

Decode base64 response

The response from web service is in base 64 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 your planning instance. The pre-requisite for this is to have EPM Automate installed on your system.

epmautomate login USERNAME PASSWORD_FILE URL IDENTITYDOMAIN
epmautomate uploadfile Positions.csv

Login to the Planning instance and then upload the file. To learn more about EPM Automate commands click here.

Invoke the Import Job in EPM

The last and 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.

epmautomate importdata Import_Position_Data Positions.csv
epmautomate logout

Improvements

You may notice that the user name and password are part of the SOAP Request payload. And that is not a good practice.

We will take a look at another Web Service in an upcoming post which helps us to add authentication details to the request header.

Shout out to my colleague Sharad for providing details of the alternate BI Web Service.

Conclusion

This approach allows you to run the job on-demand. You are not dependent on ERP team or any scheduled jobs in ERP to get the data file. There are other ways of doing the same process, however I find this to be more convenient from an EPM user’s perspective. Feel free to ask me questions on this process. If you want the complete code let me know.