Calculate Selected Tuples
By selecting tuples, you can focus your calculations in the active Smart View grid, limiting their scope to specific slices of data in your cube.
The following sections in this guide describe tuple calculation:
For the syntax for employing @GRIDTUPLES in a calculation script, see FIX…ENDFIX.
Use Case for Tuple Calculation
By selecting tuples, you can focus your calculations in the active Smart View grid, limiting their scope to specific slices of data in your cube.
Tuple selection helps you optimize asymmetric grid calculations across dimensions, avoiding over-calculation.
Essbase calculation tuples differ from tuples used in MDX queries. Calculation performance and cube size are mainly driven by the number of blocks in the database (given a specific block size). For this reason, calculation tuples are specified only for sparse member combinations. In addition, for ease of calculation scripting, multiple members from a single sparse dimension can be included in a calculation tuple specification. For example, if you specify ("New York", "California", "Actual", "Cola") as a calculation tuple, then you calculate the following cell intersections:
"New York"->"Actual"->"Cola"
"California"->"Actual"->"Cola"Consider the following symmetric grid. It is symmetrical because each product has the same markets and scenario (Actual) represented in the grid.
The following grid is asymmetric, because the Diet Cola product has fewer markets in the grid than the Cola product has.
The default calculation scope, when more than one dimension is in a FIX statement or a Smart View grid point of view (POV), is to calculate the cross product (all possible combinations) of the members in the FIX or grid. In other words, a POV-driven calculation in which product and market combinations are taken from the grid calculates all of these row-member combinations:
Cola->"New York"
Cola->"Massachusetts"
Cola->"Florida"
Cola->"Connecticut"
Cola->"New Hampshire"
"Diet Cola"->"New York"
"Diet Cola"->"Massachusetts"
"Diet Cola"->"Florida"
"Diet Cola"->"Connecticut"
"Diet Cola"->"New Hampshire"
This may be more calculation activity than you need. If you want to calculate only the combinations shown on the grid, you can specify which tuples to calculate, and limit the calculation to a smaller slice. Calculating tuples can also lower calculation time and cube size.
Cola->"New York"
Cola->"Massachusetts"
Cola->"Florida"
Cola->"Connecticut"
Cola->"New Hampshire"
"Diet Cola"->"New York"
"Diet Cola"->"Florida"
Understand Tuple-Based Calculation
A calculation tuple is a way to represent a data slice of members, from two or more sparse dimensions, to be used in a calculation.
Examples of valid calculation tuples:
("Diet Cola", "New York")("Diet Cola", "Cola", Florida)(Cola, "New Hampshire")
If you write MDX expressions, you might be aware of these tuple restrictions that apply to MDX:
- Only a single member from each dimension can be included in an MDX tuple
- All tuples in an MDX set must have the same dimensions represented, in the same order
However, when you select tuples in calculation scripts, these requirements are relaxed for convenience. You may freely write tuple expressions, and the tuples may describe member lists, as the following tuple does: (@Children(East), Cola).
Select Tuples for Point of View Calculation
An easy way to select tuples is to insert them explicitly into a calculation script, as a list inside the FIX statement.
Recall that the format of a FIX statement is as follows:
FIX (fixMbrs)
COMMANDS ;
ENDFIX
In the FIX statement below, two tuples are specified before the command block begins. The tuples are enclosed within the curly braces { } that delimit a set, which is a collection of tuples.
FIX({
(@Children(East), Cola),
("New York", Florida, "Diet Cola")
})
Sales (Sales = Sales + 10;);
ENDFIX
Another way to select tuples is contextually, based on whichever members are present in a Smart View grid POV at calculation run time. You do this by providing the @GRIDTUPLES function as an argument to FIX, in your calculation script.
FIX ({@GRIDTUPLES(Product, Market)})
Sales (Sales = Sales + 10;);
ENDFIX
If you execute this calculation script from Smart View against the grid below, then only the displayed combinations of products and markets are calculated. For example, "Diet Cola"->Massachusetts is not calculated, as it is not shown explicitly on the grid. Note that all scenarios (the third sparse dimension in this sample cube) are calculated, even though only Actual is shown on the grid. This is because the Scenario dimension is not part of the GRIDTUPLES statement in the calculation script.
Tuple selection, whether done using explicit lists of tuples or by using the @GRIDTUPLES function, is applicable only in the context of the FIX…ENDFIX calculation command. The syntax of the FIX statement is expanded to enable tuple selection:
FIX ([{ tupleList | @GRIDTUPLES(dimensionList) },] fixMbrs)
COMMANDS ;
ENDFIX
- tupleList - comma-separated set of tuples.
- dimensionList - at least two sparse dimensions whose members from the active Smart View grid are used to define the calculation regions. (In calculation scripts, you can use only sparse dimensions to define tuples.)
- fixMbrs - a member or list of members.
Examples of Tuple Selection to Reduce Calculation Scope
Using a Smart View grid and a calculation script FIX statement, you can calculate selected member tuples based on the grid point of view (POV). Alternatively, you can explicitly type the tuple combinations in your FIX statement, removing the dependency on a particular Smart View grid to define the calculation scope.
Calculating selected tuples helps you efficiently work with asymmetric regions in both calculation scripts and Smart View grids.
Consider the following examples:
- No Tuple Selection - Calculates in the default manner, based on current Smart View grid point-of-view (POV). The calculation is not limited to any specific tuples.
- Selection of Named Sparse Dimensions - Calculates tuples from two or more sparse dimensions named in a calculation script. The calculation is limited to members from the tuple dimensions that are present in the Smart View grid.
- Selection of Contextual Sparse Dimensions - Calculates tuples from sparse dimensions selected at run-time. The calculation is limited to members from the tuple dimensions present in the Smart View grid.
To try the examples, download the CalcTuple_Tuple.xlsx workbook
template from the Technical > Calc section of the
gallery folder in the Files area of
the Essbase web interface. Refer to the README
worksheet in the workbook for instructions.
No Tuple Selection
Demonstrating the default calculation behavior that occurs when you do not select tuples, the following calculation script calculates the entire cross-product of Product and Market dimension members from a Smart View grid.
With the help of two runtime substitution variables (RTSV) defined in the SET RUNTIMESUBVARS block, calculation is limited to whichever Product and Market points of view are present in the grid when the calculation is run from Smart View.
SET RUNTIMESUBVARS
{
ProductGridMembers = POV
<RTSV_HINT><svLaunch>
<description>All Product's members on the grid</description>
<type>member</type>
<dimension>Product</dimension><choice>multiple</choice>
</svLaunch></RTSV_HINT>;
MarketGridMembers = POV
<RTSV_HINT><svLaunch>
<description>All Market's members on the grid</description>
<type>member</type> <dimension>Market</dimension><choice>multiple</choice>
</svLaunch></RTSV_HINT>;
};
FIX (
&ProductGridMembers, &MarketGridMembers
)
Marketing(
Marketing = Marketing +1;
);
ENDFIX
Selection of Named Sparse Dimensions
Using the @GRIDTUPLES function to select the tuple of Product and Market dimensions, this calculation script calculates tuples for only those two dimensions, limiting its scope to those members present in a Smart View grid at the time the calculation is executed from Smart View.
FIX (
{@GRIDTUPLES(Product, Market)}
)
Marketing(
Marketing = Marketing + 1;
);
ENDFIX
By fixing on only the sparse dimensions named in the tuple, the calculation encompasses a much smaller number of blocks than a default calculation would. However, all members from dimensions not mentioned in the fix (Year, Scenario) are calculated by this calculation script.
Selection of Contextual Sparse Dimensions
Using the @GRIDTUPLES function and a runtime substitution variable, this calculation script calculates only selected tuples from the grid, based on the sparse dimension selections in the RTSV prompt.
The runtime substitution variable &DimSelections, which is defined in the SET RUNTIMESUBVARS block, limits the calculation scope to only the sparse dimensions of the cube, excluding Scenario. The @GRIDTUPLES function used in the FIX statement calls this variable, limiting how many intersections are calculated.
SET RUNTIMESUBVARS
{
DimSelections = "Version", "Site", "Entity", "Product", "Market"
<RTSV_HINT><svLaunch>
<description>List two or more sparse dimensions used for forming calculation tuples:</description>
<type>string</type>
</svLaunch></RTSV_HINT>;
};
FIX (
{@GRIDTUPLES(&DimSelections)}
)
Marketing(
Marketing = Marketing + 1;
);
ENDFIX
The calculation encompasses an even smaller number of blocks than the previous example, because in this case, the tuple definition extends to more sparse dimensions beyond Product->Market.
To try the examples, download the CalcTuple_Tuple.xlsx workbook template from the Technical > Calc section of the gallery folder in the Files area of the Essbase web interface. Refer to the README worksheet in the workbook for instructions.