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:
- Invoke BI Report
- Decode the base64 response
- Upload the File to EPM
- 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.
https://gist.github.com/raj-arun/9e7d7b8d62dfb9e4f543c094d76ca6e0
- reportAbsolutePath : Path to the report / .xdo file
- userID : user who has access to run the BI report
- password : password for the user
Now let’s write the PowerShell script to invoke the BI Report. Make sure to give the correct Oracle Cloud URL.
https://gist.github.com/raj-arun/91f4c94f5bdf73f86a10182101781d21
https://gist.github.com/raj-arun/91f4c94f5bdf73f86a10182101781d21#file-invoke_bi_report-ps1
- 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.
https://gist.github.com/raj-arun/b1f8b9801130f4bd3a88bc8f2f13a7af
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.