EPM Automate - Automating Enterprise Performance Management Cloud Solutions

Automating Enterprise Performance Management Cloud Solutions

1. 10322 EPM Automate – Automating Enterprise Performance Management Cloud Solutions 04/23/2018 Vatsal Gaonkar Principal Edgewater Ranzal

2. Agenda Introduction EPM Cloud and Automation EPM Automate Windows Batch Automation Architecture EPM Automate Algorithms Used Cases Challenges, Workarounds and other possibilities Q&A

3. Introduction

4. Introduction – Vatsal Gaonkar Summary Over 12 years of Enterprise Performance Management (EPM) experience across on-premises, cloud and hybrid deployments Design, Development and Deployment roles across approx. 30 projects over the years Speaker at Oracle / Hyperion conferences such HUGMN, DCOUAG, OAUG and ODTUG EPM Cloud Summary EPBCS design and deployment PCMCS development EPM Cloud products integration using Data Management EPM Automation design and deployment Vatsal Gaonkar [email protected] https://www.linkedin.com/in/vatsalgaonkar/ 312.405.6242

5. Introduction – Edgewater Ranzal Inc. Oracle Platinum Partner, Oracle Partner Advisory Council, 2 ACEs More than 200 consultants with an exclusive multiproduct focus across the entire Oracle Business Analytics Suite Oracle Cloud Excellence Implementer with consistent delivery of successful Oracle Cloud implementations 100% Cloud certification within Planning practice Trusted advisory relationship with Oracle Product Development, Oracle Customer Advisory Board, and Special Interest Group members Extensive experience in the planning, design, and implementation of integrated Oracle EPM, BI, and BD solutions and best practices Recognized by Oracle for Outstanding Achievement in Big Data Proven Oracle-specific methodologies, templates and supporting tools, as well as industry thought-leaders and deep technical practitioners

6. EPM Cloud and Automation

7. Modern Finance Organization Credit – Oracle Inc.

8. EPM Cloud Application Process Credit – Oracle Inc. PCMCS FCCS TRCS EPBCS PBCS

9. EPM Cloud Products Credit – Oracle Inc. EPM AUTOMATE

10. The need for Automation What is Automation? Why Automation? Integrated Process Automation examples Actuals Processing Reconcile Financials (ARCS) Consolidate Financials (FCCS) Load to EPM Planning (EPBCS) Actuals Allocation Reconcile Financials (ARCS) Consolidate Financials (FCCS) Load to Profitability (PCMCS) Allocation Model (PCMCS) Extract and Load to source (Data Management) Forecast Processing Reconcile Financials (ARCS) Consolidate Financials (FCCS) Load Actuals to EPM Planning (EPBCS) Process Forecast (EPBCS) Extract and Load to (PCMCS) Allocation Model (PCMCS) Extract and Load to Planning (Data Management)

11. EPBCS (E)PBCS Automation (PBCS platform) Metadata Load File based through Cloud Data Management Dimension files Data Load File Based through Cloud Data Management Import using pre-defined file format Data Extraction Cloud Data Management Migrations between instances

12. PCMCS PCMCS Automation (non-PBCS platform) Metadata Load On-premises Essbase Cube File based through Cloud Data Management Data Load On-premises Essbase Cube File based through Cloud Data Management Data Extraction Cloud Data Management Level 0 query extraction Integration between Pods – Calc. Vs Reporting Pods for PCMCS

13. Oracle EPM Automate

14. EPM Automate EPM Automate Utility download Windows Set Environment Variables - Path Linux Set Proxy Settings for this command line utility proxyHost proxyPort https.proxyHost https.proxyPort Currently doesn’t work with Single Sign-On (SSO) Use Native Administrator ID Presentation will concentrate on Windows Automation Oracle Documentation – https://docs.oracle.com/cloud/latest/epm-common/CEPMA/toc.htm

15. Windows Batch Automation Architecture

16. Hybrid Architecture Credit - Randy Schmitz Edgewater Ranzal Inc.

17. EPM Automate Recommendations Jump Box Central Traffic Hub for Scripts, Triggers and Files Scheduler Windows Task Scheduler 3 rd Party Schedulers (anything that can call Windows Batch) Centralized Function Library Trigger Automation Industries with Stringent Information Control Processes Error Exit Coding Exit Job on Critical Errors Cloud Data Management Dimension Build Data load and extractions Cross POD integration Centralized Batch Library Job - Function Library (Central) – Batches Integrator –

18. Ground and Cloud Trends Windows Jump Box EPBCS PCMCS Essbase Data Store File Systems 1. Files to Cloud 2. Essbase to Cloud 3. Datastore to Cloud 4. Cloud to Essbase 5. Cloud to Data Store 6. Cloud to Files 7. Cloud to Cloud 1 1,2,3 1,2,3 6 7 4,5,6 4,5,6 2 3 4 5

19. EPM Automate Algorithms

20. Swim Lane Algorithms What is an Algorithm? Why build Algorithms before program? Basis of Batch coding Troubleshooting large chunks of Code Enhancement enablement Communication / Documentation Swim Lane Algorithms Flow by Application specific artifacts Ease of use

21. Used Cases

22. Metadata Automation (EPBCS) EPBCS Dimension Sources File Existing Essbase application (and no metadata integrator) EBS If not through Data Management Zip Metadata files Create Import Jobs

23. Metadata Automation (EPBCS) Trigger Automation Look for available metadata file :RUN_SCHEDULED_PROCESS                 REM ### Copy the trigger file into the scripts directory ###                 COPY "% trigger_dir %\% trigger_file %" "% scripts_dir %\% trigger_file %" /Y                 CALL % scripts_dir :~1,-1%\%App_Name%_Load_PCMCS_Metadata.bat                 :: DEL "% trigger_dir %\% trigger_file %" EXIT

24. Metadata Automation (EPBCS) Metadata Automation Call MetadataBuild.ps1 IF %ERRORLEVEL% GTR 0 ( ECHO - ** ERROR ** A critical error was encountered while building dimension %Dimension%. Further processing of the job will be suspended...>>% log_file % SET /A returncode +=1) ELSE ( ECHO - Successfully built dimension %Dimension%... >>% log_file %) Error Handling :: * SAMPLE USAGE: *:: * call:ErrHandler "Script failed" FALSE *:: *************************************************************************************************: ErrhandlerSET tempmsg =%1ECHO % tempmsg :~1,-1% >> % log_file%IF "%2"=="TRUE" (EXIT 1) ELSE ( goto:eof ) Credit - Varun Phalswal Edgewater Ranzal Inc.

25. Metadata Automation (PCMCS) PCMCS (non-PBCS platform) Dimension Sources File Existing Essbase application (and no metadata integrator) Ensure Consumable File Format Create TESTDimLoad.txt manually (cumbersome) Perl automation perl %perl_dir%\PCMCSDimConvertor.pl % data_dir %\TESTDimMap.txt % data_dir %\TESTDimMapFinal.txt >>% log_file % 2>& Load Dimension Enable & Deploy PCMCS app

26. Metadata Automation (PCMCS) ECHO Backing up Data before Dimension Build...>>% log_file % call epmautomate login % admin_username % E:\Oracle\Automation\PWFile\%pwd_file% %url:~1,-1% "% domainname %" >>% log_file % 2>&1 call epmautomate exportqueryresults % App_Name % fileName ="%App_Name%_Level0_DataExtract.txt" exportOnlyLevel0Flg=true >>% log_file % 2>&1 call:RollFile "% profitoutbox_dir :~1,-1%\%App_Name%_Level0_DataExtract.txt" % log_retention % >>% log_file % 2>&1 call epmautomate downloadfile " profitoutbox \%App_Name%_Level0_DataExtract.txt" >>% log_file % 2>&1 call:RollFile "% data_dir :~1,-1%\%App_Name%_Level0_DataExtract.txt" % log_retention % >>% log_file % 2>&1 call:FileCopy "% profitoutbox_dir :~1,-1%\%App_Name%_Level0_DataExtract.txt" "% data_dir :~1,-1%\%App_Name%_Level0_DataExtract.txt" FALSE >>% log_file % 2>&1   :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: :: Run the metadata build based on which file exists in trigger directory :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: IF EXIST "% trigger_dir :~1,-1%\%metadata_file1:~1,-1%" (                 SET metadata_file =%metadata_file1%         CALL:RUN_PROCESS )          IF EXIST "% trigger_dir :~1,-1%\%metadata_file2:~1,-1%" (                 SET metadata_file =%metadata_file2%         CALL:RUN_PROCESS )          IF EXIST "% trigger_dir :~1,-1%\%metadata_file3:~1,-1%" (                 SET metadata_file =%metadata_file3%         CALL:RUN_PROCESS )          call epmautomate enableapp % App_Name % >>% log_file % 2>&1 call epmautomate deploycube % App_Name % isReplaceCube =true isRunNow =true comment="Redeployment after Dimensions Update" >>% log_file % 2>&1 call epmautomate deletefile profitinbox /%App_Name%_Level0_DataExtract.txt >>% log_file % 2>&1 call:Wait 10 call epmautomate uploadfile "% profitoutbox_dir :~1,-1%/%App_Name%_Level0_DataExtract.txt" profitinbox >>% log_file % 2>&1 call epmautomate loaddata % App_Name % dataLoadValue ="OVERWRITE_EXISTING_VALUES" dataFileName ="%App_Name%_Level0_DataExtract.txt" >>% log_file % 2>&1 call epmautomate runcalc % App_Name % % current_povyear %_% current_povscenario %_% current_povperiod % isClearCalculated =false isExecuteCalculations =true isRunNow =true subsetStart =999 subsetEnd =999 ruleSetName ="Reporting Aggregation" ruleName ="Optimize for Reporting Aggregation" exeType ="RULESET_SUBSET" comment="Cube Aggregation" stringDelimiter ="_" >>% log_file % 2>&1 call epmautomate logout >>% log_file % 2>&1 IF EXIST % trigger_dir :~1,-1%\% App_Name :~0,-4%MetadataBuildVariables.txt DEL % trigger_dir :~1,-1%\% App_NamE :~0,-4%MetadataBuildVariables.txt >>% log_file % 2>&1 ECHO. >> % log_file %

27. Metadata Automation (PCMCS)   :: ************************************************************************************************* :: *****                        RUN PROCESS                                                                        ***** :: ************************************************************************************************* :: * SAMPLE USAGE:                                                                                 * :: *   call:RUN_PROCESS                                                                                                                    * :: ************************************************************************************************* :RUN_PROCESS                 ECHO Archive and copy data file...                 call:RollFile "% data_dir :~1,-1%\% metadata_file :~1,-1%" % file_retention %                 call:SilentCopy "% trigger_dir :~1,-1%\% metadata_file :~1,-1%" "% data_dir :~1,-1%\% metadata_file :~1,-1%" FALSE                  ECHO File copy and archival completed.                                 ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::                 :: Build Dimension                 ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::                 ECHO Building Dimensions in % url %...>>% log_file %                 call epmautomate deletefile profitinbox /% metadata_file :~1,-1% >>% log_file % 2>&1                 call:Wait 10                 call epmautomate uploadfile "% data_dir :~1,-1%\% metadata_file :~1,-1%" profitinbox >>% log_file % 2>&1                 call:Wait 10                 SET error_message ="There were errors during the Metadata load process."                 SET /A returncode =0                 call:LoadDimensionCalcPod % App_Name % % metadata_file :~1,-5%                 IF % returncode % GTR 0 ( call:ErrHandler % error_message % TRUE) ELSE (ECHO Dimension % metadata_file :~1,-5% built. >>% log_file %)                 call:Wait 10                 DEL "% trigger_dir :~1,-1%\% metadata_file :~1,-1%" >>% log_file % 2>&1   goto:eof

28. Migration between environments :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: Migrate from Source to Target :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: call:EchoTimestamp ECHO Backing up % source_environment % before Importing Snapshot...>>% log_file % call epmautomate login % source_admin_username % C:\Oracle\Automation\PWFile\%source_pwd_file% % source_environment :~1,-1% "% domainname %" >>% log_file % 2>&1 SET error_message ="There were errors during exporting the snapshot from % source_environment %.“ SET /A returncode =0 call:EXPORTSOURCESNAPSHOT IF % returncode % GTR 0 ( call:ErrHandler % error_message % TRUE) ELSE (ECHO Snapshot Exported. >>% log_file %) call epmautomate logout >>% log_file % 2>&1 ECHO Copying Snapshot from % source_environment % to % target_environment %...>>% log_file % call epmautomate login % target_admin_username % C:\Oracle\Automation\PWFile\%target_pwd_file% % target_environment :~1,-1% "% domainname %" >>% log_file % 2>&1 call epmautomate deletefile %App_Name%_ Exp >>% log_file % 2>&1 SET error_message ="There were errors during copying the snapshot from % source_environment % to % target_environment %.“ SET /A returncode =0 call:COPYSNAPSHOT %App_Name%_ Exp IF % returncode % GTR 0 ( call:ErrHandler % error_message % TRUE) ELSE (ECHO Snapshot Copied. >>% log_file %) ECHO Exporting Data Grants from % target_environment %...>>% log_file % call epmautomate exportsnapshot %App_Name%_ DataGrants >>% log_file % 2>&1 call epmautomate logout >>% log_file % 2>&1 ECHO Recreate % target_environment % from % source_environment %...>>% log_file % call epmautomate login % target_admin_username % C:\Oracle\Automation\PWFile\%target_pwd_file% % target_environment :~1,-1% “% domainname %" >>% log_file % 2>&1 call epmautomate recreate -f >>% log_file % 2>&1 call epmautomate importsnapshot %App_Name%_ Exp >>% log_file % 2>&1 call epmautomate importsnapshot %App_Name%_ DataGrants >>% log_file % 2>&1 call epmautomate logout >>% log_file % 2>&1 ECHO. >> % log_file %

29. Data Load (PCMCS) Use of Cloud Data Management File management anywhere on the cloud Delete Upload Process EBS Integration available Trigger file processing :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: Run the data load based on which file exists in trigger directory ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::I F EXIST "% trigger_dir %\%data_file1%" ( SET data_file =%data_file1% CALL:RUN_PROCESS ) IF EXIST "% trigger_dir %\%data_file2%" ( SET data_file =%data_file2% CALL:RUN_PROCESS ) IF EXIST "% trigger_dir %\%data_file3%" ( SET data_file =%data_file3% CALL:RUN_PROCESS ) IF EXIST "% trigger_dir %\%data_file4%" ( SET data_file =%data_file4% CALL:RUN_PROCESS ) IF EXIST "% trigger_dir %\%data_file5%" ( SET data_file =%data_file5% CALL:RUN_PROCESS )

30. Data Load (PCMCS) Load Process based on the file availability Points of View in PCMCS CDM Import Mode Target Export Model – REPLACE VS STORE_DATA ECHO Archive and copy data file... call:RollFile "% data_dir :~1,-1%\% data_file :~1,-1%" % file_retention % call:FileCopy "% trigger_dir :~1,-1%\% data_file :~1,-1%" "% data_dir :~1,-1%\% data_file :~1,-1%" FALSE ECHO File copy and archival completed. :: Delete original file from inbox call epmautomate deletefile inbox/ File_PCM /% data_file :~1,-1% >>% log_file % 2>&1 call:Wait 5 :: Upload the new file to inbox call epmautomate uploadfile "% data_dir :~1,-1%\% data_file :~1,-1%" inbox/ File_PCM >>% log_file % 2>&1 :: Load data call epmautomate rundatarule FilePCMActuals % StartMonth %-% StartYear :~2% % EndMonth %-% EndYear :~2% REPLACE STORE_DATA File_PCM /% data_file :~1,-1% >>% log_file % 2>&1

31. Challenges and Workarounds

32. Challenges EPBCS Metadata (without using Data Management) epmautomate importmetadata Required to Zip the file and create an Import Job Consumable file format Refresh Database Data Data Management Export (Loading to database) Mode epmautomate loaddata REPLACE VS STORE_DATA REPLACE in BSO VS ASO Plan Types STORE_DATA in BSO VS ASO Plan Types Data Extraction epmautomate exportqueryresults 5M cells queryresultlimit

33. Challenges PCMCS Metadata (if Metadata management tool not available) Consumable file format On-ground essbase integration with PCMCS – data manipulation required Database Deployment – Incremental Vs. Replace Cube Data MDX extractions Data Management Export Mode (with data management rule) epmautomate rundatarule REPLACE VS STORE_DATA 5M cell queryresultlimit Data Extraction (with a query name) epmautomate exportqueryresults Level0 extract using exportqueryresults export 2GB files 5M cells queryresultlimit with PCMCS queries

34. Workarounds EPBCS Metadata Management – use of error exit coding IF % returncode % GTR 0 ( call:ErrHandler % error_message % TRUE) ELSE (ECHO Snapshot Copied. >>% log_file %) : Errhandler SET tempmsg =%1 ECHO % tempmsg :~1,-1% >> % log_file % IF "%2"=="TRUE" (EXIT 1) ELSE ( goto:eof ) Data Management BSO Load - Always use STORE_DATA Export mode in conjunction with a clear business rule PCMCS Metadata Management Dimconvertor.pl Error exit coding Level 0 export with 2GB increments handling (Data handling as well) SET /A Counter=n :Loop IF %counter%==0 ( call epmautomate uploadfile "% data_dir :~1,-1%/%App_Name%_Level0_DataExtract.txt" profitinbox goto:eof ) ELSE ( SET /A tempcounter =%counter% call epmautomate uploadfile "% data_dir :~1,-1%/%App_Name%_Level0_DataExtract_%counter%.txt" profitinbox ) SET /A counter=%counter%-1 goto:Loop

35. Other Automation Possibility 1 Import to ARCS Reconcile Accounts Export and Import to FCCS Consolidate FCCS Export and Import to EPBCS Trend based planning in EPBCS using loaded Actuals from FCCS Possibility 2 (Used case for us already) Import to EPBCS Workforce Calculate Workforce Extract and Load to PCMCS Allocate Direct Labor and other workforce attributes Extract and Load to EPBCS for fully allocated Budget P&L

36. Closing thoughts Data Management is the future EPM Automate for integrating cloud products Metadata management using CDM For Windows use PowerShell, if possible Ensure Error exit coding in automation design Get on EPM Automate as soon as possible Oracle Reference for EPM Automate Command library https://docs.oracle.com/cloud/latest/epm-common/CEPMA/epm_automate_command_ref_general.htm#CEPMA-GUID-5400C490-264F-493A-9EAD-267AB2A320F1

37. 10322 [email protected]