Welcome to the Machine

A few posts back we introduced you to EPM Automate by showing you how to install the tool and log in to your PBCS instance. Now let’s take a deeper dive in to one of the more practical uses of EPM Automate – data load automation. This post will focus on a few key commands used to execute a data load to your PBCS app through EPM Automate:  uploading data files to the PBCS Data Management inbox, deleting files from the Data Management inbox, executing Data Management data load rules, and running business rules in PBCS. So please join us in learning how to create a nightly data load process for PBCS, don’t be afraid – it’s easier than you might think.

Uploading a file

The uploadfile command can upload a file from the local machine to either the PBCS inbox/outbox, or the Data Management inbox/outbox.

Syntax

                epmautomate uploadfile “File_Name” [Data_Management_Folder]

Example 1 - Upload the file to the PBCS inbox/outbox

                epmautomate uploadfile “C:/Users/<someone>/Desktop/example.txt”

Note: the PBCS inbox/outbox is only accessible through the simplified interface. ConsoleàActionsàInbox/Outbox Explorer

                Example 2 - Upload the file to the specified folder (Stats) in the Data Management inbox

epmautomate uploadfile “C:/Users/<someone>/Desktop/example.txt” inbox/Stats

Deleting a file

The deletefile command searches for and deletes a file from the PBCS inbox/outbox or the Data management inbox/outbox depending on where the file is located.

                Syntax

                                epmautomate deletefile “File_Name” [Data_Management_Folder]

Example 1 - Delete the file from the PBCS inbox/outbox

                                epmautomate deletefile “example.txt”

Example 2 - Delete the file from the specified Data Management folder (Stats)

                                epmautomate deletefile “inbox/Stats/example.txt”

Note: the Data Management file path must be provided to delete the file from the Data Management inbox/outbox.

Running a Data Load Rule

The rundatarule command runs the specified data load rule in Data Management to load the data file specified (either in the data load rule or the command line). Data Load Rule status and details can be viewed in Data Management under Process Details.

                Syntax

epmautomate rundatarule Rule_Name Start_Period End_Period Import_Mode Export_Mode [File_Name]

Import Mode: used to import the data to the Data Management staging table

·         Append - Add to the existing POV data in Data Management

·         Replace - Replaces the existing POV data in Data Management

·         None - skip data import to Data Management

Export Mode: used to export the data from Data Management to the PDCS Application

·         Store_Data - Merges new data with the existing data in the PBCS application

·         Add_Data - Adds new data values to the existing data values in the PBCS application

·         Subtract_Data - Subtracts  new data values to the existing data values in the PBCS application

·         Replace_Data - Clears all data for the POV and replaces the data with the exported data.

·         None - skip the data export.

Start_Period/End_Period: Enables multi-period loads, period names must be defined in the data load rule.

File_Name: specifies the file to load – if no file is specified the file specified in the data load rule will be used.

Example 1: Single period load

                epmautomate rundatarule Load_Rule Jan-16 Jan-16 Replace Store_Data

Example 2: Multi-period load

                epmautomate rundatarule Load_Rule Jan-16 Mar-16 Replace Store_Data

Example 3: Single period, File specified

epmautomate rundatarule Load_Rule Jan-16 Jan-16 Replace Store_Data “inbox/Stats/example.txt”

Running a Business Rule

The runbusinessrule command allows a Business Rule to be run in PBCS. Business Rule status and details can be checked in the Application Job console (ToolsàJob Console) in the Classic Interface, or Jobs (ConsoleàJobs) in the Simplified Interface.

                Syntax

                                epmautomate runbusinessrule Rule_Name [Parameter=Value]

Note: Parameter=Value is used to provide values for run-time prompts, where Parameter is the name of the RTP and Value is the member being passed to the rule.

                Example 1

                                epmautomate runbusinessrule Example_Agg

                Example 2: with 2 Run Time Prompts

                                epmautomate runbusinessrule Example_Agg rtp_Year=FY16 Scenario=Actual

Note: Parameter=Value can be used multiple times in the same command.

Using a batch Script and Windows Task Scheduler to automate the load process

Batch Scripting and the windows Task Scheduler can be used to automate the On-Premise to PBCS data loads.

                Example Batch Script

C:

CD: C:\Oracle\EPM Automate\bin

Call EPMAutomate Login User_Name Password https://planning-IdentityDomain.pbcs.us2.oraclecloud.com  IdentityDomain

Call EPMAutomate uploadfile “C:/users/<someone>/desktop/example.txt”  inbox/folder

Call EPMAutomate rundatarule Load_Rule Jan-16 Jan-16 Replace Store_Data “inbox/Stats/example.txt”

Call EPMAutomate runbusinessrule Example_Agg rtp_Year=FY16 Scenario=Actual

Call EPMAutomate Logout