Developing Calculation Scripts

This chapter explains how to develop calculation scripts and how to use them to control how Essbase calculates a database. It provides some examples of calculation scripts, which you may want to adapt for your own use. For more examples, see Examples of Calculation Scripts.

This chapter includes the following sections:

For information on developing formulas, see Developing Formulas.

Using a Calculation Script

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.

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

Figure 429: Calculation Script Editor

You can use a calculation script to specify exactly how you want Essbase 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.

For most database calculations, a default calculation provides the required results. However, in certain cases, you may need to write a calculation script to control how Essbase calculates a database.

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

Creating a Calculation Script

This section provides a step-by-step example of creating and saving a calculation script.

For detailed information on creating formulas and obtaining the required calculation results, consider all the information in Calculating Data.

This example is based on the Sample Basic database, which is supplied with the Essbase server installation. This example increases all budget values by 5%. The example assumes that you have Essbase Spreadsheet Add-in installed on your computer.

To create the example calculation script using Application Manager:

  1. Start Application Manager and connect to the OLAP Server.
  2. Select the Sample application and the Basic database, and click the Calc Scripts button, .
  3. Figure 430: Application Desktop Window

    If you do not have Sample Basic installed, contact the Essbase administrator.

    If another user has Sample Basic open and locked, you can clear "Lock file" in the bottom-right corner of the application desktop window. However, if you clear "Lock file", you cannot save your work.

  4. Click New to open Calc Script Editor.
  5. Type the following calculation script. This script increases the expense values of Budget -> Marketing by 5%.
  6. FIX(Budget)
    Marketing = Marketing * 1.05;
    ENDFIX 
    

    Figure 431: Simple Calculation Script

    For more information on the FIX command, see Using the FIX Command and the Technical Reference in the docs directory.

  7. Click the Check Syntax button, , to verify that the syntax of the formula you entered is correct.
  8. The message "No errors" should be displayed at the bottom of Calc Script Editor.

  9. Click the Save button, , to save the calculation script.
  10. Type Mycalc1 for the calculation script object name, and save the calculation script on the server (the default).
  11. Close the Calc Script Editor window.

In Figure 432 Mycalc1 is displayed in the list of calculation scripts for Sample Basic.

Figure 432: Application Desktop Window Showing a Calculation Script

Calculating Sample Basic Data

You are now ready to calculate the Sample Basic database, increasing the Budget values by 5%. However, first take a look at the Sample Basic Budget values before running the calculation.

To review the Budget values of Sample Basic:

  1. Open Essbase Spreadsheet Add-in, and select Essbase > Connect to connect to Sample Basic.
  2. This example assumes that you have not changed the default Essbase Spreadsheet Add-in Retrieval Options. For more information, see the Essbase Spreadsheet Add-in User's Guide.

  3. Select Essbase > Retrieve.
  4. Essbase displays the data value from the top level of each dimension.

    Figure 433: Essbase Spreadsheet Add-in Showing Initial Data

  5. Double-click Scenario to display its members.
  6. Select Budget and select Essbase > Keep Only.
  7. Essbase displays the Budget values.

  8. Double-click Year.
  9. Essbase displays the Budget values for each quarter in the year.

    Figure 434: Essbase Spreadsheet Add-in Showing Retrieved Budget Data for Each Quarter

  10. Double-click Measures, then Profit, and then Total Expenses to display the Marketing member.
  11. Select Marketing and select Essbase > Keep Only.
  12. Essbase displays the Budget -> Marketing values. These are the values that will increase by 5%.

    Figure 435: Essbase Spreadsheet Add-in Showing Retrieved Data for Budget -> Marketing

Running a Calculation Script

Now you are ready to run the Mycalc1 calculation script, which increases the Budget -> Marketing values by 5%.

To run the Mycalc1 calculation script:

  1. Minimize but do not close the Essbase Spreadsheet Add-in window.
  2. In Application Manager, connect to OLAP Server, if you are not already connected.
  3. Select the Sample application and the Basic database, and click the Calc Scripts button, .
  4. Figure 436: Application Desktop Window Showing calculation Script

  5. Select Mycalc1 and click Run.
  6. When Essbase prompts you to select a database, ensure that Sample Basic is selected in the Select Database dialog box. Click OK.
  7. Essbase calculates the database.

    Figure 437: Calculating Message Box

Checking a Calculation

After Essbase finishes a calculation, you can check the dimensions calculated and the calculation time in the application log.

To review the application log:

  1. In Application Manager, select the Application > View Event Log menu command.
  2. In the View Log File dialog box, select Date to view the entries for the current date.
  3. When Essbase displays the application log, scroll to the end of the file to see the entries for a calculation. The entries will be similar to the ones shown in Figure 438.
  4. Figure 438: Application Log Showing Calculation Messages

    From the entries, you can see that Essbase calculated data values for the Marketing member on the Measures dimension, fixing on the Budget values. Essbase calculated the database in 19.989 seconds.

    Note: Check the "fixed members [ ]" part of the message to ensure that all members you fixed on were actually included in the calculation.

    These entries are an example of the default level of messages that Essbase provides. If required, you can display more detailed calculation messages in the application log by using the SET MSG command. For more information, see the Technical Reference in the docs directory.

  5. Close the application log viewer window.
  6. Tip: You can also review the application log using the Log Analyzer window in Administration Services. For more information, see Essbase Administration Services Online Help.

To view newly calculated data:

  1. Maximize the Essbase Spreadsheet Add-in window.
  2. The pre-calculation data values should still be displayed. If the pre-calculation data values are not displayed, repeat the steps in Calculating Sample Basic Data.

  3. Select Essbase > Retrieve to display the new data values as shown in Figure 439.

Figure 439: Essbase Spreadsheet Add-in Showing Retrieved New Data

As you can see, the data values have increased by 5%. The calculation is successful. If required, you can reload the default data into Sample Basic. See Building Dimensions and Loading Data.

Building a Calculation Script in the Calc Script Editor

You use either Calc Script Editor in Application Manager or Calculation Script Editor in Administration Services to build a calculation script. You can type the calculation script directly into the text area of Calc Script Editor, or you can use the user interface features of Calc Script Editor to build the calculation script.

Calculation scripts are ASCII text. If desired, you can create a calculation script in the text editor of your choice and paste it into Calc Script Editor.

Essbase provides a flexible set of commands that you can use to control how a database is calculated. You can construct calculation scripts from commands and formulas. Several types of commands, such as these, are discussed in the following sections:

Implementing Outline Calculations

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 in the docs directory.

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 (see Calculating Time Series Data)

CALC AVERAGE

All members tagged as First on the dimension tagged as accounts (see Calculating Time Series Data)

CALC FIRST

All members tagged as Last on the dimension tagged as accounts (see Calculating Time Series Data)

CALC LAST

Currency conversions (see Designing and Building Currency Conversion Applications)

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 in the docs directory.

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. See Controlling the Flow of 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. See Using Substitution Variables.

Calculation
Command

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 in the docs directory.

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.

Note: For a complete list of commands, see the Technical Reference in the docs directory.

Calculation
Command

To specify how Essbase treats #MISSING values during a calculation

SET AGGMISSG

To adjust the default calculator cache size

SET CACHE

To optimize the calculation of large, flat database outlines (see Performance for Database Outlines with Two or More Flat Dimensions)

SET CALCHASHTBL

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

SET FRMLBOTTOMUP

To display messages to trace a calculation.

SET MSG

SET NOTICE

To turn on and turn off Intelligent Calculation (see Optimizing with Intelligent Calculation)

SET UPDATECALC

To control how Essbase marks data blocks for the purpose of Intelligent Calculation (see Optimizing with Intelligent Calculation)

SET CLEARUPDATESTATUS

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

SET LOCKBLOCK

For currency conversions, to restrict aggregations to parents that have the same defined currency (see Designing and Building Currency Conversion Applications)

SET UPTOLOCAL



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

For example, consider the following calculation script:

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

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

Now, consider this calculation script:

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

Essbase calculates member combinations for Qtr1 with SET AGGMISSG (aggregate missing values) turned on. Essbase 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 aggregating missing values, see the SET AGGMISSG command in the Technical Reference in the docs directory. For more information on calculation passes, see Optimizing Calculations.

Adding Comments

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

To include a comment, start the comment with /* and end the comment with */. For example, consider the following comment:

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

Composing 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 Calc Script Editor syntax checker to check the syntax. For more information, see Checking Syntax.

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

Opening Calc Script Editor

Use Calc Script Editor in Application Manager or Calc Script Editor in Administration Services to create a new calculation script or open an existing calculation script.

For information on opening an existing calculation script, see Changing a Calculation Script.

To open Calc Script Editor using Application Manager:

  1. Open Application Manager and connect to the OLAP Server.
  2. In the application desktop server window, select the desired application and database.
  3. Click the Calculation Scripts button, .
  4. Essbase displays a list of all the calculation scripts associated with the application and database that you selected.

    Figure 440: Application Desktop Window

  5. To create a new calculation script, click New.
  6. To open an existing calculation script, select it in the Calc Scripts list and click Open.

    Essbase opens Calc Script Editor, shown in Figure 441.

    Figure 441: Calc Script Editor

Adding a Calculation Script

You can use Application Manager to add a new calculation script.

To add a calculation script:

  1. Open Application Manager and connect to the OLAP Server.
  2. In the application desktop server window, select the application and database with which you want to associate the new calculation script.
  3. Click the Calc Scripts button, , and then click New.
  4. Alternatively, from the Application Manager menu, select File > New > Calc Script.

Essbase opens Calc Script Editor. You can now build a calculation script. Essbase prompts you to name a calculation script when you save it. See Saving a Calculation Script.

Changing a Calculation Script

To change a calculation script, open it in Calc Script Editor. How you do that depends on where the calculation script is stored.

To open a calculation script that is on the current server:

  1. Open Application Manager and connect to the OLAP Server.
  2. In the application desktop server window, select the application and database that contains the calculation script.
  3. The following example shows the application desktop server window for an Essbase server called Aspen. The Sample Basic database is selected as shown in Figure 442.

    Figure 442: Application Desktop Server Window

  4. Click the Calc Scripts button, .
  5. Essbase displays the calculation script files (.CSC files) stored in the \ARBORPATH\app\appname\dbname directory on the server computer, where ARBORPATH is the directory in which you installed Essbase and appname and dbname are the current application and database.

    For example, assuming that the Essbase install directory is c:\essbase, if you select Sample Basic, Essbase displays the .CSC files in the c:\essbase\app\sample\basic directory.

  6. In the Calc Scripts list, select the calculation script you want to modify.
  7. Click Open.
  8. Essbase opens Calc Script Editor.

You can now edit the calculation script.

To open a calculation script that is on a different server:

  1. Open Application Manager and connect to the OLAP Server.
  2. Ensure that the focus is on the application desktop server window.
  3. From the Application Manager menu, select File > Open.
  4. Essbase displays the Open Server Object dialog box as shown in Figure 443.

    Figure 443: Open Server Object Dialog Box

  5. Click Connect to connect to the other server, and click OK.
  6. In the Open Server Object dialog box, select the application and database that contain the calculation script.
  7. In the Objects list, select the required calculation script, and click OK.
  8. Essbase displays the calculation script in Calc Script Editor.

To open a calculation script that is on a client computer:

  1. Open Application Manager and connect to the OLAP Server.
  2. In the application desktop client window, select the application and database that contains the calculation script.
  3. Click the Calc Scripts button, .
  4. Essbase displays the calculation script files (.CSC files) stored in the \ARBORPATH\CLIENT\appname\dbname directory on your client computer, where ARBORPATH is the directory in which you installed Essbase, and appname and dbname are the current application and database on your client computer.

    For example, assuming that the Essbase install directory is C:\ESSBASE, if you have an application called MYAPP01 and a database called MYDB01 on your client computer, Essbase displays the .CSC files in the C:\ESSBASE\CLIENT\MYAPP01\MYDB01 directory on your client computer.

    In the example shown in Figure 444, there are three calculation scripts already created for the MYDB01 database.

    Figure 444: Application Desktop Client Window

  5. In the Calc Scripts list, select the required calculation script.
  6. Click Open.
  7. Essbase opens Calc Script Editor. You can now edit the calculation script.

To open a calculation script that is on a client computer but is not saved as a Essbase object:

  1. From the Application Manager menu, select File > Open.
  2. Essbase displays the Open Client Object dialog box as shown in Figure 445.

    Figure 445: Open Client Object Dialog Box

  3. Click File System to connect to the other server.
  4. Essbase displays the Open Client File dialog box.

  5. Select the file that contains the required calculation script, and click OK.

Essbase displays the calculation script in Calc Script Editor.

Saving a Calculation Script

You can save a calculation script as either of the following:

If you want other users to have access to the calculation script, you need to save it on the Essbase server. If you save a calculation script on your client computer, other users do not have access to the calculation script. While you are developing a calculation script, you may want to save it on your client computer. Then move the completed script to the Essbase server.

When you save a calculation script from Calc Script Editor, by default Essbase associates it with the current application and database.

Calc scripts created using Application Manager are given a .CSC extension by default. If you run a calculation script from Application Manager or from Essbase Spreadsheet Add-in, it must have a .CSC extension. However, a calculation script is an ASCII file, and you can use MaxL or ESSCMD to run any ASCII 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 the Essbase client or Essbase server. Thus, from dialog boxes, you can dynamically create a calculation script that is based on user selections.

To save a calculation script as an object on the Essbase OLAP Server:

  1. Open Application Manager and connect to the OLAP Server.
  2. Select an application and database in the application desktop window.
  3. Click the Calc Script Editor button and select an existing calculation script, or create a new calculation script, and display it in the Calc Script Editor.
  4. In the Calc Script Editor, click the Save button, .
  5. Essbase displays the Save Server Object dialog box as shown in Figure 446.

    Figure 446: Save Server Object Dialog Box

  6. Associate the calculation script with an application or database.
  7. To associate the calculation script with an application and all the databases within the application: First, in the Application list, select the required application. Then, in the Database list, select (all dbs) to associate the calculation with all databases.

    To associate the calculation script with a database: First, in the Application list, select the application containing the database. Then, in the Database list, select the required database.

  8. In the Object Name text box, type the name that you want to give the calculation script; for example, CalcOne as shown in Figure 447. You can type up to 8 alphanumeric characters.
  9. Figure 447: Save Server Object Dialog Box

  10. Click OK.
  11. Essbase saves the calculation script as a calculation script object on the Essbase server.

Calculation script objects associated with an application are saved in the \arborpath\app\appname directory on the Essbase server computer. Calculation script objects associated with a database are saved in the \arborpath\app\appname\dbname directory on the Essbase server computer. ARBORPATH is the Essbase install directory, and appname and dbname are the application and database with which you have associated the calculation script.

For example, consider the following:

To save a calculation script as an object on your client computer:

  1. Open Application Manager and connect to the OLAP Server.
  2. Select an application and database in the application desktop window.
  3. Click the Calc Script Editor button and select an existing calculation script, or create a new calculation script, and display it in the Calc Script Editor.
  4. In the Calc Script Editor, click the Save button, .
  5. If the calculation script is new, Essbase displays either the Save Server Object dialog box or the Save Client Object dialog box, depending on whether you opened Calc Script Editor from the application desktop server window or the client window.

    If Essbase displays the Save Server Object dialog box, under Location, select Client. The Save Client Object dialog box shown in Figure 448 replaces the Save Server Object dialog box.

    Figure 448: Save Client Object Dialog Box

  6. Associate the calculation script with an application or database.
  7. To associate the calculation script with an application on your client computer and all the databases within the application: First, in the Application list, select the required application. Then, in the Database list, select (all dbs) to associate the calculation script with all databases.

    To associate the calculation script with a database: First, in the Application list, select the application containing the database. Then, in the Database list, select the required database.

  8. In the Object Name text box, type the name that you want to give the calculation script. You can type up to 8 alphanumeric characters.
  9. Click OK.
  10. Essbase saves the calculation script as a calculation script object on your client computer.

Calculation script objects associated with an application are saved in the \arborpath\client\appname directory on the Essbase client computer. Calculation script objects associated with a database are saved in the \arborpath\client\appname\dbname directory on the Essbase client computer. ARBORPATH is the Essbase install directory, and appname and dbname are the application and database with which you associate the calculation script.

For example, consider the following factors:

To save a calculation script in the file system of a client computer:

  1. Open Application Manager and connect to the OLAP Server.
  2. Select an application and database in the application desktop window.
  3. Click the Calc Script Editor button and select an existing calculation script, or create a new calculation script, and display it in the Calc Script Editor.
  4. In Calc Script Editor, click the Save button, .
  5. If the calculation script is new, Essbase displays either the Save Server Object dialog box or the Save Client Object dialog box, depending on whether you opened Calc Script Editor from the application desktop server window or the client window.

    If Essbase displays the Save Server Object dialog box, under Location, select Client. The Save Client Object dialog box shown in Figure 449 replaces the Save Server Object dialog box.

  6. In the Save Client Object dialog box, click File System.
  7. Essbase displays the Save Client File dialog box.

  8. Enter the required directory and file name, and click OK.
  9. Essbase saves the calculation script in the directory you specified.

To copy a calculation script from a client computer to the OLAP Server:

  1. Open Application Manager and connect to the OLAP Server.
  2. Select an application and database in the application desktop window.
  3. Click the Calc Script Editor button and select an existing calculation script, or create a new calculation script, and display it in the Calc Script Editor.
  4. From the Application Manager menu, select File > Save As.
  5. Essbase displays the Save Client Object dialog box as shown in Figure 449.

    Figure 449: Save Client Object Dialog Box

  6. Under Location, select Server.
  7. The Save Server Object dialog box shown in Figure 450 replaces the Save Client Object dialog box.

    Figure 450: Save Server Object Dialog Box

  8. Select the application or database with which you want to associate the calculation script. For more information, see Saving a Calculation Script.
  9. In the Object Name text box, type the name that you want to give the calculation script. You can type up to 8 alphanumeric characters.
  10. Click OK.
  11. Essbase saves the calculation script on the Essbase server. For more information, seeSaving a Calculation Script.

Running a Calculation Script

You can run a calculation script from any of the following:

If you run a calculation script from the Application Manager, you can run it on your Essbase client computer or on the OLAP Server.

When you run a calculation script from Application Manager or from Essbase Spreadsheet Add-in, you can view the calculation messages in the application log. When you use MaxL or ESSCMD to run a calculation script, Essbase displays the messages in the ESSCMD window. To display the application log, select Application > View Event Log from the Application Manager menu.

Essbase displays both of the following:

You can use these messages to tune a database during 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.

You can run a calculation script from the Application Manager desktop or from the Application Manager menu.

Note: Before you can run a calculation script in Application Manager, you must save it as a calculation script object on the Essbase server or on your client computer. See Saving a Calculation Script. To run a calculation script saved as an object on your client computer, you must run the calculation script from the desktop.

To run a calculation script using Application Manager:

  1. If the calculation script is saved on the Essbase server, open Application Manager and select the application desktop server window.
  2. If the calculation script is saved on your client computer, open the application desktop client window.

  3. In the application desktop server window or the client window, select the application and database that contains the calculation script you want to run.
  4. Click the Calc Scripts button, , to display the calculation scripts associated with the application and database that you selected.
  5. Figure 451 shows the application desktop server window for a server called Aspen.

    Figure 451: Application Desktop Server Window

  6. In the Calc Scripts list, select the calculation script that you want to run, and click Run.
  7. Essbase displays the Select Database dialog box as shown in Figure 452.

    Figure 452: Select Database Dialog Box

  8. Select the Essbase server, application, and database against which you want to run the calculation script.
  9. If you are not currently connected to the server, click Connect.

  10. Click OK.
  11. Essbase runs the calculation script against the database that you selected.

To run a calculation script from the menu:

  1. In the application desktop server window, select the application and database that contains the calculation script that you want to run.
  2. From the Application Manager, select the Database > Calculate menu command.
  3. Essbase displays the Calculate Database dialog box as shown in Figure 453.

    Figure 453: Calculate Database Dialog Box

  4. In the Calc Scripts list, select the calculation script that you want to run and click OK. Only scripts to which you have security access are displayed in the list.
  5. Essbase runs the calculation script against the database you selected in the application desktop server window.

Tip: You can execute a calculation script outside Application Manager.

Tool
Instructions
For More Information

Administration Services

Calculation Script Editor

Essbase Application Manager Online Help

MaxL

execute calculation

The Technical Reference in the docs directory

Essbase Spreadsheet Add-in

Essbase > Calculation

The Essbase Spreadsheet Add-in User's Guide

ESSCMD

RUNCALC

The Technical Reference in the docs directory



Printing a Calculation Script

You can print a calculation script from Calc Script Editor.

To print a calculation script:

  1. Open the Application Manager and connect to the OLAP Server.
  2. From the application desktop, select the application and database that contains the calculation script.
  3. Double-click the calculation script to open it in the Calc Script Editor.
  4. Select the File > Print menu command, or click the Print button, .
  5. Essbase displays the Print Calc Script dialog box.

  6. If you want to print page numbers on a calculation script, check Page Numbers.
  7. Click Print.

Deleting a Calculation Script

How you delete a calculation script depends on where it is saved.

To delete a calculation script saved as an object on the Essbase server or on a client computer:

  1. Open the Application Manager and connect to the OLAP Server.
  2. In the application desktop server or client window, select the application and database with which the calculation script is associated.
  3. Click the Calc Scripts button, .
  4. Essbase displays a list of all the calculation scripts associated with the application and database that you chose.

    In Figure 454, the calculation script, CalcOne, is shown as associated with Sample Basic.

    Figure 454: Application Desktop Server Window

  5. In the Calc Scripts list, select the calculation script that you want to delete.
  6. From the Application Manager menu, select File > Delete.
  7. Essbase displays a Confirm Delete message box.

  8. Click Yes to delete the calculation script.

To delete a calculation script saved in the file system of a client computer, delete the calculation script file by using the client computer's file system. You cannot delete the file using Application Manager.

To undo the last action: in Calc Script Editor, select Edit > Undo, or click the button.

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 do both of the following:

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; 

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

To define a formula in a calculation script, use Calc Script Editor. For example:

Expenses = Payroll + Marketing + Misc; 

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.

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

Basic Equations

You can define basic equations in a calculation script as follows:

Member = mathematical operation;

where Member is a member name from the database outline, and mathematical operation is any valid mathematical operation.

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

Margin = Sales - COGS; 

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; 

For more information on the nature of multidimensional calculations, see Multidimensional Concepts.

Conditional Equations

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

For example:

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

Essbase cycles through the database and performs the following calculations:

  1. The IF statement checks to see if the value of Sales for the current member combination is greater than 100.
  2. If Sales is greater than 100, Essbase subtracts the value in COGS from the value in Sales, multiplies the difference by 2, and places the result in Profit.
  3. If Sales is less than or equal to 100, Essbase subtracts the value in COGS from the value in Sales, multiplies the difference by 1.5, and places the result in Profit.

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 do both of the following:

Consider the interdependent formula discussed earlier. If you place the formula in a calculation script, you construct it as follows:

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

The whole of the formula is enclosed in parentheses and is associated with the Opening Inventory member, as follows:

"Opening Inventory" (IF(...)...) 

Inserting Text and Operators in a Calculation Script

You can type text and operators directly into the text area of Calc Script Editor, or you can use the toolbar buttons to add the text and operators. You can also cut, copy, and search for text in Calc Script Editor.

To type text in Calc Script Editor:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database that contains the calculation script.
  3. Double-click the calculation script to open it in the Calc Script Editor.
  4. In the Calc Script Editor, click in the text area below the toolbar.
  5. Type the appropriate text.
  6. Text is displayed at the cursor position as you type as shown in Figure 455.

    Figure 455: Calc Script Editor Showing Calculation Script

To insert an equal (=) sign in Calc Script Editor:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database that contains the calculation script.
  3. Double-click the calculation script to open it in the Calc Script Editor.
  4. Place the cursor where you want to insert the equal sign (=).
  5. Type = or click the button.

To insert a mathematical operator (+, -, X, /, %) in Calc Script Editor:

  1. Place the cursor where you want to insert the mathematical operator.
  2. Type the appropriate operator or click one of the following toolbar buttons:

For example, to insert an addition operator (+), place the cursor where you want to insert the addition (+) operator, and type + or click the button.

To insert the cross-dimensional operator ( -> ) in Calc Script Editor:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database that contains the calculation script.
  3. Double-click the calculation script to open it in the Calc Script Editor.
  4. Place the cursor where you want to insert the cross-dimensional operator.
  5. Type a - (hyphen) followed by a > (greater than symbol), or click the button.

For more information on the cross-dimensional operator, see Developing Formulas.

To insert the semicolon formula end-of-line character (;) in Calc Script Editor:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database that contains the calculation script.
  3. Double-click the calculation script to open it in the Calc Script Editor.
  4. Place the cursor at the end of the formula.
  5. Type a ; (semicolon), or click the button.

To insert a function or operator in Calc Script Editor:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database that contains the calculation script.
  3. Double-click the calculation script to open it in the Calc Script Editor.
  4. Place the cursor where you want to insert the function.
  5. Select Formula > Paste Function, or click the button.
  6. Essbase displays the Function Templates dialog box.

  7. In the Categories list, select the function category.
  8. For example, to insert the @VAR function, select Math.

  9. In the Templates list, select the required function or operator.
  10. In the example shown in Figure 456, scroll down the list and select @VAR. Essbase displays the function or operator and the default arguments below the Categories list.

    Figure 456: Function Templates Dialog Box

  11. If required, check Insert Arguments to insert default, temporary arguments for the function.
  12. Click OK.
  13. Essbase inserts @VAR at the cursor position as shown in Figure 457.

    Figure 457: Calc Script Editor With @VAR Function Inserted

    If you checked Insert Arguments, Essbase inserts @VAR and default, temporary arguments as shown in Figure 458. You can then type over the temporary arguments with the correct arguments.

    Figure 458: Calc Script Editor With @VAR Function and Arguments Inserted

To cut text in Calc Script Editor:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database that contains the calculation script.
  3. Double-click the calculation script to open it in the Calc Script Editor.
  4. Select the text that you want to cut.
  5. Select Edit > Cut, click the button, or press Ctrl + X.

To copy text in Calc Script Editor:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database that contains the calculation script.
  3. Double-click the calculation script to open it in the Calc Script Editor.
  4. Select the text that you want to copy.
  5. Select Edit > Copy, click the button, or press Ctrl + C.

To paste text in Calc Script Editor:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database that contains the calculation script.
  3. Double-click the calculation script to open it in the Calc Script Editor.
  4. Select the text that you want to paste.
  5. Select Edit > Paste, click the button, or press Ctrl + V.

To find and replace text in Calc Script Editor:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database that contains the calculation script.
  3. Double-click the calculation script to open it in the Calc Script Editor.
  4. Select Edit > Find.
  5. Essbase displays the Find dialog box as shown in Figure 459.

    Figure 459: Calc Script Editor Find Dialog Box

  6. In the Find what text box, type the characters you want to search for, and click Find Next.

To do a case-sensitive search:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database that contains the calculation script.
  3. Double-click the calculation script to open it in the Calc Script Editor.
  4. In the Find dialog box, check Match case.
  5. For example, to search for Margin but not margin, type Margin in the Find what text box and check Match case.

  6. Click Find Next.

Associating a Calculation Script with a Database

If you want to insert member names in a calculation script by selecting them within Calc Script Editor, you need to associate the calculation script with the database outline that contains the members.

To associate a calculation script with a database outline:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database that contains the calculation script.
  3. Double-click the calculation script to open it in the Calc Script Editor.
  4. Click the button or, from the Calc Script Editor menu of the Application Manager, select Options > Associate Outline.
  5. Essbase displays the Associate Client Outline Object or the Associate Server Outline Object dialog box. The latter is shown in Figure 460.

    Figure 460: Associate Server Outline Object Dialog Box

  6. Do one of the following:
  7. In the Server, Application, and Database lists, select the server, application, and database that contain the outline that you want to associate with a calculation script.
  8. In the Objects list, select the database outline.
  9. Click OK.
  10. Essbase displays the dimension names of the associated outline in the Dimensions list. You can now insert members from this list. See Associating a Calculation Script with a Database.

Essbase associates the calculation script with the database outline only while you are editing the calculation script. When you close Calc Script Editor, Essbase cancels the association. If you want to insert members from the database outline in the future, you need to re-associate the outline with the calculation script.

To insert the name of a dimension in a calculation script:

  1. Associate the database outline that contains the dimensions you want to insert. See Associating a Calculation Script with a Database.
  2. In the Calc Script Editor of the Application Manager, place the cursor where you want to insert the member name.
  3. In the Dimensions list, select the dimension that contains the member you want to insert in a formula.
  4. The name of the dimension is displayed in the Members list. If a button is displayed to the left of the dimension name, the dimension has children. Figure 461 shows the Scenario dimension in the Sample Basic database.

    Figure 461: Inserting Dimensions and Members In a Calculation Script

    If you want to insert the name of the dimension in a formula, click the dimension name in the Members list. Essbase inserts the dimension name at the cursor position.

To expand and collapse a member branch:

  1. To display a member's children, in the Members list, double-click the button next to the member name.
  2. The button changes to a button as shown in Figure 462.

    Figure 462: Expanding a Member Branch

  3. To collapse the member branch, double-click the button.
  4. Essbase does not display the member's children. The button changes to a button as shown in Figure 463.

    Figure 463: Collapsing a Member Branch

To search for a member:

  1. In the Dimensions list, select the dimension in which you want to search for a member.
  2. For example, select the Measures dimension from the Sample Basic database.

  3. Click Find Member.
  4. Essbase displays the Find dialog box as shown in Figure 464.

    Figure 464: Opening the Find Dialog Box

  5. In the Find what text box, type the characters that you want to search for.
  6. For example, to search for the Marketing member in the Measures dimension, enter market as shown in Figure 465.

    Figure 465: Searching For Members

    1. To make the search case-sensitive, check Match case.
    2. To search for whole words only, check Match whole word only.
    3. For example, to search for Margin, but not Margin % in the Sample Basic database, type margin, and check Match whole word only.

  7. Click Find Next.
  8. Essbase finds and selects the appropriate member as shown in Figure 466.

    Figure 466: Finding A Specific Member

To expand a dimension to display all members in the Calc Script Editor of the Application Manager:

  1. In the Dimensions list, select the dimension for which you want to display all the members.
  2. For example, select the Product dimension in the Sample Basic database. Figure 467 shows all of the members under the Product dimension.

    Figure 467: Expanding a Dimension

  3. Click Expand All.
  4. In the Members list, Essbase displays all members in the dimension, an example of which is shown in Figure 468.

    Figure 468: Expanding a Dimension

To display and insert alias names in the Calc Script Editor of the Application Manager:

  1. Check Use Aliases.
  2. Essbase displays the alias names for the members. Figure 469 shows the Product dimension from the Sample Basic database.

    Figure 469: Displaying and Inserting Alias Names

  3. To select a different alias table, from the Alias Table list box, select the table.
  4. When you select a member from the Members list, Essbase inserts the alias name at the cursor position. If required, Essbase automatically encloses the alias name in double quotation marks (" ").

Checking Syntax

Essbase includes a syntax checker that tells you about any syntax errors in a calculation script. For example, Essbase 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.

To check the syntax of a calculation script in Calc Script Editor of the Application Manager:

Select Syntax > Check Syntax or click the button.

Essbase displays the syntax checker results at the bottom of the Calc Script Editor window. If Essbase finds no syntax errors, it displays the following message:

If Essbase finds one or more syntax errors, it 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, Essbase displays a message similar to the following:

To step through syntax errors in Calc Script Editor of the Application Manager:

Select Syntax > Next Error or Syntax > Previous Error.

When you reach the first or last error, Essbase displays the message:

Essbase maintains the list of error messages until you check the syntax again.

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:

Essbase always recalculates the data block that contains the formula, even if the data block is marked as clean for the purposes of Intelligent Calculation. For more information, 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 more information and examples, see Calculating a Series of Member Formulas and Calculating a Series of Dimensions.

When you run a calculation script, Essbase automatically displays the calculation order of the dimensions for each pass through the database. Thus, you can tell how many times Essbase has cycled through the database during the calculation.

Essbase displays these information messages in the ESSCMD window and in the application log. To display the application log, select Application > View Event Log from the Application Manager menu.

Calculating a Series of Member Formulas

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

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

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

Qtr1;
Qtr2;
Qtr3; 

or

(Qtr1;
Qtr2;
Qtr3;) 

However, the inappropriately placed parentheses in the following example cause Essbase to cycle through the database twice, 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 Essbase to cycle through the database only once:

CALC DIM(Year, Measures); 

However, the following syntax causes Essbase to cycle through the database twice. It cycles through once for each CALC DIM command:

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

Using Substitution Variables

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, Essbase replaces the substitution variable with the value you specified for the substitution variable.

You create and specify values for substitution values in Essbase Application Manager. For more information, see Creating Applications and Databases.

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.

The ampersand (&) character prefaces a substitution variable in a calculation script. Essbase treats any string that begins with a leading ampersand as a substitution variable, replacing the variable with its 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:

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

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

Clearing Data

You can use the CLEARDATA and CLEARBLOCK calculation commands to remove data values and data blocks from a database. You can use the CLEARBLOCK DYNAMIC command to remove blocks for Dynamic Calc And Store member combinations. For more information, see Dynamically Calculating Data Values.

When you use the CLEARBLOCK command, Essbase removes the entire contents of a block, including all the dense dimension members. Essbase removes the entire block, unless CLEARBLOCK is inside a FIX command on members within the block.

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 

When you use the CLEARDATA command, Essbase changes the values of the cells you specify to #MISSING. The data blocks are not removed.

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

CLEARDATA Actual -> Colas; 

You can use the FIX command with the CLEARDATA command to clear a subset of a database. If you want to clear an entire database, you can select the Clear Data command from the Database menu in Application Manager.

For more information on the CLEARBLOCK and CLEARDATA calculation commands, see the Technical Reference in the docs directory.

Copying Data

You can use the DATACOPY calculation command to copy data cells from one range to another range 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 more information on the FIX command, see the Technical Reference in the docs directory.

Calculating a Subset of a Database

You can calculate a subset of a database, which means that you can use different formulas to calculate separate sections of a database.

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

For more information, see Calculating Lists of Members and 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 Essbase recalculates the database as efficiently as possible using Intelligent Calculation. For more information on Intelligent Calculation, see Optimizing with Intelligent Calculation. For more information on the SET CLEARUPDATESTATUS command, see the Technical Reference in the docs directory.

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, Essbase generates a list of members before calculating the formula.

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

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, Essbase 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, Essbase retrieves the block for the specified sparse dimension member or members. Thus, I/O may be greatly reduced.

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

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

However, this calculation script causes Essbase 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 

For detailed information on using the FIX command, see the Technical Reference in the docs directory.

Writing Calculation Scripts for Partitions

A Essbase OLAP Server partitioned application can span multiple servers, processors, or computers. For more information on partitioning, see Designing Partitioned Applications and Building 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 do both of the following:

Controlling Calculation Order for Partitions

You need to calculate databases in a specific order to ensure that Essbase 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 470: 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 Essbase 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.




© 2002 Hyperion Solutions Corporation. All rights reserved.
http://www.hyperion.com