1. Using Oracle Essbase
  2. Calculate Cubes
  3. Trace 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.