Oracle PBCS offers two utilities for automating tasks for PBCS. EPM Automate utility is used for uploading data or metadata files to the PBCS inbox folder, loading data to PBCS and refreshing the planning database. The File Transfer Utility EPMCopy.exe is more geared towards uploading a file to data management, running data management rules etc. The following sections describe each utility in detail. This post assumes the reader is familiar with the data load requirements of Essbase, Planning and FDMEE.
Update 09/02/15: Per the Oracle PBCS September Update, the File Transfer Utility – EPMCopy.exe will be deprecated starting with the January 1, 2016 service update and its functionality will be available within the EPM Automate Utility. Read this post for more details.
EPM Automate
EPM Automate utility can be downloaded from the Oracle PBCS workspace by navigating to Tools->Install. Clicking on ‘EPM Automate’ will prompt to save a file called EPM Automate.exe. Save and then execute the file. Install the utility by accepting all defaults. The utility will get installed in C:\Oracle\EPM Automate directory, with the utility epmautomate present in the bin folder under C:\Oracle\EPM Automate directory.
Double clicking on this utility starts the epmautomate utility. Type ‘epmautomate help’ and hit enter to see a list of all the commands that can be run using this utility.
The login command needs the username, password, url and the identity domain parameters. Here is an example of a login command:
epmautomate login admin password https://pbcs01-test-xxcorp.pbcs.us2.oraclecloud.com xxdomain
The benefit of EPM Automate is that this utility can be called in a batch script and hence allow you to automate certain tasks like uploading a file to the Planning Inbox/Outbox folder, load the data to the Planning database, run a business rule and refresh the planning database if need be. When calling from a batch script you need to invoke EPM Automate by using ‘Call’ followed by the command. You need not run the command prompt from within the EPM Automate installation bin directory. Now you can bundle your automation sequence in a windows batch script or a shell script (you need to download the Linux/Unix version and use a shell script).
Oracle PBCS Inbox/Outbox Folder
All files uploaded to Oracle PBCS using the EPM Automate utility get uploaded to the Inbox/Outbox folder. This folder can be accessed only through the Simplified Interface. To go to Simplified interface, navigate to Administer->Planning and Budgeting Service and then click on Simplified Interface. From the Simplified Interface, click on the ‘Console’ option. You will see the Inbox/Outbox Explorer under ‘Actions’
Setting up an Import Data Job
Before you use epm automate to automate certain tasks, you need to setup an import or export operation and save that as a job and then use epm automate to refer to that job and carry out the import or export operation. To setup an import job, from Console click on ‘Actions’->Import Data. (To create an export job, click on Export) On the Import Data page, click on ‘Create’.
The ‘Import Data’ page opens up with certain options to select to set up the job. The Location setting has two options. ‘Local’ and ‘Planning Inbox’. If you select Local you can browse to select a file. If you select ‘Planning Inbox’, this refers to the Planning Inbox/Outbox folder. In this example, we will select the ‘Planning Inbox’ option. Next, from the Source Type, we will select Essbase which then allows us to select the database (plan type) that you want to load data to.
Click on ‘Save as Job’ to create the job. Enter a job name and then click on save. You will receive a job saved notification. Click on ok. You will be redirected to the Console which will now show you the job you just created. Click on Close. If you need to delete this job click on settings icon and then click delete.
Setting up an Import Metadata Job
To set up an import metadata job, from the Simplified Interface, navigate to ‘Console’ and then select the ‘Dimensions’ tab.
Then from within the ‘Dimensions’ tab, click on ‘Import’ (To create an export job, click on Export)
Click on ‘Create’
Select Location as Planning Inbox; Select the dimension you want to update (the dimension is highlighted in a blue background. In this case I have selected the Account dimension) ; Specify the import file name ; Select a File Type ; Put a check on the ‘Clear Members’ in case you want to clear all members before loading the new file. For incremental updates, this box should be unchecked.
Click on ‘Save as Job’. Provide a name for the job and click on Save and then click on OK. You can enable the option to refresh the database if import metadata operation was successful. I leave this unchecked as we will be using the refresh metadata command in epm automate to do this.
Click ‘Close’ on the import metadata page. You will now see the import metadata job you created. If you need to delete this job click on settings icon and then click delete.
Setting up epm automate batch file
Now that we have setup the import data and import metadata jobs, we can refer them in the epm automate batch script to set up the automation. In this example I have setup two batch files. The first batch file “CallProcess.bat” contains the login id, URL, password etc. that I am passing as parameters to the second batch file “StartProcess.bat” containing the epm automate commands. Here are the contents of the CallProcess.bat file:
set loginid=admin@xxdomain.com set password=password set url=https://pbcs01-test-xxcorp.pbcs.us2.oraclecloud.com set domain=xxdomain call C:\PBCSAutomate\StartProcess.bat
Here are the contents of the StartProcess.bat file. The sequence is as follows – Login, delete existing accounts metadata file from Planning Inbox folder, upload new accounts metadata file to the Planning Inbox folder, run the job ‘Acc_Metadata_Job’ to import the accounts metadata file to Planning , refresh the planning application, delete the existing daily EBS actuals load file from Planning Inbox folder, upload the latest daily EBS actuals load file to Planning Inbox folder, run the Oracle PBCS import data job ‘EBS_Actuals_Job’, execute an aggregation business rule ‘AggAct’ and logout.
call epmautomate login %loginid% %password% %url% %domain% call epmautomate deletefile Import_Acc_Metadata.csv call epmautomate uploadfile "C:\PBCSAutomate\Import_Acc_Metadata.csv" call epmautomate importmetadata "Acc_Metadata_Job" Import_Acc_Metadata.csv call epmautomate refreshcube call epmautomate deletefile Daily_EBS_Actuals.txt call epmautomate uploadfile "C:\PBCSAutomate\Daily_EBS_Actuals.txt" call epmautomate importdata "EBS_Actuals_Job" call epmautomate runbusinessrule "AggAct" "planType=SalesFct" call epmautomate logout
This should give you a start on using EPM Automate. You can export data and metadata as well from PBCS. There are many other advanced ways to add more process control (creating directories for input files and passing them as parameters) or error trapping and notification functionality using windows batch commands (or Unix shell commands)
Sample Automation Scripts from Oracle PBCS Documentation:
Please click here to see sample scripts from the Oracle PBCS Documentation. This gives good information on automation including error handling.
File Transfer Utility – EPMCopy.exe
EPM Copy utility is used to upload files to the Data Management Inbox folder, run a rule to load data into a target application (Planning or Essbase). EPM Copy can be downloaded from Oracle PBCS Workspace by navigating to Tools->Install and click on File Transfer Utility. This is the link for downloading EPM Copy.exe. Save it to your computer. You do not need to install this utility.
Execute EPMCopy.exe to see the commands and examples. With EPMCopy you are able to upload an LCM snapshot or download an LCM snapshot to/from the cloud. You are also able to upload a file to the inbox folder of Data Management and run a rule to load data to the target application.
The importmode and exportmode parameters are given below
importmode: Two modes––APPEND and REPLACE––are supported. In append mode, data is added to the period while in replace mode, data of the specified period is replaced with data from the file being uploaded.
exportmode: Four modes––STORE_DATA, ADD_DATA, SUBTRACT_DATA, and OVERRIDE_ALL_DATA––are supported.
Note: To import an LCM artifact, use the following command:
https://test-cloud.pbcs.us1.oraclecloud.com/files/lcm/LCMArtifactfilename
Data Management Inbox Folder
As mentioned earlier, epmcopy.exe is used to upload files to the Inbox folder of Data Management and then run a rule to load data to the target application. You can view the Inbox folder of Data Management when you create a Data Load Rule. In Data Management, from Workbench, click on ‘Data Load Rule’ and click ‘Add’. Enter a name for the rule.
Clicking on select opens the Inbox explorer. All files uploaded using EPMCopy.exe will be visible here.
EPMCopy.exe batch commands
Given below are sample EPMCopy commands to upload a file to Data Management Inbox folder and then run a rule called Actual_Load specifying the import parameters.
epmcopy.exe USERNAME=admin@xx.com PASSWORD=password IDENTITY_DOMAIN=XXCorp FROM=C:\EBSActuals\ActData.csv TO https://test-cloud-pln.pbcs.us1.oraclecloud.com/%/files/inbox/ActData.csv LOADDATA=Actual_Load:Jan-15:Jul-15:REPLACE:STORE_DATA