1. Database Administrator's Guide for Oracle Essbase
  2. Reviewing Examples of Calculation Scripts for Block Storage Databases
  3. Forecasting Future Values

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););

The following table describes the parameters used in the forecasting calculation script:

Table 25-1 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