Developing Calculation Scripts for Block Storage Databases

You can include comments to annotate calculation scripts. Essbase ignores these comments when it runs the calculation script.

To include a comment, start the comment with /* and end the comment with */. For example:

/* This calculation script comment
   spans two lines. */

Essbase includes a syntax checker that flags syntax errors (such as a mistyped function name) in a calculation script. The results are displayed in the messages panel in Administration Services Console.

If syntax errors are not found, Essbase indicates the syntax check succeeded.

If syntax errors are found, Essbase indicates the syntax check failed and displays one error at a time. Typically, an error message includes the line number in which the error occurred and a brief description. For example, if a semicolon end-of-line character is missing at the end of a calculation script command, Essbase displays a message similar to this one:

Error: line 1: invalid statement; expected semicolon

When you reach the last error, Essbase displays the following message:

No more errors

Table 81 lists the calculation commands that perform a database calculation based on the structure and formulas in the database outline.

Table 82 lists the commands that manipulate the flow of calculations:

You can also use the IF and ENDIF commands to specify conditional calculations.

Table 83 lists the commands that declare temporary variables and, if required, set their initial values. Temporary variables store the results of intermediate calculations.

You can also use substitution variables in a calculation script. See Using Substitution Variables in Calculation Scripts.

Values stored in temporary variables exist only while the calculation script is running. You cannot report on the values of temporary variables.

Variable and array names are character strings that contain any of the following characters:

Typically, arrays are used to store variables as part of a member formula. The size of the array variable is determined by the number of members in the corresponding dimension. For example, if the Scenario dimension has four members, the following command creates an array called Discount with four entries:

ARRAY Discount[Scenario];

You can use multiple arrays at a time.

Table 84 lists the commands that define calculation behavior:

A SET command in a calculation script stays in effect until the next occurrence of the same SET command.

In the following calculation script, Essbase displays messages at the detail level (SET MSG DETAIL;) when calculating the Year dimension and displays messages at the summary level (SET MSG SUMMARY;) when calculating the Measures dimension:

SET MSG DETAIL;
CALC DIM(Year);

SET MSG SUMMARY;

CALC DIM(Measures);

Some SET calculation commands trigger additional passes through the database.

In the following calculation script, Essbase calculates member combinations for Qtr1 with SET AGGMISSG turned on, and then does a second calculation pass through the database and calculates member combinations for East with SET AGGMISSG turned off:

SET AGGMISSG ON;
Qtr1;
SET AGGMISSG OFF;

East;

Also see Using Two-Pass Calculation.

You can use equations in a calculation script to assign a value to a member. The syntax for an equation:

member = mathematical_expression;

member is a member name from the database outline and mathematical_expression is any valid mathematical expression.

Essbase evaluates the expression and assigns the value to the specified member.

In the following example, Essbase cycles through the database, subtracting the values in COGS from the values in Sales, and placing the result in Margin:

Margin = Sales - COGS;

In this example, Essbase cycles through the database, subtracting the values in Cost from the values in Retail, calculating the resulting values as a percentage of the values in Retail, and placing the results in Markup:

Markup = (Retail - Cost) % Retail;

You can also use the > (greater than) and < (less than) logical operators in equations.

In the following example, if February sales are greater than January sales, Sales Increase Flag results in a value of 1; if false, the result is a value of 0:

Sales Increase Flag = Sales -> Feb > Sales -> Jan;

When you use an IF statement as part of a member formula in a calculation script, you must:

In the following example, the entire IF…ENDIF statement is enclosed in parentheses and associated with the Profit member, Profit (IF(...)...):

Profit
(IF (Sales > 100)
   Profit = (Sales - COGS) * 2;
ELSE
   Profit = (Sales - COGS) * 1.5;
ENDIF;)

Essbase cycles through the database and performs the following calculations:

When you use an interdependent formula in a calculation script, the same rules apply as for the IF statement. You must:

In the following example, the entire formula is enclosed in parentheses and associated with the Opening Inventory member:

"Opening Inventory"
(IF(NOT @ISMBR (Jan))
   "Opening Inventory" = @PRIOR("Ending Inventory");
ENDIF;)
"Ending Inventory" = "Opening Inventory" - Sales + Additions;

When you calculate formulas, be sure to use parentheses correctly.

In the following example, incorrectly placed parentheses causes Essbase to perform two calculation passes through the database: once calculating the formulas on the members Qtr1 and Qtr2; and once calculating the formula on Qtr3:

(Qtr1;
Qtr2;)
Qtr3;

In contrast, the following configurations cause Essbase to cycle through the database only once, calculating the formulas on the members Qtr1, Qtr2, and Qtr3:

Qtr1;
Qtr2;
Qtr3;

or

(Qtr1;
Qtr2;
Qtr3;)

Similarly, the following formulas cause Essbase to cycle through the database once, calculating both formulas in one pass:

Profit = (Sales - COGS) * 1.5;
Market = East + West;

When calculating a series of dimensions, you can optimize performance by grouping the dimensions wherever possible.

For example, the following formula causes Essbase to cycle through the database only once:

CALC DIM(Year, Measures);

In contrast, the following syntax causes Essbase to cycle through the database twice, once for each CALC DIM command:

CALC DIM(Year);
CALC DIM(Measures);

When you include a substitution variable in a calculation script, Essbase replaces the substitution variable with the value you specified for the substitution variable. Substitution variables are useful, for example, when you reference information or lists of members that change frequently.

You create and specify values for substitution values in Administration Services.

You can create substitution variables at the server, application, and database levels. To use a substitution variable in a calculation script, the substitution variable must be available to the calculation script. For example, a database-level substitution variable is available only to calculation scripts within the database; a server-level substitution variable is available to any calculation script on the server.

In a calculation script, insert an ampersand (&) before a substitution variable. Essbase treats any string that begins with a leading ampersand as a substitution variable, replacing the variable with its assigned value before parsing the calculation script.

For example, in Sample.Basic, to calculate Qtr1 as the current quarter:

Also see Using Runtime Substitution Variables in Calculation Scripts.

Similar to substitution variables, a runtime substitution variable can be included in a calculation script wherever substitution variables are allowed. In the calculation script, an ampersand (&) must precede the name of the runtime substitution variable.

Runtime substitution variables are different from substitution variables in that every runtime substitution variable used in a calculation script must be declared in the SET RUNTIMESUBVARS calculation command. In SET RUNTIMESUBVARS, the name of a runtime substitution variable is required. Specifying a default value for the runtime substitution variable is optional. Also optional is a description of the runtime substitution variable's data type and data input limit, which is a string in the <RTSV_HINT>rtsv_description</RTSV_HINT> tag.

In this example of SET RUNTIMESUBVARS, three runtime substitution variables are declared: myMarket, salesNum, and PointD. Default values are specified for myMarket and pointD (for example, the value of myMarket is “New York”). The declaration for salesNum does not include a default value but does include a description in the <RTSV_HINT> tag.

SET RUNTIMESUBVARS
{
   myMarket = “New York”;
   salesNum <RTSV_HINT>salesNum: Input the value as an integer, such as 100</RTSV_HINT>;
   pointD = "Actual"->"Final";
};

At runtime, values can be provided for runtime substitution variables that do not have a default value, and default values that are specified in the SET RUNTIMESUBVARS command can be overwritten, using one of these methods:

When specifying runtime substitution variables as a string of key/value pairs, the string must be enclosed with single quotation marks, and key/value pairs must be separated by a semicolon, including a semicolon after the last runtime substitution variable in the string and before the terminal single quotation mark. In this example of a runtime substitution variable string, the name and value of four runtime substitution variables are specified (for example, the value of the runtime substitution variable named “a” is 100):

'a=100;b=@CHILDREN("100");c="Actual"->"Final";d="New York";'

When specifying runtime substitution variables in a text file, create the text file with an .rsv extension on the client computer. (Essbase does not support runtime substitution variable files located on the Essbase Server computer.) Each line in the file specifies one runtime substitution variable as a key/value pair and must end with a semicolon. In this example of an .rsv file, the name and value of four runtime substitution variables are specified (for example, the value of the runtime substitution variable named “a” is 100):

a=100;
b=200;
c=@CHILDREN("100");
d=@TODATE("DD/MM/YY","10/11/12");

When a calculation is executed, runtime substitution variable values are determined in the following order:

Consider these guidelines when using runtime substitution variables:

The rules for setting names and values for runtime substitution variables are the same as for substitution variables. See Rules for Setting Substitution Variable Names and Values.

Executing calc script 'calcprofit.csc' with runtime substitution variables {Entity = “MyCompany”; Currency = “USD”;}

SET RUNTIMESUBVARS
{
   myMarket <RTSV_HINT>myMarket: Input the value as a string, such as "New York"</RTSV_HINT>;
   salesNum <RTSV_HINT>salesNum: Input the value as an integer, such as 100</RTSV_HINT>;
   pointD <RTSV_HINT>pointD: Input the value as a member combination, such as "Actual"->"Final"</RTSVVAR_HINT>;
};

In calculation scripts, you can use system environment variables as placeholders for user-specific system settings. Because environment variables are defined at the operating system level, they are available to all calculation scripts on Essbase Server.

To declare a system environment variable, see your operating system documentation.

To use an environment variable in a calculation script, insert the dollar sign ($) character before the environment variable name. Essbase treats any string that begins with a leading dollar sign as an environment variable, replacing the variable with its assigned value before parsing the calculation script. If a member name begins with $, enclose the name in quotation marks.

When using environment variables in calculation scripts, follow these guidelines:

For example, you can use an environment variable to define the path and filename for an export file when exporting a block of data to a flat file. In the following calculation script, the path and filename (E:\temp\export1.txt) are explicitly defined:

SET DATAEXPORTOPTIONS
{
   DATAEXPORTLEVEL "ALL";
   DATAEXPORTOVERWRITEFILE ON;
};

FIX ("New York", "100-10");
   DATAEXPORT "File" "," "E:\temp\export1.txt";
ENDFIX;

You can declare an environment variable (ENV_FILE) to reference the path and filename (ENV_FILE="E:\temp\export1.txt") and use the following syntax in the calculation script:

DATAEXPORT "File" "," $ENV_FILE;

Essbase replaces the environment variable with the value taken from the user's environment.

In the following example, another environment variable (CurrMbr) is defined to export only Sales values (CurrMbr="Sales"):

SET DATAEXPORTOPTIONS
{
   DATAEXPORTLEVEL "ALL";
   DATAEXPORTOVERWRITEFILE ON;
};

FIX ("New York", "100-10", $CurrMbr);
   DATAEXPORT "File" "," $ENV_FILE;
ENDFIX;

Environment variables can also be used to parse arguments passed to RUNJAVA, an Essbase utility in which custom-defined functions can be called directly from a calculation script. For example, you can use environment variables to get user e-mail addresses.

In the following example, the RUNJAVA statement sends an e-mail notification to explicitly-defined users (to@somedomain.com and cc@mydomain.com):

RUNJAVA com.hyperion.essbase.calculator.EssbaseAlert "localhost" “to@somedomain.com” "cc@mydomain.com" "" "" "Mail Subject" "Mail Body" "";

You can declare environment variables for the users (ENV_TOMAIL=“to@somedomain.com” and ENV_CCMAIL=“to@mydomain.com”) and use the following syntax in the calculation script:

RUNJAVA com.hyperion.essbase.calculator.EssbaseAlert "localhost" $ENV_TOMAIL $ENV_CCMAIL "" "" "Mail Subject" "Mail Body" "";

Table 85 lists the commands that clear data:

The following calculation script command yields different results depending on whether the Scenario dimension is dense or sparse:

FIX(Actual)
   CLEARBLOCK NONINPUT;
ENDFIX

Sparse: The command removes only the blocks whose Scenario dimension member is Actual.

The following formula clears all the Actual data values for Colas:

CLEARDATA Actual -> Colas;

To clear an entire database, see “Clearing Data” in the Oracle Essbase Administration Services Online Help.

The DATACOPY calculation command copies data cells from one range of members to another range of members in a database. The two ranges must be the same size. For example, the following formula copies Actual values to Budget values:

DATACOPY Actual TO Budget;

You can use the FIX command to copy a subset of values. For example, the following formula copies Actual values to Budget values for the month of January only:

FIX (Jan)
   DATACOPY Actual TO Budget;
ENDFIX

See Using the FIX Command.

Member set functions generate a list of members that is based on a member you specify. For example, the @IDESCENDANTS function generates a list of all the descendants of a specified member. When you use a member set function in a formula, Essbase generates a list of members before calculating the formula.

In the following example, using the @IDESCENDANTS command on the member Total Expenses generates a list of these members—Total Expenses, itself, and its descendants, which are Marketing, Payroll, and Misc:

@IDESCENDANTS("Total Expenses");

Use the FIX command to define which members to include in the calculation.

The following example calculates only the Budget values for only the descendants of East (New York, Massachusetts, Florida, Connecticut, and New Hampshire):

FIX(Budget,@DESCENDANTS(East))
   CALC DIM(Year, Measures, Product);
ENDFIX

The following example fixes on member combinations for the children of East that have a UDA of New Mkt:

FIX(@CHILDREN(East) AND @UDA(Market,"New Mkt"))
   Marketing = Marketing * 1.1;
ENDFIX

The following example uses a wildcard match (???) to fix on member names that end in the characters -10, which are members 100-10, 200-10, 300-10, and 400-10:

FIX(@MATCH(Product, "???-10"))
   Price = Price * 1.1;
ENDFIX

When you use the FIX command only on a dense dimension, Essbase retrieves the entire block that contains the required value or values for the members that you specify. I/O is not affected, and the calculation performance time is improved.

When you use the FIX command on a sparse dimension, Essbase retrieves the block for the specified sparse dimension members. I/O may be greatly reduced.

Essbase cycles through the database once for each FIX command that you use on dense dimension members. When possible, combine FIX blocks to improve calculation performance.

For example, by using one FIX command, the following calculation script causes Essbase to cycle through the database only once, calculating both the Actual and the Budget values:

FIX(Actual,Budget)
   CALC DIM(Year, Measures);
ENDFIX

In contrast, by using two FIX commands, the following calculation script causes Essbase to cycle through the database twice: once calculating the Actual data values and once calculating the Budget data values:

FIX(Actual)
   CALC DIM(Year, Measures);
ENDFIX
FIX(Budget)
   CALC DIM(Year, Measures);
ENDFIX

You cannot FIX on a subset of a dimension that you calculate within a FIX command. For example, the following calculation script returns an error message because the CALC DIM operation calculates the entire Market dimension, although the FIX above it fixes on specific members of the Market dimension:

FIX(@CHILDREN(East) AND @UDA(Market,"New Mkt"))
   CALC DIM(Year, Measures, Product, Market);
ENDFIX

FIX commands can be nested within other FIX command blocks. However, using nested FIX commands incorrectly can result in incorrect results. For example, the intent of the following calculation script is to assign 1 to all children of East and then assign 2 to New York:

FIX (@CHILDREN(EAST))
   ''100-10''=1;
      FIX (''New York'')
         ''100-10''=2;
      ENDFIX
ENDFIX

However, the nested FIX command fixes on a subset of the dimension that is specified by the FIX command above it (which is not allowed); therefore, the script assigns 2 to all children of East because the script runs as if it were written as:

FIX (@CHILDREN(EAST),''New York'')
   ''100-10''=1;
   ''100-10''=2;
ENDFIX

Rather than using nested FIX commands, use two separate FIX command blocks. For example:

FIX (@CHILDREN(EAST))
   ''100-10''=1;
ENDFIX

FIX (''New York'')
   ''100-10''=2;
ENDFIX

The FIX command has restrictions. See the Oracle Essbase Technical Reference.

Use the EXCLUDE...ENDEXCLUDE command to define which members to exclude from the calculation. Sometimes it is easier to specify which members not to include in a calculation than to define which members to include.

When using the DATAEXPORT command to export data for direct insertion into a relational database:

By default, when inserting exported data, Essbase uses the row-insert method, in which each row is inserted one at a time. To improve performance, you can use the batch-insert method if your relational database and the ODBC driver support the functionality.

To enable batch insert, set the DATAEXPORTENABLEBATCHINSERT configuration setting in essbase.cfg to TRUE. To control the number of rows that are inserted simultaneously (instead of letting Essbase determine the batch size), use the DEXPSQLROWSIZE configuration setting to specify the number of rows in a batch (from 2 to 1000). If Essbase cannot determine whether the relational database and the ODBC driver support batch insert, it uses the row-insert method, and DEXPSQLROWSIZE (if set) is ignored.

Compared to using other methods to export data, using a calculation script has the following advantages and disadvantages:

Use the DATACOPY command to create a block for each existing block, and then perform calculations on the new blocks. For example:

DATACOPY Sales -> Actual TO Sales -> Budget;
DATACOPY Expenses -> Actual TO Expenses -> Budget;
FIX(Budget)
   (Sales = Sales -> Actual * 1.1;
   Expenses = Expenses -> Actual * .95;)
ENDFIX

Essbase creates blocks that contain the Budget values for each corresponding Actual block that exists. After the DATACOPY commands are finished, the remaining part of the script changes the values.

Using DATACOPY works well in these situations:

If you are concerned about unwanted values, instead of using DATACOPY, you can use the SET CREATENONMISSINGBLK ON calculation command, which calculates all potential blocks in memory and then stores only the calculated blocks that contain data values. The SET CREATENONMISSINGBLK calculation command can be useful when calculating values on dense or sparse dimensions.

The following example creates budget sales and expense data from existing actual data. Sales and Expenses are members in the dense Measures dimension; Budget and Actual are members in the sparse Scenario dimension.

FIX(Budget)
SET CREATENONMISSINGBLK ON
   (Sales = Sales -> Actual * 1.1;
   Expenses = Expenses -> Actual * .95;)
ENDFIX

The advantage of using the SET CREATENONMISSINGBLK command is that, when applied on dense members, only data cells that are affected by the member formula are saved. The disadvantage is that too many potential blocks may be materialized in memory, possibly affecting calculation performance. When you use this command, limit the number of potential blocks; for example, by using FIX to restrict the scope of the blocks to be calculated.

A partitioned application can span multiple servers, processors, or computers.

You can achieve significant calculation performance improvements by partitioning applications and running separate calculations on each partition. When using partitioning:

See Designing Partitioned Applications and Creating and Maintaining Partitions.

You must calculate databases in a specific order to ensure that Essbase calculates the required results.

Figure 134, Calculating Partitions illustrates partitions in which you view information from the West, Central, and East databases transparently from the Corporate database:

Figure 134. Calculating Partitions

West, Central, and East contain only actual values. Corporate contains actual and budgeted values. Although you can view West, Central, and East data in the Corporate database, the data exists only in the West, Central, and East databases—it is not duplicated in the Corporate database.

Therefore, when Essbase calculates Corporate, it must take the latest values from West, Central, and East. To obtain the required results, you must calculate West, Central, and East before you calculate Corporate.

You can save a calculation script in the following locations:

See Using Essbase to Manage Artifacts.

Before you can execute a calculation script in Administration Services, you must save it as an artifact on an Essbase Server, a client computer, or a network. See Saving Calculation Scripts.

When you use Administration Services to execute a calculation script, you can execute the calculation in the background so that you can continue working as the calculation processes. You can then check the status of the background process to see when the calculation has completed. See “Executing Calculation Scripts” in the Oracle Essbase Administration Services Online Help.

You can copy calculation scripts to applications and databases on any Essbase Server, according to your permissions. You can also copy scripts across servers as part of application migration.