When you create a calculation script, you must apply the following rules:

When you write a calculation script, use the Calculation Script Editor syntax checker to check the syntax. See Checking Syntax.

You can use the calculation commands in Table 54 to perform a database calculation that is based on the structure and formulas in the database outline.

You can use the commands in Table 55 to manipulate the flow of calculations:

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

You can use the commands in Table 56 to declare temporary variables and, if required, to 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.

You can use the commands in Table 57 to 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 when calculating the Year dimension and displays messages at the summary level 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;

See the SET AGGMISSG command in the Oracle Essbase Technical Reference. Also see Using Two-Pass Calculation.

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.  */

You can place member formulas in a calculation script. When you do, the formula overrides conflicting formulas that are applied to members in the database outline.

In a calculation script, you can perform both of these operations:

To calculate a formula that is applied to a member in the database outline, use the member name followed by a semicolon (;).

For example, the following command calculates the formula applied to the Variance member in the database outline:

Variance;

To override values that result from calculating an outline, manually apply a formula that you define in a calculation script.

For example, the following formula cycles through the database, adding the values in the members Payroll, Marketing, and Misc, and placing the result in the Expenses member.

Expenses = Payroll + Marketing + Misc;

This formula overrides any formula placed on the Expenses member in the database outline.

See Developing Formulas.

Member = mathematical expression;

Margin = Sales - COGS;

Markup = (Retail - Cost) % Retail;

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

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

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

Assume that you have a formula on a sparse dimension member, and the formula contains either of the following type of function:

Essbase always recalculates the data block that contains the formula, even if the data block is marked as clean for the purposes of Intelligent Calculation.

See Calculating Data Blocks and Understanding Intelligent Calculation.

You may achieve significant calculation performance improvements by carefully grouping formulas and dimensions in a calculation script. See the next two sections.

When you calculate formulas, avoid using parentheses unnecessarily.

In the following example, inappropriately placed parentheses cause 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. See Using Substitution Variables.

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:

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 are explicitly defined (see the text in bold):

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

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

You can declare an environment variable 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 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. The following RUNJAVA statement sends an e-mail notification to explicitly-defined users (see the text in bold):

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" "";

Using environment variables in calculation scripts is the same as using them in formulas. See Using Environment Variables in Formulas

You can use the commands in Table 58 to clear data.

If, in the Sample.Basic database, the Scenario dimension is dense, the following example removes all data cells that do not contain input data values and that intersect with member Actual from the Scenario dimension:

FIX(Actual)
   CLEARBLOCK NONINPUT;
ENDFIX

If the Scenario dimension is sparse, the following example removes only the blocks whose Scenario dimension member is Actual:

FIX(Actual)
   CLEARBLOCK NONINPUT;
ENDFIX

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.

You can use the DATACOPY calculation command to copy 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, in the Sample.Basic database, 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, in the Sample.Basic database, 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. For more information about the DATACOPY command, see the Oracle Essbase Technical Reference.

The DATAEXPORT command enables calculation scripts to export data in binary or text, or directly to a relational database. A set of data-export-related calculation commands qualify what data to export and provide various output and formatting options.

The following command sequence shows the typical calculation script structure for exporting data:

SET DATAEXPORTOPTIONS
  {
    DATAEXPORTLEVEL parameters;
    DATAEXPORTDYNAMICCALC ON | OFF;
    DATAEXPORTNONEXISTINGBLOCKS ON | OFF;
    DATAEXPORTDECIMAL n;
    DATAEXPORTPRECISION n;
    DATAEXPORTCOLFORMAT ON | OFF;
    DATAEXPORTCOLHEADER dimensionName;
    DATAEXPORTDIMHEADER ON | OFF;
    DATAEXPORTRELATIONALFILE ON | OFF;
    DATAEXPORTOVERWRITEFILE ON | OFF;
    DATAEXPORTDRYRUN ON | OFF;
   };
DATAEXPORTCOND parameters;
FIX 
  (fixMembers)
  DATAEXPORT parameters;
ENDFIX;

To develop a calculation script that exports a subset of data, first specify the SET DATAEXPORTOPTIONS command to define options for export content, format, and process (see Table 59).

Options and parameters are optional, with default values. See the Oracle Essbase Technical Reference.

When exporting data to a binary or text file, you can specify a limit for the size of the export file with the EXPORTFILESIZELIMIT configuration setting in essbase.cfg. The minimum file size limit is 1 MB. The maximum size of the export file is limited only by factors such as file system limits (for example, some systems do not support files that are larger than 2 GB), available disk space, and user limits. By default, the maximum file size for the export file is 2 GB. If the exported data exceeds the file size limit set with EXPORTFILESIZELIMIT or, if using the default 2 GB limit on a file system that does not support large files, Essbase creates multiple export files, as needed. An underscore and number is appended to the names of the additional files, starting with _1. For example, if the filename is outfile.txt and three files are created, the resulting file names are outfile.txt, outfile_1.txt, and outfile_2.txt.

Use a DATAEXPORTCOND command to select data based on data values. Then use FIX...ENDFIX or EXCLUDE...ENDEXCLUDE calculation commands to select a slice of the database to be exported. Within the FIX...ENDFIX or EXCLUDE...ENDEXCLUDE group, include the DATAEXPORT command.

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 at one time (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.

Use the DATAIMPORTBIN calculation command to import a previously exported binary export file. The SET DATAIMPORTIGNORETIMESTAMP calculation command enables you to manage the import requirement for a matching outline timestamp.

Other export methods include using ESSCMD, MaxL, and Administration Services Console for database backup. Report Writer can be used to select and format a database subset, creating an output text file (see Exporting Text Data Using Report Scripts).

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

When you use a formula on a dense member in a dense dimension, if the resultant values are from a dense dimension and the operand or operands are from a sparse dimension, Essbase does not automatically create the required blocks.

In the following example, based on Sample.Basic, assume that you want to create 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)
   (Sales = Sales -> Actual * 1.1;
   Expenses = Expenses -> Actual * .95;)
ENDFIX

Sales and Expenses, the results of the equations, are dense dimension members; the operand, Actual, is in a sparse dimension. Because Essbase executes dense member formulas only on existing data blocks, the calculation script does not create the required data blocks and Budget data values are not calculated for blocks that do not already exist.

You can solve the problem using the following techniques.

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

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

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.

The example in Figure 136, Calculating Partitions shows partitions in which you view information from the West, Central, and East databases transparently from the Corporate database.

Figure 136. 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.

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

You can save a calculation script in the following locations:

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.

After you execute a calculation script, you can check the results of the calculation in Smart View or Spreadsheet Add-in.

Essbase provides the following information about the executed calculation script:

To display more-detailed information, you can use the SET MSG SUMMARY, SET MSG DETAIL, and SET NOTICE commands in a calculation script. See Specifying Global Settings for a Database Calculation.

You can use these messages to understand how the calculation is performed and to tune it for the next calculation.

Where you view this information depends on the tool used to execute the calculation script.

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.