11 Calculate Cubes

A cube contains two types of values: values that you enter, called input data, and values that are calculated from input data.

A cube can be calculated using one of two methods. Outline calculation, which is the simplest calculation method, bases the calculation of a cube on the relationships between members in the cube outline and on any formulas that are associated with members in the outline.

Calculation script calculation lets you procedurally calculate a cube; for example, you can calculate one part of a cube before another, or copy data values between members.

The topics in this section are about calculation script calculation:

Access to Calculations

If you have the Database Update user role, you have access to run the default calculation on the cube (from Smart View), and to run specific calculation scripts provisioned to you. If you have the Application Manager or Database Manager role, you have Calc privileges and rights to execute all calculations, and to provision access to execute specific calculation scripts.

When creating or editing a calculation script in the Essbase web interface, you can use the Permissions page within the script editor to provision users to execute the script.

Create Calculation Scripts

Calculation scripts specify how block storage cubes are calculated and, therefore, override outline-defined cube calculations. For example, you can calculate cube subsets or copy data values between members.

You create calculation scripts using a script editor in the Essbase web interface.

Calculation scripts do not apply to aggregate storage applications.

  1. On the Application page, expand the application.
  2. From the Actions menu, to the right of the cube name, launch the inspector.
  3. Select the Scripts tab, and then select the Calculation Scripts tab.
  4. Click Add Image of the Add icon. to create a new calculation script.
  5. If member names are required in your calculation script, drill into the Member Tree to find the members you want to add.
    Right-click dimension or member names to insert them into the script.
  6. If function names are required in your calculation script, use the Function Name menu to find calculation functions and add them to the script.
    See the Function description under the menu to read descriptions of each function.
  7. Click Validate before saving your script.

    Validating a script verifies the script syntax. For example, incorrectly spelled function names and omitted end-of-line semicolons are identified. Validation also verifies dimension names and member names.

  8. Correct any validation errors.

    Calculation scripts can contain runtime substitution variables designed to derive the calculation scope from the point of view (POV) in a Smart View grid. These types of calculation scripts will not pass validation on the server, because the point of view can only be known from a Smart View grid.

  9. Click Save.
To learn about calculation script logic, see Developing Calculation Scripts for Block Storage Databases.
To learn about calculation functions and commands, see Calculation Functions and Calculation Commands.

Execute Calculations

After creating and saving calculation scripts, you can execute them in the script editor and perform the calculations on data loaded in your cube.

  1. Create your calculation script, or upload an existing calculation script.
  2. On the Applications page, expand an application, and select a cube.
  3. From the Actions menu, to the right of the cube name, launch the inspector.
  4. Select Scripts, and select a script.
  5. In the script editor, select Execute and then either Run in Foreground or Run in Background.
    • If you choose Run in Forground, Script execution in progress is displayed and you can't close the script editor until the calculation is completed.
    • If you choose Run in Background, you can close the script editor and later check the Jobs page for the status of the calculation.

You can also execute calculation scripts from the Jobs page or from Smart View (whether or not they contain point-of-view based substitution variables).

Calculation scripts can contain runtime substitution variables designed to derive the calculation scope from the point of view (POV) in a Smart View grid. These types of calculation scripts will not execute from the server, because the point of view can only be known from a Smart View grid.

Assign access to execute specific calculation scripts:
  1. Log into the Essbase web interface as a service administrator or power user.
  2. On the Applications page, expand an application, and select a cube.
  3. From the Actions menu, to the right of the cube name, launch the inspector.
  4. Select the Scripts tab, and then select the Calculation Scripts tab.
  5. Select a script and select the Roles tab.
  6. Add the users or groups to assign them access and save your changes. The users or groups are given permission to execute the specific calculation script.

See also: Create Calculation Scripts

Work with Files and Artifacts

Use Substitution Variables

Use substitution variables in calculation scripts to store values that might change. Use runtime substitution variables when you need different users to specify different values for the same script.

For example, if a variety of your calculation scripts, formulas, filters, report scripts, and MDX scripts all need to refer to the current month, you would not want to search and replace the month approximately every 30 days throughout your library of cube artifacts. Instead, you can define a substitution variable named CurrMonth, and change its assigned value each month to the appropriate month. All of the cube artifacts that reference the variable will then reference the appropriate month.

Here is an example of a simple substitution variable to represent the current month:

Variable name: CurrMonth

Value: Jan

Substitution variable values apply to all users who run a calculation script containing the variable. For example, if CurrMonth has the value Jan, then all scripts containing &CurrMonth will execute for Jan. The scope of a substitution variable can be:

  • global (for all applications and cubes on the server)
  • application (for all cubes in the application)
  • cube (for a single cube)

To define a substitution variable for a specific cube,

  1. In the Essbase web interface, on the Applications page, expand the application to show the cube you want to modify.

  2. From the Actions menu to the right of the cube, launch the inspector.

  3. Select the Variables tab, and click Add Image of the Add icon..

  4. Enter the variable name and value, click Save, and click Close.

To define a substitution variable for a specific application,

  1. On the Applications page, from the Actions menu to the right of the application, launch the inspector.

  2. Select the Variables tab, and click Add Image of the Add icon..

  3. Enter the variable name and value, click Save, and click Close.

To define a substitution variable globally,

  1. In Essbase, click Console.

  2. Click the Variables tab, and click Add.

  3. Enter the variable name and value, and click Save.

Once your substitution variable is defined, you can use it in calculation scripts, formulas, filters, MDX scripts, load rules, and reports. To reference the variable, prefix it with the & symbol.

Here is an example of a calculation script that references a substitution variable:

FIX(&CurrMonth)
   CALC DIM (Measures, Product);
ENDFIX

Here is an example of a formula that references a substitution variable:

@ISMBR(&CurrMonth)

Runtime substitution variables enable you to declare variables and their values in the context of a runtime action, such as a calculation script, MaxL script, or MDX query. Runtime substitution variables can be assigned to have numeric values or refer to member names. A default value can be assigned in case a user does not change an input value. Also, for calculation scripts, the variable value can be populated at runtime from the members of a dimension presented on a Smart View grid. For calculation scripts with variable values that populate at runtime, you must launch the calculation script from Smart View, as the variable has no definition outside the context of the grid.

Runtime substitution variables may be defined in the calculation script using key-value pairs:

SET RUNTIMESUBVARS
{
   myMarket = "New York";
   salesNum = 100;
   pointD = "Actual"->"Final";
}

Or, to define runtime substitution variables with values that change dynamically depending on the POV, assign the definition to POV, and use XML syntax to enable Smart View contextual prompts.

For more information, see

Set Two-Pass Calculation Properties

The Two-Pass Calculation property can be applied to members in non hybrid mode, block storage cubes to indicate members that need to be calculated twice to produce the desired value. To obtain the correct values for two-pass members, the outline is calculated, and then members that are dependent on the calculated values of other members are recalculated.

Even though two-pass calculation is a property that you can give to any non-attribute dimension member, it works only on members of the Accounts dimension and Dynamic Calc members. If two-pass calculation is assigned to any other member, it is ignored.

Two-pass calculations are supported only on block storage cubes. Hybrid mode and aggregate storage cubes use member solve order, instead of two-pass calculation, to control when members are calculated. In hybrid mode, members tagged as two pass are assigned a solve order of 100 by default.

  1. On the Applications page, expand the application.

  2. From the Actions menu, to the right of the cube name, select Outline.

  3. Click Edit.

  4. In the outline editor, find and select the member you want to modify.

  5. In the Properties pane, expand the Two-pass Calculation menu, and select True.

See Setting Two-Pass Calculations.

Trace Calculations

You can use calculation tracing to analyze member formula processing, and refine your calculation scripts.

Calculation tracing enables you to access logged information about a calculation, after the calculation script successfully executes against a cube.

Tracing a calculation does not change anything about calculation behavior. If a calculation is launched in Smart View, and the connected server has calculation tracing enabled by an administrator, Smart View displays a pop-up dialog box containing details, after the calculation runs. The calculation tracing information can be pasted from the pop-up dialog into a text editor. Or, you can find the same information in calc_trace.txt, located in the database files directory in Essbase.

The calculation tracing information can help you debug calculation script execution, in case the results of the calculation are not what you expected.

Calculation tracing is not supported on applications with scenario management enabled.

To enable calculation tracing, the administrator must first turn on the CALCTRACE application configuration parameter. After calculation tracing is enabled for your application, there are two ways to take advantage of it:

  • In Smart View, you can use context-sensitive tracing for a single cell value.
    1. In Smart View, connect a query sheet to the application for which you enabled calculation tracing.
    2. Highlight a data cell whose calculated value you would like to trace.
    3. In the Data panel of the Essbase tab, click the Calculate button and select a calculation script to execute. You will see the point-of-view from your highlighted data cell in the trace member runtime prompts.
    4. Click Launch to execute the calculation script.

      The full scope of the calculation as contained in the script will be calculated, but only the highlighted data cell context will be traced during the calculation.

    5. At the end of the calculation script, examine the Calculation Result dialog box, which shows the pre- and post-calculation results for your highlighted data cell.

      If the highlighted data cell was not modified during the calculation, you will see a message indicating that the cell was not modified.

  • In calculation scripts, you can use the SET TRACE calculation command to select data intersections to trace. SET TRACE enables you to trace multiple data cells. Additionally, you can trace sections of calculation scripts by using a combination of SET TRACE mbrList (to turn calculation tracing on over a member list) and SET TRACE OFF (to disable calculation tracing until a new SET TRACE is encountered in the script. To use SET TRACE command, you must execute the calculation script outside of Smart View, using Cube Designer or the Jobs page in the Essbase web interface.
The following calculation script is run on Sample Basic. The script includes a SET TRACE command, requesting detailed information to be logged for the data intersection (cell) that represents budgeted January sales, in the California market, for the product SKU number 100-10.
SET TRACE ("100-10", "California", "Jan", "Sales", "Budget");
FIX("California", "Budget")
   "Sales" (
      "100-10" = @MEMBER(@CONCATENATE(@NAME(@PARENT("Product")), "-20")) / 10;
   );
ENDFIX;

Sample Basic has two sparse dimensions: Product and Market. The member formula is on Sales, a member of Measures, which is a dense dimension. The FIX statement's member list only contains one sparse member, California, which belongs to the Market dimension.

The number of existing blocks in the FIX statement determines the number of times the traced cell is calculated. In this example, the calculation cycles through all existing sparse member combinations of California. Each of these combinations represents a block.

After the calculation completes, the following tracing information is logged and displayed:

Tracing cell: [100-10][California][Jan][Sales][Budget]  (Cell update count: 1)
Previous value: 840.00
Dependent values: 
	[100-20][California][Jan][Sales][Budget] = 140.00
New value: [100-10][California][Jan][Sales][Budget] = 14.00

Computed in lines: [91 - 93] using:
"Sales"(
"100-10"=@MEMBER(@CONCATENATE(@NAME(@PARENT("Product")),"-20"))/10;
)

Tracing cell: [100-10][California][Jan][Sales][Budget]  (Cell update count: 2)
Block from FIX scope: [100-30][California]
Actual block used in calculation: [100-10][California]
Previous value: 14.00
Dependent values: 
	[100-20][California][Jan][Sales][Budget] = 140.00
New value: [100-10][California][Jan][Sales][Budget] = 14.00
Computed in lines: [91 - 93] using:
"Sales"(
"100-10"=@MEMBER(@CONCATENATE(@NAME(@PARENT("Product")),"-20"))/10;
)

Tracing cell: [100-10][California][Jan][Sales][Budget]  (Cell update count: 3)
Block from FIX scope: [200-10][California]
Actual block used in calculation: [100-10][California]
Previous value: 14.00
Dependent values: 
	[200-20][California][Jan][Sales][Budget] = 520.00
New value: [100-10][California][Jan][Sales][Budget] = 52.00
Computed in lines: [91 - 93] using:
"Sales"(
"100-10"=@MEMBER(@CONCATENATE(@NAME(@PARENT("Product")),"-20"))/10;
)

[...calc iterations 4-7 are omitted from example...]

Tracing cell: [100-10][California][Jan][Sales][Budget]  (Cell update count: 8)
Block from FIX scope: [400-30][California]
Actual block used in calculation: [100-10][California]
Previous value: 9.00
Dependent values: 
	[400-20][California][Jan][Sales][Budget] = 90.00
New value: [100-10][California][Jan][Sales][Budget] = 9.00
Computed in lines: [91 - 93] using:
"Sales"(
"100-10"=@MEMBER(@CONCATENATE(@NAME(@PARENT("Product")),"-20"))/10;
)

The calculation tracing log provides the following insights about how the calculation worked, on the cell that was traced:

  • The traced cell was calculated several times, and the cell value was overwritten each time with the new value (the reported cell update count stops at 8).

  • The value of the cell, before calculation, was 840.00.

  • For each calculation occurrence, dependent values and new values are shown. Dependent values come from the member formula in the FIX statement.

  • The final value of the traced cell, after all calculation completes, is 9, but it represents the value of product "400-20"->California divided by 10.

  • Lines 91-93 of the calculation script, containing a member formula on Sales, are responsible for the updated values.

For each of the blocks cycled through, Sales is calculated using the formula:

"100-10"=@MEMBER(@CONCATENATE(@NAME(@PARENT("Product")),"-20"))/10

The formula contains a sparse member on the left hand side, which could cause the actual calculation block to be different than the initial FIX block. For example, when the calculation cycles through "California"->"100-20", the calculations are actually done in "California"->"100-10".

The trace log entries entitled Block from FIX scope and Actual block used in calculation are only printed if there is a discrepancy between the blocks in the FIX statement and the block that is represented in the member formula. These log entries can provide indications as to why there are duplicate calculations, helping you to debug your calculation scripts.

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.

Symmetric grid with Actual, Jan values for five markets for Cola, and the same five markets for Diet Cola

The following grid is asymmetric, because the Diet Cola product has fewer markets in the grid than the Cola product has.

Asymmetric grid with Actual, Jan values for five markets for Cola, but only two markets for Diet Cola

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.

Asymmetric grid with Actual, Jan values for five markets for Cola, but only two markets for Diet Cola

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.