4 Predicting Future Performance

Steps for Creating a Forecast

Forecasting is not supported at this time in the Analytic Workspace Manager graphical user interface. The following sections explain how you can generate a forecast manually and store the results in a standard form measure.

These are the steps for creating a forecast. Each one is discussed in more detail in the sections that follow.

1. Creating the Forecast Time Periods

2. Defining a Measure for the Results

3. Defining Supporting Variables (Optional)

4. Developing a Forecast Program

5. Generating a Forecast

6. Aggregating the Forecast Data

Creating the Forecast Time Periods

The future time periods that you want to forecast must be defined as members of the time dimension in your analytic workspace. If they do not exist there already, you must:

1. Add the new time periods and attributes to the relational tables in the source schema.

2. Use the Maintenance Wizard in Analytic Workspace Manager to add the new members to the Time dimension in the analytic workspace.

You should use whatever mechanism guarantees that these Time dimension members are identical to those for loading actual data at a later date.

Defining a Measure for the Results

In the Model View, define a standard form measure for the forecast results, as described in Chapter 3. You can add the measure to the cube that contains the source data for the forecast, or you can create a new cube.

When you define a standard form measure, you create several analytic workspace objects. You will use one of them, a measure_stored variable, as the target of the forecast.

Defining Supporting Variables (Optional)

If you want the forecast to use seasonal adjustment or smoothing, then define variables that the forecast can use when performing its calculations. These variables do not require any standard form metadata, because they are only used during calculation of the forecast and are not accessed by the client.

Take these steps to define the supporting variables:

1. In the Object View, expand the folder for your analytic workspace.

2. Right-click the Variables folder and choose Create Variable from the menu.

3. Define the variable with a DECIMAL data type.

4. On the Dimensions page, list the dimensions in the appropriate order for variables in the cube, which is typically Time first, then a composite dimension.

"Defining a Variable for Seasonal Adjustment" provides an example of creating supporting variables.

Developing a Forecast Program

A forecast uses several related commands that are always executed from within an OLAP DML program. Use the following commands in the order they are listed here.

1. FCOPEN function. Opens a forecasting context and returns its handle.

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 (optional). Retrieves information about the characteristics of a forecast or a trial of a forecast.

5. FCCLOSE command. Closes a forecasting context.

You can define and compile a program either in the Object View or in OLAP Worksheet. You can run a program only in OLAP Worksheet.

Example 4-1 provides a template for these commands and others that are typically used in a forecast.

VARIABLE handle INTEGER    " Define a local variable
TRAP ON OOPS                 " Redirect processing on error to OOPS label

" Select base level time periods
LIMIT time_dim TO levelrel_time 'base_data'
" Keep historical and forecast periods
LIMIT time_dim KEEP LAST n

" Open a handle for the forecast
handle = FCOPEN('forecast_name')
" Specify the forecast method
FCSET handle METHOD 'method' descriptors 
" Execute the forecast and identify source and target variables
FCEXEC handle TIME time_dim INTO target_var1 SEASONAL -
     target_var2 SMSEASONAL target_var3 source_var
FCCLOSE handle             " Close the forecast
RETURN

OOPS:
SHOW 'Error running program'

Generating a Forecast

To generate the forecast data, run the forecast program in OLAP Worksheet, using the OLAP DML CALL command:

CALL program [(args)
]

The program calculates the forecast at the base level. You can then generate summary data by running the Maintenance Wizard. Be sure not to delete dimension members as a maintenance step, because you will lose the forecast data.

Aggregating the Forecast Data

In the OLAP DML, the AGGREGATE command calculates summary data that is stored permanently in the analytic workspace, and the AGGREGATE function triggers the calculation of the remaining summary data on the fly. An object called an aggmap identifies the areas of a cube that are calculated by the AGGREGATE command.

When you create a forecast measure, you create a formula that uses the AGGREGATE function to calculate forecast aggregates on the fly. It only calculates those areas of the cube that are not identified in the aggmap as precalculated by an AGGREGATE command. Because the Maintenance Wizard does not load data or precalculate the variable that stores the data for the forecast measure, you must execute the appropriate AGGREGATE command manually.

The name of the aggmap, which has the form OBJnnnnnnnn, is identified in the formula. The AGGREGATE command has this form:

AGGREGATE variable USING aggmap [COUNTVAR counter]

Where:

variable is a MEASURE_STORED object

aggmap is an AGGREGATIONDFN object

counter is a MEASURE_COUNTVAR object

Appendix A describes these standard form objects.

Defining the Sales Forecast Measure for Global Sales

Take these steps to create a measure for Sales Forecast:

1. Expand the UNITS_CUBE folder so that you can see its subfolders: Dimensions, Measures, and Calculated Measures.

2. In the UNITS_CUBE folder, right-click Measures and choose Create Measure from the pop-up menu.

3. Create a measure named SALES_FORECAST with a decimal data type. Use the aggregation specification from the cube.

   Note: Do not map this measure to a data source.

This procedure creates several objects in the analytic workspace. The UNITS_CUBE_SALES_FORECAST_STORED variable is the target for the forecast, and it will store the forecast results.

Defining a Variable for Seasonal Adjustment

The Global Sales Forecast will use seasonal adjustment.

To create a variable for the OLAP engine to use when adjusting for seasonality, take these steps:

1. In the Object View, expand the folder for the Global analytic workspace.

2. Right-click the Variables folder and choose Create Variable from the pop-up menu.

3. On the Basic tab, specify the name UNITS_CUBE_SALES_FORECAST_SEASONAL and the Short Decimal data type.

4. On the Dimensions tab, select TIME, CUSTOMER, PRODUCT, and CHANNEL, in this order to match the dimension order of UNITS_CUBE_SALES_FORECAST_STORED.

5. Choose Create.

Developing a Forecasting Program for Global Sales

Example 4-2 shows a program named FORECAST_SALES, which forecasts sales revenue. You can use this program as the basis of forecast programs in other analytic workspaces.

Although the program contains numerous commands, only four of them are used to define and execute the forecast. The default forecast method is AUTOMATIC, which uses the best method based on the historical data.

ARG _method                TEXT   " Forecasting method
ARG _last_time             TEXT   " Long desc of last hist time period
ARG _histperiods           INT    " Number of historical periods
ARG _fcast_periods         INT    " Number of forecast periods
ARG _periodicity           INT    " Number of periods in a cycle
VARIABLE _time_level       TEXT   " Base level of time dimension
VARIABLE _channel_level    TEXT   " Base level of channel dimension
VARIABLE _product_level    TEXT   " Base level of product dimension
VARIABLE _customer_level   TEXT   " Base level of customer dimension
VARIABLE _last_time_pos    INT    " Numeric position of _last_time in time dim
VARIABLE _handle           INT    " Forecast handle
 
TRAP ON OOPS              " Divert processing on error to OOPS label
 
" Set default values for args
if _method eq na
  then _method = 'AUTOMATIC'
if _last_time eq na
  then _last_time = 'Jun-04'
if _histperiods eq na
  then _histperiods = 48
if _fcast_periods eq na
  then _fcast_periods = 18
if _periodicity eq na
  then _periodicity = 12
 
" Identify base levels of dimensions (Product is value-based)
 _time_level='MONTH'
 _channel_level='CHANNEL'
 _customer_level='SHIP_TO'
 
" Set dimension status to base level
PUSH time channel product customer
LIMIT channel TO channel_levelrel EQ _channel_level
LIMIT product TO all
LIMIT customer TO customer_levelrel EQ _customer_level
LIMIT time TO time_levelrel EQ _time_level
 
" Check time parameters of forecast and refine status of time dimension
_last_time_pos = LIMIT(INTEGER time TO time_long_description EQ _last_time)
IF _histperiods + _fcast_periods GT STATLEN(time)
  THEN SIGNAL toosmall 'You specified more time periods than are defined.'
IF _last_time_pos - _histperiods lt 0
  THEN SIGNAL nohist 'You specified too many historical periods.'
IF _last_time_pos + _fcast_periods GT STATLAST(time)
  THEN SIGNAL nofuture 'You specified too many forecast periods.'
  ELSE LIMIT time KEEP -
     (_last_time_pos - _histperiods + 1) TO (_last_time_pos + _fcast_periods)
 
" Run the forecast
_handle = FCOPEN('sales')
FCSET _handle METHOD _method HISTPERIODS _histperiods PERIODICITY _periodicity
FCEXEC _handle TIME time INTO units_cube_sales_forecast_stored -
  SEASONAL units_cube_sales_forecast_seasonal units_cube_sales
FCCLOSE _handle
POP time channel product customer
RETURN
 
OOPS:
SHOW 'Program ended in an error.'

CALL forecast_sales('holt/winters')
CALL forecast_sales(na, na, 36, 6)

Aggregating the Sales Forecast Measure

To aggregate the forecast measure, take these steps:

1. From the Tools menu, choose OLAP Worksheet.

2. Use the DESCRIBE command to view the equation for the UNITS_CUBE_SALES_FORECAST formula.

   DESCRIBE units_cube_sales_forecast
   
   DEFINE UNITS_CUBE_SALES_FORECAST FORMULA DECIMAL <TIME CUSTOMER PRODUCT CHANNEL>
   EQ aggregate(this_aw!UNITS_CUBE_SALES_FORECAST_STORED using this_aw!-
   OBJ248542689 COUNTVAR this_aw!UNITS_CUBE_SALES_FORECAST_COUNTVAR)
   
   

3. Issue a command like this one to aggregate the SALES_FORECAST measure:

   AGGREGATE units_cube_sales_forecast_stored USING OBJ248542689
   
   

4. Save the summary data by typing these commands:

   UPDATE
   COMMIT
   
   

In the Model View, right-click SALES_FORECAST and choose View Data from the pop-up menu. Verify that the forecast results have been calculated, as shown in Figure 4-1.

Figure 4-1 SALES_FORECAST displayed in Measure Data Viewer

Description of "Figure 4-1 SALES_FORECAST displayed in Measure Data Viewer"