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.
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:
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:
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.
FIX(Budget) Marketing = Marketing * 1.05; ENDFIXFigure 431: Simple Calculation Script
For more information on the FIX command, see Using the FIX Command and the Technical Reference in the docs directory.
The message "No errors" should be displayed at the bottom of Calc Script Editor.
In Figure 432 Mycalc1 is displayed in the list of calculation scripts for Sample Basic.
Figure 432: Application Desktop Window Showing a Calculation Script
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:
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.
Essbase displays the data value from the top level of each dimension.
Figure 433: Essbase Spreadsheet Add-in Showing Initial Data
Essbase displays the Budget values.
Essbase displays the Budget values for each quarter in the year.
Figure 434: Essbase Spreadsheet Add-in Showing Retrieved Budget Data for Each Quarter
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
Now you are ready to run the Mycalc1 calculation script, which increases the Budget -> Marketing values by 5%.
To run the Mycalc1 calculation script:
Figure 436: Application Desktop Window Showing calculation Script
Essbase calculates the database.
After Essbase finishes a calculation, you can check the dimensions calculated and the calculation time in the application log.
To review the application log:
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.
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:
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.
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.
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:
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 |
---|---|
All members tagged as two-pass on the dimension tagged as accounts |
|
The formula applied to a member in the database outline, where membername is the name of the member to which the formula is applied |
|
All members tagged as Average on the dimension tagged as accounts (see Calculating Time Series Data) |
|
All members tagged as First on the dimension tagged as accounts (see Calculating Time Series Data) |
|
All members tagged as Last on the dimension tagged as accounts (see Calculating Time Series Data) |
|
Currency conversions (see Designing and Building Currency Conversion Applications) |
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 |
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.
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 |
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];
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 |
|
To optimize the calculation of large, flat database outlines (see Performance for Database Outlines with Two or More Flat Dimensions) |
|
To enable parallel calculation (see Using Parallel Calculation) |
|
To increase the number of dimensions used to identify tasks for parallel calculation (see Using Parallel Calculation) |
|
To optimize the calculation of sparse dimension formulas in large database outlines (see Optimizing Calculations) |
|
To turn on and turn off Intelligent Calculation (see Optimizing with Intelligent Calculation) |
|
To control how Essbase marks data blocks for the purpose of Intelligent Calculation (see Optimizing with Intelligent Calculation) |
|
To specify the maximum number of blocks that Essbase can lock concurrently when calculating a sparse member formula |
|
For currency conversions, to restrict aggregations to parents that have the same defined currency (see Designing and Building Currency Conversion Applications) |
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.
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.*/
When you create a calculation script, you need to apply the following rules:
CALC DIM(Product, Measures); DATACOPY Plan TO Revised_Plan; "Market Share" = Sales % Sales -> Market; IF (Sales <> #MISSING) Commission = Sales * .9; ELSE Commission = #MISSING; ENDIF;You do not need to end the following commands with semicolons: IF, ELSE, ELSEIF, FIX, ENDFIX, LOOP, and ENDLOOP.
"Opening Inventory" = "Ending Inventory" - Sales + Additions;
For a complete list of member names that must be enclosed in quotation marks, see Rules for Naming Dimensions and Members.
Commission (IF(Sales < 100) Commission = 0; ENDIF;)
Profit (IF (Sales > 100) Commission = Sales * .1; ENDIF;)
"Opening Inventory" (IF (@ISMBR(Budget)) IF (@ISMBR(Jan)) "Opening Inventory" = Jan; ELSE "Opening Inventory" = @PRIOR("Ending Inventory"); ENDIF; ENDIF;)
Marketing (IF (@ISMBR(@DESCENDANTS(West)) OR @ISMBR(@DESCENDANTS(East))) Marketing = Marketing * 1.5; ELSEIF(@ISMBR(@DESCENDANTS(South))) Marketing = Marketing * .9; ELSE Marketing = Marketing * 1.1; ENDIF;)Note: If you use ELSE IF (with a space in between) rather than ELSEIF (one word) in a formula, you must supply an ENDIF for the IF statement.
FIX(Budget,@DESCENDANTS(East)) CALC DIM(Year, Measures, Product); ENDFIXThe FIX and ENDFIX statements do not need to be followed by a
semicolon (;).
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.
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:
Essbase displays a list of all the calculation scripts associated with the application and database that you selected.
Figure 440: Application Desktop Window
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.
You can use Application Manager to add a new calculation script.
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.
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:
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
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.
You can now edit the calculation script.
To open a calculation script that is on a different server:
Essbase displays the Open Server Object dialog box as shown in Figure 443.
Figure 443: Open Server Object Dialog Box
Essbase displays the calculation script in Calc Script Editor.
To open a calculation script that is on a client computer:
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
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:
Essbase displays the Open Client Object dialog box as shown in Figure 445.
Figure 445: Open Client Object Dialog Box
Essbase displays the Open Client File dialog box.
Essbase displays the calculation script in Calc Script Editor.
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:
Essbase displays the Save Server Object dialog box as shown in Figure 446.
Figure 446: Save Server Object Dialog Box
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.
Figure 447: Save Server Object Dialog Box
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:
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
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.
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:
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.
Essbase displays the Save Client File dialog box.
Essbase saves the calculation script in the directory you specified.
To copy a calculation script from a client computer to the OLAP Server:
Essbase displays the Save Client Object dialog box as shown in Figure 449.
Figure 449: Save Client Object Dialog Box
The Save Server Object dialog box shown in Figure 450 replaces the Save Client Object dialog box.
Figure 450: Save Server Object Dialog Box
Essbase saves the calculation script on the Essbase server. For more information, seeSaving 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:
If the calculation script is saved on your client computer, open the application desktop client window.
Figure 451 shows the application desktop server window for a server called Aspen.
Figure 451: Application Desktop Server Window
Essbase displays the Select Database dialog box as shown in Figure 452.
Figure 452: Select Database Dialog Box
If you are not currently connected to the server, click Connect.
Essbase runs the calculation script against the database that you selected.
To run a calculation script from the menu:
Essbase displays the Calculate Database dialog box as shown in Figure 453.
Figure 453: Calculate Database Dialog Box
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 |
---|---|---|
You can print a calculation script from Calc Script Editor.
To print a calculation script:
Essbase displays the Print Calc Script dialog box.
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:
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
Essbase displays a Confirm Delete message box.
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.
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.
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.
When you use an IF statement as part of a member formula in a calculation script, you need to do both of the following:
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:
The whole of the IF ... ENDIF statement is enclosed in parentheses and associated with the Profit member, Profit (IF(...)...).
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(...)...)
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:
Text is displayed at the cursor position as you type as shown in Figure 455.
To insert an equal (=) sign in Calc Script Editor:
To insert a mathematical operator (+, -, X, /, %) in Calc Script Editor:
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:
For more information on the cross-dimensional operator, see Developing Formulas.
To insert the semicolon formula end-of-line character (;) in Calc Script Editor:
To insert a function or operator in Calc Script Editor:
Essbase displays the Function Templates dialog box.
For example, to insert the @VAR function, select Math.
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
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:
To copy text in Calc Script Editor:
To paste text in Calc Script Editor:
To find and replace text in Calc Script Editor:
Essbase displays the Find dialog box as shown in Figure 459.
Figure 459: Calc Script Editor Find Dialog Box
To do a case-sensitive search:
For example, to search for Margin but not margin, type Margin in the Find what text box and check Match case.
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:
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
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:
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:
The
button changes to a
button as shown in Figure 462.
Figure 462: Expanding a Member Branch
Essbase does not display the member's children. The
button changes to a
button as shown in Figure 463.
For example, select the Measures dimension from the Sample Basic database.
Essbase displays the Find dialog box as shown in Figure 464.
Figure 464: Opening the Find Dialog Box
For example, to search for the Marketing member in the Measures dimension, enter market as shown in Figure 465.
Figure 465: Searching For Members
For example, to search for Margin, but not Margin % in the Sample Basic database, type margin, and check Match whole word only.
Essbase finds and selects the appropriate member as shown in Figure 466.
To expand a dimension to display all members in the Calc Script Editor of the Application Manager:
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
In the Members list, Essbase displays all members in the dimension, an example of which is shown in Figure 468.
To display and insert alias names in the Calc Script Editor of the Application Manager:
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
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 (" ").
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.
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.
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.
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;
(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;
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);
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.
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.
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.
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.
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.
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.
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:
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 |