ORACLE HYPERION CALC MANAGER – Part 2 – Creating a Planning Rule
In Part 1 of this series we introduced Calc Manager, providing a general overview and explanation of some new terms. In this post, we will walk through the development of a rule for Hyperion Planning using the graphical interface within Calc Manager.
Again, in order to access Calc Manager, log on to Hyperion Workspace. Once in Workspace, the navigation path is:
Once in Calc Manager, you’ll land on the System View tab, which appears as follows:
For purposes of this demonstration, I have created an EPMA enabled Planning application from the Sample.Basic application that we all know and love. When the Planning node is expanded, this is what I see:
First, to help illustrate functionality available in Calc Manager, I’m going to create a script component that contains my standard SET commands for the rule. In order to create the script component, right click on “Scripts” and click on “New”. Give your script a name and click on “OK”. This will launch the Component Designer.
From here, you have two options. If you know what your SET commands need to be, you’re free to type them in directly. If you wish to be prompted through the process, click on the button at the top left corner of the Component Designer window. This will launch a window with all of the calc functions and SET commands. The following shot displays the function selection interface for SET commands.
For my purposes, I’m going to directly type my SET commands into the Component Designer. Once complete, save and validate.
Think of script components as an easy way to reuse code…SET commands, standard cube aggregations and the like.
Once we have saved the script component with our SET commands, it’s time to develop our rule. To begin, right click on “Rules” under the database node and select “New”. Give your rule a name and click on “OK”. This will launch you into the Rule Designer window.
In this example, I’ll create a rule that aggregates the cube, using a run time prompt for the Version dimension.
We can now begin to develop our rule. First, we’ll select the script component for our SET commands that we developed earlier. Simply drag this into the rule designer to the right of “Start”. The Rule Designer window now looks like this:
Let’s take this opportunity to create our variable for the Version dimension run time prompt. Go to the “Tools” menu and select “Variables”. Once the Variable Navigator launches, expand the Planning, application, and database nodes. I’m going to create a run time prompt variable for the Version dimension. Right click on your rule name and select “New”. Once I populate the fields on the “Replacement” tab, my screen looks like this:
When complete, save the variable. Now, back to our rule…
We’re going to specify members for our “Fix” statement. To do this, select “Member Range” in the New Objects portion of the Rule Palette.
To add to the rule, drag and drop to the right of the SET command script. My screen looks like this:
Next, we’ll populate the members for our Fix statement. I’ll start with Measures. For my rule, I want to select all of the level 0 measures. Once I click on the Value field for the Measures dimension, an Actions box appears.
I want to select a Function. This invokes the function selection window that we observed earlier. I want to select @LEVMBRS from the list, which will then prompt for the dimension and level number.
I select Measures from the drop down box and enter “0” for the level name. I’m going to repeat this process for all of my dense and non aggregating sparse dimensions, with the exception of the Version dimension. This will be handled via the run time prompt. For the Version dimension, select “Variable” in the Actions box. Change the Category selection to “Rule” and this is what we see.
Highlight the variable and click OK. My member range box looks like this:
Now, we’ll develop the script component to aggregate the Product and Market dimensions. I’m going to drag a script from the “New Objects” portion of the Rule Palette into my member range. The graphical display looks like:
Again, I’m going to select a function (AGG in this case). I then select Products and Market from the dimension selector.
Now, save and validate. To deploy the rule to Planning, select Quick Deploy:
Once deployed, the rule can be run from Planning.
In this post, we’ve provided a walk through on developing a new rule using the graphical designer. More experienced developers can directly code the calc in script mode. To convert to script mode, select “Edit” and “Script” from the menu.
In the next post, due by July 31, we’ll explore templates and ruleset creation. In the meantime, please leave a comment if you have any questions!