EPM Automate (EPM-A) is a command-line tool that allows users to perform tasks within an EPM Cloud environment. Tasks can be run directly in the command line or as a batch or PowerShell script file. EPM-A also works on Linux. In the background, EPM automate is leveraging REST APIs to communicate with target applications.
EPM Automate Utility enables Service Administrators to automate many repeatable tasks including the following:
- Import and export metadata, data, artifact and application snapshots, templates, and Data Management mappings
- Upload files into service instances, list files and delete files from the service
- Download snapshots, reports, metadata and data files from the service
- Run business rules on data, and refresh the application
- Copy data from one database to another: typically, from a block storage database to an aggregate storage database or from a block storage database to another block storage database
- Run a Data Management batch rule
- Generate Data Management reports, provisioning reports, and user audit reports
- Import pre-mapped balance data, currency rates, pre-mapped transactions, balances data, and profiles into Oracle Account Reconciliation Cloud
- Copy profiles to a period to initiate the reconciliation process
- Deploy the calculation cube of an Oracle Profitability and Cost Management Cloud application
- Clear, copy, and delete Point of Views in Oracle Profitability and Cost Management Cloud applications • Replay Oracle Smart View for Office load on a service instance to enable performance testing under heavy load
- Import supplemental data from a file into Oracle Financial Consolidation and Close Cloud
Enterprise Data Management (EDM) is a metadata management tool that works with EPM and other products, not even limited to Oracle. It can be used to manage dimension members and hierarchies for nearly any application. EDM is included in the Enterprise Suite of EPM Cloud for a limited usage of 5000 members. Utilizing EDM to streamline maintenance across applications reduces monthly maintenance hours and keeps application dimensions synchronized.
Oracle views integration as the movement of data files among its products, regardless of the background process. This is the case with EDM just as it has been with Data Management accepting data from any source system going back to FDMEE and earlier. On the EDM site, Oracle touts the power of REST interfaces. Oracle provides a basic script in the EPM-A guide for synchronizing a dimension and mapping between EDM to any EPM Cloud product using EPM-A. As these scripts can’t be used directly in the production environment, we have two options: manually push metadata or integrate with EPM Automate.
Let’s use FCC as an example. To integrate EDM with FCC for a single dimension requires 8 EPM-A commands. Add a database refresh and a single consolidation and it becomes 10 steps. Performing these steps manually is cumbersome. A user would need to access a dozen or more screens across two environments, as well as export and import files for each dimension, by application.
Using EDM for a single dimension for a single application is not the intended use case. Account, Entity, and all custom dimensions for FCC, and PBCS would be a normal starting point. Each dimension adds 4 steps to our example. Each target application would nearly duplicate the total. As dimensions and target applications rise, the benefits of using EPM-A grow quickly. Pushing metadata to test environments is the best practice. A manual process requires a full doubling of effort. A scripted process needs only another copy with a different target URL.
Depending on the size of the dimension and application, the refresh and consolidation steps could take several minutes to several hours. It is not efficient to have an administrator performing all these steps manually and watching them progress. Another win for EPM Automate.
It is worth noting here that this process accesses and moves data between multiple environments which could not be accomplished with Groovy. Groovy can only perform a single function (currently) and cannot connect to another instance and cannot access any data outside of that instance, up or down.
The last two benefits of using EPM-A with EDM have nothing to do with Oracle. First, scheduling. Once the process is written in a script it can be executed with Windows Task Scheduler or your scheduling system of choice. Run the process at a specific time and day, run it off-hours, and even run it every day. Second, script functions. While this whole process can be done with just EPM-A commands, it is greatly enhanced with some scripting best practices. Write information to a log file like start and stop times and any errors. Trap and handle errors. A fully EPM-A script will plow through commands when errors happen. This is not ideal and could cause issues in the target applications. Notify stakeholders with an email of successful completion or when an error occurs and what it is.
Source Data + Simple Scripts + Scheduler = Connected Automation!
Manual Vs Automation
The amount of time it took to take manual steps for walkthrough 5 and resolve errors is likely a double-digit number of minutes. And the amount of time it took to take automated steps for walkthrough 5 and resolve errors is far less than that! Additionally, you can do something else with your time after kicking off the routine. You can really see the power of EPM Automate when you consider automating multiple jobs in one routine daily. In multiple business cases, you can see how useful these scripting processes can be.
For example, imagine setting up the automation to load multiple load files instead of going through all the clicks in Data Management for each load file. Thus, effective scripting and process organization are key and will help you save time in troubleshooting.
The following script is an example of how an EDM to EPM metadata push could work for Account and Cost Center dimensions to PBCS and FCC based on the sample script in the EPM Automate guide from Oracle.
set EDMUSER=EDMUser@datavail.com
set EDMSVR=https://epm5-default.epm.us2.oraclecloud.com/epmcloud
set EDMPWDFILE=C:\Oracle\EDM.epw
set EDMAPP=PLAN
set EDMDIM=Account
set EDMLOC=C:\Oracle\Logs\EDMlog.txt
set EPMUSER=EPMUser@datavail.com
set EPMSVR=https://epm2-default.epm.us2.oraclecloud.com/epmcloud
set FCCSVR=https://epm-default.epm.us2.oraclecloud.com/epmcloud
set EPMIMPJOB=EDM_Account
set DIMFILE=inbox/Account.csv
SET LOG_FILENAME=EDM_EPM_Push
set log_path=C:\Oracle\Client1\Logs
SET JOBLOGS=%LOG_PATH%\%JOBNAME%.log
ECHO %date% %time% Starting >> %JOBLOGS%
::Export EDM PBCS dimensions
call EPM-Automate login %EDMUSER% %EDMPWDFILE% %EDMSVR%
call EPM-Automate exportdimension %EDMAPP% Account Account.csv
call EPM-Automate exportdimension %EDMAPP% "Cost Center" CostCenter.csv
ECHO %date% %time% Dimensions PBCS exported >> %JOBLOGS%
call EPM-Automate logout
call EPM-Automate login %EPMUSER% %EDMPWDFILE% %EPMSVR%
::Import dimensions to PBCS
call EPM-Automate deletefile "Account.csv"
call EPM-Automate deletefile "CostCenter.csv"
call EPM-Automate copyFileFromInstance "Account.csv" %EDMUSER% %EDMPWDFILE% %EDMSVR% "Account.csv"
call EPM-Automate copyFileFromInstance "CostCenter.csv" %EDMUSER% %EDMPWDFILE% %EDMSVR% "CostCenter.csv"
call EPM-Automate Importmetadata EDM_Account
call EPM-Automate Importmetadata EDM_CostCenter
if %errorlevel% NEQ 0 goto :ERR_EXIT ImportDimsPBCS
ECHO %date% %time% Dimensions imported to PBCS >> %JOBLOGS%
::Refresh PBCS
call EPM-Automate refreshcube APP_Refresh
if %errorlevel% NEQ 0 goto :ERR_EXIT RefreshPBCS
ECHO %date% %time% Refresh complete PBCS >> %JOBLOGS%
call EPM-Automate logout
call EPM-Automate login %EDMUSER% %EDMPWDFILE% %EDMSVR%
::Export EDM PBCS dimensions
call EPM-Automate exportdimension FCCS Account Account.csv
call EPM-Automate exportdimension FCCS "Cost Center" CostCenter.csv
ECHO %date% %time% Dimensions FCCS exported >> %JOBLOGS%
call EPM-Automate logout
call EPM-Automate login %EPMUSER% %EDMPWDFILE% %FCCSVR%
::Import dimensions to FCCS
call EPM-Automate deletefile "Account.csv"
call EPM-Automate deletefile "CostCenter.csv"
call EPM-Automate copyFileFromInstance "Account.csv" %EDMUSER% %EDMPWDFILE% %EDMSVR% "Account.csv"
call EPM-Automate copyFileFromInstance "CostCenter.csv" %EDMUSER% %EDMPWDFILE% %EDMSVR% "CostCenter.csv"
call EPM-Automate Importmetadata "EDM_Account"
call EPM-Automate Importmetadata "EDM_CostCenter"
if %errorlevel% NEQ 0 goto :ERR_EXIT ImportDimsFCCS
ECHO %date% %time% Dimensions FCCS imported >> %JOBLOGS%
call EPM-Automate refreshCube Full_Refresh
if %errorlevel% NEQ 0 goto :ERR_EXIT RefreshFCCS
ECHO %date% %time% Refresh complete FCCS >> %JOBLOGS%
call EPM-Automate runbusinessrule Consolidate Scenario=Actual Year=FY22 Period=Dec Entity="FCCS_Total Geography"
if %errorlevel% NEQ 0 goto :ERR_EXIT ConsolFCCS
ECHO %date% %time% Consolidate complete FCCS >> %JOBLOGS%
call EPM-Automate logout
:CLEAN_EXIT
ECHO %date% %time% Clean Exit >> %JOBLOGS%
set "emailsubject="EDM to EPM Succeeded""
call EPM-Automate sendmail Mail@datavail.com %emailsubject% BODY="EDM to EPM success."
GOTO :LEAVE
:ERR_EXIT
ECHO %date% %time% Error at %1 >> %JOBLOGS%
ECHO %date% %time% Error Level %ERRORLEVEL%
set "emailsubject="EDM to EPM Failed at step %1""
call EPM-Automate sendmail Mail@datavail.com %emailsubject% BODY="EDM to EPM failed. Review log for details."
GOTO :LEAVE
:LEAVE
Exit /b
Even though it feels like going back to 1985, EPM Automate can be a powerful tool for the Cloud. EPM Automate is a command-line tool provided by Oracle for use with EPM Cloud products, runs on REST API, and can be used on Windows and Linux.
Download our presentation to learn more about the benefits of using EPM Automate