The & command prefaces a substitution variable in a calculation script.
&variableName;
variableName | The name of the substitution variable set on the database. |
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.
&CurQtr;
becomes
Qtr1;
if the substitution variable &CurQtr has the value "Qtr1".
The AGG command consolidates database values. An AGG command ignores all member formulas; it simply consolidates parent/child relationships.
AGG (dimList);
dimList | Name of a single dimension or a comma-delimited list of dimensions. |
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:
AGG(Market); AGG(Product,Market,Scenario);
CALC ALL
CALC DIM
SET AGGMISSG
The ARRAY command declares one-dimensional array variables.
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.
|
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.
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).
The CALC ALL command calculates and aggregates the entire database based on the database outline
.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. |
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.
CALC ALL; CALC ALL EXCEPT DIM(Product);
CALC DIM
SET UPDATECALC
SET CALCHASHTBL
SET FRMLBOTTOMUP
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.
CALC AVERAGE;
CALC AVERAGE;
The CALC DIM command calculates the formulas and aggregations associated with each member of all the specified dimensions.
CALC DIM (dimList);
dimList | Dimension or comma-delimited list of dimensions to be calculated. |
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.
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);
CALC ALL
SET UPDATECALC
SET CLEARUPDATESTATUS
The CALC FIRST command calculates all members tagged in the database outline as time balance First.
CALC FIRST;
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.
CALC FIRST;
The CALC LAST command calculates all members tagged in the database outline as time balance Last.
CALC LAST;
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.
CALC LAST;
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.
CALC TWOPASS;
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.
CALC TWOPASS;
CCONV calculates currency conversions. This command is available only if your company has purchased the optional Hyperion Essbase Currency Conversion product.
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. |
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:
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.
essbase.cfg
file
must be set to TRUE (the default) to enable the CCONV TOLOCALRATE
command.
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.
CCTRACK (essbase.cfg
file)
SET UPTOLOCAL
For more information on converting currencies, see the Database Administrator's Guide.
The CLEARBLOCK command clears blocks of data and sets them to
#MISSING
.
CLEARBLOCK ALL | UPPER | NONINPUT | DYNAMIC ;
ALL | Clears all data blocks. |
UPPER | Clears 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. |
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.
CLEARBLOCK ALL; CLEARBLOCK UPPER; CLEARBLOCK NONINPUT; CLEARBLOCK DYNAMIC;
The CLEARDATA command clears data values from the database and sets them to #MISSING
.
CLEARDATA mbrName;
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
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.
CLEARDATA Budget;
clears all Budget data.
CLEARDATA Budget->Colas;
clears only Budget data for the Colas product family.
CLEARDATA does not work if placed in an IF statement.
The DATACOPY command copies a range of data cells to another range within the database.
DATACOPY mbrName1 TO mbrName2;
mbrName1 and mbrName2 | Any valid single member name or member combination, or a function that returns a single member or member combination. |
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.
DATACOPY Plan TO Revised_Plan;
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.
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. |
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;
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.
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. |
IF (condition) statement; IF (condition) statement; ELSEIF (condition) statement; ENDIF; statement; ENDIF;
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;
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.
ENDIF;
"Opening Inventory" (IF (@ISMBR(Budget)) IF (@ISMBR(Jan)) "Opening Inventory" = Jan; ELSE "Opening Inventory" = @PRIOR("Ending Inventory"); ENDIF; ENDIF;)
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;
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.
FIX (fixMbrs)
COMMANDS ;
ENDFIX
fixMbrs |
A member name or list of members from any number of database dimensions. fixMbrs
can also contain:
|
COMMANDS | The commands you want to be executed for the duration of the FIX. |
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.
A OR B AND C
is the same as ((A OR B)
AND C)
. However, if you use (A OR (B AND C))
, Hyperion Essbase
evaluates the sub-expression in parentheses (B AND C)
before the
whole expression, producing a different result.
FIX(@ATTRIBUTE(Can))
. You must use these functions; FIX(Can)
is not supported.
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"))
Calculator function @RANGE and the cross-dimensional operator (->) cannot be used inside a FIX/ENDFIX statement.
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.
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. |
IF (@ISMBR(@ATTRIBUTE(Can))) ...
. You must use these functions; IF(@ISMBR(Can))
is not supported.
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.
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.
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. |
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.
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
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.
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;
The SET AGGMISSG command specifies whether Hyperion Essbase aggregates #MISSING
values in the database.
SET AGGMISSG ON | OFF ;
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.
SET AGGMISSG OFF; CALC ALL; CALC PERCENTS;
The SET CACHE command specifies the size of the calculator cache.
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. |
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.
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.
CALCCACHE TRUE | FALSE (essbase.cfg
file setting)
CALCCACHEHIGH | DEFAULT | LOW (essbase.cfg
file setting)
SET MSG (calculation script)
The SET CALCHASHTBL command optimizes the calculation of large, flat database outlines.
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. |
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.
SET CALCHASHTBL ON;
CALCOPTCALCHASHTBL (essbase.cfg
file setting)
CALCHASHTBLMEMORY (essbase.cfg
file setting)
SET CALCPARALLEL enables parallel calculation in place of the default serial calculation.
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. |
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.
SET CALCPARALLEL 3;
This example enables up to three threads to be used to perform calculation tasks at the same time.
SET CALCTASKDIMS specifies the number of sparse dimensions included in the identification of tasks for parallel calculation.
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. |
SET CALCTASKDIMS specifies how many of the sparse dimensions in an outline are used to identify potential tasks that can be run in parallel.
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.
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.
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.
|
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.
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.
SET UPDATECALC
UPDATECALC (essbase.cfg
file)
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.
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.
|
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.
SET FRMLBOTTOMUP ON;
CALCOPTFRMLBOTTOMUP (essbase.cfg
file setting)
The SET LOCKBLOCK command specifies the maximum number of blocks that Hyperion Essbase can get addressability to concurrently when calculating a sparse member formula.
SET LOCKBLOCK HIGH | DEFAULT | LOW;
HIGH, DEFAULT, and LOW | Levels defining the number of blocks that Hyperion Essbase can get addressability to concurrently. |
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.
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.
CALCLOCKBLOCK (essbase.cfg
file)
The SET MSG command traces your calculations.
SET MSG SUMMARY | DETAIL | ERROR | WARNS | INFO | NONE | ONLY;
SUMMARY | Displays calculation settings and provides statistics
on the number of:
|
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. |
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.
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
The SET NOTICE command monitors the progress of your calculation by providing completion notices at intervals during the calculation.
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.
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.
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]
CALCNOTICE (essbase.cfg
) file
SET MSG
The SET UPDATECALC command turns intelligent calculation on and off.
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. |
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.
SET UPDATECALC ON; SET UPDATECALC OFF;
SET CLEARUPDATESTATUS
UPDATECALC (essbase.cfg
file)
The SET UPTOLOCAL command restricts consolidations to those parents with the same defined currency. The default is OFF.
SET UPTOLOCAL ON | OFF ;
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:
If you want to consolidate values above this level, you must use CCONV to convert the values to a master rate before consolidating.
SET UPTOLOCAL ON; SET UPTOLOCAL OFF;
CCONV
CCTRACK (essbase.cfg
file setting)
The VAR command declares a temporary variable that contains a single value.
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.
#MISSING
.
VAR Target = 1200; VAR Break1, Break2, Break3;
Copyright 1991-2002 Hyperion Solutions Corporation. All rights reserved.