Skip Headers

Oracle® OLAP DML Reference
10g Release 1 (10.1)

Part Number B10339-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF


Use the FORECAST command to forecast data by one of three methods: straight-line trend, exponential growth, or Holt-Winters extrapolation. FORECAST performs the calculation according to the method you specify and optionally stores the result in a variable in your analytic workspace.

You can then execute FORECAST.REPORT to produce a standard report of the forecast. You can also use the INFO function to obtain portions of the results for use in your own customized reports or for further analysis.


Most applications forecast data using a forecasting context rather than using the FORECAST command. See "Using a Forecasting Context" for more information.




     [TIME dimension] [FCNAME nametime-series


argument is one or more of the following:

     ALPHA n

     BETA n

     GAMMA n


     FCSMOOTHED name

     FCSEASONAL name

     FCTREND name



Specifies the number of periods to forecast. The default is zero. When you supply a LENGTH, you must also supply the FCNAME option.


Specifies that the forecasting technique is a straight-line extrapolation of historical data. (Default)


Specifies that the forecasting technique is an extrapolation of historical data using a constant period-to-period percentage growth.


Specifies that the forecasting technique is the Holt-Winters method, an extrapolation method that allows for both a linear trend and seasonal fluctuations in the data. Oracle OLAP first constructs three statistically related series for each time period of the historical data. (See "Holt-Winters Constructed Series".) Then, Oracle OLAP produces a forecast from the three series for the specified number of periods into the future.

You can supply several arguments that affect the results of the Holt-Winters forecast. The only required one is PERIODICITY. For the others, Oracle OLAP chooses a reasonable value based on the data available.


The length of the seasonal cycle, where p is an expression that specifies an integer greater than or equal to 2. For example, when the data you are analyzing has monthly values, then p is 12.

PERIODICITY is required when you use the METHOD WINTERS keyword.


Smoothing constants for the first three series calculated for the Holt-Winters forecast (See "Holt-Winters Constructed Series"). ALPHA is for the smoothed data series; BETA is for the seasonal index series; and GAMMA is for the trend series. The value n is a decimal expression greater than 0 and less than or equal to 1. Each value is optional. When you omit one, Oracle OLAP calculates an optimal smoothing constant for that series that minimizes the Mean Absolute Percent Error of the one-period-ahead forecasts in the historical time periods.


STSMOOTHED specifies the starting value of the smoothed data series (See "Holt-Winters Constructed Series"). The value n is a decimal expression greater than 0. When you specify STSMOOTHED, you must also specify STSEASONAL and STTREND. When you omit it, Oracle OLAP calculates a starting value.

STSEASONAL specifies the starting values for the seasonal index series (See "Holt-Winters Constructed Series"). N-series is an array of decimal values, one for each period in a seasonal cycle. The number of values needed is the same as the number specified for PERIODICITY (See "Holt-Winters Starting Values"). When you specify STSEASONAL, you must also specify STSMOOTHED and STTREND. When you omit it, Oracle OLAP calculates the starting values.

STTREND specifies the starting value of the trend series (See "Holt-Winters Constructed Series"). N is a decimal value. When you specify STTREND, you must also specify STSMOOTHED and STSEASONAL. When you omit it, Oracle OLAP calculates a starting value.


Numeric variables in which Oracle OLAP can store the data calculated for the smoothed data series, the seasonal index series, and the trend series (See "Holt-Winters Constructed Series"). The variable specified by name must have the TIME dimension as one of its dimensions. The series calculations produce DECIMAL results, but Oracle OLAP will convert the values to the data type of name before storing them. You can save any or all of the preliminary series. When you do not save a series, Oracle OLAP discards the values after completing the forecast.

TIME dimension

The name of the dimension considered to be the time dimension. The current status of dimension determines the number of periods of historical data used to calculate the forecast. The status of the time dimension must be an increasing, consecutive range of values. LENGTH specifies how many values immediately beyond this range will be forecast.

When time-series has only one dimension, the time dimension will default to that. When time-series has more than one dimension, and one of the dimensions has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time dimension will default to that. Otherwise, you must specify the time dimension, even when the additional dimensions are limited to a single value. FORECAST only uses the first value in the status for dimensions other than the time dimension.


The name of a numeric variable in which to store the values calculated by FORECAST. Name must be dimensioned by the time dimension; it can have other dimensions as well. When the data type of name is not decimal, FORECAST converts the values to the appropriate data type.

Fitted values, which correspond to the historical data, are stored in name for the current status of the time dimension. Forecasted values are stored in name for the number of periods specified by LENGTH. These forecasted periods immediately follow the current status of the time dimension.

For the Holt-Winters method, the fitted values are one-period-ahead forecasts calculated at the previous period. The final forecasted values are extrapolated from the fitted data.

For the TREND and EXPONENTIAL methods, FORECAST obtains the fitted values by evaluating the regression equation over the current status of the time dimension.


An expression that specifies the time series to be forecast. Time-series must be a numeric expression that is dimensioned by the time dimension. When time-series has other dimensions, FORECAST uses the first value only in their current status. The time-series is the historical data from which FORECAST calculates fitted and forecasted values. (See the explanation for FCNAME.)


Using a Forecasting Context

Instead of calculating a simple forecast using the FORECAST command, you can perform more complex forecasting using a forecasting context that you manipulate with the following OLAP DML statements:

  1. FCOPEN function -- Creates a forecasting context.

  2. FCSET command -- Specifies the characteristics of a forecast.

  3. FCEXEC command -- Executes a forecast and populates Oracle OLAP variables with forecasting data.

  4. FCQUERY function -- Retrieves information about the characteristics of a forecast or a trial of a forecast.

  5. FCCLOSE command -- Closes a forecasting context.

Forecasting Multidimensional Expressions

When you want to forecast all the values of a multidimensional expression, you can use a program that puts the FORECAST command inside one or more FOR loops to loop over all the remaining dimensions of the expression.

Obtaining Portions of Results

YOu can obtain portions of the results of FORECAST for your own reports or further analysis, using an INFO statement.

Order of Arguments

You can specify the arguments for FORECAST in any order, except that time-series, the expression specifying the data to be forecast, must be last.

Time-series Data Handling

Each method has its own criteria for handling the input data specified in time-series.

Zero Values

All methods allow zero values in the historical data, specified by time-series, but those time periods are excluded from the Mean Absolute Percent Error (MAPE) calculation.

Holt-Winters Constructed Series

The Holt-Winters forecasting method constructs three statistically related series, which are used to make the actual forecast. These series are:

  1. The smoothed data series, which is the original data with seasonal effects and random error removed.

  2. The seasonal index series, which is the seasonal effect for each period. A value greater than one represents a seasonal increase in the data for that period, and a value less than one is a seasonal decrease in the data. The Holt-Winters method allows seasonal effects to vary over time, so there is a seasonal index value for every historical period.

  3. The trend series, which is the change in the data for each period with the seasonal effects and random error removed. The Holt-Winters method allows the trend effect to vary over time, so there is a trend value for every historical period.

Holt-Winters Omitted Arguments

For the Holt-Winters method, when you omit the STSMOOTHED, STTREND, and STSEASONAL phrases, Oracle OLAP calculates the necessary starting values using an algorithm from Statistical Methods for Forecasting by Abraham and Ledolter (See "Further Reading on Forecasting"). You should let Oracle OLAP calculate the starting values when you have little experience with Holt-Winters forecasting.

Holt-Winters Starting Values

When you specify starting values, Oracle OLAP obtains the STSEASONAL starting values by unraveling the values to make a list. The list must have at least the number of values as specified by PERIODICITY. Any more values are ignored; fewer values cause an error. The STSEASONAL expression can be multidimensional and does not have to have the same dimensions as the historical data. (For information about the order of the list when a dimensioned expression is unraveled, see UNRAVEL.)

Getting Calculated Values

You can find out the values that Oracle OLAP calculates for ALPHA, BETA, and GAMMA and for STSMOOTHED, STSEASONAL, and STTREND by using the INFO function.

Further Reading

For additional information about forecasting and forecasting methods, we suggest the latest editions of the books listed in "Further Reading on Forecasting".


Example 13-27 Using the EXPONENTIAL Method

The following statements create a variable called fcst.sales, limit the dimensions of the sales variable, use the EXPONENTIAL method to forecast sportswear sales for the Chicago district for 1997, and store the results of the calculation in fcst.sales.

DEFINE fcst.sales DECIMAL <month>
LIMIT product TO 'Sportswear'
LIMIT district TO 'Chicago'
LIMIT month TO 'Jan95' TO 'Dec96'
time month sales

You can now execute FORECAST.REPORT as illustrated in "Report of Forecast Using the EXPONENTIAL Method" to see the values that have been generated.

Example 13-28 Using the WINTERS Method

The following statements limit the month dimension, then calculate a forecast that takes into account seasonal influences, using the WINTERS method.

DEFINE fcst.sales DECIMAL <montH>
LIMIT month TO year 'Yr95' 'Yr96'
time month, FCNAME fcst.sales, sales

You can now execute FORECAST.REPORT as illustrated in "Report of Forecast Using the WINTERS Method" to see the values that have been generated.