Developing Calculation Scripts

In This Section:

Understanding Calculation Scripts

Understanding Calculation Script Syntax

Planning Calculation Script Strategy

Reviewing the Process for Creating Calculation Scripts

The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.

Also see:

Understanding Calculation Scripts

A calculation script, which contains a series of calculation commands, equations, and formulas, allows you to define calculations other than those defined by the database outline.

In a calculation script, you can perform a default calculation (CALC ALL) or a calculation of your choosing (for example, you can calculate part of a database or copy data values between members). You must write a calculation script to do any of the following tasks:

  • Calculate a subset of a database

    See Calculating a Subset of a Database.

  • Change the calculation order of the dense and sparse dimensions in a database

  • Perform a complex calculation in a specific order or perform a calculation that requires multiple iterations through the data (for example, some two-pass calculations require a calculation script)

  • Perform any two-pass calculation on a dimension without an accounts tag

    See Using Two-Pass Calculation.

  • Perform a currency conversion

    See Designing and Building Currency Conversion Applications.

  • Calculate member formulas that differ from formulas in the database outline (formulas in a calculation script override formulas in the database outline)

  • Use an API interface to create a custom calculation dynamically

  • Use control of flow logic in a calculation (for example, to use the IF…ELSE…ENDIF or the LOOP…ENDLOOP commands)

  • Clear or copy data from specific members

    See Copying Data.

  • Define temporary variables for use in a database calculation

    See Declaring Data Variables.

  • Force a recalculation of data blocks after you have changed a formula or an accounts property on the database outline

  • Control how Essbase uses Intelligent Calculation when calculating a database

    See Understanding Intelligent Calculation.

The following calculation script, based on the Sample.Basic database, calculates the Actual values from the Year, Measures, Market, and Product dimensions:

FIX (Actual)
   CALC DIM(Year, Measures, Market, Product);
ENDFIX

Using Calculation Script Editor in Administration Services Console, you can create calculation scripts by:

  • Entering the calculation script in the text area of the script editor

  • Using the UI features of the script editor to build the script

  • Creating the script in the text editor and pasting the script text into Calculation Script Editor

See “About Calculation Script Editor” in the Oracle Essbase Administration Services Online Help.

Calculation scripts created using Administration Services are given a .csc extension by default. If you run a calculation script from Administration Services, Smart View, or Spreadsheet Add-in, the file must have a .csc extension. However, because a calculation script is a text file, you can use MaxL or ESSCMD to run any text file as a calculation script.

A calculation script can also be a string defined in memory and accessed through the API on an Essbase client or an Essbase Server. Therefore, from dialog boxes, you can dynamically create a calculation script that is based on user selections.

Understanding Calculation Script Syntax

Essbase provides a flexible set of commands that you can use to control how a database is calculated. You can construct calculation scripts from commands and formulas. In Calculation Script Editor, script elements are color-coded to aid readability and you can enable autocompletion to help build scripts interactively as you type. See “About Calculation Script Editor” in the Oracle Essbase Administration Services Online Help.

Computation, control of flow, and data declarations are discussed in the following sections.

For a full list of calculation script commands and syntax, see the Oracle Essbase Technical Reference.

Understanding the Rules for Calculation Script Syntax

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

  • End each formula or calculation script command with a semicolon (;):

    Example 1:

    CALC DIM(Product, Measures);

    Example 2:

    DATACOPY Plan TO Revised_Plan;

    Example 3:

    "Market Share" = Sales % Sales -> Market;

    Example 4:

    IF (Sales <> #MISSING)
       Commission = Sales * .9;
       ELSE
          Commission = #MISSING;
    ENDIF;

    You do not need to end the following commands with semicolons:

    IF
    ENDIF
    ELSE
    ELSIF
    FIX
    ENDFIX
    EXCLUDE
    ENDEXCLUDE
    LOOP
    ENDLOOP

    Note:

    Although not required, it is good practice to follow each ENDIF statement in a formula with a semicolon.

  • Enclose a member name in double quotation marks (" "), if that member name meets any of the following conditions:

  • If you are using an IF statement or an interdependent formula, enclose the formula in parentheses to associate it with the specified member. For example, the following formula is associated with the Commission member in the database outline:

    Commission
    (IF(Sales < 100)
       Commission = 0;
    ENDIF;)
  • End each IF statement in a formula with an ENDIF statement. For example, the previous formula contains a simple IF...ENDIF statement.

  • If you are using an IF statement that is nested within another IF statement, end each IF with an ENDIF statement. For example:

    "Opening Inventory"
    (IF (@ISMBR(Budget))
       IF (@ISMBR(Jan))
          "Opening Inventory" = Jan;
       ELSE 
          "Opening Inventory" = @PRIOR("Ending Inventory");
       ENDIF;
    ENDIF;)
  • You do not need to end ELSE or ELSEIF statements with ENDIF statements. For example:

    Marketing
    (IF (@ISMBR(@DESCENDANTS(West)) OR @ISMBR(@DESCENDANTS(East)))
       Marketing = Marketing * 1.5;
    ELSEIF(@ISMBR(@DESCENDANTS(South)))
       Marketing = Marketing * .9;
    ELSE 
       Marketing = Marketing * 1.1;
    ENDIF;)

    Note:

    If you use ELSE IF (with a space) rather than ELSEIF (one word) in a formula, you must supply an ENDIF for the IF statement.

  • End each FIX statement with an ENDFIX statement. For example:

    FIX(Budget,@DESCENDANTS(East))
       CALC DIM(Year, Measures, Product);
    ENDFIX
  • End each EXCLUDE statement with an ENDEXCLUDE statement.

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

Understanding Calculation Commands

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.

Table 54. List of Commands for Calculating a Database

Calculation

Command

The entire database, based on the outline

CALC ALL

A specified dimension or dimensions

CALC DIM

All members tagged as two-pass on the dimension tagged as accounts

CALC TWOPASS

The formula applied to a member in the database outline, where membername is the name of the member to which the formula is applied

membername

All members tagged as Average on the dimension tagged as accounts

CALC AVERAGE

All members tagged as First on the dimension tagged as accounts

CALC FIRST

All members tagged as Last on the dimension tagged as accounts

CALC LAST

Currency conversions

CCONV

Controlling the Flow of Calculations

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

Table 55. List of Commands to Control the Flow of Calculations

Calculation

Command

Calculate a subset of a database by inclusion

FIX…ENDFIX

Calculate a subset of a database by exclusion

EXCLUDE…ENDEXCLUDE

Specify the number of times that commands are iterated

LOOP…ENDLOOP

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

Note:

You cannot branch from one calculation script to another calculation script.

Declaring Data Variables

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).

Table 56. List of Commands for Declaring Data Variables

Calculation

Command

Declare one-dimensional array variables

ARRAY

Declare a temporary variable that contains a single value

VAR

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:

  • Letters a–z

  • Numerals 0–9

  • Special characters: $ (dollar sign), # (pound sign), and _ (underscore)

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.

Specifying Global Settings for a Database Calculation

You can use the commands in Table 57 to define calculation behavior:

Table 57. List of Commands for Defining Calculation Behavior

Calculation

Command

Specify how Essbase treats #MISSING values during a calculation

SET AGGMISSG

Adjust the default calculator cache size

SET CACHE

Enable parallel calculation (see Using Parallel Calculation)

SET CALCPARALLEL

Increase the number of dimensions used to identify tasks for parallel calculation (see Using Parallel Calculation)

SET CALCTASKDIMS

Optimize the calculation of sparse dimension formulas in large database outlines (see Optimizing Formulas on Sparse Dimensions in Large Database Outlines)

SET FRMLBOTTOMUP

Display messages to trace a calculation

SET MSG

SET NOTICE

Turn on and turn off Intelligent Calculation (see Turning Intelligent Calculation On and Off)

SET UPDATECALC

Control how Essbase marks data blocks for Intelligent Calculation (see Using the SET CLEARUPDATESTATUS Command)

SET CLEARUPDATESTATUS

Specify the maximum number of blocks that Essbase can lock concurrently when calculating a sparse member formula

SET LOCKBLOCK

Turn on and turn off the Create Blocks on Equation setting (controls creation of blocks when you assign nonconstant values to members of a sparse dimension) (see Nonconstant Values Assigned to Members in a Sparse Dimension)

SET CREATEBLOCKEQ

Enable calculations on potential data blocks and save these blocks when the result is not #MISSING

SET CREATENONMISSINGBLK

(Currency conversions) Restrict consolidations to parents that have the same defined currency (see Calculating Databases)

SET UPTOLOCAL

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.

Adding Comments

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

Planning Calculation Script Strategy

You can enter a calculation script directly into the text area of Calculation Script Editor, or you can use the UI features of Calculation Script Editor to build the calculation script.

Using Formulas in a Calculation Script

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:

  • Calculate a member formula on the database outline

  • Define a formula

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.

Note:

You cannot apply formulas to shared members or label only members.

See Developing Formulas.

Basic Equations

You can use equations in a calculation script to assign 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.

For example, the following formula causes Essbase to cycle through the database, subtracting the values in COGS from the values in Sales and placing the result in Margin:

Margin = Sales - COGS;

The next formula 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 it is true that February sales are greater than January sales, Sales Increase Flag results in a 1 value; if false, the result is a 0 value:

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

Conditional Equations

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

  • Associate the IF statement with a single member

  • Enclose the IF statement in parentheses

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:

  1. The IF statement checks whether the value of Sales for the current member combination is greater than 100.

  2. If Sales is greater than 100, Essbase subtracts the value in COGS from the value in Sales, multiplies the difference by 2, and places the result in Profit.

  3. If Sales is less than or equal to 100, Essbase subtracts the value in COGS from the value in Sales, multiplies the difference by 1.5, and places the result in Profit.

Interdependent Formulas

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

  • Associate the formula with a single member

  • Enclose the formula in parentheses

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;

Using a Calculation Script to Control Intelligent Calculation

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

  • Relationship (for example, @PRIOR or @NEXT)

  • Financial (for example, @NPV or @INTEREST)

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.

Grouping Formulas and Calculations

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

Calculating a Series of Member Formulas

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;

Calculating a Series of Dimensions

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

Using Substitution Variables in Calculation Scripts

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:

  • Create a substitution variable for the current quarter (&CurQtr) and assign it the value Qtr1

  • Create a calculation script that uses the &CurQtr substitution variable

    FIX(&CurQtr)
       CALC DIM(Measures, Product);
    ENDFIX

Using Environment Variables in Calculation Scripts

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.

Note:

Environment variables cannot be used in MDX queries.

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:

  • Environment variable names:

    • Must consist of alphanumeric characters or underscores (_)

    • Cannot include nonalphanumeric characters, such as hyphens (-), asterisks (*), and slashes (/)

    • Cannot exceed 320 bytes (for Unicode-mode and non-Unicode mode applications)

  • Environment variable values:

    • May contain any character except a leading dollar sign ($)

    • Whether numeric or non-numeric, must be enclosed in quotation marks (" ")—for example:

      MY_PROD="100"
      ENV_FILE="E:\temp\export1.txt"

      For non-numeric values, if you do not enclose the value in quotation marks when you define the environment variable, Essbase automatically encloses the value with quotation marks when the environment variable is passed.

      For numeric values, Essbase does not automatically enclose the value with quotation marks when the variable is passed. (The reason is that Essbase cannot determine if you intend to pass a numeric value or a member name. For example, if you use a calculation script statement such as 'Sales = $MY_SALES' where MY_SALES=700, the intent is to pass the numeric value of 700. If, however, Essbase encloses MY_SALES in quotation marks, MY_SALES is treated as a member name. The member name would be passed, not the numeric value, causing an error.) If you want the numeric value of the variable to be treated as a string, you must enclose the value with quotation marks when you define the environment variable.

    • Cannot exceed 256 bytes (for Unicode-mode and non-Unicode mode applications)

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

Clearing Data

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

Table 58. List of Commands for Clearing Data

Calculation

Command

Changes the values of the cells you specify to #MISSING. The data blocks are not removed.

You can use the FIX command with the CLEARDATA command to clear a subset of a database.

CLEARDATA

Remove the entire contents of a block, including all the dense dimension members.

Essbase removes the entire block, unless CLEARBLOCK is inside a FIX command on members within the block.

CLEARBLOCK

Removes consolidated level blocks.

Remove blocks containing derived values. Applies to blocks that are completely created by a calculation operation, not to blocks into which any values were loaded.

Remove blocks for Dynamic Calc and Store member combinations.

See Dynamically Calculating Data Values.

CLEARBLOCK DYNAMIC

Remove empty blocks

CLEARBLOCK EMPTY

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.

Copying Data

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.

Calculating a Subset of a Database

*  To calculate a subset of a database, use one of the following methods:

    Note:

    When Intelligent Calculation is turned on, the newly calculated data blocks are not marked as clean after a partial calculation of a database. When you calculate a subset of a database, you can use the SET CLEARUPDATESTATUS AFTER command to ensure that the newly calculated blocks are marked as clean. Using this command ensures that Essbase recalculates the database as efficiently as possible using Intelligent Calculation. See Understanding Intelligent Calculation.

    Calculating Lists of Members

    You can use a member set function to 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 Sample.Basic database, the following example generates a list of these members—Total Expenses, Marketing, Payroll, and Misc:

    @IDESCENDANTS("Total Expenses");

    See the Oracle Essbase Technical Reference.

    Using the FIX Command

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

    The following examples are based on the Sample.Basic database.

    • This 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
    • This 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

      For information on defining UDAs, see Creating and Changing Database Outlines.

    • This example uses a wildcard match to fix on member names that end in the characters -10, which in Sample.Basic 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 command, this calculation script causes Essbase to cycle through the database twice: once calculating the Actual data values; 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 statement.

    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

    Note:

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

    Using the Exclude Command

    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.

    Note:

    The EXCLUDE command has some restrictions. See the Oracle Essbase Technical Reference.

    Using DATAEXPORT to Export Data

    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).

    Table 59. List of SET DATAEXPORTOPTIONS Commands

    CalculationCommand
    Content options
    Specify all, level 0, or input data valueDATAEXPORTLEVEL
    Control export of dynamically calculated valuesDATAEXPORTDYNAMICCALC
    Specify whether to export data from all potential data blocks or only from existing data blocks.DATAEXPORTNONEXISTINGBLOCKS
    Specify the number of decimal positions in the exported valuesDATAEXPORTDECIMAL
    Specify the total number of positions in the exported valuesDATAEXPORTPRECISION
    Output format options
    Specify columnar or noncolumnar formatDATAEXPORTCOLFORMAT
    Specify a dense dimension for the column headerDATAEXPORTCOLHEADER
    Include a header record that lists all dimension names in the same order as the data in the fileDATAEXPORTDIMHEADER
    Format the text export file for importing the data into a relational databaseDATAEXPORTRELATIONALFILE
    Processing options
    Specify whether an existing file with the same name and location is replacedDATAEXPORTOVERWRITEFILE
    Enable validating the set of calculation commands and viewing export statistics, including a time estimate—without having to perform the entire export processDATAEXPORTDRYRUN

    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:

    • The table to which the data is to be written must exist prior to data export

    • Table and column names cannot contain spaces

    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.

    Note:

    If DATAEXPORTENABLEBATCHINSERT is set to TRUE and DEXPSQLROWSIZE is set to 1, batch insert is disabled (as a DEXPSQLROWSIZE setting of 1 inserts rows one at a time).

    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:

    • Advantages

      • Enables exporting a subset of data

      • Supports multiple targets: flat files, relational databases, and binary files

      • Provides options for type, format, or data

      • As part of a calculation script, can be deployed in a batch process

      • Can be very fast when the dynamic calculation export option is not used because DATAEXPORT directly accesses Kernel storage blocks in memory

      • Provides, through binary export/import, a faster way to back up and restore data because the compressed format used by binary export requires less storage for the export files

      • Can be used as a debug tool to trace batch calculation results by using the DATAEXPORT command before and after other calculation commands to track data changes

    • Disadvantages

      • Contains limited data formatting options compared to Report Writer formatting

      • Works with stored members and Dynamic Calc members only, with no support for attribute members and alias names

      • Not supported for aggregate storage databases

      • Cannot export data directly to the client

      • Can significantly impact performance when exporting dynamic calculation data, unless you set DATAEXPORTNONEXISTINGBLOCKS to ON.

    Enabling Calculations on Potential Blocks

    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.

    Using DATACOPY to Copy Existing Blocks

    You can 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 when:

    • There is a mathematical relationship between values in existing blocks and their counterparts created by the DATACOPY.

      For example, in the preceding example, the Budget values can be calculated based on the existing Actual values.

      Caution!

      DATACOPY creates the new blocks with identical values in all cells from the source blocks. If the formula performs only on a portion of the block, these copied cells remain at the end of calculation, potentially resulting in unwanted values.

    • None of the blocks that are copied contain only #MISSING values.

      If #MISSING values exist, blocks are written that contain only #MISSING values. Unneeded #MISSING blocks require Essbase resource and processing time.

    Using SET CREATENONMISSINGBLK to Calculate All Potential Blocks

    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

    Note:

    If SET CREATEBLOCKONEQ ON is set for sparse dimensions, SET CREATENONMISSINGBLK ON temporarily overrides it until a SET CREATENONMISSINGBLK OFF command is encountered or the calculation script is completed. See Nonconstant Values Assigned to Members in a Sparse Dimension.

    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.

    See the Oracle Essbase Technical Reference.

    Writing Calculation Scripts for Partitions

    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:

    • Evaluate the performance impact on the overall database calculation. To improve performance, you can:

      • Redesign the overall calculation to avoid referencing remote values that are in a transparent partition in a remote database

      • Dynamically calculate a value in a remote database.

        See Dynamically Calculating Data in Partitions.

      • Replicate a value in the database that contains the applicable formula.

        For example, if replicating quarterly data for the Eastern region, replicate only the values for Qtr1, Qtr2, Qtr3, and Qtr4, and calculate the parent Year values locally.

    • Ensure that a referenced value is up-to-date when Essbase retrieves it. Choose one of the options previously discussed (redesign, dynamically calculate, or replicate) or calculate the referenced database before calculating the formula.

    See Designing Partitioned Applications and Creating and Maintaining Partitions.

    Controlling Calculation Order for 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

    The image illustrates partitions (West, East, and Central) in the Corporate database.

    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.

    Reviewing the Process for Creating Calculation Scripts

    Use this process to create a calculation script:

    1. Create a calculation script or open an existing calculation script.

      See “Creating Scripts” or “Opening Scripts” in the Oracle Essbase Administration Services Online Help.

    2. Enter or edit the contents of the calculation scripts.

      See “About Calculation Script Editor” in the Oracle Essbase Administration Services Online Help for information about:

      • Associating a script with an outline

      • Searching an outline tree for members

      • Inserting dimensions, members, and aliases in a script from an outline tree

      • Inserting functions and commands in a script from a tree

      • Using syntax autocompletion

      • Checking script syntax

      • Executing scripts

      • Viewing color-coded script elements

      • Searching for text in a script

      • Changing fonts

    3. Validate the calculation script.

      See Checking Syntax and “Checking Script Syntax” in the Oracle Essbase Administration Services Online Help.

    4. Save the calculation script.

      See Saving Calculation Scripts and “Saving Scripts” in the Oracle Essbase Administration Services Online Help.

    5. Execute the calculation script.

      See Executing Calculation Scripts, Checking the Results of Calculations, and “Executing Calculation Scripts” in the Oracle Essbase Administration Services Online Help.

    6. If necessary, perform other operations on the calculation script.

      In the Oracle Essbase Administration Services Online Help, see the following topics:

      • “Locking and Unlocking Objects”

      • “Copying Scripts”

      • “Renaming Scripts”

      • “Deleting Scripts”

      • “Printing Scripts”

    Checking Syntax

    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

    *  To check the syntax of a calculation script in Calculation Script Editor, see “Checking Script Syntax” in the Oracle Essbase Administration Services Online Help.

      Note:

      The syntax checker cannot determine semantic errors, which occur when a calculation script does not work as you expect. To find semantic errors, run the calculation and check the results to ensure they are as you expect.

      Saving Calculation Scripts

      You can save a calculation script in the following locations:

      • As a file on a client computer.

      • As an artifact on an Essbase Server, which allows other users to access the calculation script. You can associate the script with the following artifacts:

        • An application and all the databases within the application, which lets you run the script against any database in the application.

          Calculation scripts associated with an application are saved in the ARBORPATH/app/appname directory on the Essbase Server computer.

        • A database, which lets you run the script against the specified database.

          Calculation scripts associated with a database are saved in the ARBORPATH/app/appname/dbname directory on the Essbase Server computer.

      *  To save a calculation script using Calculation Script Editor, see “Saving Scripts” in the Oracle Essbase Administration Services Online Help.

        Executing Calculation Scripts

        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.

        *  To execute a calculation script, use a tool:

        Tool

        Topic

        Location

        Administration Services

        Executing Calculation Scripts

        Oracle Essbase Administration Services Online Help

        MaxL

        execute calculation

        Oracle Essbase Technical Reference

        ESSCMD

        RUNCALC

        Oracle Essbase Technical Reference

        Spreadsheet Add-in

        ESSBASE, then CALCULATION

        Oracle Essbase Spreadsheet Add-in User's Guide

        Smart View

        Calculating Data

        Oracle Hyperion Smart View for Office Online Help

          Checking the Results of Calculations

          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:

          • Calculation order of the dimensions for each pass through the database

          • Total calculation time

          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.

          • Administration Services, Spreadsheet Add-in, and Smart View: Application log

            See Viewing the Essbase Server and Application Logs.

          • MaxL: Standard output (command-line window)

            The amount of information depends on the message level set in MaxL Shell.

          • ESSCMD: ESSCMD window or standard output (command-line window)

            The amount of information depends on the message level set in ESSCMD.

          Copying Calculation Scripts

          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.

          *  To copy a calculation script, use a tool:

          Tool

          Topic

          Location

          Administration Services

          Copying Scripts

          Oracle Essbase Administration Services Online Help

          MaxL

          create calculation as

          Oracle Essbase Technical Reference

          ESSCMD

          COPYOBJECT

          Oracle Essbase Technical Reference