Reviewing Examples of Calculation Scripts for Block Storage Databases

In This Section:

About These Calculation Script Examples

Calculating Variance

Calculating Database Subsets

Loading New Budget Values

Calculating Product Share and Market Share Values

Allocating Costs Across Products

Allocating Values Within a Dimension

Allocating Values Across Multiple Dimensions

Goal-Seeking Using the LOOP Command

Forecasting Future Values

The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.

Also see Comparison of Aggregate and Block Storage.

About These Calculation Script Examples

All examples in this chapter are based on the Sample.Basic database.

For examples that use the Intelligent Calculation commands SET UPDATECALC and SET CLEARUPDATESTATUS, see Reviewing Examples That Use SET CLEARUPDATESTATUS and Reviewing Examples and Solutions for Multiple-Pass Calculations.

Calculating Variance

This example includes a calculation of the variance percentage between Budget and Actual values.

Figure 135, Variance and Variance % in the Scenario Dimension shows an outline in which Variance and Variance % are tagged as Dynamic Calc, two-pass members.

Figure 135. Variance and Variance % in the Scenario Dimension

This image shows Variance and Variance % tagged as Dynamic Calc, two-pass members.

During a default calculation, Essbase aggregates the values on the Market and Product dimensions. Because percentage values do not aggregate correctly, the Variance % formula must be recalculated after the default calculation.

Because Variance % is tagged as a Dynamic Calc, two-pass member, Essbase dynamically calculates Variance % values when they are retrieved. The dynamic calculation overwrites the incorrect values with the correctly calculated percentages.

If you choose not to tag Variance % as a Dynamic Calc, two-pass member, use the following calculation script—which assumes that Intelligent Calculation is turned on (the default)—to perform a default calculation and to recalculate the formula on Variance %:

CALC ALL;
SET UPDATECALC OFF;
SET CLEARUPDATESTATUS AFTER;
"Variance %";

Essbase performs the following actions:

  1. Performs a default calculation of the database (CALC ALL).

    Alternatively, you can run a default calculation of the database outline without using a calculation script.

  2. Turns off Intelligent Calculation (SET UPDATECALC OFF).

  3. Marks the calculated blocks calculated by the variance formula of the calculation script as clean, even though the variance calculation is a partial calculation of the database (CLEARUPDATESTATUS AFTER).

    By default, data blocks are marked as clean only after a full calculation of the database.

  4. Cycles through the database calculating the formula for Variance %.

See Choosing Two-Pass Calculation Tag or Calculation Script and Using Two-Pass Calculation.

For information on calculating statistical variance, see the Oracle Essbase Technical Reference.

Calculating Database Subsets

This example shows how a regional Marketing manager can calculate her respective area of the database. The calculation script uses @DESCENDENTS(East) to limit the calculations to the East region, as it calculates the Year, Measures, and Product dimensions for each child of East.

Figure 136, East, West, South, and Central Members in the Market Dimension shows an outline of the East, West, South, and Central members in the Market dimension:

Figure 136. East, West, South, and Central Members in the Market Dimension

This image shows the outline of the Market dimension, as described in the text preceding the image.

Example script:

/* Calculate the Budget data values for the descendants of East */

FIX(Budget, @DESCENDANTS(East))
   CALC DIM(Year, Measures, Product);
ENDFIX

/* Consolidate East */

FIX(Budget)
   @DESCENDANTS(East);
ENDFIX

Essbase performs the following actions:

  1. Fixes on the Budget values of the descendants of East.

  2. Calculates the Year, Measures, and Product dimensions in one pass of the database for all Budget values of the descendants of East.

  3. Fixes on the Budget values for all members on the other dimensions.

  4. Aggregates the descendants of East and places the result in East.

Loading New Budget Values

This example calculates Budget values and then recalculates the Variance and Variance % members.

Example script:

/* Calculate all Budget values */

FIX(Budget)
   CALC DIM(Year, Product, Market, Measures);
ENDFIX

/* Recalculate the Variance and Variance % formulas, which requires two passes */

Variance;
"Variance %";

Essbase performs the following actions:

  1. Fixes on the Budget values.

  2. Calculates all Budget values.

    The CALC DIM command is used to calculate all the dimensions except for the Scenario dimension, which contains Budget.

  3. Calculates the formula applied to Variance in the database outline.

  4. Calculates the formula applied to Variance % in the database outline.

Calculating Product Share and Market Share Values

This example calculates product share and market share values for each market and each product. The share values are calculated as follows:

  • Each member as a percentage of the total

  • Each member as a percentage of its parent

Assume that you added four members to the Measures dimension:

  • Market Share

  • Product Share

  • Market %

  • Product %

Example script:

/* First consolidate the Sales values to ensure that they are accurate */

FIX(Sales)
   CALC DIM(Year, Market, Product);
ENDFIX

/* Calculate each market as a percentage of the total market for each product */

"Market Share" = Sales % Sales -> Market;

/* Calculate each product as a percentage of the total product for each market */

"Product Share" = Sales % Sales -> Product;

/* Calculate each market as a percentage of its parent for each product */

"Market %" = Sales % @PARENTVAL(Market, Sales);

/* Calculate each product as a percentage its parent for each market */

"Product %" = Sales % @PARENTVAL(Product, Sales);

Essbase performs the following actions:

  1. Fixes on the Sales values and consolidates all the Sales values.

    The CALC DIM command is used to calculate the Year, Market, and Product dimensions. The Measures dimension contains the Sales member and therefore is not consolidated. The Scenario dimension is label only and therefore does not need to be consolidated.

  2. Cycles through the database and calculates Market Share by taking the Sales value for each product in each market for each month and calculating this Sales value as a percentage of total Sales in all markets for each product (Sales -> Market).

  3. Calculates Product Share by taking the Sales value for each product in each market for each month and calculating this Sales value as a percentage of total Sales of all products in each market (Sales -> Product).

  4. Calculates Market % by taking the Sales value for each product in each market for each month and calculating this Sales value as a percentage of the Sales value of the parent of the current member on the Market dimension.

    The @PARENTVAL function is used to obtain the Sales value of the parent on the Market dimension.

  5. Calculates Product % by taking the Sales value for each product in each market for each month, and calculating this Sales value as a percentage of the Sales value of the parent of the current member on the Product dimension.

    The @PARENTVAL function is used to obtain the Sales value of the parent on the Product dimension.

Allocating Costs Across Products

This example allocates overhead costs to each product in each market for each month. Overhead costs are allocated based on each product’s Sales value as a percentage of the total Sales for all products.

Assume that you added two members to the Measures dimension:

  • OH_Costs for the allocated overhead costs

  • OH_TotalCost for the total overhead costs

Example script:

/* Declare a temporary array called ALLOCQ based on the Year dimension */

ARRAY ALLOCQ[Year];

/* Turn the Aggregate Missing Values setting off. If this is your system default, omit this line */

SET AGGMISSG OFF;

/* Allocate the overhead costs for Actual values */

FIX(Actual)
   OH_Costs (ALLOCQ=Sales/Sales->Product; OH_Costs = 
   OH_TotalCost->Product * ALLOCQ;);

/* Calculate and consolidate the Measures dimension */

   CALC DIM(Measures);
ENDFIX

Essbase performs these calculations:

  1. Creates a one-dimensional array called ALLOCQ to store the value of Sales as a percentage of total Sales temporarily for each member combination.

    The size of ALLOCQ is based on the number of members in the Year dimension.

  2. #MISSING values are not aggregated to their parents (SET AGGMISSG OFF). Data values stored at parent levels are not overwritten.

    If SET AGGMISSG OFF is your system default, omit this line. See Consolidating #MISSING Values.

    • Fixes on the Actual values.

    • Cycles through the member combinations for Actual and calculates OH_Costs.

    • Takes the Sales value for each product in each market for each month and calculates it as a percentage of total Sales for all products in each market (Sales -> Product). The result is placed in ALLOCQ.

    • Takes the total overhead costs for all products (OH_TotalCost -> Product) and multiplies it by the value it has just placed in ALLOCQ. The result is placed in OH_Costs.

      Note that both equations are enclosed in parentheses ( ) and associated with the OH_Costs member: OH_Costs (equation1; equation2;).

  3. Calculates and consolidates the Measures dimension.

Allocating Values Within a Dimension

This example uses the @ALLOCATE function to allocate budgeted total expenses across expense categories for two products. The budgeted total expenses are allocated based on the actual values for the previous year.

Assume that you made the following changes, as shown in the outline in Figure 137, Modified Measures and Scenario Dimensions:

  • Added a child, Lease, under Total Expenses in the Measures dimension

  • Added a child, PY Actual, to the Scenario dimension

  • Removed the Dynamic Calc tag from the Total Expenses member

Figure 137. Modified Measures and Scenario Dimensions

This image shows the outline of the modified Measures and Scenario dimensions, as described in the text preceding the image.

Assume that data values of 1000 and 2000 are loaded into Budget -> Total Expenses for Colas and Root Beer, respectively. These values must be allocated to each expense category, evenly spreading the values based on the nonmissing children of Total Expenses from PY Actual. The allocated values must be rounded to the nearest dollar.

Example script:

/* Allocate budgeted total expenses based on prior year */

FIX("Total Expenses")
   Budget = @ALLOCATE(Budget->"Total Expenses",
   @CHILDREN("Total Expenses"),"PY Actual",,
   spread,SKIPMISSING,roundAmt,0,errorsToHigh)
ENDFIX

The results of the calculation script:

                             Budget   PY Actual
Colas       Marketing        334 *    150
            Payroll          #MI      #MI
            Lease            333      200
            Misc             333      100
            Total Expenses   1000     450
Root Beer   Marketing        500      300
            Payroll          500      200
            Lease            500      200
            Misc             500      400
            Total Expenses   2000     1100

* Rounding errors are added to this value. See step 5.

  Essbase cycles through the database, performing the following calculations:

  1. Fixes on the children of Total Expenses.

    Using a FIX statement with @ALLOCATE may improve calculation performance.

  2. For Budget -> Colas -> Marketing, divides 1 by the count of nonmissing values for each expense category in PY Actual -> Colas for each month.

    In this case, 1 is divided by 3, because there are 3 nonmissing expense values for Budget -> Colas.

  3. Takes the value from step 2 (.333), multiplies it by the value for Budget -> Colas -> Total Expenses (1000), and rounds to the nearest dollar (333). The result is placed in Budget -> Colas -> Marketing.

  4. Repeats step 2 and step 3 for each expense category for Budget -> Colas and then for Budget -> Root Beer.

  5. As specified in the calculation script, rounds allocated values to the nearest whole dollar.

    Essbase makes a second pass through the block to make the sum of the rounded values equal to the allocation value (for example, 1000 for Budget -> Colas -> Total Expenses). In this example, there is a rounding error of 1 for Budget -> Colas -> Total Expenses, because the expense categories add up to 999, not 1000, which is the allocation value. Because all allocated values are identical (333), the rounding error of 1 is added to the first value in the allocation range, Budget -> Colas -> Marketing (thus a value of 334).

Allocating Values Across Multiple Dimensions

This example uses the @MDALLOCATE function to allocate a loaded value for budgeted total expenses across three dimensions. The budgeted total expenses are allocated based on the actual values of the previous year.

Assume that you made the following changes:

  • Added a child, PY Actual, to the Scenario dimension

  • Copied data from Actual into PY Actual

  • Cleared data from Budget

For this example, a value of 750 (for Budget -> Total Expenses -> Product -> East -> Jan) must be allocated to each expense category for the children of product 100 across the states in the East. The allocation uses values from PY Actual to determine the percentage share that each category should receive.

Example script:

/* Allocate budgeted total expenses based on prior year, across 3 dimensions */

SET UPDATECALC OFF;
FIX (East, “100”, “Total Expenses”)
   BUDGET = @MDALLOCATE(750,3,@CHILDREN(“100”),@CHILDREN("Total Expenses"),@CHILDREN(East),"PY Actual",,share);
ENDFIX

The values for PY Actual:

         Jan 
         PY Actual
                         Marketing    Payroll   Misc   Total Expenses
100–10   New York        94           51        0      145
         Massachusetts   23           31        1      55
         Florida         27           31        0      58
         Connecticut     40           31        0      71
         New Hampshire   15           31        1      47
100-20   New York        199          175       2      376
         Massachusetts   #MI          #MI       #MI    #MI
         Florida         #MI          #MI       #MI    #MI
         Connecticut     26           23        0      49
         New Hampshire   #MI          #MI       #MI    #MI
100-30   New York        #MI          #MI       #MI    #MI
         Massachusetts   26           23        0      49
         Florida         #MI          #MI       #MI    #MI
         Connecticut     #MI          #MI       #MI    #MI
         New Hampshire   #MI          #MI       #MI    #MI
100      New York        #MI          #MI       #MI    #MI
         Massachusetts   12           22        1      35
         Florida         12           22        1      35
         Connecticut     94           51        0      145
         New Hampshire   23           31        1      55 
         East            237          220       3      460

  Essbase cycles through the database, performing these calculations:

  1. Fixes on East, the children of 100, and Total Expenses.

    Using a FIX statement with @MDALLOCATE may improve calculation performance.

  2. Before performing the allocation, determines what share of 750 (the value to be allocated) each expense category should receive, for each product-state combination, using the shares of each expense category from PY Actual. Starting with PY Actual -> 100-10 -> New York, Essbase divides the value for the first expense category, Marketing, by the value for PY Actual-> 100-10 -> East -> Total Expenses to calculate the percentage share of that category.

    For example, Essbase divides the value for PY Actual -> 100-10 -> New York -> Marketing (94) by the value for PY Actual -> 100-10 -> East -> Total Expenses (460), which yields a percentage share of approximately 20.4% for the Marketing category.

  3. Repeats step 2 for each expense category, for each product-state combination.

  4. During the allocation, Essbase uses the percentage shares calculated in step 2 and step 3 to determine what share of 750 should be allocated to each child of Total Expenses from Budget, for each product-state combination.

    For example, for Marketing, Essbase uses the 20.4% figure calculated in step step 2, takes 20.4% of 750 (approximately 153), and places the allocated value in Budget -> 100-10 -> New York -> Marketing (see the results that follow this procedure).

  5. Repeats step 4 for each expense category and for each product-state combination, using the percentage shares from PY Actual calculated in step 2 and step 3.

  6. Consolidates the expense categories to yield the values for Total Expenses.

The results of the allocation for Budget:

         Jan 
         Budget
                         Marketing    Payroll   Misc   Total Expenses
100–10   New York        153.26       83.15     0      236.41
         Massachusetts   37.50        50.54     1.63   89.67
         Florida         44.02        50.54     0      94.56
         Connecticut     65.22        50.54     0      115.76
         New Hampshire   24.26        50.54     1.63   76.63
100-20   New York        #MI          #MI       #MI    #MI
         Massachusetts   #MI          #MI       #MI    #MI
         Florida         42.39        37.50     0      79.89
         Connecticut     #MI          #MI       #MI    #MI
         New Hampshire   #MI          #MI       #MI    #MI
100-30   New York        #MI          #MI       #MI    #MI
         Massachusetts   #MI          #MI       #MI    #MI
         Florida         #MI          #MI       #MI    #MI
         Connecticut     #MI          #MI       #MI    #MI
         New Hampshire   19.57        35.87     1.63   57.07
100      New York        153.26       83.15     0      236.41
         Massachusetts   37.50        50.54     1.63   89.67
         Florida         86.41        88.04     0      174.46
         Connecticut     65.22        50.54     0      115.76
         New Hampshire   44.02        86.41     3.26   133.70
         East            386.41       358.70    4.89   750

Goal-Seeking Using the LOOP Command

This example shows how to calculate the sales value you must reach to obtain a certain profit on a specific product. In this case, the calculation script adjusts the Budget value of Sales to reach a goal of 15,000 Profit for Jan.

As shown in the outline in Figure 138, Measures Dimension, assume that no members are tagged as Dynamic Calc, and that the Profit per Ounce member (under Ratios in the Measures dimension) is not included in the calculation.

Figure 138. Measures Dimension

This image shows the outline of the Measures dimension, as described in the text preceding the image.

Assume that, before running the goal-seeking calculation script, the data values are:

Product, Market, Budget   Jan
Profit                    12,278.50
   Margin                 30,195.50
      Sales               49,950.00
      COGS                19,755.00
   Total Expenses         17,917.00
      Marketing           3,515.00
      Payroll             14,402.00
      Misc                0
Inventory                 Label Only member
Ratios                    Label Only member
   Margin %               60.45
   Profit %               24.58

Example script:

/* Declare the temporary variables and set their initial values*/

VAR
   Target = 15000,
   AcceptableErrorPercent = .001,
   AcceptableError,
   PriorVar,
   PriorTar,
   PctNewVarChange = .10,
   CurTarDiff,
   Slope,
   Quit = 0,
      DependencyCheck,
      NxtVar;

/*Declare a temporary array variable called Rollback based on the Measures dimension */

ARRAY Rollback [Measures];

/* Fix on the appropriate member combinations and perform the goal-seeking calculation*/

FIX(Budget, Jan, Product, Market)
   LOOP (35, Quit)
      Sales (Rollback = Budget;
      AcceptableError = Target * (AcceptableErrorPercent);
      PriorVar = Sales;
      PriorTar = Profit;
      Sales = Sales + PctNewVarChange * Sales;);
      CALC DIM(Measures);
      Sales (DependencyCheck = PriorVar - PriorTar;
      IF(DependencyCheck <> 0) CurTarDiff = Profit - Target;
         IF(@ABS(CurTarDiff) > @ABS(AcceptableError))
            Slope = (Profit - PriorTar) / (Sales - PriorVar);
            NxtVar = Sales - (CurTarDiff / Slope);
            PctNewVarChange = (NxtVar - Sales) / Sales;
         ELSE
            Quit = 1;
         ENDIF;
      ELSE
         Budget = Rollback;
         Quit = 1;
      ENDIF;);
   ENDLOOP
   CALC DIM(Measures);
ENDFIX

  Essbase performs the following calculations:

  1. Declares the required temporary variables using the VAR command. Where appropriate, the initial values are set.

  2. Declares a one-dimensional array called Rollback to store the Budget values.

    The size of Rollback is based on the number of members in the Measures dimension.

  3. Fixes on the Jan -> Budget values for all Product and Market members.

  4. Ensures that the commands between LOOP and ENDLOOP are cycled through 35 times for each member combination. If, however, the Quit variable is set to 1, the LOOP is broken and the calculation continues after the ENDLOOP command.

  5. Cycles through the member combinations, performing the following calculations:

    1. Places the Budget -> Sales value in the Rollback temporary array variable.

    2. Calculates the acceptable error, by multiplying the Target value (15000) by the AcceptableErrorPercent value (0.001). The result is placed in the AcceptableError variable.

    3. Retains the current Sales value, and places the Sales value for the current member combination in the PriorVar temporary variable.

    4. Retains the current Profit value, and places the Profit value for the current member combination in the PriorTar temporary variable.

    5. Calculates a new Sales value by multiplying the PctNewVarChange value (0.1) by the current Sales value, and adding the current Sales value. The result is placed in Sales.

    6. Calculates and consolidates the Measures dimension.

    7. Subtracts the PriorTar value from the PriorVar value, and places the result in the DependencyCheck temporary variable.

    8. Checks that DependencyCheck is not 0 (zero) (IF).

      • If DependencyCheck is not 0, subtracts the Target value (15000) from the current Profit and places the result in the CurTarDiff temporary variable.

        The IF command checks whether the absolute value (irrespective of the + or – sign) of CurTarDiff is greater than the absolute value of AcceptableError:

        • If greater than AcceptableError, calculates the Slope, NxtVar, and PctNewVarChange temporary variables.

        • If not greater than AcceptableError, breaks the LOOP command by setting the value of Quit to 1. The calculation continues after the ENDLOOP command.

      • If DependencyCheck is 0, places the value in the Rollback array into Budget. Essbase breaks the LOOP command by setting the value of Quit to 1. The calculation continues after the ENDLOOP command.

  6. Calculates and consolidates the Measures dimension.

The results for product 100-10:

Product, Market, Budget   Jan
Profit                    15,000.00
   Margin                 32,917.00
      Sales               52,671.50
      COGS                19,755.00
   Total Expenses         17,917.00
      Marketing           3,515.00
      Payroll             14,402.00
      Misc                0
Inventory                 Label Only member
Ratios                    Label Only member
   Margin %               28.47839913
   Profit %               62.49489762

Forecasting Future Values

This example uses a linear regression forecasting method to produce a trend (@TREND), or line, that starts with the known data values from selected previous months and continues with forecasted values based on the known values, and shows how to check the results of the trend for “goodness of fit” to the known data values. In this case, the calculation script forecasts sales data for June–December, assuming that data currently exists only up to May.

Assume that the Measures dimension contains an additional child, ErrorLR, where the goodness-of-fit results are placed.

Example script:

Sales
(@TREND(@LIST(Jan,Mar,Apr),@LIST(1,3,4),,
  @RANGE(ErrorLR,@LIST(Jan,Mar,Apr)),
    @LIST(6,7,8,9,10,11,12),
      Jun:Dec,LR););

Table 89 describes the parameters used in the forecasting calculation script:

Table 89. Parameters Used in the Example Calculation Script for Forecasting Future Values

Parameter

Description

@LIST(Jan,Mar,Apr)

Represents the Ylist, or the members that contain the known data values.

The @LIST function groups the three members as a comma-delimited list and keeps the list separate from other parameters.

@LIST(1,3,4)

Represents the Xlist, or the underlying variable values. Because Feb and May are skipped, Essbase numbers the Ylist values as 1,3,4.

,

The extra comma after the Xlist parameter indicates that a parameter (weightList) was skipped.

This example uses a default weight of 1.

@RANGE(ErrorLR,@LIST(Jan,Mar,Apr)

Represents the errorList, or the member list where results of the goodness of fit of the trend line to Ylist are placed.

The values placed in errorList are the differences between the data points in Ylist and the data points on the trend line that is produced.

The @RANGE function combines the ErrorLR member with Ylist (Jan, Mar, Apr) to produce a member list.

@LIST(6,7,8,9,10,11,12)

Represents the XforecastList, or the underlying variable values for which the forecast is sought. This example forecasts values consecutively for Jun–Dec, so the values are 6,7,8,9,10,11,12.

Jun:Dec

Represents the YforecastList, or the member list into which the forecast values are placed. This example forecasts values for Jun–Dec, based on the values for Jan, Mar, and Apr.

LR

Specifies the Linear Regression method.

  Essbase cycles through the database, performing the following calculations:

  1. Finds the known data values on which to base the trend (Sales for Jan, Mar, Apr), as specified by the Ylist and Xlist parameters.

  2. Calculates the trend line using Linear Regression and places the results in Sales for Jun–Dec, as specified by the YforecastList parameter.

  3. Calculates the goodness of fit of the trend line for the data values for Jan, Mar, and Apr, and places the results in ErrorLR for those months.

    For example, the value in ErrorLR for Jan (4.57) means that after Essbase calculates the trend line, the difference between the Sales value for Jan (2339) and the Jan value on the trend line is 4.57. The ErrorLR values for Feb and May are #MISSING, because these months were not part of Ylist.

The results of the calculation script:

      100 West Actual
      Sales     ErrorLR
Jan   2339      4.57
Feb   2298      #MI
Mar   2313      -13.71
Apr   2332      9.14
May   2351      #MI
Jun   2315.14   #MI
Jul   2311.29   #MI
Aug   2307.49   #MI
Sep   2303.57   #MI
Oct   2299.71   #MI
Nov   2295.86   #MI
Dec   2292      #MI