Developing Calculation Scripts Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Developing Calculation Scripts


This chapter explains how to develop calculation scripts and how to use them to control the way Analytic Services calculates a block storage database. This chapter provides some examples of calculation scripts, which you may want to adapt for your own use. This chapter also shows you how to create and execute a simple calculation script. This chapter is not relevant to aggregate storage databases.

This chapter includes the following sections:

For a comprehensive discussion of developing formulas, which may be used in calculation scripts or in an outline, see Developing Formulas. For more examples, see Reviewing Examples of Calculation Scripts.

For information about copying, renaming, locking, and deleting calculation scripts, see Using Analytic Services to Manage Objects.

Understanding Calculation Scripts

A calculation script contains a series of calculation commands, equations, and formulas. You use a calculation script to define calculations other than the calculations that are defined by the database outline.

Calculation scripts are text files. Using Calculation Script Editor, you can create calculation scripts by:

When you save a calculation script, it is given a .csc extension by default. If you run a calculation script from Essbase Administration Services or from Essbase Spreadsheet Services, it must have a .csc extension. However, since a calculation script is basically a text file, you can use MaxL or ESSCMD to run any text file as a calculation script.

For more information about Calculation Script Editor, see "About Calculation Script Editor" in Essbase Administration Services Online Help.

For example, the following calculation script calculates the Actual values in the Sample Basic database.

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

You can use a calculation script to specify exactly how you want Analytic Services to calculate a database. For example, you can calculate part of a database or copy data values between members. You can design and run custom database calculations quickly by separating calculation logic from the database outline.

Analytic Services allows you to perform a default calculation (CALC ALL) or a calculation of your own choosing that you specify in a calculation script to control how Analytic Services calculates a database.

For example, you need to write a calculation script if you want to do any of the following:

Understanding Calculation Script Syntax

Analytic Services provides a flexible set of commands that you can use to control how a database is calculated. You can construct calculation scripts from commands and formulas. In Calculation Script Editor, the different elements of the script are color-coded to aid in script readability. You can enable auto-completion to help build scripts interactively as you type. For more information, see "About Calculation Script Editor" in Essbase Administration Services Online Help.

Computation, control of flow, and data declarations are discussed in the following sections. For a full list of calculation script commands, see the Technical Reference.

Understanding the Rules for Calculation Script Syntax

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

When you write a calculation script, you can use the Calculation Script Editor syntax checker to check the syntax. For a brief discussion of the syntax checker, see Checking Syntax.

Note: For detailed information on calculation script syntax, see the Technical Reference.

Understanding Calculation Commands

You can use the following calculation commands to perform a database calculation that is based on the structure and formulas in the database outline.

Note: For a complete list of calculation commands and syntax, see the Technical Reference.


Calculation
Command

The entire database, based on the outline

CALC ALL

A specified dimension or dimensions

CALC DIM

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

CALC TWOPASS

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

membername

All members tagged as Average on the dimension tagged as accounts

CALC AVERAGE

All members tagged as First on the dimension tagged as accounts

CALC FIRST

All members tagged as Last on the dimension tagged as accounts

CALC LAST

Currency conversions

CCONV



Controlling the Flow of Calculations

You can use the following commands to manipulate the flow of calculations. For detailed information on these commands, see the Technical Reference.


Calculation
Commands

Calculate a subset of a database

FIX ... ENDFIX

Specify the number of times that commands are iterated

LOOP ... ENDLOOP



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

Note: You cannot branch from one calculation script to another calculation script.

Declaring Data Variables

You can use the following commands to declare temporary variables and, if required, to set their initial values. Temporary variables store the results of intermediate calculations.

You can also use substitution variables in a calculation script. For a discussion where, why, and how to use substitution variables, see Using Substitution Variables in Calculation Scripts.


Calculation
Commands

Declare one-dimensional array variables

ARRAY

Declare a temporary variable that contains a single value

VAR



For detailed information on the these commands, see the Technical Reference.

Values stored in temporary variables exist only while the calculation script is running. You cannot report on the values of temporary variables.

Variable and array names are character strings that contain any of the following characters:

Typically, arrays are used to store variables as part of a member formula. The size of the array variable is determined by the number of members in the corresponding dimension. For example, if the Scenario dimension has four members, the following command creates an array called Discount with four entries. You can use more than one array at a time.

ARRAY Discount[Scenario]; 
 

Specifying Global Settings for a Database Calculation

You can use the following commands to define calculation behavior. For a detailed discussion of each specific command, see the Technical Reference.


Calculation
Command

To specify how Analytic Services treats #MISSING values during a calculation

SET AGGMISSG

To adjust the default calculator cache size

SET CACHE

To enable parallel calculation (see Using Parallel Calculation)

SET CALCPARALLEL

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

SET CALCTASKDIMS

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

SET FRMLBOTTOMUP

To display messages to trace a calculation.

SET MSG

SET NOTICE

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

SET UPDATECALC

To control how Analytic Services marks data blocks for the purpose of Intelligent Calculation (see Using the SET CLEARUPDATESTATUS Command)

SET CLEARUPDATESTATUS

To specify the maximum number of blocks that Analytic Services can lock concurrently when calculating a sparse member formula

SET LOCKBLOCK

To turn on and turn off the Create Blocks on Equation setting. This setting controls creation of blocks when you assign non-constant values to members of a sparse dimension (see Non-Constant Values Assigned to Members in a Sparse Dimension

SET CREATEBLOCKEQ

To enable calculations on potential data blocks and save these blocks when the result is not #MISSING.

SET CREATENONMISSINGBLK

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

SET UPTOLOCAL



A SET command in a calculation script stays in effect until the next occurrence of the same SET command.

Consider the following calculation script:

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

Analytic Services displays messages at the detail level when calculating the Year dimension. However, when calculating the Measures dimension, Analytic Services displays messages at the summary level.

Some SET calculation commands trigger additional passes through the database. Consider this calculation script:

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

Analytic Services calculates member combinations for Qtr1 with SET AGGMISSG turned on. Analytic Services then does a second calculation pass through the database and calculates member combinations for East with SET AGGMISSG turned off. For more information on the setting for consolidating missing values, see the SET AGGMISSG command in the Technical Reference. For more information on calculation passes, see Using Two-Pass Calculation.

Adding Comments

You can include comments to annotate calculation scripts. Analytic Services ignores these comments when it runs the calculation script.

To include a comment, start the comment with /* and end the comment with */. Consider the following comment:

/*   This is a calculation script comment
     that spans two lines.*/ 
 

Planning Calculation Script Strategy

You can type a calculation script directly into the text area of Calculation Script Editor, or you can use the user interface features of Calculation Script Editor to build the calculation script.

Using Formulas in a Calculation Script

You can place member formulas in a calculation script. When you place formulas in a calculation script, they override any conflicting formulas that are applied to members in the database outline.

In a calculation script, you can perform both of the following operations:

To calculate a formula that is applied to a member in the database outline, simply use the member name followed by a semicolon (;); for example:

Variance; 
 

This command calculates the formula applied to the Variance member in the database outline.

To override values that result from calculating an outline, manually apply a formula that you define in a calculation script, using Calculation Script Editor or by creating a .txt file. The following formula cycles through the database, adding the values in the members Payroll, Marketing, and Misc and placing the result in the Expenses member. This formula overrides any formula placed on the Expenses member in the database outline.

Expenses = Payroll + Marketing + Misc; 
 

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

For more information about formulas, see Developing Formulas.

Basic Equations

You can use equations in a calculation script to assign value to a member, as follows:

Member = mathematical expression; 
 

In this equation, Member is a member name from the database outline, and mathematical expression is any valid mathematical expression. Analytic Services evaluates the expression and assigns the value to the specified member.

For example, the following formula causes Analytic Services to cycle through the database, subtracting the values in COGS from the values in Sales and placing the result in Margin:

Margin = Sales - COGS; 
 

To cycle through a database means that Analytic Services takes a calculation pass through the database. For more information on calculation passes, see Using Two-Pass Calculation.

The next formula cycles through the database subtracting the values in Cost from the values in Retail, calculating the resulting values as a percentage of the values in Retail, and placing the results in Markup:

Markup = (Retail - Cost) % Retail; 
 

You can also use the > (greater than) and < (less than) logical operators in equations; for example:

Sales Increase Flag = Sales->Feb > Sales->Jan; 
 

If it is true that February sales are greater than January sales, Sales Increase Flag results in a 1 value; if false, the result is a 0 value.

Conditional Equations

When you use an IF statement as part of a member formula in a calculation script, you need to perform both of the following tasks:

A sample IF statement is illustrated in the following example:

Profit 
(IF (Sales > 100)
   Profit = (Sales - COGS) * 2;
ELSE
  Profit = (Sales - COGS) * 1.5;
ENDIF;) 
 

Analytic Services cycles through the database and performs 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 the difference by 2, and places the result in Profit.

  3. If Sales is less than or equal to 100, Analytic Services subtracts the value in COGS from the value in Sales, multiplies the difference by 1.5, and places the result in Profit.

The whole of the IF ... ENDIF statement is enclosed in parentheses and associated with the Profit member, Profit (IF(...)...).

Interdependent Formulas

When you use an interdependent formula in a calculation script, the same rules apply as for the IF statement. You need to perform both of the following tasks:

Consider the interdependent formula discussed earlier. If you place the formula in a calculation script, you enclose the whole formula in parentheses and associate it with the Opening Inventory member, as follows:

"Opening Inventory" 
(IF(NOT @ISMBR (Jan))"Opening Inventory" =
    @PRIOR("Ending Inventory"));
    ENDIF;
"Ending Inventory" = "Opening Inventory" - Sales + Additions;) 
 

Using a Calculation Script to Control Intelligent Calculation

Assume that you have a formula on a sparse dimension member and the formula contains either of the following:

Analytic Services always recalculates the data block that contains the formula, even if the data block is marked as clean for the purposes of Intelligent Calculation. For more information, see Calculating Data Blocks. For more information about Intelligent Calculation, see Optimizing with Intelligent Calculation.

Grouping Formulas and Calculations

You may achieve significant calculation performance improvements by carefully grouping formulas and dimensions in a calculation script. For a discussion of the appropriate use of parentheses and for examples, see Calculating a Series of Member Formulas and Calculating a Series of Dimensions.

When you run a calculation script, Analytic Services automatically displays the calculation order of the dimensions for each pass through the database so that you can tell how many times Analytic Services has cycled through the database during the calculation. Analytic Services writes these information messages in the application log. The messages can also be viewed during your session in the following ways:

To display the application log, see Viewing the Analytic Server and Application Logs.

Calculating a Series of Member Formulas

When you calculate formulas, avoid using parentheses unnecessarily. The following formulas cause Analytic Services to cycle through the database once, calculating both formulas in one pass:

Profit = (Sales - COGS) * 1.5;
Market = East + West; 
 

Similarly, the following configurations cause Analytic Services to cycle through the database only once, calculating the formulas on the members Qtr1, Qtr2, and Qtr3:

Qtr1;
Qtr2;
Qtr3; 
 

or

(Qtr1;
Qtr2;
Qtr3;) 
 

However, the inappropriately placed parentheses in the following example causes Analytic Services to perform two calculation passes through the database, once calculating the formulas on the members Qtr1 and Qtr2 and once calculating the formula on Qtr3:

(Qtr1;
Qtr2;)
Qtr3; 
 

Calculating a Series of Dimensions

When you calculate a series of dimensions, you can optimize performance by grouping the dimensions wherever possible.

For example, the following formula causes Analytic Services to cycle through the database only once:

CALC DIM(Year, Measures); 
 

However, the following syntax causes Analytic Services to cycle through the database twice, because Analytic Services cycles through once for each CALC DIM command:

CALC DIM(Year);
CALC DIM(Measures); 
 

Using Substitution Variables in Calculation Scripts

You can use substitution variables in calculation scripts. Substitution variables are useful, for example, when you reference information or lists of members that change frequently.

When you include a substitution variable in a calculation script, Analytic Services replaces the substitution variable with the value you specified for the substitution variable.

You create and specify values for substitution values in Essbase Administration Services. For a comprehensive discussion of substitution variables, see Using Substitution Variables.

You can create variables at the server, application, and database levels. When you use a substitution variable in a calculation script, it must be available to the calculation script. For example, if you create a substitution variable at the database level, it is only available to calculation scripts within the database. However, if you create a variable at the server level, it is available to any calculation script on the server.

Add the ampersand (&) character before a substitution variable in a calculation script. Analytic Services treats any string that begins with a leading ampersand as a substitution variable, replacing the variable with its assigned value before parsing the calculation script.

For example, &CurQtr; becomes Qtr1; if you have given the substitution variable &CurQtr the value Qtr1.

Consider an example in which you want to calculate Sample Basic data for the current quarter. You can use the following calculation script to perform this calculation:

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

You then define the substitution variable CurQtr as the current quarter; for example, Qtr3. Analytic Services replaces the variable CurQtr with the value Qtr3 when it runs the calculation script.

Clearing Data

You can use the following commands to clear data. If you want to clear an entire database, see "Clearing Data" in the Essbase Administration Services Online Help.


Calculation
Command

Changes the values of the cells you specify to #MISSING. The data blocks are not removed. You can use the FIX command with the CLEARDATA command to clear a subset of a database.

CLEARDATA

Removes the entire contents of a block, including all the dense dimension members. Analytic Services removes the entire block, unless CLEARBLOCK is inside a FIX command on members within the block.

CLEARBLOCK

Removes blocks for Dynamic Calc and Store member combinations. For more information, see Dynamically Calculating Data Values.

CLEARBLOCK DYNAMIC



The following examples are based on the Sample Basic database. If the Scenario dimension is dense, the following example removes all the data cells that do not contain input data values and intersect with member Actual from the Scenario dimension.

FIX(Actual)
CLEARBLOCK NONINPUT;
ENDFIX

If the Scenario dimension is sparse, the following formula removes only the blocks whose Scenario dimension member is Actual. The other blocks remain:

FIX(Actual)
CLEARBLOCK NONINPUT;
ENDFIX 
 

For example, the following formula clears all the Actual data values for Colas:

CLEARDATA Actual -> Colas; 
 

Copying Data

You can use the DATACOPY calculation command to copy data cells from one range of members to another range of members in a database. The two ranges must be the same size.

For example, in the Sample Basic database, the following formula copies Actual values to Budget values:

DATACOPY Actual TO Budget; 
 

You can use the FIX command to copy a subset of values.

For example, in the Sample Basic database, the following formula copies Actual values to Budget values for the month of January only:

FIX (Jan)
DATACOPY Actual TO Budget;
ENDFIX 
 

For more information about the DATACOPY command, see the Technical Reference. For more information about the FIX command, see Using the FIX Command.

Calculating a Subset of a Database

To calculate a subset of a database, use either of the following methods:

For information about using member lists, see Calculating Lists of Members. For examples of use of the FIX command, see Using the FIX Command.

Note: When you have Intelligent Calculation turned on, the newly calculated data blocks are not marked as clean after a partial calculation of a database. When you calculate a subset of a database, you can use the SET CLEARUPDATESTATUS AFTER command to ensure that the newly calculated blocks are marked as clean. Using this command ensures that Analytic Services recalculates the database as efficiently as possible using Intelligent Calculation. For a comprehensive discussion of Intelligent Calculation, see Optimizing with Intelligent Calculation.

Calculating Lists of Members

You can use a member set function to generate a list of members that is based on a member you specify. For example, you can use the @IDESCENDANTS function to generate a list of all the descendants of a specified member.

In the Sample Basic database, @IDESCENDANTS("Total Expenses"); generates the following list of members-Total Expenses, Marketing, Payroll, and Misc.

When you use a member set function in a formula, Analytic Services generates a list of members before calculating the formula.

For detailed information on these and other member set functions, see the Technical Reference.

Using the FIX Command

The FIX ... ENDFIX commands are particularly useful to calculate a carefully defined subset of the values in a database. For example, the following calculation script calculates only the Budget values for only the descendants of East (New York, Massachusetts, Florida, Connecticut, and New Hampshire) in the Sample Basic database:

FIX(Budget,@DESCENDANTS(East))
CALC DIM(Year, Measures, Product);
ENDFIX 
 

The next example fixes on member combinations for the children of East that have a user-defined attribute (UDA) of New Mkt. For information on defining UDAs, see Creating and Changing Database Outlines.

FIX(@CHILDREN(East) AND @UDA(Market,"New Mkt"))
Marketing = Marketing * 1.1;
ENDFIX 
 

The next example uses a wildcard match to fix on member names that end in the characters -10. In Sample Basic, this example fixes on the members 100-10, 200-10, 300-10, and 400-10.

FIX(@MATCH(Product, "???-10"))
Price = Price * 1.1;
ENDFIX 
 

When you use the FIX command only on a dense dimension, Analytic Services retrieves the entire block that contains the required value or values for the member or members that you specify. Thus, I/O is not affected, and the calculation performance time is improved.

When you use the FIX command on a sparse dimension, Analytic Services retrieves the block for the specified sparse dimension member or members. Thus, I/O may be greatly reduced.

Analytic Services cycles through the database once for each FIX command that you use on dense dimension members. When possible, combine FIX blocks to improve calculation performance. For example, the following calculation script causes Analytic Services to cycle through the database only once, calculating both the Actual and the Budget values:

FIX(Actual,Budget)
CALC DIM(Year, Measures);
ENDFIX 
 

However, this calculation script causes Analytic Services to cycle through the database twice, once calculating the Actual data values and once calculating the data values for Budget:

FIX(Actual)
CALC DIM(Year, Measures);
ENDFIX
FIX(Budget)
CALC DIM(Year, Measures);
ENDFIX 
 

You cannot FIX on a subset of a dimension that you calculate within a FIX statement. For example, the following calculation script returns an error message because the CALC DIM operation calculates the entire Market dimension, although the FIX above it fixes on specific members of the Market dimension.

FIX(@CHILDREN(East) AND @UDA(Market,"New Mkt"))
CALC DIM(Year, Measures, Product, Market);
ENDFIX 
 

Note: The FIX command has some restrictions. For detailed information on the restrictions and on using the FIX command, see the Technical Reference.

Enabling Calculations on Potential Blocks

When you use a formula on a dense member in a dense dimension, if the resultant values are from a dense dimension and the operand or operands are from a sparse dimension, Analytic Services does not automatically create the required blocks.

Consider an example from Sample Basic, in which you want to create budget sales and expense data from existing actual data. Sales and Expenses are members in the dense Measures dimension. Budget and Actual are members in the sparse Scenario dimension.

FIX(Budget)
      (Sales = Sales -> Actual * 1.1;
       Expenses = Expenses -> Actual * .95;)
ENDFIX 
 

Note that Sales and Expenses, the results of the equations, are dense dimension members, and the operand, Actual, is in a sparse dimension. Because Analytic Services executes dense member formulas only on existing data blocks, the calculation script above does not create the required data blocks and Budget data values are not calculated for blocks that do not already exist.

You can solve the problem using the following techniques, each with its own advantages and disadvantages:

Using DATACOPY to Copy Existing Blocks

You can use the DATACOPY command to create a new block for each existing block, and then perform calculations on the new blocks, as shown in the following example:

DATACOPY Sales -> Actual TO Sales -> Budget;
DATACOPY Expenses -> Actual TO Expenses -> Budget;
FIX(Budget)
      (Sales = Sales -> Actual * 1.1;
       Expenses = Expenses -> Actual *  .95;)
ENDFIX 
 

Analytic Services creates blocks that contain the Budget values for each corresponding Actual block that already exists. After the DATACOPY commands are finished, the remaining part of the script changes the values.

Using DATACOPY works well in the following circumstances:

Caution: DATACOPY creates the new blocks with identical values in ALL cells from the source blocks. If the formula only performs on a portion of the block, these copied cells will remain at the end of calculation, potentially resulting in unwanted values.

Using SET CREATENONMISSINGBLK to Calculate All Potential Blocks

If you are concerned about unwanted values, instead of using the DATACOPY approach described above, you can use the SET CREATENONMISSINGBLK ON calculation command. The SET CREATENONMISSINGBLK ON calculation command calculates all potential blocks in memory and then stores only the calculated blocks that contain data values.

The following example script demonstrates using the SET CREATENONMISSINGBLK ON calculation command to create budget sales and expense data from existing actual data. Sales and Expenses are members in the dense Measures dimension. Budget and Actual are members in the sparse Scenario dimension.

FIX(Budget)
SET CREATENONMISSINGBLK ON
      (Sales = Sales -> Actual * 1.1;
       Expenses = Expenses -> Actual * .95;)
ENDFIX 
 

The SET CREATENONMISSINGBLK calculation command can be useful when calculating values on dense or sparse dimensions. For additional information about this command, see the Technical Reference.

Note: If the Create Blocks on Equations setting for sparse dimensions is ON, the SET CREATENONMISSINGBLK ON temporarily overrides the Create Blocks on Equations setting until a SET CREATENONMISSINGBLK OFF command is encountered or the calculation script is completed. For more information about the Create Blocks on Equations setting, see Non-Constant Values Assigned to Members in a Sparse Dimension or the SET CREATEBLOCKONEQ calculation command in the Technical Reference.

The advantage to using the SET CREATENONMISSINGBLK command is that, when applied on dense members, only data cells that are affected by the member formula are saved. The disadvantage is that too many potential blocks may be materialized in memory, possibly affecting calculation performance. When you use this command, limit the number of potential blocks; for example, by using FIX to restrict the scope of the blocks to be calculated.

For additional information about using the SET CREATENONMISSINGBLK ON calculation command, see the Technical Reference.

Writing Calculation Scripts for Partitions

A partitioned application can span multiple servers, processors, or computers. For a comprehensive discussion of partitioning, see Designing Partitioned Applications and Creating and Maintaining Partitions.

You can achieve significant calculation performance improvements by partitioning applications and running separate calculations on each partition.

However, when you use partitioning, you need to perform both of the following tasks:

Controlling Calculation Order for Partitions

You need to calculate databases in a specific order to ensure that Analytic Services calculates the required results. For example, consider the following partitions in which you view information from the West, Central, and East databases transparently from the Corporate database.

Figure 200: Calculating Partitions

West, Central, and East contain only actual values. Corporate contains actual and budgeted values. Although you can view the West, Central, and East data in the Corporate database, the data exists only in the West, Central, and East databases; it is not duplicated in the Corporate database.

Therefore, when Analytic Services calculates Corporate, it needs to take the latest values from West, Central, and East. To obtain the required results, you need to calculate West, Central, and East before you calculate Corporate.

Reviewing the Process for Creating Calculation Scripts

Use this process to create a calculation script:

  1. Create a new calculation script or open an existing calculation script.

    See "Creating Scripts" or "Opening Scripts" in Essbase Administration Services Online Help.

  2. Enter or edit the contents of the calculation scripts.

    In the Essbase Administration Services Online Help, see "About Calculation Script Editor." This may include some or all of the following tasks:

  3. Validate the calculation script.

    See Checking Syntax and "Checking Script Syntax" in the Essbase Administration Services Online Help.

  4. Save the calculation script.

    See Saving Calculation Scripts and "Saving Scripts" in the Essbase Administration Services Online Help.

  5. Execute the calculation script.

    See Executing Calculation Scripts, Checking the Results of Calculations, and "Executing Calculation Scripts" in the Essbase Administration Services Online Help.

  6. If necessary, perform other operations on the calculation script.

    In the Essbase Administration Services Online Help, see the following topics:

For more information on calculation scripts, see Understanding Calculation Scripts.

Checking Syntax

Analytic Services includes a syntax checker that tells you about any syntax errors in a calculation script. For example, Analytic Services tells you if you have typed a function name incorrectly. The syntax checker cannot tell you about semantic errors in a calculation script. Semantic errors occur when a calculation script does not work as you expect. To find semantic errors, always run the calculation, and check the results to ensure they are as you expect.

Analytic Services displays the syntax checker results in the messages panel in Essbase Administration Services Console. If Analytic Services finds no syntax errors, that is indicated in the messages panel. One error is displayed at a time.

If Analytic Services finds one or more syntax errors, it usually displays the number of the line that includes the error and a brief description of the error. For example, if you do not include a semicolon end-of-line character at the end of a calculation script command, Analytic Services displays a message similar to the following:

Error: line 1: invalid statement; expected semicolon 
 

When you reach the first or last error, Analytic Services displays the following message:

No more errors 
 

To check the syntax of a calculation script in Calculation Script Editor, see "Checking Script Syntax" in the Essbase Administration Services Online Help.

Saving Calculation Scripts

Calculation scripts created using Administration Services are given a .csc extension by default. If you run a calculation script from Administration Services or from Essbase Spreadsheet Services, it must have a .csc extension. However, a calculation script is a text file, and you can use MaxL or ESSCMD to run any text file as a calculation script.

A calculation script can also be a string defined in memory. You can access this string via the API on an Analytic Services client or an Analytic Server. Thus, from dialog boxes, you can dynamically create a calculation script that is based on user selections.

You can save a calculation script in the following locations:

To save a calculation script using Calculation Script Editor, see "Saving Scripts" in Essbase Administration Services Online Help.

Executing Calculation Scripts

Before you can execute a calculation script in Administration Services, you must save it as an object on the Analytic Server, on a client computer, or on a network. See Saving Calculation Scripts.

When you use Administration Services to execute a calculation script, you can execute the calculation in the background so that you can continue working as the calculation processes. You can then check the status of the background process to see when the calculation has completed. For more information, see "Executing Calculation Scripts" in Essbase Administration Services Online Help.

To execute a calculation script, use any of the following methods:


Tool
Topic
Location

Administration Services

Executing Calculation Scripts

Essbase Administration Services Online Help

MaxL

execute calculation

Technical Reference

ESSCMD

RUNCALC

Technical Reference

Essbase Spreadsheet Add-in

ESSBASE > CALCULATION

Essbase Spreadsheet Add-in User's Guide

Essbase Spreadsheet Services

Calculating with a Script

Essbase Spreadsheet Services Online Help



Checking the Results of Calculations

After you execute a calculation script, you can check the results of the calculation in the Spreadsheet Add-in or other appropriate tool.

When you execute a calculation using Administration Services or Spreadsheet Services, you can view the calculation messages in the application log. When you use ESSCMD to execute a calculation script, you can view the messages in the ESSCMD window. When you use MaxL or ESSCMD to execute a calculation script, Analytic Services displays the messages to the standard output (command-line window), depending on the level of messages you have set to display in MaxL Shell or ESSCMD.

To display the application log, see Viewing the Analytic Server and Application Logs.

Analytic Services provides the following information:

You can use these messages to understand how the calculation is performed and to tune it for the next calculation. To display more detailed information, you can use the SET MSG SUMMARY, SET MSG DETAIL, and SET NOTICE commands in a calculation script. For more information, see Specifying Global Settings for a Database Calculation.

Copying Calculation Scripts

You can copy calculation scripts to applications and databases on any Analytic Server, according to your permissions. You can also copy scripts across servers as part of application migration.

To copy a calculation script, use any of the following methods:


Tool
Topic
Location

Administration Services

Copying Scripts

Essbase Administration Services Online Help

MaxL

create calculation as

Technical Reference

ESSCMD

COPYOBJECT

Technical Reference





Hyperion Solutions Corporation link