Oracle came out with a bunch of good scripts for many of the repeatable tasks performed in a Cloud instance. For example
- Refresh Database
- Import and export metadata
- Import and export Planning application data
- Launch Business Rule(s)
- Copy data from one database to another database
- Upload files into the Oracle PBCS repository
- Download/Delete files from Oracle PBCS repository
- Export and import application and artifact snapshots using Application Management
- List the files in the Oracle PBCS repository
One of my EPM colleagues at Perficient has already published blogs explaining EPM Automate and methods to implement it. Please read the blog EPM Automate Utility which explains the steps to install the EPM Automate Utility with a few examples.
My 2 cents are to integrate EPM Automate with Excel using Macros. The actual idea popped up when users started loving the features of EPM Automate but they were not comfortable running the script. Most of the users were Business Analysts with a minimal IT background. So we thought of leveraging the usage of Excel Macros to call the EPM Automate Script. Users had no issue using Excel as they were very much used to it.
In this blog, I’ll share a simple example of how to integrate the EPM Automate script with Excel. Here’s a step-by-step approach on how to call the EPM Automate script from Excel using macros to refresh the cube.
- Before starting please make sure you have the EPM Automate installed in your machine.
- Make sure you have the “EPM Automate” folder in your Oracle Directory.
- Open a notepad or Notepad++ editor to write a batch program.
- Write the below code
SET user= firstname.lastname@example.org
CALL epmautomate login %user% C:\Projects\xxxxxxx\pwd.epw %url% %domain%
CALL epmautomate logout
- Save the batch file.
- Open a new Excel sheet.
- Navigate to “Developer” > “Macros”. Create a new macro “Refresh”.
Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.
- Click on “Create”. VB Macro editor application is opened.
- Enter below code
Application.DisplayAlerts = False
Application.DisplayAlerts = True
ThisWorkbook.Saved = True
Dim executeStatement As Double
Dim cmdBatch As String
cmdBatch = “C:\Projects\xxxx.bat”
executeStatement = Shell(“cmd /k ” & cmdBatch, vbNormalFocus)
- Save the code. Close the Macro Editor.
- Create a new menu in Excel for EPM Automate. Navigate File > Options > Customize Ribbon
- To add the Macro please select “Macros” as shown below
- Create the new tab as shown in the above screenshot.
- Excel adds a new Tab “EPM Automate” with a “Refresh”
- Connect to Development instance from “Smart View” > “Panel”.
- Update “Account” Dimension with new members.
- Click on “Smart View” > “Submit Data”
- Changes are applied to Account Dimension.
- Navigate to “EPM Automate”.
- Click on “Refresh”.
- Macro executes the batch program which internally calls the EPM Automate to refresh the cube.
- Windows dialog box gets closed after refresh is completed.
We can also pass parameters to the batch program from the Excel file. If a customer doesn’t want to add an extra Toolbar, then we can add command buttons in the Excel file to call batch program.