Automate Daily Data Integration

This scenario explores the use of a sample script to automate data integration on a regular basis.

Create a batch (.bat) or shell (.sh) file that contains script similar to the following to automate data integration-related activities. The following sample script for Windows automates daily application data integration by completing these activities:

  • Sign into an environment using the specified URL, user name and password file.
  • Delete DailyPlanData if it is present.
  • Upload DailyPlanData into the service.
  • Run business rule Clear Plan Targets on plan type Plan1.
  • Import data using job name LoadDailyPlan.
  • Run business rule Balance Sheet - Plan.
  • Run business rule Allocate Plan Targets.
  • Delete DailyTarget.zip if it is present.
  • Export data into DailyTarget.zip using job name ExportDailyTarget.
  • Download DailyTarget.zip to your server and appends the timestamp.
  • Sign out of the environment.

Note:

If you repurpose this script for your use, ensure that you modify the values of url, user, and passwordfile parameters. Additionally, you may modify the values of dataimportfilename, dataexportfilename, importdatajobname, exportdatajobname, br_clear, br_calculatebalancesheet, and br_allocatetarget parameters to suit your requirements

See Automating Script Execution for information on scheduling the script using Windows Task Scheduler.

automateDailyIntegration.bat Script Contents

@echo off
rem Sample Script to demonstrate daily data integration with 
rem Cloud EPM application.
rem This script uploads Plan data, clears target numbers,
rem runs a business rule to calculate balance sheet data, and 
rem recalculates target numbers on the Vision demo application
rem Please update these parameters
SET url=https://example.oraclecloud.com
SET user=serviceAdmin
SET dataimportfilename=DailyPlanData.csv
SET dataexportfilename=DailyTarget
SET importdatajobname=LoadDailyPlan
SET exportdatajobname=ExportDailyTarget
SET br_clear=Clear Plan Targets
SET br_calculatebalancesheet=Balance Sheet - Plan
SET br_allocatetarget=Allocate Plan Targets
SET passwordfile=examplePassword.epw

rem Executing EPM Automate commands
CD /D %~dp0
call epmautomate login %user% %passwordfile% %url%
IF %ERRORLEVEL% NEQ 0 goto :ERROR
for /f %%i in ('call epmautomate listfiles') do if %%i==%dataimportfilename% (call epmautomate deletefile %%i)
IF %ERRORLEVEL% NEQ 0 goto :ERROR
call epmautomate uploadfile %dataimportfilename%
IF %ERRORLEVEL% NEQ 0 goto :ERROR
call epmautomate runbusinessrule "%br_clear%"
IF %ERRORLEVEL% NEQ 0 goto :ERROR
call epmautomate importdata "%importdatajobname%"
IF %ERRORLEVEL% NEQ 0 goto :ERROR
call epmautomate runbusinessrule "%br_calculatebalancesheet%"
IF %ERRORLEVEL% NEQ 0 goto :ERROR
call epmautomate runbusinessrule "%br_allocatetarget%" "TargetVersion=Baseline"
IF %ERRORLEVEL% NEQ 0 goto :ERROR
for /f %%i in ('call epmautomate listfiles') do if %%i=="%dataexportfilename%.zip" (call epmautomate deletefile %%i)
IF %ERRORLEVEL% NEQ 0 goto :ERROR
call epmautomate exportdata %exportdatajobname% "%dataexportfilename%.zip"
IF %ERRORLEVEL% NEQ 0 goto :ERROR
call epmautomate downloadfile "%dataexportfilename%.zip"
IF %ERRORLEVEL% NEQ 0 goto :ERROR
rem Section to rename the file
Set Timestamp=%date:~4,2%_%date:~7,2%_%date:~10,4%_%time:~1,1%%time:~3,2%%
ren "%dataexportfilename%.zip" "%dataexportfilename%_%Timestamp%.zip"
call epmautomate logout
IF %ERRORLEVEL% NEQ 0 goto :ERROR
:EOF
echo Scheduled Task Completed successfully
exit /b %errorlevel%
:ERROR
echo Failed with error #%errorlevel%.
exit /b %errorlevel%