Photo by Danny Meneses from Pexels

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).

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

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.

  1. userID : user who has access to run the BI report
  2. password : password for the user
  • 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.

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

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.

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.

Manager Strategic Cloud Services (Oracle Cloud ERP, EPM), Integration Specialist, Big Data, Data Science & Python Enthusiast)