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.
Tip:
Most applications forecast data using a forecasting context rather than using a FORECAST statement. See "Forecasting Programs" for more information.FORECAST [LENGTH n] -
[METHOD {TREND|EXPONENTIAL|WINTERS PERIODICITY p [argument...]}] -
[TIME dimension] [FCNAME name] time-series
where argument is one or more of the following clauses that specify the characteristics of the forecast:
Specifies the number of periods to forecast. The default is zero. When you supply a LENGTH, you must also supply the FCNAME option.
(Default) Specifies that the forecasting technique is a straight-line extrapolation of historical data.
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 equals 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 converts 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.
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 is forecast.
When time-series has only one dimension, the time dimension defaults to that. When time-series has multiple dimensions and one dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, then the time dimension defaults to that type. 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.)
Forecasting Multidimensional Expressions
When you want to forecast all the values of a multidimensional expression, you can use a program that puts a FORECAST statement inside one or more FOR loops to loop over all the remaining dimensions of the expression.
YOu can obtain portions of the results of FORECAST for your own reports or further analysis, using an INFO statement.
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.
Each method has its own criteria for handling the input data specified in time-series.
TREND -- Requires at least two values that are not NA; accepts zero and negative values; ignores NA values
EXPONENTIAL -- Requires at least two positive values; ignores zero, negative, and NA values
WINTERS -- Accepts zero and negative values; fills in NA values by calculating a weighted moving average
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:
The smoothed data series, which is the original data with seasonal effects and random error removed.
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.
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. Let Oracle OLAP calculate the starting values when you have little experience with Holt-Winters forecasting.
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 the UNRAVEL function.)
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.
Getting a Report of the Forecast
The FORECAST.REPORT program produces a standard report of a forecast created using the FORECAST command.
The report shows the parameters of the forecast, including the forecast formula and Mean Absolute Percent Error, followed by a display of the forecasted values. To produce this report, type the following.
FORECAST.REPORT
Example 9-139 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' FORECAST LENGTH 12 METHOD EXPONENTIAL FCNAME fcst.sales - time month sales
You can now execute FORECAST.REPORT to see the values that have been generated. Running the FORECAST.REPORT program for that forecast produces the following report.
Forecasting Analysis
====================
Variable to Forecast: SALES
Forecast dimension: MONTH
Forecast method: EXPONENTIAL
Mean absolute percent error: 16.64%
Forecast Equation: SALES = 87718.0009541883 *
(1.00553383457899 ** MONTH)
MONTH Actual Value Fitted Value
-------------------- ------------ ------------
Jan95 72,123.47 88,203.42
Feb95 80,071.75 88,691.52
Mar95 78,812.69 89,182.33
Apr95 97,413.26 89,675.85
May95 94,406.65 90,172.10
... ... ...
Dec96 72,095.02 100,140.38
... ... ...
Example 9-140 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' FORECAST LENGTH 12 METHOD WINTERS - PERIODICITY 12, ALPHA .5, BETA .5, GAMMA .5 - time month, FCNAME fcst.sales, sales
You can now execute FORECAST.REPORT to see the values that have been generated. Running the FORECAST.REPORT program for that forecast produces the following report.
Forecasting Analysis
====================
Variable to Forecast: SALES
Forecast dimension: MONTH
Forecast method: WINTERS
Alpha: 0.50
Beta: 0.50
Gamma: 0.50
Periodicity: 12
Mean absolute percent error: 0.20%
MONTH Actual Value Fitted Value
-------------------- ------------ ------------
Jan95 72,123.47 72,154.67
Feb95 80,071.75 80,027.51
Mar95 78,812.69 79,171.08
Apr95 97,413.26 97,200.81
May95 94,406.65 94,464.71
.... ... ...
Dec97 77,867.23