Calculate a Subset of Data in the Cube

To avoid calculating more than you need to, calculate a subset of data in the Essbase database. You can indicate subsets using any combination of member set functions, FIX statements to specify inclusions, and EXCLUDE statements to specify exclusions.

To calculate a subset of data in the cube, 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 enabled, 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.

Calculate Lists of Members

Essbase member set calculation 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");

Use the FIX Command

Use the FIX command in Essbase block storage calc scripts to define which members to include in the calculation. FIX blocks enable you to optimize performance by avoiding overcalculation. FIX also enables you to calculate separate portions of the cube using different formulas.

A FIX block in your calc script helps you define slices of data to include in the calculation.

For example, the following FIX block 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

Use the Exclude Command

Use the EXCLUDE..ENDEXCLUDE command block in Essbase block storage calc scripts 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.

Specifying members that should not be calculated in an EXCLUDE…ENDEXCLUDE command may be simpler than specifying a complex combination of member names in a FIX…ENDFIX command.