Calculation Scripts: Commands
• Data declarations
• Control Flow
• Functional
• Conditionals
Data Declarations:
These commands are used to declare, and set the initial values of, temporary variables. The values stored in a variable may not be directly returned from a spreadsheet, because they only exist while the calculation script is being processed. If you want to report these values, you need to create members within the database outline, or assign the values from the variables into existing members.
• Array
• Var
Array: Declares one-dimensional array variables.
Syntax: ARRAY arrayVariableName [dimName] = { constList};
Ex: ARRAY discount [Scenario] = {1, 2, 3, 4};
ARRAY discount [Scenario], tmpProduct[Product];
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).
Var: Declares a temporary variable that contains a single value.
Syntax: VAR varName [= value];
Ex: VAR Target = 1200;
VAR Break1, Break2, Break3;
Notes
• The name of the variable cannot duplicate a database member name.
• If a value is not declared, it is set to #MISSING.
• VAR commands can only be assigned values within a member calculation or when VAR is declared.
• You can also use a single VAR command to declare multiple variables by supplying a comma-delimited list of variable names.
Control Flow:
Control Flow commands are used to iterate a set of commands or to restrict the commands' effect to a specified subset (partition) database. They control the calculation flow of a calculation script. The FIX…ENDFIX command can be used to restrict a calculation to a particular member or members, while the LOOP…ENDLOOP command allows you to repeat a command as necessary.
• FIX….END FIX
• LOOP…END LOOP
FIX….END FIX:
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
Example
FIX (Budget)
CALC DIM (Year, Measures, Product, Market);
ENDFIX
FIX (Budget, Jan, Feb, Mar, @DESCENDANTS(Profit))
CALC DIM (Product, Market);
ENDFIX
Notes
• FIX commands can be nested within other FIX command blocks.
• FIX statements can only be used in calculation scripts, not in outline member formulas.
• AND/OR operators have the same precedence; Analytic Services evaluates them from left to right. Use parentheses to group the expressions.
• Inside FIX statements, the AND operator represents the intersection of two sets; the OR operator represents the union of two sets. In formulas, these operators are Boolean operators. Using the AND or OR operators on members that are from different dimensions, returns:
o AND: An empty set. The FIX statement is ignored and the calculation continues with a warning message.
o OR: The union of two members sets. FIX (Jan OR Market) is identical to FIX (Jan, Market).
• NOT operators are not supported in FIX statements. Use the @REMOVE function with FIX statements.
• You do not need to follow ENDFIX with a semicolon.
• You can specify attributes in FIX statements using the @ATTRIBUTE and @WITHATTR functions; for example FIX(@ATTRIBUTE(Can)).
• You cannot use a FIX statement on a dimension if it is a subset of a dimension that you calculate within the FIX statement.
• Dynamic calc members are ignored in a FIX statement. If the only member in a FIX statement is a dynamic calc member, an error message is displayed stating that the FIX statement cannot contain a dynamic calc member.
• If the FIX command is issued from a calculation script and produces an empty set, that part of the calculation is ignored, and the calculation continues to the next statement. The application log entry for the calculation shows that the FIX statement evaluated to an empty set (Calculating [...] with fixed members []).
• Calculator function @RANGE and the cross-dimensional operator (->) cannot be used inside a FIX/ENDFIX statement.
LOOP… END LOOP:
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
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
Functional
Functional commands are used to perform operations such as calculation, data copying, clearing data, and currency conversion.
AGG:
Consolidates database values. An AGG command ignores all member formulas; it simply consolidates parent/childrelationships.
Syntax AGG (dimList);
Example AGG (Market);
AGG (Product,Market,Scenario);
Notes
• AGG only works with sparse dimensions.
• When a dimension contains fewer than six consolidation levels, AGG is typically faster than CALC. Conversely, the CALC command is usually faster on dimensions with six or more levels.
• AGG follows the rules for any defined FIX command.
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.
CALC ALL: Calculates and aggregates the entire database based on the database outline.
Syntax CALC ALL [EXCEPT DIM (dimList) | MBR (mbrList)];
Example CALC ALL;
CALC ALL EXCEPT DIM(Product);
CALC DIM: Calculates the formulas and aggregations associated with each member of all 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.
Syntax CALC DIM (dimList);
Example CALC DIM(Accounts);
CALC DIM(Dense1,Sparse1,Sparse2,Dense2);
In the above 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 AVERAGE: 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.
Syntax CALC AVERAGE;
Example CALC AVERAGE;
Notes
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: Calculates all members tagged in the database outline as time balance First.
Syntax CALC FIRST;
Example 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.
Notes
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: Calculates all members tagged in the database outline as time balance Last.
Syntax CALC LAST;
Example CALC LAST;
CALC TWOPASS: Calculates all members tagged in the database outline as two-pass. These members must be on a dimension tagged as accounts.
Syntax CALC TWOPASS;
Example 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.
CCONV: Calculates currency conversions. This command is available only if your company has purchased the Currency Conversion option.
Syntax CCONV currExchMbr | TOLOCALRATE curType;
Example CCONV YEN;
Converts all the data values from local currency values (for example, Swiss 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.
Notes
The CCTRACK setting in the essbase.cfg file must be set to TRUE (the default) to enable the CCONV TOLOCALRATE command.
CCTRACK: Controls whether exchange rates are tracked as Analytic Services calculates currency conversions. This setting does not apply to aggregate storage databases.
Syntax CCTRACK TRUE | FALSE
Example CCTRACK TRUE
Notes
• When CCTRACK is turned on the following restrictions apply:
o If you are using currency partitions, you cannot use a CCONV command with a FIX statement to convert a subset of a currency partition (a calculation script attempting such a FIX will not validate).
o If you are not using currency partitions, you must use CCONV with a FIX statement.
• Setting CCTRACK to FALSE turns off the tracking system with the following results:
o The CCONV assumes that the data is unconverted (in local currency). If you accidentally run the CCONV command multiple times on the same data, the resulting data will be inaccurate.
o Similarly, the currency report options assume that the data is unconverted (in local currency). If the data has already been converted in the database, it is reconverted at report time, resulting in inaccurate data.
o The restrictions on using the FIX and DATACOPY commands in currency conversions do not apply. For example, if you are using currency partitions, you can now use the FIX command with the CCONV command to calculate a subset of a currency partition. If you are not using currency partitions, you can use CCONV without a FIX statement.
CLEARCCTRACK: Clears the internal exchange rate tables created by the CCTRACK setting.
Syntax CLEARCCTRACK;
Example CLEARDATA Actual;
FIX (Actual)
CLEARCCTRACK;
ENDFIX
Clears the Actual data, fixes on the Actual data (in the converted partition) and clears the internal exchange rate tables for the Actual data.
SET CCTRACKCALC: Specifies whether Analytic Services checks the flags set by the CCTRACK setting to determine if the currency data has already been converted.
Syntax SET CCTRACKCALC ON | OFF;
Notes: The SET CCTRACKCALC command is valid only when CCTRACK is set to TRUE (the default).
Example SET CCTRACKCALC OFF;
FIX(Actual)
CCONV "XchR"->US$;
ENDFIX
CALC ALL;
Fixes on the the Actual currency partition and forces the conversion of the Actual data regardless of whether Analytic Services has flagged the data as already being converted, converting the data using the XchR, US$ rate from the currency database. Recalculates the database
SET UPTOLOCAL: 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 Switzerland use the Swiss franc (CHF) as the unit of currency. Therefore, all children of Switzerland, such as the cities Geneva, Zurich, and Lucerne, consolidate to Switzerland. Consolidation stops at this level, however, because Europe also contains countries that use other currencies. The following database outline example illustrates this situation:
CLEARBLOCK: Clears blocks of data and sets them to #MISSING.
Syntax CLEARBLOCK ALL | UPPER | NONINPUT | DYNAMIC;
Example CLEARBLOCK ALL; (Clears all data blocks)
CLEARBLOCK UPPER ;( Clears all consolidated level blocks)
CLEARBLOCK NONINPUT; (Clears blocks containing values derived from calculations. Applies to blocks that are completely created by a calculation operation. Cannot be a block into which certain values were loaded through a data load operation)
CLEARBLOCK DYNAMIC; (Clears blocks containing values derived from Dynamic Calc and Store member combinations)
If all the cells are empty or contain #MISSING, 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 Analytic Services must pass through these additional data blocks. CLEARBLOCK clears blocks before a calculation occurs.
If you use CLEARBLOCK within a FIX command, Analytic Services clears only the data cells within the fixed range, and not the entire block.
Notes
If you regularly enter data values directly into a consolidated level, the UPPER option overwrites your data. In this case, you should use the NONINPUT option, which only clears blocks that contains calculated values.
CLEARDATA: Clears data values from the database and sets them to #MISSING.
Syntax CLEARDATA mbrName;
Example CLEARDATA Budget; clears all Budget data.
CLEARDATA Budget->Colas; clears only Budget data for the Colas product family.
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. 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.
Notes
CLEARDATA does not work if placed in an IF statement.
DATACOPY: Copies a range of data cells to another range within the database.
Syntax DATACOPY mbrName1 TO mbrName2;
Example DATACOPY Plan TO Revised_Plan;
Notes
• The size of the copied dimensions must be equal to the destination (TO) size.
• DATACOPY follows the rules for any defined FIX command.
• To prevent creation of #MISSING blocks, add the entry SET COPYMISSINGBLOCK OFF to the essbase.cfg configuration file.
SET AGGMISSG: Specifies whether Analytic Services consolidates #MISSING values in the database.
Syntax SET AGGMISSG ON | OFF ;
Example SET AGGMISSG OFF;
CALC ALL;
CALC PERCENTS;
Notes: SET AGGMISSG commands apply to calculating sparse dimensions.
SET CACHE: 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, Analytic Services uses a default value of 200,000 bytes. The maximum calculator cache size that you can specify is 200,000,000 bytes
OFF Analytic Services does not use a calculator cache
ALL Analytic Services uses a calculator cache, even when you do not calculate at least one full sparse dimension
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 Analytic Services does not use a calculator cache.
Description
SET CACHE specifies the size of the calculator cache.
Analytic Services 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, Analytic Services uses a calculator cache using the CALCCACHE TRUE | FALSE setting in the essbase.cfg file. By default, CALCCACHE is set to TRUE.
Analytic Services uses the calculator cache providing that:
• Your database has at least two sparse dimensions.
• You calculate at least one, full sparse dimension (unless you specify the CALCCACHE ALL option).
You can use this command more than once within a calculation script. You can display the calculator cache setting using the SET MSG command.
SET CLEARUPDATESTATUS: Specifies when Analytic Services marks data blocks as clean. This clean status is used during intelligent calculation.
Syntax SET CLEARUPDATESTATUS AFTER | ONLY | OFF;
AFTER Analytic Services marks calculated data blocks as clean, even if you are calculating a subset of your database.
ONLY Analytic Services 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 Analytic Services 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.
Example 1 SET CLEARUPDATESTATUS AFTER;
FIX ("New York")
CALC DIM (Product);
ENDFIX
New York is a member on the sparse Market dimension. Analytic Services 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. Analytic Services does not mark the child, Input blocks as clean, because they are not calculated.
Example 2 SET CLEARUPDATESTATUS ONLY;
CALC ALL;
Analytic Services 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.
Example 3 SET CLEARUPDATESTATUS ONLY;
FIX ("New York")
CALC DIM (Product);
ENDFIX
New York is a member on the sparse Market dimension. Analytic Services 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.
Example 4 SET CLEARUPDATESTATUS OFF;
CALC ALL;
CALC TWOPASS;
Analytic Services calculates all the dirty data blocks in the database. The calculated data blocks remain dirty; Analytic Services does not mark them as clean. Analytic Services 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.
SET FRMLBOTTOMUP: Optimizes the calculation of complex formulas on sparse dimensions in large database outlines. This command tells Analytic Services 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: The SET FRMLBOTTOMUP 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:
• For information on complex formulas and top-down calculations, see the Database Administrator's Guide.
• Forcing a bottom-up calculation on a formula may produce results that are inconsistent with a top-down calculation if:
o The formula contains complex functions (for example, range functions)
o The formula's dependencies are not straightforward
• Before using the SET FRMLBOTTOMUP command in a production environment, be sure to check the validity of calculation results produced when the command is enabled (set to ON).
Example SET FRMLBOTTOMUP ON;
SET LOCKBLOCK: Specifies the maximum number of blocks that Analytic Services 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 Analytic Services can get addressability to concurrently.
Example If the essbase.cfg file contains the following settings:
CALCLOCKBLOCKHIGH 500
CALCLOCKBLOCKDEFAULT 200
CALCLOCKBLOCKLOW 50 then:
SET LOCKBLOCK HIGH; means that Analytic Services can get addressability to up to 500 data blocks when calculating one block.
SET LOCKBLOCK DEFAULT; means that Analytic Services can get addressability to up to 200 data blocks when calculating one block.
SET LOCKBLOCK LOW; means that Analytic Services can get addressability to up to 50 data blocks when calculating one block
SET MSG: Sets the level of messaging you want returned about calculations, and enables simulated 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 Analytic Services 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 Analytic Services to perform a simulated calculation only. You may disregard any error message during validation that indicates Analytic Services does not recognize a command.
NOTE: When you use this parameter, Analytic Services generates some empty upper-level blocks. Make sure to clear upper-level blocks (or non-input blocks if you load data into upper level blocks in your model) at the end of the simulation/command.
We recommend using SET MSG ONLY with the calculation script commands SET NOTICE HIGH and CALC ALL. For more information, see the Database Administrator's Guide sections on optimizing calculations.
SET NOTICE: 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.
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 UPDATECALC: Turns intelligent calculation on or off.
Syntax SET UPDATECALC ON | OFF;
ON Analytic Services 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 Analytic Services calculates all data blocks, regardless of whether they have been updated.
Conditionals:
When you use an IF statement as part of a member formula in a calc script, you need to:
• Associate it with a single member
• Enclose it in parentheses
For example: Profit (IF (Sales > 100)
Profit = (Sales - COGS) * 2;
ELSE
Profit = (Sales - COGS) * 1.5;
ENDIF;);
Analytic Services cycles through the database, performing the following calculations:
1. The IF statement checks to see if the value of Sales for the current member combination is greater than 100.
2. If Sales is greater than 100, Analytic Services subtracts the value in COGS from the value in Sales, multiplies it by 2, and places the result in Profit.
3. If Sales is less than, or equal to 100, Analytic Services subtracts the value in the COGS member from the value in the Sales member, multiplies it by 1.5, and places the result in the Profit member.
The whole of the IF ... ENDIF statement is enclosed in parentheses and associated with the Profit member, Profit (IF(...)...).
IF: 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;
Subscribe to:
Post Comments
(
Atom
)
Please help me to make this site better.
ReplyDeletegood info
ReplyDeleteThank you Koti Sir .. for your valuble information ..
ReplyDelete