Calculation Scripts:- Session 6

In this session we will be discussing on different sections of a calc script, a typical calc script contains:

1. Information Section,

2. Housekeeping Section,

3. Baseline FIX Section,

4. Other Fixes and Commands Section,

5. Rollup or Aggregation Section,

6. Back Calculations, If needed.

Information section contains information like: Who created the script, what it does, any 

instructions and other basic details and information on the script. Housekeeping contains all the 

SET commands. SET commands prepare the calculator for proper processing of commands 

and these commands are restricted to a script, they donot make any permanent settings 

change at Essbase Server. Lets discuss few of the SET commands in detail:

SET AGGMISSG: The SET AGGMISSG command specifies whether Hyperion Essbase 

aggregates #MISSING values in the database. We can set this to either ON or OFF. Ex: if 

below is the existing data for a particular intersection:

This is the new data we lock and sent to Essbase:

If we have used SET AGGMISSG ON; in our calc scipt which Aggregates entity then the result 

will be:

If we have used SET AGGMISSG OFF; then the result will be:

When the aggmissg was on Essbase have aggregated #missing for Jan column and overwrite 

value at APAC with #missing in other case the parent value was intact when the child are 

#missing.

SET CALCPARALLEL: SET CALCPARALLEL enables parallel calculation in place of the 

default serial calculation. In our example SET CALCPARALLEL 3;This enables up to three 

threads to be used to perform calculation tasks at the same time.Essbase analyzes each pass 

of a calculation to determine whether parallel calculation is possible. If it is not, Essbase uses 

serial calculation even if CALCPARALLEL is set. The value of Calcparallel is usually set one 

less then the processor. Ex: if you have 4 processors then you can set value as 3, anyway 

Essbase re-adjust the value if needed.

SET LOCKBLOCK:The SET LOCKBLOCK command specifies the maximum number of 

blocks that Hyperion Essbase can address concurrently when calculating a sparse member 

formula.

SET MSG: The SET MSG command indicates the level of messaging that you wish to receive 

during your calculations.Syntax:

SET MSG SUMMARY | DETAIL | ERROR | WARNS | INFO | NONE | ONLY;

SET UPDATECALC: The SET UPDATECALC command turns intelligent calculation on and 

off. (More on intelligent Calculations later)

Baseline Fix defines the focus of the calculation, most of the times its a fix on the members of 

Year, Scenario and Version. In Baseline Fix we write few commands or more fixes if needed. 

Once the calculation is done we write Aggregation.

Back Calculations: Members like % gives a wrong value at aggregated levels so we need to 

write a back calculation for them. Why we need back Calc? Lets understand the same by an 

example:

When we calculate "X% of Sales" and Aggregate Period the value of X% at Qtr1 will 0.6. This 

is because first Account's dimension is calculated to populate the value of "X% of Sales" Once 

the Calculation of Accounts is done we Aggregate Account's dimension members to Qtrs, sum 

of the Account's values of Jan, Feb and Mar is passed to Qtr1, hence we got 0.6. Which is 

actually a wrong value, to take care of such scenarios, Once the aggregation is finished we re 

calculate the % values. We can Write a simple calc here:

CALC DIM ("Account", "Period" );

AGG ("Entity" );

/* back calculation */

"X%" = "X% of Sales" / "Sales";

This will re-calculate the value of X% and correct the values at parent levels. You might not have 

noticed we too have the wrong values of % in our previous sessions, this is the way to take care 

of % values and Make sure that Back Calculations are written once the aggregation is done. 

One of the reasons why we call it Back Calc, because we are calculating the database back 

after aggregation.

Cheers..!!!

Rahul