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 FIX command has restrictions. See the Oracle Essbase Technical Reference.