In This Section:
The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.
Also see:
A calculation script, which contains a series of calculation commands, equations, and formulas, allows you to define calculations other than those defined by the database outline.
In a calculation script, you can perform a default calculation (CALC ALL) or a calculation of your choosing (for example, you can calculate part of a database or copy data values between members). You must write a calculation script to do any of the following tasks:
- Calculate a subset of a database
See Calculating a Subset of a Database. - Change the calculation order of the dense and sparse dimensions in a database
- Perform a complex calculation in a specific order or perform a calculation that requires multiple iterations through the data (for example, some two-pass calculations require a calculation script)
- Perform any two-pass calculation on a dimension without an accounts tag
See Using Two-Pass Calculation. - Perform a currency conversion
See Designing and Building Currency Conversion Applications. - Calculate member formulas that differ from formulas in the database outline (formulas in a calculation script override formulas in the database outline)
- Use an API interface to create a custom calculation dynamically
- Use control of flow logic in a calculation (for example, to use the IF…ELSE…ENDIF or the LOOP…ENDLOOP commands)
- Clear or copy data from specific members
See Copying Data. - Define temporary variables for use in a database calculation
See Declaring Data Variables. - Force a recalculation of data blocks after you have changed a formula or an accounts property on the database outline
- Control how Essbase uses Intelligent Calculation when calculating a database
See Understanding Intelligent Calculation.
The following calculation script, based on the Sample.Basic database, calculates the Actual values from the Year, Measures, Market, and Product dimensions:
Using Calculation Script Editor in Administration Services Console, you can create calculation scripts by:
See “About Calculation Script Editor” in the Oracle Essbase Administration Services Online Help.
Calculation scripts created using Administration Services are given a .csc extension by default. If you run a calculation script from Administration Services, Smart View, or Spreadsheet Add-in, the file must have a .csc extension. However, because a calculation script is a text file, you can use MaxL or ESSCMD to run any text file as a calculation script.
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. In Calculation Script Editor, script elements are color-coded to aid readability and you can enable autocompletion to help build scripts interactively as you type. See “About Calculation Script Editor” in the Oracle Essbase Administration Services Online Help.
For a full list of calculation script commands and syntax, see the Oracle Essbase Technical Reference.
- End each formula or calculation script command with a semicolon (;):
Example 1:
CALC DIM(Product, Measures);Example 2:
DATACOPY Plan TO Revised_Plan;Example 3:
"Market Share" = Sales % Sales -> Market;Example 4:
You do not need to end the following commands with semicolons:IF (Sales <> #MISSING) Commission = Sales * .9; ELSE Commission = #MISSING; ENDIF;
IF ENDIF ELSE ELSIF FIX ENDFIX EXCLUDE ENDEXCLUDE LOOP ENDLOOP
Note:Although not required, it is good practice to follow each ENDIF statement in a formula with a semicolon.- Enclose a member name in double quotation marks (" "), if that member name meets any of the following conditions:
- Contains spaces. For example:
"Opening Inventory" = "Ending Inventory" - Sales + Additions; - Is the same as an operator, function name, or keyword.
See Using Dimension and Member Names in Calculation Scripts, Report Scripts, Formulas, Filters, Substitution Variable Values and Environment Variable Values. - Includes any nonalphanumeric character; for example, hyphen ( - ), asterisk ( * ), or slash ( / ).
See Using Dimension and Member Names in Calculation Scripts, Report Scripts, Formulas, Filters, Substitution Variable Values and Environment Variable Values. - Contains only numerals or starts with a numeral; for example, “100” or “10Prod”.
- Begins with an ampersand (&). The leading ampersand (&) is reserved for substitution variables. If a member name begins with &, enclose it in quotation marks. Do not enclose substitution variables in quotation marks in a calculation script.
- Contains a dot (.); for example, 1999.Jan or .100.
- Contains spaces. For example:
- If you are using an IF statement or an interdependent formula, enclose the formula in parentheses to associate it with the specified member. For example, the following formula is associated with the Commission member in the database outline:
Commission(IF(Sales < 100) Commission = 0; ENDIF;)
- End each IF statement in a formula with an ENDIF statement. For example, the previous formula contains a simple IF...ENDIF statement.
- If you are using an IF statement that is nested within another IF statement, end each IF with an ENDIF statement. For example:
"Opening Inventory" (IF (@ISMBR(Budget)) IF (@ISMBR(Jan)) "Opening Inventory" = Jan; ELSE "Opening Inventory" = @PRIOR("Ending Inventory"); ENDIF; ENDIF;) - You do not need to end ELSE or ELSEIF statements with ENDIF statements. For example:
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;) - End each FIX statement with an ENDFIX statement. For example:
FIX(Budget,@DESCENDANTS(East)) CALC DIM(Year, Measures, Product); ENDFIX - End each EXCLUDE statement with an ENDEXCLUDE statement.
When you write a calculation script, use the Calculation Script Editor syntax checker to check the syntax. See Checking Syntax.
You can use the calculation commands in Table 54 to perform a database calculation that is based on the structure and formulas in the database outline.
Table 54. List of Commands for Calculating a Database
You can use the commands in Table 56 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 in Calculation Scripts).
Table 56. List of Commands for Declaring Data Variables
| Calculation | Command |
|---|---|
| Declare one-dimensional array variables | ARRAY |
| Declare a temporary variable that contains a single value | VAR |
Values stored in temporary variables exist only while the calculation script is running. You cannot report on the values of temporary variables.
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:
ARRAY Discount[Scenario];
You can use multiple arrays at a time.
Table 57. List of Commands for Defining Calculation Behavior
| Calculation | Command |
|---|---|
| Specify how Essbase treats #MISSING values during a calculation | SET AGGMISSG |
| Adjust the default calculator cache size | SET CACHE |
| Enable parallel calculation (see Using Parallel Calculation) | SET CALCPARALLEL |
| Increase the number of dimensions used to identify tasks for parallel calculation (see Using Parallel Calculation) | SET CALCTASKDIMS |
| Optimize the calculation of sparse dimension formulas in large database outlines (see Optimizing Formulas on Sparse Dimensions in Large Database Outlines) | SET FRMLBOTTOMUP |
| Display messages to trace a calculation | SET MSG SET NOTICE |
| Turn on and turn off Intelligent Calculation (see Turning Intelligent Calculation On and Off) | SET UPDATECALC |
| Control how Essbase marks data blocks for Intelligent Calculation (see Using the SET CLEARUPDATESTATUS Command) | SET CLEARUPDATESTATUS |
| Specify the maximum number of blocks that Essbase can lock concurrently when calculating a sparse member formula | SET LOCKBLOCK |
| Turn on and turn off the Create Blocks on Equation setting (controls creation of blocks when you assign nonconstant values to members of a sparse dimension) (see Nonconstant Values Assigned to Members in a Sparse Dimension) | SET CREATEBLOCKEQ |
| Enable calculations on potential data blocks and save these blocks when the result is not #MISSING | SET CREATENONMISSINGBLK |
| (Currency conversions) Restrict consolidations to parents that have the same defined currency (see Calculating Databases) | SET UPTOLOCAL |
A SET command in a calculation script stays in effect until the next occurrence of the same SET command.
In the following calculation script, Essbase displays messages at the detail level when calculating the Year dimension and displays messages at the summary level when calculating the Measures dimension:
In the following calculation script, Essbase calculates member combinations for Qtr1 with SET AGGMISSG turned on, and then does a second calculation pass through the database and calculates member combinations for East with SET AGGMISSG turned off:
See the SET AGGMISSG command in the Oracle Essbase Technical Reference. Also see Using Two-Pass Calculation.
You can enter a calculation script directly into the text area of Calculation Script Editor, or you can use the UI features of Calculation Script Editor to build the calculation script.
You can place member formulas in a calculation script. When you do, the formula overrides conflicting formulas that are applied to members in the database outline.
To calculate a formula that is applied to a member in the database outline, use the member name followed by a semicolon (;).
For example, the following command calculates the formula applied to the Variance member in the database outline:
Variance;
To override values that result from calculating an outline, manually apply a formula that you define in a calculation script.
For example, the following formula cycles through the database, adding the values in the members Payroll, Marketing, and Misc, and placing the result in the Expenses member.
Expenses = Payroll + Marketing + Misc;
This formula overrides any formula placed on the Expenses member in the database outline.
You can use equations in a calculation script to assign value to a member. The syntax for an equation:
Member = mathematical expression;
Member is a member name from the database outline, and mathematical expression is any valid mathematical expression.
Essbase evaluates the expression and assigns the value to the specified member.
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;
In the following example, if it is true that February sales are greater than January sales, Sales Increase Flag results in a 1 value; if false, the result is a 0 value:
Sales Increase Flag = Sales -> Feb > Sales -> Jan;
In the following example, the entire IF…ENDIF statement is enclosed in parentheses and associated with the Profit member, Profit (IF(...)...):
- The IF statement checks whether the value of Sales for the current member combination is greater than 100.
- 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.
- 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.
Assume that you have a formula on a sparse dimension member, and the formula contains either of the following type of function:
In the following example, inappropriately placed parentheses cause Essbase to perform two calculation passes through the database: once calculating the formulas on the members Qtr1 and Qtr2; and once calculating the formula on Qtr3:
In contrast, the following configurations cause Essbase to cycle through the database only once, calculating the formulas on the members Qtr1, Qtr2, and Qtr3:
Similarly, the following formulas cause Essbase to cycle through the database once, calculating both formulas in one pass:
When calculating a series of dimensions, you can optimize performance by grouping the dimensions wherever possible.
CALC DIM(Year, Measures);
When you include a substitution variable in a calculation script, Essbase replaces the substitution variable with the value you specified for the substitution variable. Substitution variables are useful, for example, when you reference information or lists of members that change frequently.
You create and specify values for substitution values in Administration Services. See Using Substitution Variables.
You can create substitution variables at the server, application, and database levels. To use a substitution variable in a calculation script, the substitution variable must be available to the calculation script. For example, a database-level substitution variable is available only to calculation scripts within the database; a server-level substitution variable is available to any calculation script on the server.
In calculation scripts, you can use system environment variables as placeholders for user-specific system settings. Because environment variables are defined at the operating system level, they are available to all calculation scripts on Essbase Server.
Note:
Environment variables cannot be used in MDX queries.
To declare a system environment variable, see your operating system documentation.
To use an environment variable in a calculation script, insert the dollar sign ($) character before the environment variable name. Essbase treats any string that begins with a leading dollar sign as an environment variable, replacing the variable with its assigned value before parsing the calculation script. If a member name begins with $, enclose the name in quotation marks.
When using environment variables in calculation scripts, follow these guidelines:
- Environment variable names:
- Must consist of alphanumeric characters or underscores (_)
- Cannot include nonalphanumeric characters, such as hyphens (-), asterisks (*), and slashes (/)
- Cannot exceed 320 bytes (for Unicode-mode and non-Unicode mode applications)
- Environment variable values:
- May contain any character except a leading dollar sign ($)
- Whether numeric or non-numeric, must be enclosed in quotation marks (" ")—for example:
MY_PROD="100" ENV_FILE="E:\temp\export1.txt"
For non-numeric values, if you do not enclose the value in quotation marks when you define the environment variable, Essbase automatically encloses the value with quotation marks when the environment variable is passed.
For numeric values, Essbase does not automatically enclose the value with quotation marks when the variable is passed. (The reason is that Essbase cannot determine if you intend to pass a numeric value or a member name. For example, if you use a calculation script statement such as 'Sales = $MY_SALES' where MY_SALES=700, the intent is to pass the numeric value of 700. If, however, Essbase encloses MY_SALES in quotation marks, MY_SALES is treated as a member name. The member name would be passed, not the numeric value, causing an error.) If you want the numeric value of the variable to be treated as a string, you must enclose the value with quotation marks when you define the environment variable. - Cannot exceed 256 bytes (for Unicode-mode and non-Unicode mode applications)
For example, you can use an environment variable to define the path and filename for an export file when exporting a block of data to a flat file. In the following calculation script, the path and filename are explicitly defined (see the text in bold):
SET DATAEXPORTOPTIONS
{
DATAEXPORTLEVEL "ALL";
DATAEXPORTOVERWRITEFILE ON;
};
FIX ("New York", "100-10");
DATAEXPORT "File" "," "E:\temp\export1.txt";
ENDFIX;
You can declare an environment variable to reference the path and filename, ENV_FILE="E:\temp\export1.txt", and use the following syntax in the calculation script:
DATAEXPORT "File" "," $ENV_FILE;
Essbase replaces the environment variable with the value taken from the user's environment.
In the following example, another environment variable is defined to export only Sales values (CurrMbr="Sales"):
SET DATAEXPORTOPTIONS
{
DATAEXPORTLEVEL "ALL";
DATAEXPORTOVERWRITEFILE ON;
};
FIX ("New York", "100-10", $CurrMbr);
DATAEXPORT "File" "," $ENV_FILE;
ENDFIX;
Environment variables can also be used to parse arguments passed to RUNJAVA, an Essbase utility in which custom-defined functions can be called directly from a calculation script. For example, you can use environment variables to get user e-mail addresses. The following RUNJAVA statement sends an e-mail notification to explicitly-defined users (see the text in bold):
RUNJAVA com.hyperion.essbase.calculator.EssbaseAlert "localhost" “to@somedomain.com” "cc@mydomain.com" "" "" "Mail Subject" "Mail Body" "";
You can declare environment variables for the users, ENV_TOMAIL=“to@somedomain.com” and ENV_CCMAIL=“to@mydomain.com”, and use the following syntax in the calculation script:
RUNJAVA com.hyperion.essbase.calculator.EssbaseAlert "localhost" $ENV_TOMAIL $ENV_CCMAIL "" "" "Mail Subject" "Mail Body" "";
Using environment variables in calculation scripts is the same as using them in formulas. See Using Environment Variables in Formulas
Table 58. List of Commands for Clearing Data
| Calculation | Command |
|---|---|
| Changes the values of the cells you specify to #MISSING. The data blocks are not removed. You can use the FIX command with the CLEARDATA command to clear a subset of a database. | CLEARDATA |
| Remove 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. | CLEARBLOCK |
| Removes consolidated level blocks. | |
| Remove blocks containing derived values. Applies to blocks that are completely created by a calculation operation, not to blocks into which any values were loaded. | |
| Remove blocks for Dynamic Calc and Store member combinations. See Dynamically Calculating Data Values. | CLEARBLOCK DYNAMIC |
| Remove empty blocks | CLEARBLOCK EMPTY |
If, in the Sample.Basic database, the Scenario dimension is dense, the following example removes all data cells that do not contain input data values and that intersect with member Actual from the Scenario dimension:
If the Scenario dimension is sparse, the following example removes only the blocks whose Scenario dimension member is Actual:
CLEARDATA Actual -> Colas;
To clear an entire database, see “Clearing Data” in the Oracle Essbase Administration Services Online Help.
You can use the DATACOPY calculation command to copy data cells from one range of members to another range of members in a database. The two ranges must be the same size.
For example, in the Sample.Basic database, the following formula copies Actual values to Budget values:
DATACOPY Actual TO Budget;
For example, in the Sample.Basic database, the following formula copies Actual values to Budget values for the month of January only:
See Using the FIX Command. For more information about the DATACOPY command, see the Oracle Essbase Technical Reference.
- Create a formula using member set functions to calculate lists of members.
See Calculating Lists of Members. - Use the FIX...ENDFIX commands to calculate a range of values by inclusion.
See Using the FIX Command. - Use the EXCLUDE...ENDEXCLUDE commands to calculate a range of values by exclusion.
See Using the Exclude Command.
When Intelligent Calculation is 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. See Understanding Intelligent Calculation.
You can use a member set function to generate a list of members that is based on a member you specify. For example, the @IDESCENDANTS function generates a list of all the descendants of a specified member. When you use a member set function in a formula, Essbase generates a list of members before calculating the formula.
In the Sample.Basic database, the following example generates a list of these members—Total Expenses, Marketing, Payroll, and Misc:
@IDESCENDANTS("Total Expenses");
The following examples are based on the Sample.Basic database.
- This example calculates only the Budget values for only the descendants of East (New York, Massachusetts, Florida, Connecticut, and New Hampshire):
- This example fixes on member combinations for the children of East that have a UDA of New Mkt:
For information on defining UDAs, see Creating and Changing Database Outlines.
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 members that you specify. 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 members. 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, by using one FIX command, the following calculation script causes Essbase to cycle through the database only once, calculating both the Actual and the Budget values:
In contrast, by using two FIX command, this calculation script causes Essbase to cycle through the database twice: once calculating the Actual data values; once calculating the Budget data values:
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:
Use the EXCLUDE...ENDEXCLUDE command to define which members to exclude from the calculation. Sometimes it is easier to specify which members not to include in a calculation than to define which members to include.
Note:
The EXCLUDE command has some restrictions. See the Oracle Essbase Technical Reference.
The DATAEXPORT command enables calculation scripts to export data in binary or text, or directly to a relational database. A set of data-export-related calculation commands qualify what data to export and provide various output and formatting options.
The following command sequence shows the typical calculation script structure for exporting data:
SET DATAEXPORTOPTIONS
{
DATAEXPORTLEVEL parameters;
DATAEXPORTDYNAMICCALC ON | OFF;
DATAEXPORTNONEXISTINGBLOCKS ON | OFF;
DATAEXPORTDECIMAL n;
DATAEXPORTPRECISION n;
DATAEXPORTCOLFORMAT ON | OFF;
DATAEXPORTCOLHEADER dimensionName;
DATAEXPORTDIMHEADER ON | OFF;
DATAEXPORTRELATIONALFILE ON | OFF;
DATAEXPORTOVERWRITEFILE ON | OFF;
DATAEXPORTDRYRUN ON | OFF;
};
DATAEXPORTCOND parameters;
FIX
(fixMembers)
DATAEXPORT parameters;
ENDFIX;
To develop a calculation script that exports a subset of data, first specify the SET DATAEXPORTOPTIONS command to define options for export content, format, and process (see Table 59).
Table 59. List of SET DATAEXPORTOPTIONS Commands
| Calculation | Command |
|---|---|
| Content options | |
| Specify all, level 0, or input data value | DATAEXPORTLEVEL |
| Control export of dynamically calculated values | DATAEXPORTDYNAMICCALC |
| Specify whether to export data from all potential data blocks or only from existing data blocks. | DATAEXPORTNONEXISTINGBLOCKS |
| Specify the number of decimal positions in the exported values | DATAEXPORTDECIMAL |
| Specify the total number of positions in the exported values | DATAEXPORTPRECISION |
| Output format options | |
| Specify columnar or noncolumnar format | DATAEXPORTCOLFORMAT |
| Specify a dense dimension for the column header | DATAEXPORTCOLHEADER |
| Include a header record that lists all dimension names in the same order as the data in the file | DATAEXPORTDIMHEADER |
| Format the text export file for importing the data into a relational database | DATAEXPORTRELATIONALFILE |
| Processing options | |
| Specify whether an existing file with the same name and location is replaced | DATAEXPORTOVERWRITEFILE |
| Enable validating the set of calculation commands and viewing export statistics, including a time estimate—without having to perform the entire export process | DATAEXPORTDRYRUN |
Options and parameters are optional, with default values. See the Oracle Essbase Technical Reference.
When exporting data to a binary or text file, you can specify a limit for the size of the export file with the EXPORTFILESIZELIMIT configuration setting inessbase.cfg. The minimum file size limit is 1 MB. The maximum size of the export file is limited only by factors such as file system limits (for example, some systems do not support files that are larger than 2 GB), available disk space, and user limits. By default, the maximum file size for the export file is 2 GB. If the exported data exceeds the file size limit set with EXPORTFILESIZELIMIT or, if using the default 2 GB limit on a file system that does not support large files, Essbase creates multiple export files, as needed. An underscore and number is appended to the names of the additional files, starting with _1. For example, if the filename is outfile.txt and three files are created, the resulting file names are outfile.txt, outfile_1.txt, and outfile_2.txt.
Use a DATAEXPORTCOND command to select data based on data values. Then use FIX...ENDFIX or EXCLUDE...ENDEXCLUDE calculation commands to select a slice of the database to be exported. Within the FIX...ENDFIX or EXCLUDE...ENDEXCLUDE group, include the DATAEXPORT command.
When using the DATAEXPORT command to export data for direct insertion into a relational database:
- The table to which the data is to be written must exist prior to data export
- Table and column names cannot contain spaces
By default, when inserting exported data, Essbase uses the row-insert method, in which each row is inserted one at a time. To improve performance, you can use the batch-insert method if your relational database and the ODBC driver support the functionality.
To enable batch insert, set the DATAEXPORTENABLEBATCHINSERT configuration setting in essbase.cfg to TRUE. To control the number of rows that are inserted at one time (instead of letting Essbase determine the batch size), use the DEXPSQLROWSIZE configuration setting to specify the number of rows in a batch (from 2 to 1000). If Essbase cannot determine whether the relational database and the ODBC driver support batch insert, it uses the row-insert method, and DEXPSQLROWSIZE (if set) is ignored.
Note:
If DATAEXPORTENABLEBATCHINSERT is set to TRUE and DEXPSQLROWSIZE is set to 1, batch insert is disabled (as a DEXPSQLROWSIZE setting of 1 inserts rows one at a time).
Use the DATAIMPORTBIN calculation command to import a previously exported binary export file. The SET DATAIMPORTIGNORETIMESTAMP calculation command enables you to manage the import requirement for a matching outline timestamp.
Other export methods include using ESSCMD, MaxL, and Administration Services Console for database backup. Report Writer can be used to select and format a database subset, creating an output text file (see Exporting Text Data Using Report Scripts).
Compared to using other methods to export data, using a calculation script has the following advantages and disadvantages:
- Advantages
- Enables exporting a subset of data
- Supports multiple targets: flat files, relational databases, and binary files
- Provides options for type, format, or data
- As part of a calculation script, can be deployed in a batch process
- Can be very fast when the dynamic calculation export option is not used because DATAEXPORT directly accesses Kernel storage blocks in memory
- Provides, through binary export/import, a faster way to back up and restore data because the compressed format used by binary export requires less storage for the export files
- Can be used as a debug tool to trace batch calculation results by using the DATAEXPORT command before and after other calculation commands to track data changes
- Disadvantages
- Contains limited data formatting options compared to Report Writer formatting
- Works with stored members and Dynamic Calc members only, with no support for attribute members and alias names
- Not supported for aggregate storage databases
- Cannot export data directly to the client
- Can significantly impact performance when exporting dynamic calculation data, unless you set DATAEXPORTNONEXISTINGBLOCKS to ON.
When you use a formula on a dense member in a dense dimension, if the resultant values are from a dense dimension and the operand or operands are from a sparse dimension, Essbase does not automatically create the required blocks.
In the following example, based on Sample.Basic, assume that you want to create budget sales and expense data from existing actual data. Sales and Expenses are members in the dense Measures dimension; Budget and Actual are members in the sparse Scenario dimension.
Sales and Expenses, the results of the equations, are dense dimension members; the operand, Actual, is in a sparse dimension. Because Essbase executes dense member formulas only on existing data blocks, the calculation script does not create the required data blocks and Budget data values are not calculated for blocks that do not already exist.
You can use the DATACOPY command to create a block for each existing block, and then perform calculations on the new blocks. For example:
DATACOPY Sales -> Actual TO Sales -> Budget; DATACOPY Expenses -> Actual TO Expenses -> Budget; FIX(Budget) (Sales = Sales -> Actual * 1.1; Expenses = Expenses -> Actual * .95;) ENDFIX
Essbase creates blocks that contain the Budget values for each corresponding Actual block that exists. After the DATACOPY commands are finished, the remaining part of the script changes the values.
- There is a mathematical relationship between values in existing blocks and their counterparts created by the DATACOPY.
For example, in the preceding example, the Budget values can be calculated based on the existing Actual values.
- None of the blocks that are copied contain only #MISSING values.
If #MISSING values exist, blocks are written that contain only #MISSING values. Unneeded #MISSING blocks require Essbase resource and processing time.
If you are concerned about unwanted values, instead of using DATACOPY, you can use the SET CREATENONMISSINGBLK ON calculation command, which calculates all potential blocks in memory and then stores only the calculated blocks that contain data values. The SET CREATENONMISSINGBLK calculation command can be useful when calculating values on dense or sparse dimensions.
The following example creates budget sales and expense data from existing actual data. Sales and Expenses are members in the dense Measures dimension; Budget and Actual are members in the sparse Scenario dimension.
FIX(Budget) SET CREATENONMISSINGBLK ON (Sales = Sales -> Actual * 1.1; Expenses = Expenses -> Actual * .95;) ENDFIX
If SET CREATEBLOCKONEQ ON is set for sparse dimensions, SET CREATENONMISSINGBLK ON temporarily overrides it until a SET CREATENONMISSINGBLK OFF command is encountered or the calculation script is completed. See Nonconstant Values Assigned to Members in a Sparse Dimension.
The advantage of using the SET CREATENONMISSINGBLK command is that, when applied on dense members, only data cells that are affected by the member formula are saved. The disadvantage is that too many potential blocks may be materialized in memory, possibly affecting calculation performance. When you use this command, limit the number of potential blocks; for example, by using FIX to restrict the scope of the blocks to be calculated.
You can achieve significant calculation performance improvements by partitioning applications and running separate calculations on each partition. When using partitioning:
- Evaluate the performance impact on the overall database calculation. To improve performance, you can:
- Redesign the overall calculation to avoid referencing remote values that are in a transparent partition in a remote database
- Dynamically calculate a value in a remote database.
See Dynamically Calculating Data in Partitions. - Replicate a value in the database that contains the applicable formula.
For example, if replicating quarterly data for the Eastern region, replicate only the values for Qtr1, Qtr2, Qtr3, and Qtr4, and calculate the parent Year values locally.
- Ensure that a referenced value is up-to-date when Essbase retrieves it. Choose one of the options previously discussed (redesign, dynamically calculate, or replicate) or calculate the referenced database before calculating the formula.
You must calculate databases in a specific order to ensure that Essbase calculates the required results.
The example in Figure 136, Calculating Partitions shows partitions in which you view information from the West, Central, and East databases transparently from the Corporate database.
- Create a calculation script or open an existing calculation script.
See “Creating Scripts” or “Opening Scripts” in the Oracle Essbase Administration Services Online Help. - Enter or edit the contents of the calculation scripts.
See “About Calculation Script Editor” in the Oracle Essbase Administration Services Online Help for information about:
- Associating a script with an outline
- Searching an outline tree for members
- Inserting dimensions, members, and aliases in a script from an outline tree
- Inserting functions and commands in a script from a tree
- Using syntax autocompletion
- Checking script syntax
- Executing scripts
- Viewing color-coded script elements
- Searching for text in a script
- Changing fonts
- Validate the calculation script.
See Checking Syntax and “Checking Script Syntax” in the Oracle Essbase Administration Services Online Help. - Save the calculation script.
See Saving Calculation Scripts and “Saving Scripts” in the Oracle Essbase Administration Services Online Help. - Execute the calculation script.
See Executing Calculation Scripts, Checking the Results of Calculations, and “Executing Calculation Scripts” in the Oracle Essbase Administration Services Online Help. - If necessary, perform other operations on the calculation script.
In the Oracle Essbase Administration Services Online Help, see the following topics:
Essbase includes a syntax checker that flags syntax errors (such as a mistyped function name) in a calculation script. The results are displayed in the messages panel in Administration Services Console.
If syntax errors are not found, Essbase indicates the syntax check succeeded.
If syntax errors are found, Essbase indicates the syntax check failed, and displays one error at a time. Typically, an error message includes the line number in which the error occurred and a brief description. For example, if a semicolon end-of-line character is missing at the end of a calculation script command, Essbase displays a message similar to this one:
Error: line 1: invalid statement; expected semicolon
No more errors
To check the syntax of a calculation script in Calculation Script Editor, see “Checking Script Syntax” in the Oracle Essbase Administration Services Online Help.
Note:
The syntax checker cannot determine semantic errors, which occur when a calculation script does not work as you expect. To find semantic errors, run the calculation and check the results to ensure they are as you expect.
- As a file on a client computer.
- As an artifact on an Essbase Server, which allows other users to access the calculation script. You can associate the script with the following artifacts:
- An application and all the databases within the application, which lets you run the script against any database in the application.
Calculation scripts associated with an application are saved in the ARBORPATH/app/appname directory on the Essbase Server computer. - A database, which lets you run the script against the specified database.
Calculation scripts associated with a database are saved in the ARBORPATH/app/appname/dbname directory on the Essbase Server computer.
- An application and all the databases within the application, which lets you run the script against any database in the application.
Before you can execute a calculation script in Administration Services, you must save it as an artifact on an Essbase Server, a client computer, or a network. See Saving Calculation Scripts.
When you use Administration Services to execute a calculation script, you can execute the calculation in the background so that you can continue working as the calculation processes. You can then check the status of the background process to see when the calculation has completed. See “Executing Calculation Scripts” in the Oracle Essbase Administration Services Online Help.
After you execute a calculation script, you can check the results of the calculation in Smart View or Spreadsheet Add-in.
To display more-detailed information, you can use the SET MSG SUMMARY, SET MSG DETAIL, and SET NOTICE commands in a calculation script. See Specifying Global Settings for a Database Calculation.
You can use these messages to understand how the calculation is performed and to tune it for the next calculation.
Where you view this information depends on the tool used to execute the calculation script.
- Administration Services, Spreadsheet Add-in, and Smart View: Application log
See Viewing the Essbase Server and Application Logs. - MaxL: Standard output (command-line window)
The amount of information depends on the message level set in MaxL Shell. - ESSCMD: ESSCMD window or standard output (command-line window)
The amount of information depends on the message level set in ESSCMD.
You can copy calculation scripts to applications and databases on any Essbase Server, according to your permissions. You can also copy scripts across servers as part of application migration.
To copy a calculation script, use a tool:
http://docs.oracle.com/cd/E12825_01/epm.111/esb_dbag/frameset.htm?dcadevcs.htm
No hay comentarios:
Publicar un comentario