Developing Calculation Scripts for Block Storage Databases

In This Section:

Using Calculation Scripts

Process for Creating Calculation Scripts

Understanding Calculation Script Syntax

Using Calculation Commands

Using Formulas in Calculation Scripts

Using a Calculation Script to Control Intelligent Calculation

Grouping Formulas and Calculations

Using Substitution and Environment Variables in Calculation Scripts

Clearing and Copying Data

Calculating a Subset of a Database

Exporting Data Using the DATAEXPORT Command

Enabling Calculations on Potential Blocks

Using Calculation Scripts on Partitions

Saving, Executing, and Copying Calculations Scripts

Checking Calculation Results

Estimating Disk Size for a Calculation

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

Also see:

All of the examples in this chapter are based on the Sample.Basic database.

For more information about the calculation commands referenced in this chapter, see the Oracle Essbase Technical Reference.

Using 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 perform 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 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 user interface features of the script editor to build the script

  • Creating the script in a 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.

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

    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.

  4. Save the calculation script.

    See Saving Calculation Scripts.

  5. Execute the calculation script.

    See Executing Calculation Scripts.

  6. Check the results of the calculation script.

    See Checking Calculation Results.

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

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.

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

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

    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, Oracle recommends ending 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 validate the syntax. See Checking Syntax.

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

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. See Checking Calculation Results.

Using Calculation Commands

The topics in this section discuss calculation commands, grouped by functionality. See:

Calculating the Database Outline

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

Table 81. List of Commands for Calculating a Database

Command

Calculation

CALC ALL

The entire database, based on the outline

CALC DIM

A specified dimension or dimensions

CALC TWOPASS

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

membername

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

CALC AVERAGE

All members tagged as Average on the dimension tagged as accounts

CALC FIRST

All members tagged as First on the dimension tagged as accounts

CALC LAST

All members tagged as Last on the dimension tagged as accounts

CCONV

Currency conversions

Controlling the Flow of Calculations

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

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

Command

Calculation

FIX…ENDFIX

Calculate a subset of a database by inclusion

EXCLUDE…ENDEXCLUDE

Calculate a subset of a database by exclusion

LOOP…ENDLOOP

Specify the number of times that commands are iterated

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

Note:

Essbase does not allow branching from one calculation script to another calculation script.

Declaring Data Variables

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.

Table 83. List of Commands for Declaring Data Variables

Command

Calculation

ARRAY

Declare one-dimensional array variables

VAR

Declare a temporary variable that contains a single value

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

Table 84 lists the commands that define calculation behavior:

Table 84. List of Commands for Defining Calculation Behavior

Command

Calculation

SET AGGMISSG

Specify how Essbase treats #MISSING values during a calculation

SET CACHE

Adjust the default calculator cache size

SET CALCPARALLEL

Enable parallel calculation (see Using Parallel Calculation)

SET CALCTASKDIMS

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

SET FRMLBOTTOMUP

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

SET MSG

SET NOTICE

Display messages to trace a calculation

SET UPDATECALC

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

SET CLEARUPDATESTATUS

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

SET LOCKBLOCK

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

SET CREATEBLOCKEQ

Turn on and turn off the Create Blocks on Equation setting, which controls the 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 CREATENONMISSINGBLK

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

SET UPTOLOCAL

For currency conversions, restrict consolidations to parents that have the same defined currency (see Calculating Databases)

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.

Using Formulas in Calculation Scripts

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. The formula overrides any formula placed on the Expenses member in the database outline:

Expenses = Payroll + Marketing + Misc;

Note:

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

See:

Also see Developing Formulas for Block Storage Databases.

Basic Equations

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;

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:

Calculating a Series of Member Formulas

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;

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 and Environment Variables in Calculation Scripts

Substitution variables are used to reference information that changes frequently; environment variables are used as placeholders for user-specific system settings. See:

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.

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. For example:

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

Also see Using Substitution Variables.

Using Environment Variables in Calculation Scripts and Formulas

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 applications and 320 characters for 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 because 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 applications and 256 characters for 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 (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" "";

Clearing and Copying Data

You can clear a subset of data from a database and copy data values from one set of members to another set of members. See:

Clearing Data

Table 85 lists the commands that clear data:

Table 85. List of Commands for Clearing Data

Command

Calculation

CLEARDATA

Change the values of the cells you specify to #MISSING; the data blocks are not removed.

Use the FIX command with the CLEARDATA command to clear a subset of a database.

CLEARBLOCK

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 UPPER

Remove consolidated level blocks.

CLEARBLOCK NONINPUT

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.

CLEARBLOCK DYNAMIC

Remove blocks for Dynamic Calc and Store member combinations.

See Dynamically Calculating Data Values.

CLEARBLOCK EMPTY

Remove empty blocks.

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

FIX(Actual)
   CLEARBLOCK NONINPUT;
ENDFIX
  • Dense: The command removes all data cells that do not contain input data values and that intersect with the member Actual from the Scenario dimension.

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.

Copying Data

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.

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

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

Using the FIX Command

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.

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 restrictions. See the Oracle Essbase Technical Reference.

Exporting Data Using the DATAEXPORT Command

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;

The following tables list the SET DATAEXPORTOPTIONS commands, which are all optional:

Table 86. SET DATAEXPORTOPTIONS Commands: Content Options

CommandCalculation
DATAEXPORTLEVELSpecify all, level 0, or input data value
DATAEXPORTDYNAMICCALCControl export of dynamically calculated values
DATAEXPORTNONEXISTINGBLOCKSSpecify whether to export data from all potential data blocks or only from existing data blocks
DATAEXPORTDECIMALSpecify the number of decimal positions in the exported values
DATAEXPORTPRECISIONSpecify the total number of positions in the exported values

Table 87. SET DATAEXPORTOPTIONS Commands: Output Format Options

CommandCalculation
DATAEXPORTCOLFORMATSpecify columnar or noncolumnar format
DATAEXPORTCOLHEADERSpecify a dense dimension for the column header
DATAEXPORTDIMHEADERInclude a header record that lists all dimension names in the same order as the data in the file
DATAEXPORTRELATIONALFILEFormat the text export file for importing the data into a relational database

Table 88. SET DATAEXPORTOPTIONS Commands: Processing Options

CommandCalculation
DATAEXPORTOVERWRITEFILESpecify whether an existing file with the same name and location is replaced
DATAEXPORTDRYRUNEnable validating the set of calculation commands and viewing export statistics—including a time estimate—without having to perform the entire export process

  To develop a calculation script that exports a subset of data:

  1. Specify the SET DATAEXPORTOPTIONS command to define options for export content (see Table 86, SET DATAEXPORTOPTIONS Commands: Content Options), format (see Table 87, SET DATAEXPORTOPTIONS Commands: Output Format Options), and process (see Table 88, SET DATAEXPORTOPTIONS Commands: Processing Options).

  2. Use a DATAEXPORTCOND command to select data based on data values.

  3. Use FIX...ENDFIX or EXCLUDE...ENDEXCLUDE calculation commands to select a slice of the database to be exported.

  4. Within the FIX...ENDFIX or EXCLUDE...ENDEXCLUDE group, include the DATAEXPORT command.

    If you are using the DATAEXPORT command to insert the exported data directly into a relational database, see Exporting Data into a Relational Database.

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

Exporting Data into a Relational Database

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.

Note:

64-bit Essbase does not support using the DATAEXPORT batch-insert method to export data directly into a SQL data source.

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.

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

Advantages and Disadvantages of Exporting Data Using a Calculation Script

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 (DATAEXPORTDYNAMICCALC) 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 (DATAEXPORTDYNAMICCALC) is used, unless DATAEXPORTNONEXISTINGBLOCKS is set 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, 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

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:

  • 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 the 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 SET CREATEBLOCKONEQ ON 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.

Using Calculation Scripts on Partitions

See:

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.

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

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

Saving, Executing, and Copying Calculations Scripts

See:

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.

See Using Essbase to Manage Artifacts.

  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 User's Guide Help

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

Checking Calculation Results

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.

Estimating Disk Size for a Calculation

You can estimate the disk size required for a single CALC ALL for a full data load or a partial data load by using the ESTIMATEFULLDBSIZE configuration setting.

See Estimating Calculation Affects on Database Size.