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.

  • 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 Planning.

Note:

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

  • If you are using the PDF version of this document: To avoid line breaks and footer information that will render this script unusable, copy the script from the HTML version of this topic.

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

@echo off

rem Sample Script to demonstrate daily data integration with 
rem Oracle Planning and Budgeting Cloud Services 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 input the below parameters
SET url=https://example.oraclecloud.com
SET user=serviceAdmin
SET domain=ExampleDomain
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 password=%1

rem Executing EPM Automate commands

CD /D %~dp0
call epmautomate login %user% %password% %url% %domain%
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%