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


View PDF 
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 handleexpression
where
handle expression is one of the following:
Arguments
Indicates whether the risk of overadjustment 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 24hour periods, only an average hourly value is forecast, which is then multiplied by 24 hourly factors to give the value for each hour.
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 HoltWinters 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.
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.
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 HoltWinters 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.
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.
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.
Specifies the value of Gamma.
MAX specifies the maximum value of Gamma. Gamma is the seasonal parameter that is used for the HoltWinters 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.
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 timeseries expression specified in the FCEXEC command.
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
.
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/(Ky))
. 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 nonseasonal 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 HoltWinters 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.
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
.
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
.
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
.
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 cyclespec 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 finergrained search for factors that affect the data.
To specify a single cycle, set cyclespec 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 cyclespec 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 cyclespec 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.
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
.
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.
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.
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 HoltWinters 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
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 1210 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".