Integrate EPM Automate with Hyperion Smart View in Cloud

Source Image:https://blogs.perficient.com/2015/05/06/using-epm-file-transfer-utility-and-automate-utility

EPM Automate

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.

Steps:

1. Before starting please make sure you have the EPM Automate installed in your machine.

2. Make sure you have the “EPM Automate” folder in your Oracle Directory.
3. Open a notepad or Notepad++ editor to write a batch program.
4. Write the below code

SET url=https://xxxxxxxxxxxxx.oraclecloud.com

SET domain=xxxxxx

SET user= xxxxxx@yyyyyy.com

CALL epmautomate login %user% C:\Projects\xxxxxxx\pwd.epw %url% %domain%

epmautomate refreshcube

CALL epmautomate logout        

5. Save the batch file.
6. Open a new Excel sheet.
7. Navigate to “Developer” > “Macros”. Create a new macro “Refresh”.

The IT Leader's Guide to Multicloud Readiness

This guide provides practical key insights and important factors to consider to make informed decisions in your multicloud journey.

Download the Guide

8. Click on “Create”. VB Macro editor application is opened.

9. Enter below code

    Sub Refresh()

      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)

 End Sub

10. Save the code. Close the Macro Editor.
11. Create a new menu in Excel for EPM Automate. Navigate File > Options > Customize Ribbon
12. To add the Macro please select “Macros” as shown below

13. Create the new tab as shown in the above screenshot.
14. Excel adds a new Tab “EPM Automate” with a “Refresh”
15. Connect to Development instance from “Smart View” > “Panel”.
16. Update “Account” Dimension with new members.
17. Click on “Smart View” > “Submit Data”
18. Changes are applied to Account Dimension.
19. Navigate to “EPM Automate”.
20. Click on “Refresh”.
21. Macro executes the batch program which internally calls the EPM Automate to refresh the cube.
22. 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.