We have discussed the EPM automate many times. From an admin perspective, it is very useful. It enables service admins to remotely perform tasks within Oracle EPM Cloud instances and automate many repeatable tasks, such as import and export of metadata and data, refresh database, launch business rules, run data load rules, etc. But from an end user perspective, EPM automate might be a bit technical. Then can we make it more user friendly, and if we can, where should we achieve that? Well, the answer is, for sure we can make it user friendly. Since Excel is our friend, we can integrate the EPM automate into Excel.

 

The following is my personal Excel file. I have customized the Excel ribbon and integrated some useful EPM automate functions to this new EPM Automate tab. Next, we are going to see how to integrate the EPM automate together, so that you will be able to customize your Excel as well.

 

Step1 Download and Install EPM Automate

First thing first, since we are using EPM automate, we need to download and install EPM automate to your local machine. To download it, we can just simply log into one of the EPM application and then go to the download section.

 

There are many commands that we can use in EPM automate. Before we start integrating it to Excel, it would be better if we know what we want to use EPM automate and what EPM automate can do for us. We can check the EPM automate Command Reference page.

 

Step2 Create Batch File

Then we will need to create a batch file first on our local machine to trigger the EPM automate commands.

 

For example, creating a batch file for refreshing database.

 

Step 3 Create Excel Macro

Now, we will need to move onto the Excel side. We will create a macro to execute the batch program which internally calls the EPM Automate to refresh the cube.

 

This is the VB script for calling that batch file.

 

Step4 Customize Excel Ribbon

We have the batch file and macro ready. Now it is time to customize Excel tab / UI to make it more user friendly.

Go to Customize Ribbon section, and add a new tab and group.

 

Select the Refresh macro that we just created.

 

Step5 Testing

Simply click the Refresh button, the EPM automate command shell will be prompted.

 

To really test the process, we can log into the EPM application and check the job log. Once the job is done, the EPM automate command shell will be terminated.

 

The result is what we are looking for and we are good to go. Next time, when we want to refresh database, we can just simply open an Excel file and click the Refresh button from the EPM Automate tab. In the meantime, we could integrate more EPM automate command to Excel. Other than creating a new ribbon, we can add a command button to call the batch file as well, if users do not want to have a new tab.

 

Above is an example of how to integrate the EPM Automate script with Excel. I will discuss more about this in the future posts. See you next time.