Business Rules To Launch Them All

Justin Biard -

So you have yourself one of those fancy on-premise Oracle EPM systems built with Hyperion Planning and Oracle Essbase. If you didn't do that or don't plan to do so, then you might want to skip the rest of this post.

Introduction

When you start to do something interesting (and by interesting I mean manual labor intensive) with Oracle EPM you may want to automate some (or many) parts of the solution. For years many of us focused on Oracle Data Integrator (ODI) as a framework for automation. Examples of automation for Hyperion as it is used in this post include:

  • Loading source system data and metadata
  • Deploying metadata from Planning to Essbase
  • Creating nightly backups
  • Comparing source systems and target applications
  • Checking for errors and sending email notifications
  • Running miscellaneous batch, MaxL, et al. processes

This post is not about ODI. This post is about showing you how to use the tools you probably already have to integrate systems with batch processes. You will also learn how to empower users along the way. This approach is very flexible. You could plug in just about anything you want at the "end of the line", including ODI. (Reference [7] in the diagram below.)

Let's get started!

This diagram shows how a batch process can begin all the way back at the users' keyboard and mouse. This diagram also shows a possible flow for executing jobs that actually start on the Essbase server. This is only meant to be logical. Your physical infrastructure might have Essbase on the same server as the web-tier. (i.e. Exalytics) I won't get deep into infrastructure here but I will mention it when necessary.

A user as mentioned above could be an administrator, a super user or an end user depending on your specific requirements. (Who needs the ability to run a batch?)

This may look like a long and somewhat complicated process to go through just to kick off a batch job but, remember, this is a behind the scenes look. From an end-users' perspective, this will look and feel like an "easy button". The interesting part is what makes the easy button work.

The End

This is about "Reference [5], [6] and [7]" from the diagram above. I will start at the end so we can talk about some important assumptions and give you a sense of where we are going. Then I will go back to the beginning of our diagram to cover parts 1..4 to tie this all together.

Assumption #1

This approach uses the server running Essbase to start a batch process. More specifically, an esssvr(.exe) process (depending on the operating system) running on the Essbase server with a given set of permissions and environment variables will start a calculation script.

The calculation script will contain the keyword RUNJAVA which will instruct Essbase to call a static method of a Java class (i.e. MaxLFunctions). The Java method will start a MaxL shell essmsh(.exe) to run some MaxL commands. For example, here is a Calculation Manager rule that starts a MaxL script:

For Windows:

RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLFunctions 
  "false" 
  "D:\automation\plan1\start_gl_actuals_load.msh";

For Linux/Unix:

RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLFunctions 
  "false" 
  "/u01/automation/plan1/start_gl_actuals_load.msh";

Remember this is a calculation script. The path of the MaxL script supplied in this example must exist on, or be accessible to, the Essbase server.

Note: the false parameter means to wait for the MaxL script (or your batch process) to complete. A value of true here would mean to start the MaxL script on a background thread and then return immediately.

The following diagram illustrates the concept further.

Note: Because Essbase uses Java for custom defined functions and for all of this to work behind the scenes, Java's built-in sandbox security model applies. If you are using a version of Oracle EPM that includes the MaxLFunctions noted above then Oracle should have already updated the Java security policy to grant permissions. If not, or you need to write your own Java class for some other reason then you will also need to update the udf.policy file stored with Essbase to grant permissions to your own Java classes.

Keep in mind, I am intentionally simplifying some parts or not discussing every possible option. There are other Java functions provided by Oracle that I do not describe and leave that for you to research on your own.

As shown in the diagram above, you have two options when you call your MaxL script and they each have their own place, however, even for simple scripts I would still recommend Option 2.

  1. Run MaxL commands to interact with Essbase - or -
  2. Use the MaxL shell <...> command to run any external program available on the Essbase server. (Or on remote servers with some operating system-specific remote procedure calls)

MaxL is a purpose-built language for interacting with Essbase. It has some very basic control flow and error trapping capabilities but it is not very robust. By using Option 2 to start your MaxL scripts you gain a lot of control and flexibility over how your scripts are called. You also get to choose what you use to automate your jobs and how you deal with errors (or warnings). You also gain the ability to easily import global configuration settings that can tie into your overall automation framework or strategy.

Assuming Option 2 is selected then our MaxL script called by Calculation Manager (start_gl_actuals_load.msh in this example) may include something like the following MaxL commands:

For Windows:

/* 
  On Demand launch the GL actuals load 
*/
shell 'D:\automation\plan1\gl_actuals_load.bat';
exit;

For Linux/Unix:

/* 
  On Demand launch the GL actuals load 
*/
shell '/u01/automation/plan1/gl_actuals_load';
exit;

Note: shell is a special MaxL keyword that allows you to run external commands on your operating system. You can use this to launch any kind of process as required. From my experience, Essbase starts the MaxL shell with a pretty empty PATH environment variable. You will likely need to specify the full path to whatever your initial executable is going to be.

MaxL is the method we use to start that process on the Essbase server. The possibilities of what you can accomplish using this approach are innumerable. There are common use cases and I will describe some of those use cases in future posts.

NB: Like other "background" processes, jobs started using this approach will not be visible to the user or on the terminal. As such, do not pause indefinitely or try to get input from a user.

Assumption #2

Your infrastructure (network, servers, operating systems, security and software), applications, business logic, functional processes, requirements, and available skill sets are going to be unique or at least customized for your business in some way.

Because of this and Assumption #1, this approach is not meant to be prescriptive. My hope is that if you need to do this then what we cover here will give you some ideas on how to get started (maybe 80% done) and from there you will need to plug in the parts that make your build unique.

The Beginning

From an end user's perspective, what have built essentially becomes an easy button. There is no longer any need to login to the remote server over RDP or SSH because jobs can be executed directly from task lists (or form saves / loads) in Hyperion Planning. This is the part where you empower your users and reduce some reliance on IT. To make this work well though, there are some things you should consider:

  1. Business Rules -> Launch will always return a message to the user stating "success" even if the actual batch job or program has failed.
  2. Users will not need access to the server to run jobs but they also won't have visibility into the back end state (per #1) so plan to add notification of some kind to the batch jobs. (Email on failure, update a cell in Essbase tied to a Smart List, post a message to an intranet web page, etc...).
  3. In the case of email, make sure some kind of log files are attached or that they are placed in a location where the user of the "easy button" can get them. This part will make the life-cycle of the "easy button" more or less self-service.
  4. Consider multi-user environments or the fact that users sometimes press save twice or may double-click the Launch button. In those cases, be sure to add some self-awareness to your batch processes so that they are not accidentally executed more than once concurrently where it matters.
  5. Sometimes jobs have dependency conflicts that are avoided simply by running them at the correct time of day or in the correct sequence. Make sure the jobs you give users the ability to run take this into account or at least train the users to make them aware of the conditions.
  6. Sometimes batch jobs can be long running. Consider this when giving users the ability to launch a job from Hyperion Planning. Long running jobs that will exceed the Calculation Manager timeout for Workspace should probably be run asynchronously so the user is not forced to wait just to see a "timeout" warning.
  7. Finally, give yourself some way to disable all batch jobs that could be run on demand from Hyperion Planning. Empowering users is a great way to free up the flow of traffic through IT but it also means you sometimes need a way to put up road blocks when needed.

Note: you can increase the length of the timeout for Calculation Manger rules in Hyperion Planning. Check out this post on epminsight.com for more information.

Here is an example of a "live" easy button in Hyperion Planning. This one is attached to a smart list which apparently contains tasks for the Finance team.

When a user presses Launch they are presented with a status wheel that spins while the batch jobs is working. (Unless you specified true in your RUNJAVA command to run the script asynchronously.)

Until finally the user is presented with the ambiguous "success" status message from Planning. This is ambiguous because the batch job may have actually failed although this is not currently escalated to Hyperion Planning.

Here is an example of the business rule attached to this task list item that is used to launch the batch job (or shell script). This rule contains the RUNJAVA command to start a MaxL script called start_gl_actuals_load.msh. Perhaps this MaxL script will launch a shell script called gl_actuals_load.

Here is an example of the same rule but instead of waiting for the batch job to complete, the job is run asynchronously. Note: the original false parameter is now marked as true which means start the MaxL script on a background thread and return to Planning immediately.

Pro Tip: Hyperion Planning task lists and calculation manager rules (as shown above) both leverage the Hyperion security model. Use this to your advantage and simplify access controls for task execution. You can easily enable or disable access to tasks, give teammates access while you are on vacation, assign specific groups of users with access to launch specific jobs, keep a handy "admin only" task list, Etc. You also get the added benefit of a self-documenting processes with task lists.

Note: I am showing examples based on version 11.1.2.4 of Oracle EPM, however, a majority of this content can and should still apply in versions older than 11.1.2.3 with some slight modification.

  • Most notably, the availability of Planning / Calculation Manager-specific Java classes and custom defined functions (CDF) which may not be available in your release. In that case you can still make all of this work but will need to supply your own CDF to accomplish the same task. (and this is very doable.)
  • If you have not yet converted to Calculation Manager then, in addition to writing your own Java class, you will also need to translate these tips for Hyperion Business Rules.

Conclusion

We started with the following diagram and then jumped in headfirst by starting at the end of the process. We covered a lot of ground in this post and still we are just scratching the surface.

We reviewed how jobs are executed on the Essbase server and how those jobs can be attached to Calculation Manager rules on Task Lists within Hyperion Planning. We discussed that MaxL is able to call external commands within your operating system using the shell command. Therefore you will be able to run anything that is accessible to the role or user associated with the ESSSVR process on the Essbase server.

Hopefully you found this introduction to running batch jobs from Calculation Manager rules to be useful and that it gave you some ideas about how you could use this in your own environment.

Cheers!