Oracle® OLAP DML Reference 11g Release 1 (11.1) Part Number B2812601 


View PDF 
Use the FORECAST command to forecast data by one of three methods: straightline trend, exponential growth, or HoltWinters 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.Syntax
FORECAST [LENGTH n] 
[METHOD {TRENDEXPONENTIALWINTERS PERIODICITY p [argument...]}] 
[TIME dimension] [FCNAME name] timeseries
where argument is one or more of the following clauses that specify the characteristics of the forecast:
Arguments
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 straightline extrapolation of historical data.
Specifies that the forecasting technique is an extrapolation of historical data using a constant periodtoperiod percentage growth.
Specifies that the forecasting technique is the HoltWinters 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 "HoltWinters 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 HoltWinters 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 HoltWinters forecast (See "HoltWinters 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 oneperiodahead forecasts in the historical time periods.
STSMOOTHED specifies the starting value of the smoothed data series (See "HoltWinters 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 "HoltWinters Constructed Series"). Nseries 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 "HoltWinters 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 "HoltWinters 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 "HoltWinters 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.
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 timeseries has only one dimension, the time dimension will default to that. When timeseries 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 HoltWinters method, the fitted values are oneperiodahead 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. Timeseries must be a numeric expression that is dimensioned by the time dimension. When timeseries has other dimensions, FORECAST uses the first value only in their current status. The timeseries is the historical data from which FORECAST calculates fitted and forecasted values. (See the explanation for FCNAME.)
Notes
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.
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 timeseries, the expression specifying the data to be forecast, must be last.
Timeseries Data Handling
Each method has its own criteria for handling the input data specified in timeseries.
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
Zero Values
All methods allow zero values in the historical data, specified by timeseries, but those time periods are excluded from the Mean Absolute Percent Error (MAPE) calculation.
HoltWinters Constructed Series
The HoltWinters 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 HoltWinters 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 HoltWinters method allows the trend effect to vary over time, so there is a trend value for every historical period.
HoltWinters Omitted Arguments
For the HoltWinters 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. You should let Oracle OLAP calculate the starting values when you have little experience with HoltWinters forecasting.
HoltWinters 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 the UNRAVEL function.)
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.
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
Examples
Example 9138 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 9139 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