Calculation Commands


&
AGG
ARRAY
CALC ALL
CALC AVERAGE
CALC DIM
CALC FIRST
CALC LAST
CALC TWOPASS
CCONV
CLEARBLOCK
CLEARDATA
DATACOPY
ELSE
ELSEIF
ENDIF
FIX..ENDFIX
IF
LOOP..ENDLOOP
SET Commands
SET AGGMISSG
SET CACHE
SET CALCHASHTBL
SET CALCPARALLEL
SET CALCTASKDIMS
SET CLEARUPDATESTATUS
SET FRMLBOTTOMUP
SET LOCKBLOCK
SET MSG
SET NOTICE
SET UPDATECALC
SET UPTOLOCAL
VAR

&

The & command prefaces a substitution variable in a calculation script.

Syntax

&variableName;

variableName The name of the substitution variable set on the database.

Description

Hyperion Essbase treats any string that begins with a leading & as a substitution variable and substitutes these variables with their values before it parses the calculation script.

Example

&CurQtr;

becomes

Qtr1;

if the substitution variable &CurQtr has the value "Qtr1".


AGG

The AGG command consolidates database values. An AGG command ignores all member formulas; it simply consolidates parent/child relationships.

Syntax

AGG (dimList);

dimList Name of a single dimension or a comma-delimited list of dimensions.

Description

The AGG command is used for a limited set of high-speed consolidations. Although AGG is faster than any of the CALC commands when calculating sparse dimensions, it cannot calculate formulas; it can only perform aggregations based on the database structure. The AGG command aggregates a list of sparse dimensions. The aggregation is based on the hierarchical relationship defined in the database outline. If a member has a customized calculation defined in the outline, the formula is ignored and the result does not match the relationship defined by the database outline.

If you want to aggregate a dimension that contains formulas:

  1. Calculate any members that are "leaf" members (that is, level 0).
  2. Aggregate the dimension, using the AGG command.
  3. Calculate all other members with formulas that have not been calculated yet.

Notes

Example

AGG(Market);
AGG(Product,Market,Scenario);

See Also

CALC ALL
CALC DIM
SET AGGMISSG


ARRAY

The ARRAY command declares one-dimensional array variables.

Syntax

ARRAY arrayVariableName [dimName] = { constList};

arrayVariableName Comma-delimited list of one or more array variable names.
dimName Dimension whose size determines the size of the array variable. You must surround the dimName with brackets [ ] when using the ARRAY command.
constList Optional list of data values used to initialize the array variable(s). If no initialization is performed, then the array variables are set to #MISSING. The order of the values corresponds to the order of the members in the dimension used to define the array.

Description

This command declares one-dimensional array variables. Typically, arrays are used to temporarily store variables as part of a member formula. The variables are contained within the calc script and cease to exist after the calculation script ends. The size of the array variable is determined by the corresponding dimension (e.g., if dimension Period has 12 members, ARRAY Discount[Period] has 12 members). More than one array can be created at the same time. To do so, simply separate the different array declarations with commas within the ARRAY command, as shown in the example that follows.

You can calculate data for an array directly as part of a member formula. As the member formula is processed, each value in the array is assigned as its member is evaluated in the calculation.

Example

ARRAY discount[Scenario];

Which yields an array of 4 entries, with the values 1 through 4 entered in those four entries.

ARRAY discount[Scenario] = {1, 2, 3, 4};
ARRAY discount[Scenario], tmpProduct[Product];

Which yields two arrays, one of 4 members (corresponding to the dimension Scenario, and called discount) and one of 9 members (corresponding to dimension Product, and called tmpProduct).

See Also

VAR


CALC ALL

The CALC ALL command calculates and aggregates the entire database based on the database outline

.

Syntax

CALC ALL [EXCEPT DIM (dimList) | MBR (mbrList)];

EXCEPT Defines an exception list of dimensions or members that are to be excluded from the calculation.
DIM Single-dimension specification.
dimList Optional comma-delimited list of dimensions.
MBR Single-member specification.
mbrList Optional comma-delimited list of members, member set functions, or range functions.

Description

This command calculates and aggregates the entire database based on the database outline. The order in which dimensions are processed depends on the definitions of the various dimension in the database outline. Refer to the Database Administrator's Guide for more information.

Example

CALC ALL;
CALC ALL EXCEPT DIM(Product);

See Also

CALC DIM
SET UPDATECALC
SET CALCHASHTBL
SET FRMLBOTTOMUP


CALC AVERAGE

The CALC AVERAGE command calculates all members tagged in the database outline as time balance Average or Average Non-Missing. All other member calculations are ignored during this process.

Note: This command calculates the members based on the dimension tagged as accounts only; it does not do a Time Series calculation on the dimension tagged as time.

Syntax

CALC AVERAGE;

Example

CALC AVERAGE;

See Also

CALC FIRST
CALC LAST


CALC DIM

The CALC DIM command calculates the formulas and aggregations associated with each member of all the specified dimensions.

Syntax

CALC DIM (dimList);

dimList Dimension or comma-delimited list of dimensions to be calculated.

Description

This command calculates the formulas and aggregations associated with each member of all of the specified dimensions. The order in which dimensions are calculated depends on whether they are dense or sparse. All of the dense dimensions are calculated first, in the order that the dense dimensions appear in dimList. The sparse dimensions are then calculated in a similar order.

Example

CALC DIM(Accounts);

CALC DIM(Dense1,Sparse1,Sparse2,Dense2);

In this last example, the calculation order would be: Dense1, Dense2, Sparse1, Sparse2. If your dimensions need to be calculated in a particular order, use separate CALC DIM commands, as in:

CALC DIM(Dense1);
CALC DIM(Sparse1);
CALC DIM(Sparse2);
CALC DIM(Dense2);

See Also

CALC ALL
SET UPDATECALC
SET CLEARUPDATESTATUS


CALC FIRST

The CALC FIRST command calculates all members tagged in the database outline as time balance First.

Syntax

CALC FIRST;

Description

This command calculates all members tagged in the database outline as time balance First. All other member calculations are ignored during this process. When this command is used, it calculates all members tagged with First.

Note: This command calculates the members based on the dimension tagged as Accounts only; it does not do a Time Series calculation on the dimension tagged as Time.

Example

CALC FIRST;

See Also

CALC AVERAGE
CALC LAST


CALC LAST

The CALC LAST command calculates all members tagged in the database outline as time balance Last.

Syntax

CALC LAST;

Description

This command calculates all members tagged in the database outline as time balance Last. All other member calculations are ignored during this process. When this command is used, it calculates all members tagged with Last.

Note: This command calculates the members based on the dimension tagged as Accounts only; it does not do a Time Series calculation on the dimension tagged as Time.

Example

CALC LAST;

See Also

CALC AVERAGE
CALC FIRST


CALC TWOPASS

The CALC TWOPASS command calculates all members tagged in the database outline as two-pass. These members must be on a dimension tagged as accounts.

Syntax

CALC TWOPASS;

Description

This command calculates all members tagged in the database outline as two-pass. These members must be on a dimension tagged as accounts. The database outline member formula is applied at each consolidated level of the database. All other member calculations are ignored during this process.

Example

CALC TWOPASS;


CCONV

CCONV calculates currency conversions. This command is available only if your company has purchased the optional Hyperion Essbase Currency Conversion product.

Syntax

CCONV currExchMbr | TOLOCALRATE curType;

currExchMbr Currency name containing the required exchange rate. This is a member from the currency database.
TOLOCALRATE Converts a converted currency back to the original, local rate.
curType Currency type. This is a member from the CurType dimension in the currency database.

Description

You convert data values from a local to a common, converted currency using the CCONV currExchMbr command. For example, you might convert data from a variety of European currencies into US$. You can then convert the data values back to the original, local currency using the CCONV TOLOCALRATE curType command.

You can convert all or part of your main database using the rates defined in your currency database. You can keep both the local and converted values in your main database, or you can overwrite the local values with the converted values.

If you want to overwrite local values with converted values:

You do not need to create a CURPARTITION dimension in your main database. Use the CCONV command in a calculation script to convert all the data in your database.

Note: You cannot use the FIX command if the CCTRACK setting is set to TRUE (the default) in the essbase.cfg file and you are not using a CURPARTITION dimension.

If you want to keep both local and converted values:

In your main database, you need to define the members that store the local and converted values. You do this by creating a CURPARTITION dimension. The CURPARTITION dimension has two partitions, one for local values and one for converted values.

To convert data, complete the following steps:

  1. Use the DATACOPY command to copy data from the local to the converted partition.
  2. Use the FIX command to calculate only the converted partition and use the CCONV command to convert the data.
  3. Use the CALC command to recalculate your database.

To convert currencies, you need to create a currency database and define specific dimensions in your main database. For more information, see the Database Administrator's Guide.

Notes

Example

CCONV YEN;

converts all the data values from local currency values (for example, French Francs and US$) to Japanese Yen using the YEN exchange rate from the currency database.

 
CCONV TOLOCALRATE "Act xchg";

converts all the data values back to the local currencies using the Act xchg currency type from the currency database.

 
CCONV Actual->US$;

converts all the data values from local currencies to US$ using the Actual, US$ exchange rate from the currency database.

 
FIX (Act)
    CCONV TOLOCALRATE "Act xchg";
ENDFIX

converts the data in the Act currency partition back to the local currencies using the Act xchg currency type from the currency database.

 
DATACOPY Act TO Actual;
FIX (Actual)
    CCONV "Act xchg"->US$;
ENDFIX
CALC ALL;

copies Actual data values from the local currency partition to the converted currency partition. Fixes on the Actual data (in the converted partition) and converts it using the Act xchg, US$ rate from the currency database. Recalculates the database.

See Also

CCTRACK (essbase.cfg file)
SET UPTOLOCAL

For more information on converting currencies, see the Database Administrator's Guide.


CLEARBLOCK

The CLEARBLOCK command clears blocks of data and sets them to #MISSING.

Syntax

CLEARBLOCK ALL | UPPER | NONINPUT | DYNAMIC ;

ALLClears all data blocks.
UPPERClears all consolidated level blocks.
NONINPUT Clears blocks containing values derived from calculations.
DYNAMIC Clears blocks containing values derived from Dynamic Calc And Store member combinations.

Description

The CLEARBLOCK command clears some or all of a database's cells and changes the values of those cells to #MISSING values, and removes the block. This command is useful when you need to clear old data values across data blocks before loading new values into the database.

CLEARBLOCK is especially useful when you want to optimize the calculation speed of a database. When a database is initially calculated, numerous consolidated level sections (blocks) are created. Subsequent calculations against the same set of data take longer since Hyperion Essbase must pass through these additional data blocks. CLEARBLOCK clears blocks before a calculation occurs.

If you use CLEARBLOCK within a FIX command, Hyperion Essbase clears only the data cells within the fixed range, and not the entire block.

Notes

Example

CLEARBLOCK ALL;
CLEARBLOCK UPPER;
CLEARBLOCK NONINPUT;
CLEARBLOCK DYNAMIC;

See Also

CLEARDATA


CLEARDATA

The CLEARDATA command clears data values from the database and sets them to #MISSING.

Syntax

CLEARDATA mbrName;

mbrName Any valid single member name or member combination, or a function that returns a single member or member combination.

Description

The CLEARDATA command clears a well-defined section of a database's cells and changes the values of those cells to #MISSING values. This command is useful when you need to clear existing data values before loading new values into a database. CLEARDATA can only clear a section of a database. It cannot clear the entire database. To clear the entire database, select Database > Clear Data > ALL from the Application Manager main menu.

CLEARDATA does not clear blocks, even if all data values in a block are #MISSING. Use CLEARBLOCK if you wish to clear blocks from the database, which can improve performance.

Example

CLEARDATA Budget;

clears all Budget data.

 
CLEARDATA Budget->Colas;

clears only Budget data for the Colas product family.

Notes

CLEARDATA does not work if placed in an IF statement.

See Also

CLEARBLOCK


DATACOPY

The DATACOPY command copies a range of data cells to another range within the database.

Syntax

DATACOPY mbrName1 TO mbrName2;

mbrName1 and mbrName2Any valid single member name or member combination, or a function that returns a single member or member combination.

Description

The DATACOPY command copies data values from one subset of the database to another. This command is useful when you must maintain an original set of data values and perform changes on the copied data set.

DATACOPY is commonly used as part of the currency conversion process. DATACOPY is also useful when you need to define multiple iterations of plan data.

To reduce typing, if any dimension(s) represented by the members in mbrName1 are not represented in mbrName2, then by default the same member or members from mbrName1 are assumed to exist in mbrName2 to complete the range. The reverse is not true. Any dimension explicitly represented in mbrName2 MUST be represented by another member of the same dimension in mbrName1.

The ranges specified by both mbrName1 and mbrName2 must be of the same size. The same dimensions represented by the members that make up mbrName1 must also be present in mbrName2.

Notes

Example

DATACOPY Plan TO Revised_Plan;


ELSE

The ELSE command designates a conditional action to be performed in an IF statement. All actions placed after the ELSE in an IF statement are performed only if the test in the IF statement generates a value of FALSE.

Syntax

ELSE statement ; [ ...statement; ] ENDIF;

statement Those operations that are to be performed in the event that the IF test including the ELSE command produces a FALSE, or 0, result.

Notes

Example

The following example is based on the Sample Basic database. This calculation script tests to see if the current member in the Market dimension is a descendant of West or East. If so, Hyperion Essbase multiplies the value for Marketing by 1.5. If the current member is not a descendant of West or East, Hyperion Essbase multiplies the value for Marketing by 1.1.

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

See Also

ELSEIF
ENDIF
IF


ELSEIF

The ELSEIF command designates a conditional test and conditions that are performed if the preceding IF test generates a value of FALSE. For this reason, multiple ELSEIF commands are allowed following a single IF.

Syntax

ELSEIF( condition ) statement ; [ ...statement ; ]
ELSEIF | ELSE | ENDIF

condition Formula or function that returns a Boolean value of TRUE (a nonzero value) or FALSE (a zero value).
statement Those operations that are to be performed in the event that the IF test (including the ELSE command) produces a FALSE, or 0, result.

Notes

     IF (condition)
         statement;
         IF (condition)
            statement;
         ELSEIF (condition)
                statement;
         ENDIF;
      statement;
      ENDIF;

Example

The following example is based on the Sample Basic database. This calculation script tests to see if the current member in the Market dimension is a descendant of West or East. If so, Hyperion Essbase multiplies the value for Marketing by 1.5. The calculation script then tests to see if the current member is a descendant of South. If so, Hyperion Essbase multiplies the value for Marketing by .9. If the current member is not a descendant of West, East, or South, Hyperion Essbase multiplies the value for Marketing by 1.1.

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;

See Also

ELSE
ENDIF
IF


ENDIF

The ENDIF command marks the end of an IF command sequence. The ENDIF command can be used only in conjunction with IF or IF ... ELSEIF statements.

Syntax

ENDIF;

Notes

"Opening Inventory"
  (IF (@ISMBR(Budget))
        IF (@ISMBR(Jan))
        "Opening Inventory" = Jan;
        ELSE
        "Opening Inventory" = @PRIOR("Ending Inventory");
        ENDIF;
  ENDIF;)

Example

The following example is based on the Sample Basic database. This calculation script tests to see if the current member in the Market dimension is a descendant of West or East. If so, Hyperion Essbase multiplies the value for Marketing by 1.5. The calculation script then tests to see if the current member is a descendant of South. If so, Hyperion Essbase multiplies the value for Marketing by .9. If the current member is not a descendant of West, East, or South, Hyperion Essbase multiplies the value for Marketing by 1.1.

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;

See Also

ELSE
ELSEIF
IF


FIX...ENDFIX

The FIX...ENDFIX command block restricts database calculations to a subset of the database. All commands nested between the FIX and ENDFIX statements are restricted to the specified database subset.

Syntax

FIX (fixMbrs)
COMMANDS ;
ENDFIX

fixMbrs A member name or list of members from any number of database dimensions. fixMbrs can also contain:
  • AND/OR operators. Use the AND operator when all conditions must be met. Use the OR operator when one condition of several must be met.
  • Member set functions, which are used to build member lists based on other members.
COMMANDS The commands you want to be executed for the duration of the FIX.

Description

The FIX command is a block command that allows you to define a fixed range of dimensions or members to which the associated commands are restricted. The FIX command is often used to calculate a subset of the database. This is useful because it allows you to calculate separate portions of the database using different formulas, if necessary. It also allows you to calculate the sub-section much faster than you would otherwise.

The ENDFIX command ends a FIX command block. As shown in the following example, you call ENDFIX after all of the commands in the FIX command block have been called and before the next element of the calculation script.

Notes

Example

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

FIX (Budget, Jan, Feb, Mar, @DESCENDANTS(Profit))
   CALC DIM (Product, Market);
ENDFIX
 

The following example fixes on the children of East and the members on the Market dimension that have a UDA (User-Defined Attribute) of New Mkt.

FIX (@CHILDREN(East) OR @UDA(Market, "New Mkt"))
 

The following example fixes on the children of East that have a UDA (User-Defined Attribute)of New Mkt and the members on the Market dimension that have a UDA of Big Mkt.

FIX((@CHILDREN(East) AND @UDA(Market, "New Mkt")) OR @UDA(Market,"Big Mkt"))

Notes

Calculator function @RANGE and the cross-dimensional operator (->) cannot be used inside a FIX/ENDFIX statement.

See Also

LOOP...ENDLOOP


IF

The IF() command performs conditional tests within a formula. Using the IF statement, you can define a Boolean test, as well as formulas to be calculated if the test returns either a TRUE or FALSE value.

Syntax

IF( condition ) statement ; [ ...statement ; ] [ ELSEIF...statement | ELSE...statement]
ENDIF;

condition Formula or function that returns a Boolean value of TRUE (a nonzero value) or FALSE (a zero value).
statement Operations to be performed depending on the results of the test.

Notes

Example

IF(
	@ISMBR(@DESCENDANTS(Europe))
OR	@ISMBR(@DESCENDANTS(Asia))
  )
   Taxes = "Gross Margin" * "Foreign Tax Rate";
ELSE
   Taxes = "Gross Margin" * "Domestic Tax Rate";
ENDIF;

This test checks to see if the current cell includes a member that is a descendant of either the Europe or Asia members. If it does, the formula calculates the taxes for the member based on the foreign tax rate. If the current cell does not include a member from one of those groups, then the domestic tax rate is used for the tax calculation.

The next three commands are included here for convenience. These commands can be used only with the IF command.

See Also

ELSE
ELSEIF
ENDIF


LOOP...ENDLOOP

The LOOP...ENDLOOP command block specifies the number of times to iterate calculations. All commands between the LOOP and ENDLOOP statements are performed the number of times that you specify.

Syntax

LOOP (integer, [break])
COMMANDS ;
ENDLOOP

integer The integer constant that indicates the number of times to execute the commands contained in the loop block.
break Optional parameter used to break the iterative process of a loop. break must be the name of a temporary variable (VAR). Setting the value of the variable to 1 during the execution of the loop causes the loop to break at the beginning of its next iteration.
COMMANDS Those commands that you want to be executed for the duration of the LOOP.

Description

LOOP is a block command that defines a block of commands for repeated execution. As with the FIX command, you can nest LOOP statements if necessary.

The ENDLOOP command ends a LOOP command block. It terminates the LOOP block and occurs after the commands in the LOOP block, but before any other commands.

Example

In this example, the LOOP command finds a solution for Profit and Commission. This operation is done as a loop because Profit and Commission are interdependent: Profit is needed to evaluate Commission, and Commission is needed to calculate Profit. This example thus provides a model for solving simultaneous formulas.

FIX("New York",Camera,Actual,Mar)
   LOOP(30)
      Commission = Profit * .15;
      Profit = Margin - "Total Expenses" - Commission;
   ENDLOOP;
ENDFIX

See Also

FIX...ENDFIX


SET Commands

Description

SET commands in a calculation script are procedural. The first occurrence of a SET command in a calculation script stays in effect until the next occurrence of the same SET command.

Example

In the following example, Hyperion Essbase displays messages at the DETAIL level when calculating the Year dimension. However, when calculating the Measures dimension Hyperion Essbase displays messages at the SUMMARY level.

SET MSG DETAIL;CALC DIM(Year);
SET MSG SUMMARY;CALC DIM(Measures);
 

In the following example, Hyperion Essbase calculates member combinations for Qtr1 with the SET AGGMISSG setting turned on. Hyperion Essbase then does a second calculation pass through the database and calculates member combinations for East with the AGGMISSG setting turned off. For more information on calculation passes, see the Database Administrator's Guide.

SET AGGMISSG ON;Qtr1;
SET AGGMISSG OFF;East;


SET AGGMISSG

The SET AGGMISSG command specifies whether Hyperion Essbase aggregates #MISSING values in the database.

Syntax

SET AGGMISSG ON | OFF ;

Description

SET AGGMISSG specifies whether Hyperion Essbase aggregates #MISSING values in the database. The default behavior of SET AGGMISSG is determined by the global setting for the database, as described in the Database Administrator's Guide.

Notes

Example

SET AGGMISSG OFF;
CALC ALL;
CALC PERCENTS;

SET CACHE

The SET CACHE command specifies the size of the calculator cache.

Syntax

SET CACHE HIGH | DEFAULT | LOW | OFF | ALL;

HIGH, DEFAULT, and LOW Levels defining the size of the calculator cache. You set the values of HIGH, DEFAULT and LOW in the essbase.cfg file. If you do not set the value of DEFAULT in the essbase.cfg file, Hyperion Essbase uses a default value of 200,000 bytes. The maximum calculator cache size that you can specify is 200,000,000 bytes.
OFF Hyperion Essbase does not use a calculator cache.
ALL Hyperion Essbase uses a calculator cache, even when you do not calculate at least one full sparse dimension.

Description

SET CACHE specifies the size of the calculator cache.

Hyperion Essbase uses the calculator cache to create and track data blocks during calculation. Using the calculator cache significantly improves your calculation performance. The size of the performance improvement depends on the configuration of your database.

You can choose one of three levels. The size of the calculator cache at each level is defined using the CALCCACHE{HIGH | DEFAULT | LOW} settings in the essbase.cfg file.

The level you choose depends on the amount of memory your system has available and the configuration of your database. For detailed information on setting the size of your calculator cache, see the Database Administrator's Guide.

You can specify whether, by default, Hyperion Essbase uses a calculator cache using the CALCCACHE TRUE | FALSE setting in the essbase.cfg file. By default CALCCACHE is set to TRUE.

Hyperion Essbase uses the calculator cache providing that:

You can use this command more than once within a calculation script.

You can display the calculator cache setting using the SET MSG command.

Notes

Example

If the essbase.cfg file contains the following settings:

CALCCACHEHIGH  1000000
CALCCACHEDEFAULT  300000
CALCCACHELOW  200000

then:

 
SET CACHE HIGH;

sets a calculator cache of up to 1,000,000 bytes for the duration of the calculation script.

 
SET CACHE DEFAULT;

sets a calculator cache of up to 300,000 bytes for the duration of the calculation script.

 
SET CACHE LOW;

sets a calculator cache of up to 200,000 bytes for the duration of the calculation script.

 
SET CACHE ALL;
SET CACHE LOW;

sets a calculator cache of 200,000 bytes to be used even when you do not calculate at least one, full sparse dimension.

 
SET CACHE OFF;

means that Hyperion Essbase does not use a calculator cache.

See Also

CALCCACHE TRUE | FALSE (essbase.cfg file setting)
CALCCACHEHIGH | DEFAULT | LOW (essbase.cfg file setting)
SET MSG (calculation script)


SET CALCHASHTBL

The SET CALCHASHTBL command optimizes the calculation of large, flat database outlines.

Syntax

SET CALCHASHTBL ON|OFF;

ON Turns on optimization of large, flat database outlines.
OFF Turns off optimization of large, flat database outlines. The default setting is OFF. You can change this setting using CALCOPTCALCHASHTBL=TRUE in the essbase.cfg file.

Description

When you enable this feature, Hyperion Essbase uses a hash table to optimize use of the calculator cache for large, flat databases. A large flat database is, for example, a database in which one or members has over 5000 children. Using this feature may significantly improve the performance of a CALC ALL of the database or CALC DIM of the dimension containing the member with over 5000 children. You can set the limit of the hash table using the CALCHASHTBLMEMORY setting in the essbase.cfg file. For more information on the calculator cache, see the Database Administrator's Guide.

This feature has no effect if you are using parallel calculation. For more information about parallel calculation, see the Database Administrator's Guide.

Example

SET CALCHASHTBL ON;

See Also

CALCOPTCALCHASHTBL (essbase.cfg file setting)
CALCHASHTBLMEMORY (essbase.cfg file setting)



SET CALCPARALLEL

SET CALCPARALLEL enables parallel calculation in place of the default serial calculation.

Syntax

SET CALCPARALLEL [appname [dbname]] n;

appname An optional parameter specifying that parallel calculation applies to all databases on the named application. If you specify a value for appname and do not specify a value for dbname, the setting applies to all databases in the specified application. If you do not specify an application, you cannot specify a database, and the setting applies to all applications and databases on the Essbase OLAP Server.
dbname An optional parameter specifying that parallel calculation applies only to the database named. If you specify a value for dbname but do not include appname, the parameter is ignored and parallel calculation is enabled for all applications and databases on the OLAP Server.
n A required parameter, an integer from 0-4, specifying the number of threads to be made available for parallel calculation. The default value, 0, specifies serial calculation: no parallel calculation takes place. Values 1-4 specifies parallel calculation with 1-4 threads. Values less than 0 return an error. Values greater than 4 are interpreted as 4.

Note: Values less than 0 treated differently than CALCPARALLEL configuration setting.

Description

SET CALCPARALLEL enables Essbase to use parallel calculation. Essbase analyzes each pass of a calculation to determine whether parallel calculation is possible. If it is not, Essbase uses serial calculation even if CALCPARALLEL is set.

Example

SET CALCPARALLEL 3;

This example enables up to three threads to be used to perform calculation tasks at the same time.

Notes

See Also

SET CALCTASKDIMS
CALCTASKDIMS configuration setting
CALCPARALLEL configuration setting


SET CALCTASKDIMS

SET CALCTASKDIMS specifies the number of sparse dimensions included in the identification of tasks for parallel calculation.

Syntax

SET CALCTASKDIMS [appname [dbname]] n;

appname An optional parameter specifying that SET CALCTASKDIMS applies to all databases on the named application. If you specify a value for appname and do not specify a value for dbname, the setting applies to all databases in the specified application. If you do not specify an application, you cannot specify a database, and the setting applies to all applications and databases on the Essbase OLAP Server.
dbname An optional parameter specifying that SET CALCTASKDIMS applies only to the database named. If you specify a value for dbname but do not include appname, the parameter is ignored, and parallel calculation is enabled for all applications and databases on the OLAP Server.
n A required parameter, an integer specifying the number of sparse dimensions to be included when Essbase identifies tasks that can be performed at the same time.

The default value, 1, indicates that only the last sparse dimension in the outline will be used to identify tasks. A value of 2, for example, indicates that the last and second-to-last sparse dimensions in the outline are used.

Because each unique combination of members from the selected sparse dimensions is a potential task, the potential number of parallel tasks is the product of the number of members of the selected dimensions. The maximum value is the number of sparse dimensions in the outline.

Essbase issues an error if the value is less than 1. A value greater than the number of sparse dimensions in the outline is interpreted as the largest valid value.

Using the calculator bitmap cache can affect this value. See the Essbse Administrators Guide discussion of parallel calculation for more information.

Note: Values less than 0 treated differently than CALCTASKDIMS configuration setting.

Description

SET CALCTASKDIMS specifies how many of the sparse dimensions in an outline are used to identify potential tasks that can be run in parallel.

Example

SET CALCTASKDIMS Sample Basic 2;

This example specifies that for application Sample and database Basic the last two sparse dimensions in the outline will be used to identify potential tasks to be performed at the same time during a calculation pass.

Notes

See Also

SET CALCPARALLEL
CALCTASKDIMS configuration setting
CALCPARALLEL configuration setting

SET CLEARUPDATESTATUS

SET CLEARUPDATESTATUS specifies when Hyperion Essbase marks data blocks as clean. This clean status is used during intelligent calculation. For detailed information on intelligent calculation, see the Database Administrator's Guide.

The data blocks in your database have a calculation status of either clean or dirty. When Hyperion Essbase does a full calculation of your database, it marks the calculated data blocks as clean. When a data block is clean, Hyperion Essbase will not recalculate the data block on subsequent calculations, provided that intelligent calculation is turned on.

To ensure the accuracy of your calculation results, consider carefully the effect of the SET CLEARUPDATESTATUS AFTER command on your calculation. For more information, see the Database Administrator's Guide.

If you do not use SET CLEARUPDATESTATUS, Hyperion Essbase does not mark calculated data blocks as clean when you calculate a subset of your database. Hyperion Essbase marks data blocks as clean only on a full calculation (CALC ALL;) or when Hyperion Essbase calculates all members in a single calculation pass through your database. For more information, see the Hyperion Essbase Database Administrator's Guide.

Syntax

SET CLEARUPDATESTATUS AFTER | ONLY | OFF;

AFTER Hyperion Essbase marks calculated data blocks as clean, even if you are calculating a subset of your database.
ONLY Hyperion Essbase marks the specified data blocks as clean but does not actually calculate the data blocks. This does the same as AFTER, but disables calculation.
OFF Hyperion Essbase does not mark the calculated data blocks as clean. Data blocks are not marked as clean, even on a default calculation (CALC ALL;) of your database. The existing clean or dirty status of the calculated data blocks remains unchanged.

Description

SET CLEARUPDATESTATUS specifies when Hyperion Essbase marks data blocks as clean.

If you calculate a subset of your database, you may want to use the SET CLEARUPDATESTATUS AFTER command to ensure that the calculated blocks are marked as clean. However, consider carefully the effect of this command on your calculation to ensure that your calculation results are correct.

Warnings

When you use the SET CLEARUPDATESTATUS command to mark calculated data blocks as clean, consider carefully the following questions:

Which data blocks are calculated?

Only calculated data blocks will be marked as clean. For detailed information, see the Hyperion Essbase Database Administrator's Guide.

Are concurrent calculations going to affect the same data blocks?

Do not use the SET CLEARUPDATESTATUS AFTER command with concurrent calculations unless you are certain that the different calculations will not need to calculate the same data block or blocks. If concurrent calculations attempt to calculate the same data blocks, with intelligent calculation turned on, Hyperion Essbase may not recalculate the data blocks, because they are already marked as clean. For more information and an example, see the Database Administrator's Guide.

Are the same data blocks to be recalculated on a second calculation pass through the database?

If you calculate data blocks on a first calculation pass through your database, Hyperion Essbase marks them as clean. If you then attempt to calculate the same data blocks on a subsequent pass with intelligent calculation turned on, Hyperion Essbase does not recalculate the data blocks, because they are already marked as clean. For more information and an example, see the Hyperion Essbase Database Administrator's Guide.

Example

The following examples are based on the Sample Basic database. They assume that intelligent calculation is turned on (the default). For information on turning intelligent calculation on and off, see the SET UPDATECALC command.

 
SET CLEARUPDATESTATUS AFTER;
FIX ("New York")
CALC DIM(Product);
ENDFIX

New York is a member on the sparse Market dimension. Hyperion Essbase searches for dirty parent data blocks for New York (for example "New York"->Colas in which Colas is a parent member). It calculates these dirty blocks based on the Product dimension and marks them as clean. Hyperion Essbase does not mark the child, Input blocks as clean, because they are not calculated.

 
SET CLEARUPDATESTATUS ONLY;
CALC ALL;

Hyperion Essbase searches for all the dirty blocks in the database and marks them as clean. It does not calculate the blocks, even though a CALC ALL; command is used.

 
SET CLEARUPDATESTATUS ONLY;
FIX ("New York")
CALC DIM(Product);
ENDFIX

New York is a member on the sparse Market dimension. Hyperion Essbase searches for dirty parent data blocks for New York (for example "New York"->Colas in which Colas is a parent member). It marks them as clean. It does not calculate the data blocks. It does not mark the child blocks as clean because they are not calculated. For example, if

"New York"->100-10 

is dirty, it remains dirty.

 
SET CLEARUPDATESTATUS OFF;
CALC ALL;
CALC TWOPASS;

Hyperion Essbase calculates all the dirty data blocks in the database. The calculated data blocks remain dirty, Hyperion Essbase does not mark them as clean. Hyperion Essbase then calculates those members tagged as Two-Pass on the dimension tagged as Accounts. Again, it does not mark the calculated data blocks as clean.

For more examples, see the Database Administrator's Guide.

See Also

SET UPDATECALC
UPDATECALC (essbase.cfg file)


SET FRMLBOTTOMUP

The SET FRMLBOTTOMUP command optimizes the calculation of complex formulas on sparse dimensions in large database outlines. This command tells Hyperion Essbase to perform a bottom-up calculation on formulas that would otherwise require a top-down calculation.

Syntax

SET FRMLBOTTOMUP ON|OFF;

ON Turns on the bottom-up sparse formula calculation method.
OFF Turns off the bottom-up sparse formula calculation method. The default setting is OFF. You can change this setting by using CALCOPTFRMLBOTTOMUP TRUE in the essbase.cfg file.

Description

This command optimizes the calculation of complex formulas on sparse dimensions in large database outlines. You might want to turn on this setting when using the CALC ALL; and CALC DIM; commands to calculate the database.

Notes

Example

SET FRMLBOTTOMUP ON;

See Also

CALCOPTFRMLBOTTOMUP (essbase.cfg file setting)


SET LOCKBLOCK

The SET LOCKBLOCK command specifies the maximum number of blocks that Hyperion Essbase can get addressability to concurrently when calculating a sparse member formula.

Syntax

SET LOCKBLOCK HIGH | DEFAULT | LOW;

HIGH, DEFAULT, and LOW Levels defining the number of blocks that Hyperion Essbase can get addressability to concurrently.

Description

SET LOCKBLOCK specifies the maximum number of blocks that Hyperion Essbase can get addressability to concurrently when calculating a block.

You can choose one of three levels. The number of blocks that can Hyperion Essbase can get addressability to at each level is defined using the CALCLOCKBLOCK setting in the essbase.cfg file.

When a block is calculated, Hyperion Essbase locks (gets addressability to) the block along with the blocks containing its children. Hyperion Essbase calculates the block and then releases it along with the blocks containing its children.

By default Hyperion Essbase allows up to 100 blocks to be locked (addressable) concurrently when calculating a block. This is sufficient for most database calculations.

However, you may want to set a number higher than 100 if you are consolidating very large numbers of children in a formula calculation. This setting ensures that Hyperion Essbase can get addressability to all the required blocks when calculating a data block and that performance will not be impaired.

Notes

Example

If the essbase.cfg file contains the following settings:

CALCLOCKBLOCKHIGH           500
CALCLOCKBLOCKDEFAULT        200
CALCLOCKBLOCKLOW            50

then:

SET LOCKBLOCK HIGH;

means that Hyperion Essbase can get addressability to up to 500 data blocks when calculating one block.

SET LOCKBLOCK DEFAULT;

means that Hyperion Essbase can get addressability to up to 200 data blocks when calculating one block.

SET LOCKBLOCK LOW;

means that Hyperion Essbase can get addressability to up to 50 data blocks when calculating one block.

See Also

CALCLOCKBLOCK (essbase.cfg file)


SET MSG

The SET MSG command traces your calculations.

Syntax

SET MSG SUMMARY | DETAIL | ERROR | WARNS | INFO | NONE | ONLY;

SUMMARY Displays calculation settings and provides statistics on the number of:
  • Data blocks created, read, and written
  • Data cells calculated
DETAIL Provides the same information as SUMMARY. In addition, it displays a detailed information message every time Hyperion Essbase calculates a data block.
ERROR Displays only error messages.
WARNS Displays only warning and error messages.
INFO Displays information, warning, and error messages.
NONE Displays no messages during the life of the calculation script. However, because error messages may contain vital information, they are still displayed.
ONLY Instructs Essbase to perform a simulated calculation only. You may disregard any error message during validation that indicates Essbase does not recognize command.

Description

SET MSG SUMMARY and SET MSG DETAIL tell you:

In addition, the SET MSG DETAIL command provides an information message every time Hyperion Essbase calculates a data block.

The SET MSG command applies only to the calculation script in which it is used.

Notes

Example

SET MSG ERROR;

displays only the error messages.

 
SET MSG SUMMARY;

produces the following sample output:

[Tue Apr  4 05:11:16 1995] local/Sample/Basic/Qatest/Info(1012672)
Calculator Information Message:

Maximum Number of Lock Blocks: [100] Blocks

Completion Notice Messages: [Disabled]

Calculations On Updated Blocks Only: [Enabled]

Clear Update Status After Full Calculations: [Enabled]

Calculator Cache With Multiple Bitmaps For: [Market]

[Tue Apr  4 05:11:19 1995] local/Sample/Basic/Qatest/Info(1012672)
Calculator Information Message: 

Total Block Created: [0.0000e+00] Blocks

Sparse Calculations: [4.3000e+01] Writes and [4.3000e+01] Reads

Dense Calculations: [4.3200e+02] Writes and [4.3200e+02] Reads

Sparse Calculations: [1.7200e+02] Cells

Dense Calculations: [4.3200e+02] Cells


SET MSG DETAIL;

produces the following sample output:

[Thu Mar 30 16:27:26 1995] local/Sample/Basic/Qatest/Info(1012669)
Calculator Information Message:

Maximum Number of Lock Blocks: [100] Blocks

Completion Notice Messages: [Disabled]

Calculations On Updated Blocks Only: [Enabled]

Clear Update Status After Partial Calculations: [Disabled]

Calculator Cache With Multiple Bitmaps For: [Market]

 [Thu Mar 30 16:27:26 1995] local/Sample/Basic/Qatest/Info(1012669)
Calculator Information Message: Executing Block - [100], [East]

[Thu Mar 30 16:27:26 1995] local/Sample/Basic/Qatest/Info(1012669)

Calculator Information Message: Executing Block - [Product], [East]

[Thu Mar 30 16:27:26 1995] local/Sample/Basic/Qatest/Info(1012669)

Calculator Information Message: Executing Block - [100], [Market]

[Thu Mar 30 16:27:26 1995] local/Sample/Basic/Qatest/Info(1012669)

Calculator Information Message: Executing Block - [Product], [Market]

[Thu Mar 30 16:27:26 1995] local/Sample/Basic/Qatest/Info(1012669)
Calculator Information Message:

Total Block Created: [0.0000e+00] Blocks

Sparse Calculations: [4.0000e+00] Writes and [2.2000e+01] Reads

Dense Calculations: [0.0000e+00] Writes and [0.0000e+00] Reads

Sparse Calculations: [3.8080e+03] Cells

Dense Calculations: [0.0000e+00] Cells

See Also

SET NOTICE


SET NOTICE

The SET NOTICE command monitors the progress of your calculation by providing completion notices at intervals during the calculation.

Syntax

SET NOTICE HIGH | DEFAULT | LOW;

HIGH, DEFAULT, and LOW Levels defining the frequency and number of completion notices.

You can set the values of HIGH, DEFAULT, and LOW using the CALCNOTICE setting in the essbase.cfg file. If you do not set the value of DEFAULT in the essbase.cfg file, Hyperion Essbase uses a default value of 10, which provides 10 completion messages at 10% intervals during the calculation.

Description

This setting provides completion notices at intervals during the calculation. The number of notices depends on the level you specify.

You can specify the number of notices for each level using the CALCNOTICE setting in the essbase.cfg file.

The interval between notices is approximate. Hyperion Essbase measures the interval by taking the number of data blocks already calculated as a percentage of the total number of possible data blocks in your database. For example, if there are 10,000 possible blocks and you specify 5 notices, Hyperion Essbase notifies you when the calculation approximately reaches block 2000, 4000, 6000, 8,000 and 10,000. However, if only the blocks 1,000 - 4,000 exist, then Hyperion Essbase displays only two notices.

For partial calculations and calculations with multiple passes through your database, the interval between completion notices is very approximate.

Notes

Example

If the essbase.cfg file contains the following settings:

CALCNOTICEHIGH  50
CALCNOTICEDEFAULT  20
CALCNOTICELOW   5

then:

SET NOTICE HIGH;

displays 50 completion notices at 2% intervals.

SET NOTICE DEFAULT;

displays 20 completion notices at 5% intervals.

 
SET NOTICE LOW;

displays 5 completion notices at 20% intervals.

 
SET NOTICE LOW;

might produce the following sample output:

[Thu Apr  6 10:09:19 1995] Local/Sample/Basic/Qatest/Info(1012669)
Calculating [ Measures(All members) Year(All members) Scenario(All members) Product(All members) Market(All members)]
 [Thu Apr  6 10:09:19 1995] Local/Sample/Basic/Qatest/Info(1012672)
Calculator Information Message:


Maximum Number of Lock Blocks: [100] Blocks


Completion Notice For Every: [ 10.000%] Of Blocks


Calculations On Updated Blocks Only: [Disabled]


Clear Update Status After Full Calculations: [Enabled]


Calculator Cache With Multiple Bitmaps For: [Market]



[Thu Apr  6 10:09:21 1995] Local/Sample/Basic/Qatest/Info(1012672)


Calculator Information Message: Completion Notice For Block Number [49]




[Thu Apr  6 10:09:22 1995] Local/Sample/Basic/Qatest/Info(1012672)


Calculator Information Message: Completion Notice For Block Number [97]


[Thu Apr  6 10:09:24 1995] Local/Sample/Basic/Qatest/Info(1012672)


Calculator Information Message: Completion Notice For Block Number [145]



[Thu Apr  6 10:09:25 1995] Local/Sample/Basic/Qatest/Info(1012672)


Calculator Information Message: Completion Notice For Block Number [193]


[Thu Apr  6 10:09:27 1995] Local/Sample/Basic/Qatest/Info(1012672)


Calculator Information Message: Completion Notice For Block Number [241]

See Also

CALCNOTICE (essbase.cfg) file
SET MSG


SET UPDATECALC

The SET UPDATECALC command turns intelligent calculation on and off.

Syntax

SET UPDATECALC ON | OFF;

ON Hyperion Essbase calculates only blocks marked as dirty (see Description). Dirty blocks include updated blocks and their dependent parents (see Notes). The default setting is ON. You can change this default using the UPDATECALC TRUE | FALSE setting in the essbase.cfg file.
OFF Hyperion Essbase calculates all data blocks, regardless of whether they have been updated.

Description

SET UPDATECALC turns intelligent calculation on and off.

Using intelligent calculation, Hyperion Essbase calculates only dirty blocks, such as updated data blocks and their dependent parents. Therefore, the calculation is very efficient.

All data blocks in the database are marked as either clean or dirty. If a data block is clean, then Hyperion Essbase knows that the block does not need to be recalculated.

By default, all data blocks are marked as clean after a full calculation of the database but not after a partial calculation of the database. If required, you can change this default behavior using the SET CLEARUPDATESTATUS command in your calculation script.

Notes

Example

SET UPDATECALC ON;

SET UPDATECALC OFF;

See Also

SET CLEARUPDATESTATUS
UPDATECALC (essbase.cfg file)


SET UPTOLOCAL

The SET UPTOLOCAL command restricts consolidations to those parents with the same defined currency. The default is OFF.

Syntax

SET UPTOLOCAL ON | OFF ;

Description

This command restricts consolidations to those parents with the same defined currency. The default is OFF. For example, all cities in France use the franc (FF) as the unit of currency. Therefore, all children of France, such as the cities Paris, Nancy, and Avignon, consolidate to France. Consolidation stops at this level, however, because Europe also contains countries that use other currencies. The following database outline example illustrates this situation:

Outline example

If you want to consolidate values above this level, you must use CCONV to convert the values to a master rate before consolidating.

Notes

Example

SET UPTOLOCAL ON;

SET UPTOLOCAL OFF;

See Also

CCONV
CCTRACK (essbase.cfg file setting)


VAR

The VAR command declares a temporary variable that contains a single value.

Syntax

VAR varName [= value] ;

varName Name of the temporary variable.
value Optional parameter that declares the data value.

You can also use a single VAR command to declare multiple variables by supplying a comma-delimited list of variable names.

Notes

Example

VAR Target = 1200;

VAR Break1, Break2, Break3;

See Also

ARRAY


Copyright 1991-2002 Hyperion Solutions Corporation. All rights reserved.