Oracle EPM cloud has set of EPM Automate commands which are used to automate administration process for EPBCS. Admins can write batch scripts on MS-Dos, PowerShell or other batch scripting program to call the EPM Automate commands. Batch scripts can also be scheduled using Task Scheduler in Windows. This blog is for the admins who have good understanding of scripting and EPM Automate commands.
Oracle has introduced a new EPM Automate command in March – Release to upgrade the existing epmautomate commands. Please make sure the EPM Automate commands are upgraded using the below command.
command : epmautomate upgrade
One of the tasks for EPBCS Admins is to load the data from the legacy system to their Oracle EPBCS Cloud. FDMEE jobs are defined to load the data files to the EPBCS application. FDMEE is very fast in loading data files into the Oracle cloud but if the job has to be run multiple times in a day then this becomes a big pain for Admins. This blog is to show how we can automate the process using the EPM Automate commands in EPBCS to launch a rule and to generate error records in a readable format for Admins.
Readers require good scripting knowledge on Batch scripting as EPM automate log file need to be scanned using scripts to derive the process_ID from the log. Process_ID is the unique identifier of the Data rule instance submitted in FDMEE and the log file is postfix with Process_ID. Below is the logical flow with some code snapshots.
Logical Flow :
- Set the variables for inbox and error directory
- Login to EPBCS application using EPM Automate.
- Read the data file from the source directory.
- Upload the data file to data management file folders using EPM Automate command.
- Rename the file to ensure the file has been uploaded.
- Submit a report job using command runDMReport. Steps to create a batch file to run the report and derive process_ID can be referred in one of my blog
- Listfiles derives the list of files in the Data Management folders.
- Derive the last report name from the outbox/reports data management folder.
- Derive the process_id of the report submitted from the variable %lastline%.
- Add 1 to the process_ID of the report job to get the process_id of the data rule which will be submitted for loading the GL data.
- Submit the data rule which loads the file uploaded in the above step.
- After the job gets over, Download the process log based on the process id derived in our earlier step.
- Scan the log file for errors to see if there are any user specific errors. Common FDMEE error is 3303 which are encountered if there are any missing members in the EPBCS Application.
- Push the file which has captured the error details to error log directory.
- Delete the old error files so that admins can see always the latest error file.
- Below screenshot shows the kick-out file where Account member A_10101 is not a valid member.
FDMEE errors related to 3303 are more common but there may be additional error like input data file or folder missing. Batch program has to make sure all the errors are captured properly as the expectation is to refer the error file instead of log file. Log files are big and may not be easy for EPBCS admins to understand the issue. Above error file format can be opened in XL for easy analysis purpose.
After the script is tested successfully. Task Scheduler can be used to schedule the batch file based on Admin requirement. There are many blogs which can help in setting up a task in Task Scheduler. One of the blog with clear steps can be viewed here.