In Part 1 of this series, we introduced Calc Manager, providing a general overview and explanation of some new terms. In the second post in the series, we walked through the development of a Planning rule that utilized a run time prompt. In this post, we’ll explore templates provided within Calc Manager.
As with the Rule Designer, which is a great tool to help less experienced developers build rules, templates provide a simple way to develop rules for basic tasks in Planning and Essbase…tasks such as copying, clearing, exporting, allocating, and aggregating data. In addition, you can design your own templates.
We’ll begin by logging on to Hyperion Workspace and navigating to Calc Manager. Once in Workspace, the navigation path is: Navigate -> Administer -> Calculation Manager.
Once in Calc Manager, you’ll land on the System View tab, which appears as follows:
Once again, I’ll use my EPMA enabled version of my Planning app based on Sample.Basic.
To access predefined templates, right click on “Rules”. Once you give the rule a name, the graphical designer is launched. In the “Existing Objects” window, you should find a list of the pre-existing templates. A list of the system templates follows:
CLEAR DATA
In order to use the system template to Clear Data, drag and drop “Clear Data” from the System Templates to the Rule Designer. This will then invoke a member selection window asking you to specify the data to clear. Keep in mind that this template generates a calc script utilizing the CLEARBLOCK command as opposed to a CLEARDATA command.
In my sample app, I select “FY11” for the Years dimension and “Final” for the Version dimension. The dropdown box for “Clearblock Option” can be used to define the blocks to be cleared…”All” is the default. The code that is generated appears below.
FIX ("FY11","Final")
CLEARBLOCK ALL;
ENDFIX
COPY DATA
The Copy Data template helps to walk the calc developer through the process of copying data from one slice of the database to another.
In the remainder of the wizard, you select the “Copy From” member and the “Copy To” member. The calc script generated follows:
FIX (@RELATIVE("Measures" , 0),@RELATIVE("Periods" ,0),@RELATIVE("Product" , 0),@RELATIVE("Market" , 0),@RELATIVE("Years" , 0),"Budget")
DATACOPY "Working" TO "Final";
ENDFIX
AMOUNT-UNIT-RATE
The Amount-Unit-Rate template allows the developer to build a calc script to solve for either an amount, unit, or rate, basically whichever is missing. I’ve added a couple of measures to my application to facilitate the demo. Using the member selection wizard, I’ve selected “Sales” as my amount, “Cases” as my unit, and “Revenue per Case” as my rate. The script generated by the template follows:
"Sales"(
IF ("Sales" == #missing and "Cases" != #missing and "Revenue per Case" != #missing)
"Sales" = "Cases" * "Revenue per Case";
ELSEIF ("Sales" != #missing and "Cases" == #missing and "Revenue per Case" != #missing)
"Cases" = "Sales" / "Revenue per Case";
ELSEIF ("Sales" != #missing and "Cases" != #missing and "Revenue per Case" == #missing)
"Revenue per Case" = "Sales" / "Cases";
ELSE
"Sales" = "Cases" * "Revenue per Case";
ENDIF
)
ALLOCATIONS
Two types of allocation templates are provided within Calc Manager. The first template, Allocate Level to Level, allows you to allocate from one level to another. In my example with my Planning app, you would use this template to allocate marketing expenses from product family to product using a driver like revenue. This approach utilizes @ANCESTVAL to build the script.
The second template, Allocate Simple, allocates values based on a predefined relationship, such as Marketing->Market * Cases/Cases->Market.
Both templates walk the developer through the setup of the allocations, selecting members that are fixed throughout the process, offset members (if any), etc.
AGGREGATION
The aggregation template aids the developer to create a script to aggregate the application. The first screen of the wizard, pictured below, allows you to select members for the FIX statement in the aggregation – here you would limit the calc to a particular version, scenario, or your non aggregating sparse dimension members.
The next screen prompts for dense dimensions to aggregate. However, if dynamic calcs are properly utilized, this should not be necessary.
The third screen asks for sparse dimensions for the aggregation. You should exclude any non aggregating sparse dimensions from this selection.
Next, you’re prompted for partial aggregations of dense dimensions. Again – if dynamic calcs are used properly, this should not be an issue.
In the final screen of the wizard, the developer selects settings for the script…
The code generated by Calc Manager follows:
SET AGGMISSG ON;
SET FRMLBOTTOMUP ON;
SET CACHE HIGH;
FIX (@RELATIVE("Years" , 0),"Working","Budget")
CALC DIM ("Product");
CALC DIM ("Market");
ENDFIX
Please note that this code is not optimized. In this example, I would use the following:
AGG (“Product”,”Market”);
The code as generated by Calc Manager will result in an extra pass through the database – the calc can be accomplished with a single pass. Additionally, AGG can be used in place of CALC DIM if there are no formulas on the dimensions being calculated. Generally speaking, stored formulas on sparse dimensions should be avoided due to performance issues.
SET Commands
The next template walks the user through setting various SET commands for the calc. This is a fairly straightforward exercise.
EXPORT DATA
This is another straightforward template that helps create a data export calc script. You need to define the fixed members for the export, delimiter, #MISSING value, export type (flat file, relational), etc.
In the final part of this series, due for posting on August 13, we’ll walk through the creation of a ruleset. If you have any questions before the next post, please leave a comment!
Think of script components as an easy way to reuse code…SET commands, standard cube aggregations and the like. 
