Developing Calculation Scripts for Block Storage Databases

In This Section:

Using 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, Runtime 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.

  • 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

If you run a calculation script from Smart View, 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.

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.

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

Using Calculation Commands

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

Calculating the Database Outline

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

Table 78. 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 79 lists the commands that manipulate the flow of calculations:

Table 79. 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

FIXPARALLEL...ENDFIXPARALLEL

Enable parallel calculation on a block of commands

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

THREADPARVAR and TASKPARVAR

Declare a temporary variable that can be used within a FIXPARALLEL block.

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 81 lists the commands that define calculation behavior:

Table 81. 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 Enabling CALCPARALLEL Parallel Calculation.

SET CALCTASKDIMS

Increase the number of dimensions used to identify tasks for parallel calculation. See Identifying Additional Tasks for Parallel Calculation.

SET CLEARUPDATESTATUS

Control how Essbase marks data blocks for Intelligent Calculation. See Using the SET CLEARUPDATESTATUS Command.

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 FRMLBOTTOMUP

Optimize the calculation of sparse dimension formulas in large database outlines. See Optimizing Formulas on Sparse Dimensions in Large Database Outlines.

SET LOCKBLOCK

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

SET MSG

SET NOTICE

Display messages to trace a calculation.

SET RUNTIMESUBVARS

Declare runtime substitution variables that are used in a calculation script. See Using Runtime Substitution Variables in Calculation Scripts Run in Essbase and Using Runtime Substitution Variables in Calculation Scripts Run in Smart View

SET UPDATECALC

Turn on and turn off Intelligent Calculation. See Turning Intelligent Calculation On and Off.

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

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

For general information on substitution variables, see Using Substitution Variables.

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 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 Runtime Substitution Variables in Calculation Scripts Run in Essbase.

The information in this topic applies to running a calculation script with runtime substitution variables in Essbase. Also see Using Runtime Substitution Variables in Calculation Scripts Run in Smart View.

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

Runtime substitution variables are different from substitution variables in that every runtime substitution variable used in a calculation script must be declared in the SET RUNTIMESUBVARS calculation command, with a name and default value.

Note:

If a default value is not included in the runtime substitution variable declaration in SET RUNTIMESUBVARS, an error occurs when the calculation script is validated. Oracle recommends that you provide a default value to avoid the validation error and, when running the calculation script, provide the expected value. However, if you do not provide a default value, you can still provide a value at runtime using the execute calculation MaxL statement with the with runtimesubvars grammar.

A description of the runtime substitution variable's data type and data input limit is a string in the <RTSV_HINT>rtsv_description</RTSV_HINT> tag. This tag is optional when the calculation script with runtime substitution variables is run in Essbase; see Specifying Data Type and Input Limit for Runtime Substitution Variables in Calculation Scripts Run in Essbase. The <RTSV_HINT> tag, with additional metadata, is required when the calculation script with runtime substitution variables is run in Smart View. See Using Runtime Substitution Variables in Calculation Scripts Run in Smart View.

In this example of SET RUNTIMESUBVARS, three runtime substitution variables are declared: myMarket, salesNum, and PointD. Default values are specified for each runtime substitution variable (for example, the value of myMarket is “New York”). This example applies to a calculation script that is run in Essbase:

SET RUNTIMESUBVARS
{
   myMarket = "New York";
   salesNum = 10;
   pointD = "Actual"->"Final";
};

At runtime, the default values that are specified in the SET RUNTIMESUBVARS command can be overwritten, using one of these methods:

  • execute calculation MaxL statement with the with runtimesubvars grammar, in which runtime substitution variables are specified as a string of key/value pairs.

    Using the SET RUNTIMESUBVARS example above, at runtime you can overwrite the salesNum default value of 10 with 500 by using the following MaxL statement:

    execute calculation appName.dbName.calcScriptName with runtimesubvars 'salesNum=500';

    Using this MaxL statement also allows you to provide values for runtime substitution variables that do not have a default value in the SET RUNTIMESUBVARS declaration.

  • An API call in which runtime substitution variables are specified as a string of key/value pairs: IEssCube.calcFileWithRunTimeSubVars (Java API) or EssCalcWithRuntimeSubVars (C API)

  • An API call in which runtime substitution variables can be specified in a text file on the client computer or as a string of key/value pairs: IEssCube.calcFileWithRunTimeSubVarFile (Java API) or EssCalcFileWithRuntimeSubVars (C API)

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

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

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

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

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

  1. Values specified through the execute calculation MaxL statement with the with runtimesubvars grammar, or the APIs (IEssCube.calcFileWithRunTimeSubVars or IEssCube.calcFileWithRunTimeSubVarFile Java APIs; EssCalcWithRuntimeSubVars or EssCalcFileWithRuntimeSubVars C APIs).

  2. Default values specified in the SET RUNTIMESUBVARS calculation command.

Consider these guidelines when using runtime substitution variables:

  • If you declare a runtime substitution variable in SET RUNTIMESUBVARS but do not use the runtime substitution variable in the calculation script, Essbase ignores the unused runtime substitution variable declaration (no warning or exception is generated).

  • Runtime substitution variables have a higher precedence than substitution variables. Therefore, if a substitution variable and a runtime substitution variable have the same name (for example, myProduct), the value of the runtime substitution variable overwrites the value of the substitution variable.

  • If multiple runtime substitution variables have the same name but have different values, only the value of the first instance of the runtime substitution variable is used; all other subsequent values are ignored.

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

Specifying Data Type and Input Limit for Runtime Substitution Variables in Calculation Scripts Run in Essbase

The information in this topic applies to running a calculation script with runtime substitution variables in Essbase. Also see Using Runtime Substitution Variables in Calculation Scripts Run in Smart View.

In the SET RUNTIMESUBVARS calculation command, the runtime substitution variable declaration can include the <RTSV_HINT>rtsv_description</RTSV_HINT> tag, in which rtsv_description is a string that describes the data type and data input limit (for example, an integer not greater than 100) for the runtime substitution variable. The rtsv_description string is not used in the calculation.

The IEssIterator.getCalcFileRunTimeSubVars or IEssIterator.getCalcRunTimeSubVars Java API methods or EssGetRuntimeSubVars C API retrieves all of the information (name, default value, and description) that is specified in the runtime substitution variable declaration in SET RUNTIMESUBVARS. The rtsv_description string can then be used to prompt a user to input a value at runtime or to validate input data before passing the value to the calculation script.

In this example of SET RUNTIMESUBVARS, each declaration specifies the name, default value, and description of the runtime substitution variable:

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

Logging Runtime Substitution Variables

To log runtime substitution variables that are used in a calculation script, set the ENABLERTSVLOGGING configuration setting in the essbase.cfg file to TRUE. Logging can be implemented at the Essbase Server, application, or database level.

Runtime substitution variable log entries are written to the application log file. Essbase writes one entry to the application log for each string of key/value pairs (or a list of key/value pairs specified in an .rsv file when using the IEssCube.calcFileWithRunTimeSubVarFile Java API or EssCalcFileWithRuntimeSubVars C API).

In the following example, two runtime substitution variables (Entity and Currency) and their values are logged in one entry:

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

The information in this topic applies to running a calculation script with runtime substitution variables in Smart View. Also see Using Runtime Substitution Variables in Calculation Scripts Run in Essbase.

Calculation scripts that are launched in Smart View can include runtime substitution variables.

To use a calculation script that includes runtime substitution variables in Smart View, the runtime substitution variable declaration in the SET RUNTIMESUBVARS calculation command must include the <RTSV_HINT> tag. Additionally, the <RTSV_HINT> tag must include the <svLaunch> tag. Typically, the <svLaunch> tag includes additional XML tags that provide metadata for executing the calculation script in Smart View.

Syntax of a runtime substitution variable definition for use in Smart View:

SET RUNTIMESUBVARS
{
   rtsv = POV
   <RTSV_HINT>
      <svLaunch>
         <description>rtsv_description</description>
         <type>member | string | number</type>
         <dimension>dimName</dimension>
         <choice>single | multiple</choice>
         <allowMissing>true | false</allowMissing>
      </svLaunch>
   </RTSV_HINT>
};

The runtime substitution variable value must be set to the POV so that only a visible slice of data in the database is calculated.

In Smart View, when you select a calculation script that includes runtime substitution variables on the Calculation Scripts dialog box, the Runtime Prompts area is populated with fields based on how the runtime substitution variables are defined in the SET RUNTIMESUBVARS calculation command. You can run the calculation script as defined or you can use the runtime prompts to enter different variable information based on the data type.

Note:

In Smart View, you cannot run a calculation script that includes runtime substitution variables if the SET RUNTIMESUBVARS command does not include the <RTSV_HINT><svLaunch>...</svLaunch></RTSV_HINT> tags.

Example: Runtime Substitution Variable Set to POV

To set a runtime substitution variable to the POV, the value of the runtime substitution variable must be set to “POV” and the data type must be set to member.

By default, the calculation script uses the active member in the POV at runtime unless the Smart View user specifies a different member in the runtime prompt.

In this example, assume that the database has these dimensions: Account, Entity, Period and Scenario. Three runtime substitution variables are defined (named Entity, Scenario, and Period) and the value for each variable is set to POV. The runtime substitution variables are referenced in the FIX statement as &Entity, &Scenario, and &Period.

SET RUNTIMESUBVARS {
   Entity = POV
      <RTSV_HINT>
         <svLaunch>
            <description>Entities to Copy</description>
            <type>member</type>
            <dimension>Entity</dimension>
            <allowMissing>false</allowMissing>
            <choice>multiple</choice>
         </svLaunch>
      </RTSV_HINT>;
   Scenario = POV
      <RTSV_HINT>
         <svLaunch>
            <description>Scenarios to Copy</description>
            <type>member</type> 
            <allowMissing>false</allowMissing>
            <dimension>Scenario</dimension>
            <choice>multiple</choice>
         </svLaunch>
      </RTSV_HINT>;
   Period   = POV
      <RTSV_HINT>
         <svLaunch>
            <description>Period to Copy</description>
            <type>member</type>
            <allowMissing>false</allowMissing>
            <dimension>Period</dimension>
            <choice>single</choice>
         </svLaunch>
      </RTSV_HINT>;
}

FIX(&Entity, &Scenario, &Period)
“Opening Balance” (
@PREV(“Closing Balance”);
)

Note:

In Essbase, you cannot run a calculation script that includes a runtime substitution variable that is set to POV.

XML Tag Reference—Calculation Scripts with Runtime Substitution Variables for Smart View

Runtime substitution variables for Smart View XML tags defined:

  • <RTSV_HINT>

    Required tag for defining runtime substitution variables for use in Smart View.

  • <svLaunch>

    Required tag that indicates that the runtime substitution variable is defined for use in Smart View.

    This tag is the parent tag for these tags: <description>, <type>, <dimension>, <choice>, and <allowMissing>.

  • <description>rtsv_description</description>

    The rtsv_description is a string that describes the runtime substitution variable. The string is not used in the calculation.

  • <type>value</type>

    Valid data type values are:

    • member—The runtime substitution variable value must be defined as the POV.

      Note:

      If the runtime substitution variable value is set to POV, the <type> value must be member. Also see the <choice> tag.

      In Essbase, you cannot run a calculation script that includes a runtime substitution variable that is set to the POV.

    • string—The runtime substitution variable value can be defined as a single member name or a comma separated list of member names (for example, "New York","Florida"), or a date.

      When using the string data type, the Member Selection dialog box is not available; therefore, the Smart View user must be sure to use the correct syntax (enclosing a member name in quotes, separating multiple member names with a comma, or, for a date, matching the format of the date string to the format that is defined in the calculation script—mm-dd-yyyy or dd-mm-yyyy).

    • number—The runtime substitution variable value must be defined as a number

  • <dimension>dimName</dimension>

    Name of the dimension.

    Note:

    This XML tag is supported only if the <type> value is member.

  • <choice>value</choice>

    Valid choice values are:

    • single:

      • If there is a single member on the grid or POV, that member is used.

      • If a dimension is on the POV, the active member is used.

      • If a dimension is on the POV and there are multiple members, an error occurs.

    • multiple—All dimension members on the grid or the POV are used.

    Note:

    This XML tag is supported only if the <type> value is member.

  • <allowMissing>boolean</allowMissing>

    Specifies whether to allow or suppress data cells for which no data exists in the database.

    Valid values: true and false.

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.

Clearing Data

Table 82 lists the commands that clear data:

Table 82. 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;

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.

Note:

DATAEXPORT to binary files is not supported across Essbase releases, and is only supported between 64-bit operating systems.

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 83. SET DATAEXPORTOPTIONS Commands: Content Options

CommandCalculation
DATAEXPORTLEVEL

Specify a data value: ALL, LEVEL0, or INPUT

In specifying the data value for the DataExportLevel option, use these guidelines:

  • The values are case-insensitive. For example, you can specify LEVEL0 or level0.

  • Enclosing the value in quotation marks is optional. For example, you can specify LEVEL0 or “LEVEL0”.

  • If the value is not specified, Essbase uses the default value of ALL.

  • If the value is incorrectly expressed (for example, LEVEL 0 or LEVEL2), Essbase uses the default value of ALL.

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 84. 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 85. 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 83, SET DATAEXPORTOPTIONS Commands: Content Options), format (see Table 84, SET DATAEXPORTOPTIONS Commands: Output Format Options), and process (see Table 85, 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.

    Note:

    DATAIMPORTBIN is not supported across Essbase releases, and is only supported between 64-bit operating systems.

    The SET DATAIMPORTIGNORETIMESTAMP calculation command enables you to manage the import requirement for a matching outline timestamp.

Other export methods include using MaxL and ESSCMD for database backup.

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.

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 127, Calculating Partitions illustrates partitions in which you view information from the West, Central, and East databases transparently from the Corporate database:

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

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.

Executing Calculation Scripts

  To execute a calculation script, use a tool:

Tool

Topic

Location

MaxL

execute calculation

Oracle Essbase Technical Reference

ESSCMD

RUNCALC

Oracle Essbase Technical Reference

Smart View

Calculating Data

Oracle Smart View for Office User's Guide

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

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.

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.

  • 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 Effects on Database Size.