Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02

## FCSET

You must use the FCSET command in combination with other OLAP DML statements as outlined in "Forecasting Programs". For additional information about forecasting and forecasting methods, we suggest the latest editions of the books listed in "Further Reading on Forecasting".

Syntax

FCSET handle-expression

where

handle expression is one of the following:

ALLOCLAST {YES|NO}
ALPHA {MAX|MIN|STEP} decimal
APPROACH {'APPAUTO'|'APPMANUAL'|'APPHYBRID'}
BETA {MAX|MIN|STEP} decimal
COMPSMOOTH {YES|NO}
CYCDECAY {MAX|MIN} decimal
GAMMA {MAX|MIN|STEP} decimal
HISTPERIODS integer
MAXFACTOR decimal
METHOD method-text-expression
MINFCFACTOR decimal
MPTDECAY {MAX|MIN} decimal
NTRIALS integer
PERIODICITY cycle-spec
RATIO decimal
SMOOTHING {YES|NO}
TRANSFORM {'TRNOSEA'|'TRSEA'|'TRMPT'}
TRENDHOLD {MAX|MIN|STEP} decimal
WINDOWLEN integer

Arguments

ALLOCLAST {NO|YES}

Indicates whether the risk of over-adjustment should be reduced by allocating, instead of forecasting, the last cycle.

• NO specifies that the last cycle should be forecast. (Default)

• YES specifies that only the average value for one period of the cycle is forecast. That average value is then multiplied by factors to give the remaining points in that period. For example, when the last cycle has 24-hour periods, only an average hourly value is forecast, which is then multiplied by 24 hourly factors to give the value for each hour.

ALPHA {MAX|MIN|STEP} decimal

Specifies the value for Alpha.

• MAX specifies the maximum value of Alpha. Alpha is the level or baseline parameter that is used for the Single Exponential Smoothing, Double Exponential Smoothing, and Holt-Winters forecasting methods. You can specify any decimal value from 0.0 through 1.0. The default value is 0.3.

• MIN specifies the minimum value of Alpha. You can specify any decimal value from 0.0 through 1.0. The default value is 0.1.

• STEP specifies the value of the interval that Oracle OLAP uses when it determines the value of Alpha. You can specify any decimal value from 0.05 through 0.2 as long as the value evenly divides the difference between ALPHA MAX and ALPHA MIN. The default value is 0.1.

APPROACH {'APPAUTO'|'APPMANUAL'|'APPHYBRID'}

Specifies the approach that Oracle OLAP takes when the it executes the forecast.

• 'APPAUTO' indicates that Oracle OLAP tests all of the possible models and options for these models and chooses and uses the model that best fits the data. (Default)

• 'APPMANUAL' indicates that Oracle OLAP creates a forecast using the values specified in the FCSET commands for this forecasting context.

• 'APPHYBRID' indicates that, using the options that are specified in the FCSET commands for this forecasting context as the base options, Oracle OLAP tests all of the possible models and options for these models and chooses and uses the model that best fits the data.

BETA {MAX|MIN|STEP} decimal

Specifies the value of Beta.

• MAX specifies the maximum value of Beta. Beta is the trend parameter that controls the estimate of the trend. Beta is used for the Double Exponential Smoothing and Holt-Winters forecasting methods. You can specify any decimal value from 0.0 through 1.0. The default value is 0.3.

• MIN specifies the minimum value of Beta. You can specify any decimal value from 0.0 through 1.0. The default value is 0.1.

• STEP specifies the value of the interval that Oracle OLAP uses when it determines the value of Beta. You can specify any decimal value from 0.05 through 0.2 as long as the value evenly divides the difference between BETA MAX and BETA MIN. The default value is 0.1.

COMPSMOOTH {YES|NO}

Indicates whether optimization should be done on the median smoothed data series.

• NO specifies that the methods are done using the original historical time series data. (Default)

• YES specifies that optimization is done on the median smoothed data series, which results in more smoothed or "baseline" forecasts.

CYCDECAY {MAX|MIN} decimal

Specifies the value of the cyclical decay.

• MAX specifies the maximum value of the cyclical decay parameter. Cyclical decay pertains to how seriously Oracle OLAP considers deviations from baseline activity when it performs linear and nonlinear regressions. You can specify any decimal value from 0.2 through 1.0 as long as the difference between CYCDECAY MIN and CYCDECAY MAX is evenly divided by 0.4. The default value is 1.0.

• MIN specifies the minimum value of the cyclical decay parameter. You can specify any decimal value from 0.2 through 1.0 as long as the difference between CYCDECAY MIN and CYCDECAY MAX is evenly divided by 0.4. The default value is 0.2.

GAMMA {MAX|MIN|STEP} decimal

Specifies the value of Gamma.

• MAX specifies the maximum value of Gamma. Gamma is the seasonal parameter that is used for the Holt-Winters forecasting method. You can specify any decimal value from 0.0 through 1.0. The default value is 0.3.

• MIN specifies the minimum value of Gamma. You can specify any decimal value from 0.0 through 1.0. The default value is 0.1.

• STEP specifies the value of the interval that Oracle OLAP uses when it determines the value of Gamma. You can specify any decimal value from 0.05 through 0.2 as long as the value evenly divides the difference between GAMMA MAX and GAMMA MIN. The default value is 0.1.

HISTPERIODS integer

The number of historical periods. You can specify any integer value from `1` through `50000`, which is the maximum number of time dimension values that can be present in the time-series expression specified in the FCEXEC command.

MAXFCFACTOR decimal

Specifies the upper bound on the forecast data. The number you specify indicates a multiple of the largest value in the historical series. For example, when you specify `10.0`, the upper bound will be 10 times the largest value in the historical series. The default value is `100.0`.

METHOD 'method''

Specifies the method that you want Oracle OLAP to use. You can specify one of the following keywords for method:

• AUTOMATIC specifies that Oracle OLAP should determine and use the method that is the best fit for the data. (Default)

• LINREG specifies the linear regression method in which a linear relationship `(y=a*x+b)` is fitted to the data.

• NLREG1 specifies the nonlinear regression method 1 in which a linear relationship `(y'=a*x'+b)` is fitted to a transformation of the original data; in this case, `x'=log(x) and y'=log(y)`. This results in the development of a polynomial model between `x` and `y(y=c*x^a)`.

• NLREG2 specifies the nonlinear regression method 2 in which a linear relationship `(y'=a*x'+b)` is fitted to a transformation of the original data; in this case, `x'=x and y'=ln(y)`. This results in the development of an exponential model between x and `y(y=c*e^ax)`.

• NLREG3 specifies the nonlinear regression method 3 in which a linear relationship `(y'=a*x'+b)` is fitted to a transformation of the original data; in this case, `x'=log(x)` and `y'=y`. This results in the development of a logarithmic model between x and `y(y=a*log(x)+b)`.

• NLREG4 specifies the nonlinear regression method 4 in which a linear relationship `(y'=a*x'+b)` is fitted to a transformation of the original data; in this case, `x'=1/x and y'=1/y`. This results in the development of an asymptotic curve `(y=x/(a+bx))`.

• NLREG5 specifies the nonlinear regression method 5 in which a linear relationship `(y'=a'*x+b)` is fitted to a transformation of the original data; in this case, `x'=x and y'=ln(y/(K-y))`. This results in the development of an exponential asymptotic curve `(y=cKe^ax/(1+ce^ax))`.

• SESMOOTH specifies the single exponential smoothing method in which the current estimate is taken as a geometrically weighted average of past values, and all future values are given this same value. This method is intended for short term forecasts of non-seasonal data.

• DESMOOTH specifies the double exponential smoothing method in which the current estimate is taken as a geometrically weighted average of past values, and this is added to a trend term calculated by the same method. Single exponential smoothing is therefore applied to both the series and the trend term.

• HOLT/WINTERS specifies the Holt-Winters method that is used on seasonal data, in which double exponential smoothing methods with trend damping are combined with multiplicative seasonal factors, which are estimated using single exponential smoothing.

MINFCFACTOR decimal

Specifies the lower bound on the forecast data. The number you specify indicates a multiple of the smallest value in the historical series. You can specify any decimal value from `0.0` through `1.0`. For example, when you specify `0.5` the lower bound will be half the smallest value in the historical series. The default value is `0.0`.

MPTDECAY {MAX|MIN} decimal

Specifies the value of the parameter that Oracle OLAP uses when it adjusts the decay of estimates of base values that it uses when it unravels the predictions on a moving periodic total (MPT) series.

• MAX specifies the maximum value of the parameter that Oracle OLAP uses when it adjusts the decay of estimates of base values that it uses when it unravels the predictions on a moving periodic total (MPT) series. You can specify any decimal value from `0.2` through `1.0` as long as the difference between MPTDECAY MIN and MPTDECAY MAX is evenly divided by `0.4`. The default value is `1.0`.

• MIN specifies the minimum value of the parameter that Oracle OLAP uses when it adjusts the decay of estimates of base values that it uses when it unravels the predictions on a moving periodic total (MPT) series. You can specify any decimal value from `0.2` through `1.0` as long as the difference between MPTDECAY MIN and MPTDECAY MAX is evenly divided by `0.4`. The default value is `0.2`.

NTRIALS integer

Specifies the number of trials that Oracle OLAP runs to determine the forecast. You can specify any integer value from `1` through `3`. The default value is `3`.

PERIODICITY cycle-spec

Specifies either the number of periods for a single cycle or the number of periods in each of a set of nested cycles.

You do not have to specify this parameter when you are using a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. In this case, Oracle OLAP derives the periodicity from the number of time dimension periods that constitute a year (for example, there are 26 WEEK periods in a year).

When you are not using a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, the default value for cycle-spec is `1`, which specifies that the data is not grouped at all (that is, each period is logically independent).

Cycles are groupings of time periods that repeat through the time span of the data. For example, daily periods can be grouped into a weekly cycle and weekly periods can be grouped into a yearly cycle. In this case, the cycles are said to be nested, with the yearly cycle more aggregate than the weekly cycle, and the weekly cycle more detailed than the yearly cycle. By specifying cycles at a more detailed level, you allow OLAP to conduct a finer-grained search for factors that affect the data.

• To specify a single cycle, set cycle-spec to an integer from `1` through `25000`. The integer indicates the number of periods into which the cycle should be divided. For example, the INTEGER `12` specifies that the cycle should be divided into 12 periods.

• To specify a series of nested cycles, set cycle-spec to a series of up to six integers enclosed in parentheses and separated by commas. Each value in the series is the number of periods in one of the nested cycles. The cycles are ordered from most aggregate to least aggregate. For example, when cycle-spec is `(52,7)`, this indicates two cycles in which the most aggregate cycle is divided into 52 periods and each of those periods is divided into seven periods. In this example, the year is divided into 52 weeks, and each of those weeks is divided into seven days.

RATIO decimal

Specifies the ratio of the size of the window that Oracle OLAP uses for smoothing and the total number of historical periods. Oracle OLAP uses this value to determine the number of backcast periods. You can specify any decimal value from `1/26` through `1/2`. The default value is `1/3`.

SMOOTHING {YES|NO}

Indicates whether Oracle OLAP should smooth the data for the forecast. The default value is `NO`. Specify `YES` when you want Oracle OLAP to smooth the data.

TRANSFORM {'TRNOSEA'|'TRSEA'|'TRMPT'}

The data filter that Oracle OLAP uses when executing the forecast.

• 'TRNOSEA' indicates that Oracle OLAP will not seasonally adjust the data. (Default)

• 'TRSEA' indicates that Oracle OLAP will transform using a filter that seasonally adjusts the data.

• 'TRMPT' indicates that Oracle OLAP will transform using a moving periodic total (MPT) filter.

TRENDHOLD {MAX|MIN|STEP} decimal

Specifies the value of the trend.

• MAX specifies the maximum value of the trend hold parameter that indicates trend reliability in Double Exponential Smoothing and Holt-Winters forecasting methods. You can specify any decimal value from `0.0` through `1.0`. The default value is `0.8`.

• MIN specifies the minimum value of the trend hold parameter. You can specify any decimal value from `0.0` through `1. 0`. The default value is `0.4`.

• STEP specifies the value of the interval that Oracle OLAP uses when it determines the value of the trend hold parameter. You can specify any decimal value from `0.1` through `0.2`. The value of TRENDHOLD STEP must evenly divide the difference between TRENDHOLD MAX and TRENDHOLD MIN. The default value is `0.2`

WINDOWLEN integer

Specifies the number of points that Oracle OLAP uses when it determines median values when it performs median smoothing. Median smoothing eliminates extreme variations in the data by replacing each data point in a series by the median value of itself and its neighbors. You can specify any integer value from `1` through `13`. The default value is `3`.

Examples

Example 12-10 A Forecasting Program

Suppose you define a program named `autofcst` program to perform a forecast from the data that is in an input variable named `fcin1`. The `fcin1` variable is dimensioned by a time dimension named `timedim`. Assume that you have defined a program named `autofcst` with the following definition and specification.

```DEFINE autofcst PROGRAM
PROGRAM
" Using the Automatic forecasting method
" Suppose you want to create a forecast from the data in
" an input variable named fcin1 that is dimensionsed by
" a time dimension named timedim.
"
" Open a forecasting context
hndl = FCOPEN('MyForecast')
" Initialize the target variables
fcout1 = NA
fcseas1 = NA
fcsmseas1 = NA
" Specify that the forecast be of the AUTOMATIC type
fcset hndl method 'automatic'
" Execute the forecast
FCEXEC hndl time timedim INTO fcout1 -
seasonal fcseas1 smseasonal fcsmseas1 backcast fcin1
" Create a report showing the input and output of the forecast
REPORT DOWN timedim fcin1 fcout1 fcseas1 fcsmseas1
" Run a program named queryall to retrieve the characteristics
" of the forecasting trials
QUERYALL
" Close the forecasting context
FCCLOSE hndl
END

```

The `autofcst` program opens a forecasting context, sets the option of the forecast to AUTOMATIC, reports on the forecasted data, and queries and reports the characteristics of the various trials that Oracle OLAP performed to determine the method to use, and closes the forecasting context.

The `autofcst` program contains the following report command that displays a report of the input to and the output from the forecast.

```REPORT DOWN timedim fcin1 fcout1 fcseas1 fcsmseas1

```

The sample report created by this statement follows.

```TIMEDIM          FCIN1      FCOUT1    FCSEAS1   FCSMSEAS1
-------------- ---------- ---------- ---------- ----------
Jan97                  NA         NA 1.06725482 1.02926773
Feb97                  NA         NA .978607917 .945762221
Mar97                  NA         NA 1.12699278 .860505188
Apr97                  NA         NA .576219022 .905284834
May97                  NA         NA .920601317 .907019312
Jun97                  NA         NA 0.91118344  1.0580697
Jul97                  NA         NA 1.07886483 1.05597234
Aug97                  NA         NA 1.08101034   1.054612
Sep97                  NA         NA 1.08077427 1.05361672
Oct97               2,914         NA 1.08351799 1.05380407
Nov97               2,500         NA 1.01126778 1.04504316
Dec97               2,504         NA 1.08370549 1.03104272
Jan98               3,333         NA         NA         NA
Feb98               2,512         NA         NA         NA
Mar98               2,888         NA         NA         NA
...                 ...           ...        ...        ...
Jan01                  NA 3,371.7631         NA         NA
Feb01                  NA 2,736.4811         NA         NA
Mar01                  NA 3,408.3656         NA         NA
Apr01                  NA 714.277175         NA         NA
May01                  NA 2,502.9315         NA         NA
Jun01                  NA 3,195.3626         NA         NA
Jul01                  NA 3,911.6058         NA         NA
Aug01                  NA  4,000.651         NA         NA
Sep01                  NA 4,220.2658         NA         NA
Oct01                  NA 3,416.0208         NA         NA
Nov01                  NA 2,827.3943         NA         NA
Dec01                  NA 2,990.8629         NA         NA

```

The `queryall` program and a sample report created from its output is shown in "Querying a Forecast".