Automating Calc Manager Rules for Oracle Hyperion Planning

Paul Hoch

By
January 21st, 2015


Oracle’s Calculation Manager has a host of features for creating business rules across the Hyperion suite of products. Today I want to focus on how to automate those rules in a Hyperion Planning environment. If you have Calculation Manager rules that you would like to schedule to run on a periodic basis, including rules with run-time prompts, this post is for you.

Step 1: Create an encrypted password file

The automation utilities for Planning include the ability to create an encrypted password file. This is a recommended step in the process of automating rules.

On the Planning server go to the utilities directory (on my development environment this is located at E:\Oracle\Middleware\user_projects\PLANNING0\Planning\planning1)

Open a command prompt and navigate to that directory. Run the PasswordEncryption.cmd utility providing a filename and path for the encrypted file output:

CalcManager_1

It will prompt you for the password to encrypt. Once you type it in, it will generate the password file “password.txt” in the directory specified.

Step 2: Create the Runtime Prompts file

Select the rule you want to automate from the business rules section of Planning. My example rule is called “Rollup” and has a prompt for an Entity in the rule when a user runs it from a web form. I would like to have the same rule run nightly and have it run for “Total Entity”. Select “Total Entity” in the prompt box and then click on the “Create runtime prompt values file” button.

calcmanager_2

This will create a file with the name of the rule (in my example Rollup.xml) on the Planning server that contains the runtime prompt values specified. On my development server it’s here (E:\Oracle\Middleware\user_projects\PLANNING0\Planning\planning\RTP\admin):

Calcmanager_3

If you open the file in notepad you’ll see that it’s not actually an xml file, just plain text with the values supplied. My rule contains Personal Preference variables in addition to the run time prompts and they are also spelled out in the XML file.

calcmanager_4

You could create this file yourself from scratch following the syntax used in this example.

Step 3: Launch the rule using the /validate parameter to make sure you are doing it right using the Command Line!

The Syntax is:

CalcMgrCmdLineLauncher.cmd -f:password.txt /A:PlanBase /U:Admin /D:WrkForce /R:Rollup/F:E:\Oracle\Middleware\user_projects\PLANNING0\Planning\planning1\RTP\admin\Rollup.xml /Validate

Where

–f specifies the name of the password file we created in step 1.

/A is the name of the application.

/U is the username that the password corresponds to.

/D is the name of the database.

/R is the name of the rule

/F is the name and location of the runtime prompts values file we created in Step 2.

You should see it parse the variables and just return to the command prompt.

calcmanager_5

Step 4: Run for real

If everything looks good, remove the /validate parameter and run it. You can now wrap this up in a batch file and schedule it to run as needed using your operating systems utilities. If in the future the password changes for the user that you are using to run this you will need to re-run the Password Encryption utility to create a new password file.

A simple batch file to run this would look like this:

calcmanager_6

In a windows environment you could then use the Task Scheduler to call this batch file on a frequency of your choice.

 


Paul Hoch

About Paul Hoch

Paul Hoch has over 18 years of EPM and Hyperion experience, specializing in Essbase and Planning. He has successfully completed large-scale EPM implementations for clients in industries ranging from retail to government and financial services to health care. Paul has served as solution architect and team lead for implementations and he has developed and delivered advanced team training. He also blogs and presents regularly on EPM topics, including Essbase, Planning, Hybrid Cloud implementations, PBCS, EPBCS, Smart View, Calc Manager, migrating to the cloud, and more.

One comment on “Automating Calc Manager Rules for Oracle Hyperion Planning”

  1. Thanks Paul, for this excellent description. This was useful for me.
    Regards,
    Philip

Leave a Reply

Your email address will not be published. Required fields are marked *