1. Database Administrator's Guide for Oracle Essbase
  2. Developing Calculation Scripts for Block Storage Databases
  3. Calculating a Subset of a Database

Calculating a Subset of a Database

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

  • Create a formula using member set functions to calculate lists of members.

  • Use the FIX...ENDFIX commands to calculate a range of values by inclusion.

  • Use the EXCLUDE...ENDEXCLUDE commands to calculate a range of values by exclusion.

Note:

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.

Calculating Lists of Members

Member set functions 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 following example, using the @IDESCENDANTS command on the member Total Expenses generates a list of these members—Total Expenses, itself, and its descendants, which are Marketing, Payroll, and Misc:

@IDESCENDANTS("Total Expenses");

Using the FIX Command

Use the FIX command to define which members to include in the calculation.

The following example calculates only the Budget values for only the descendants of East (New York, Massachusetts, Florida, Connecticut, and New Hampshire):

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

The following example fixes on member combinations for the children of East that have a UDA of New Mkt:

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

The following example uses a wildcard match (???) to fix on member names that end in the characters -10, which are 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 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: 

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

In contrast, by using two FIX commands, the following calculation script causes Essbase to cycle through the database twice: once calculating the Actual data values and once calculating the Budget data values: 

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 command. 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

FIX commands can be nested within other FIX command blocks. However, using nested FIX commands incorrectly can result in incorrect results. For example, the intent of the following calculation script is to assign 1 to all children of East and then assign 2 to New York:

FIX (@CHILDREN(EAST))
   "100-10"=1;
      FIX ("New York")
         "100-10"=2;
      ENDFIX
ENDFIX

However, the nested FIX command fixes on a subset of the dimension that is specified by the FIX command above it (which is not allowed); therefore, the script assigns 2 to all children of East because the script runs as if it were written as:

FIX (@CHILDREN(EAST),''New York'')
   "100-10"=1;
   "100-10"=2;
ENDFIX

Rather than using nested FIX commands, use two separate FIX command blocks. For example:

FIX (@CHILDREN(EAST))
   "100-10"=1;
ENDFIX

FIX ("New York")
   "100-10"=2;
ENDFIX

The variable (varName) that is defined by a VAR calculation command cannot be used within the FIX member statement. The FIX members are evaluated before the calculation is executed, and variables are evaluated during runtime after the FIX statement is set. Because variables can change during the calculation execution, you cannot use the variable as part of the FIX statement. The following example shows the incorrect use of the variable in the FIX member statement: 

VAR varName=1;
FIX (@relative(@memberat(@List("Product1","Product2"),varName),0))
   COMMANDS;
ENDFIX

Using the Exclude Command

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.