Database Reference
In-Depth Information
script consider making the accounts dynamic. In-line formulas are faster than formulas in
script. Include the calculation for “mth End Bal” in the first FIx. Can @DESCEnDAntS
(“Segment 1”) be changed to process only level-0 blocks. In the second script make sure
that all of the dimensions truly need the CALC DIm, otherwise use Agg if there are
nondynamic upper-level members to be calculated. Is it feasible to clear the upper blocks
prior to the calc dim? It might make sense to combine #3 and #4 if it is possible to clear
and calculate “Cur Est” in a single pass. often times I find that certain calculation func-
tions are culprits. Consider eliminating the function and writing the code long hand.
make use of the block counts during the tuning. reducing the number of blocks almost
always results in better performance. Do not be afraid to experiment.
give this method a try. It is truly eye opening.
4 .7. 3
Script Variables: Are They Useful?
Those new to calculation scripts attempt to use variables because they have used them
in procedural languages. They usually are not successful because they do not understand
how Essbase loops through the dense array. I use variables more than most because I do
not like to add members to the database if they provide no multidimensional value.
variables are particularly useful as switches to control execution within a script, collect
running totals, and store data values used in calculations. As an introduction, this first
script uses variables to illustrate the Essbase order of operation within a dense block.
The database is Sample.Basic and the dense block has measures and year in that order.
set updatecalc off;
set Msg Summary;
VAR vcount = 0;
FIX("100-10",Florida)
Actual (
IF (@ISMBR(@IDESCENDANTS ("Year")))
vcnt=vcnt + 1;
Actual = vCnt;
ENDIF;)
ENDFIX
The script defines a variable named “vcount” which is initialized as zero. here are the
rules for variables.
• variables persist for the life of the script.
• variables store only numeric data.
• variables must be referenced within a member calculation block.
The script above processes on a single block of data that is Product = “100-10,” market =
“Florida,” and Scenario = “Actual.” As each member of the dense block is processed, the
variable “vcount” is incremented. The new “vcount” value is written to the current member.
Figure 4.11 illustrates the block that was modified by the variable calc script. The cells
in column B were processed first starting with B3. Cell B5 looks odd because margin
is a dynamically calculated as Sales-CogS. A value of 3 was written to B5, but it was
trumped by the formula.
In this exercise, the months are processed in order and all accounts are processed
before moving to the next month. Is that always the case? Currently the dense order is
measures and year. Let us flip the dimension order to year first and measures second in
the outline order and rerun the calculation, viewing the results in Figure 4.12.
