| Oracle® OLAP Application Developer's Guide 10g Release 1 (10.1) Part Number B10333-02 |
|
|
View PDF |
| Oracle® OLAP Application Developer's Guide 10g Release 1 (10.1) Part Number B10333-02 |
|
|
View PDF |
This chapter introduces the tools available in an analytic workspace to generate a forecast. It explains how to store the forecast in a standard form measure, and how to create a standard form cube for forecast results.
This chapter contains the following chapters:
The OLAP DML supports simple linear regressions, several non-linear regression methods, single exponential smoothing, double exponential smoothing, and the Holt-Winters method. If you are unsure of which method to use, you can have the OLAP engine decide the best fit for your data based on past performance.
Most forecasts are calculated at the base level. You then aggregate the base-level forecast data to generate forecast aggregates. Typically, you do not generate forecast aggregates from the aggregates of actual data. The examples in this chapter assume that you wish to generate forecast aggregates in this way.
However, at times you may want to generate forecasts at the aggregate level and then allocate the data to lower levels. This method of forecasting is also supported.
These are the steps for creating a forecast. Each one is discussed in more detail in the sections that follow.
Verify that the time periods for the forecast have been created in your time dimension. Add them if necessary.
Define the variables that will be used to store the results.
Write a program that generates the forecast.
Compile and run the program.
Check the results.
Add the results measure to a cube. Optionally, first create a new cube for forecasting results.
Create a new aggregation plan or modify an existing one to include the measure containing the forecast results. Deploy the aggregation plan.
Enable the analytic workspace for your applications.
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:
Add the new members and their attributes to the Time dimension table in the source schema.
Use the Refresh wizard in Analytic Workspace Manager to add the new members to the dimension in the analytic workspace.
You should use whatever mechanism guarantees that these Time dimension members will be identical when you load actual data.
A forecast requires a minimum of one variable for the results, and up to three variables if you want seasonal and smoothed seasonal forecasts. These variables typically have the same dimensions and data type as the variable used to generate the forecast.
Take these steps to define the variables for a forecast:
Define the results variable as a standard form measure.
Refer to "Adding Custom Measures to a Cube" for instructions on defining the variable and the aggregate formula, and for registering the measure.
For a seasonal forecast, define a second variable for the seasonal factors. Do not assign standard form properties to this variable. Instead, do the following:
In the Object View, expand the folder for your analytic workspace.
Right-click Variables and choose Create Variable from the menu.
Define the variable with a DECIMAL data type.
On the Dimensions page, list the dimensions in the appropriate order for variables in your cube, typically Time first, then a composite dimension.
For a smoothed seasonal forecast, define a third variable for the smoothing factors. Copy the seasonal factors variable by right-clicking the variable and choosing Create Like.
A forecast uses several related commands that are always executed from within an OLAP DML program. These commands define a forecasting context. Use the following commands in the order they are listed here.
FCOPEN function. Opens a forecasting context and returns its handle.
FCSET command. Specifies the characteristics of a forecast.
FCEXEC command. Executes a forecast and populates Oracle OLAP variables with forecasting data.
FCQUERY function (optional). Retrieves information about the characteristics of a forecast or a trial of a forecast.
FCCLOSE command. Closes a forecasting context.
|
See Also: For descriptions of the various forecasting methods, information about querying forecast trials, and the full syntax of these commands and functions, refer to the Oracle OLAP DML Reference. |
Example 10-1 provides a template for these commands and others that are typically used in a forecast.
Example 10-1 Template for 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'
Cubes provide a method of organizing measures with similar characteristics. There is no practical limit on the number of measures that you can associate with a particular cube. However, you may prefer to create a separate cube for some calculated measures, even though they have the same characteristics as an existing cube. For example, while you can add forecast measures to an existing cube with actual measures, you might not want to risk confusing them.
The metadata for cubes includes information about the source variables for its measures, such as the names of the composite and the aggregation maps. The following discussion assumes that these objects already exist. If not, refer to Chapter 6.
A cubedef object is a text dimension that lists the names of a cube's dimensions, as described in "Standard Form Cubes"Take the following steps to create a cubedef object using Analytic Workspace Manager.
Open the Object View and expand the folder for your analytic workspace.
Expand the Dimension folder and right-click a cubedef dimension for an existing cube.
Choose Create Like from the menu.
The Create Like dialog is displayed.
Type a name for the new cube.
To conform with the other cube names in the workspace, the name should end with _CUBE, such as SALES_CUBE.
Select the new cubedef dimension from the Dimension folder, and make these changes in the property viewer:
Basic page: Type a new description.
Properties page: Delete these properties: AW$LOADPRGS, LOAD_TYPE, SOURCE_NAME, SOURCE_OWNER. Then edit the other property values so they are appropriate for the new object. For more information about these properties, refer to Table 8-13.
The new cube does not use a load program to obtain data, and it should not appear in the Refresh wizard.
Open OLAP Worksheet. Use commands such as the following to add the names of dimensions as values:
MAINTAIN cube ADD 'dimension' 'dimension' ...
For example: MAINTAIN new_cube ADD 'PRODUCT' 'TIME'
From the File menu, choose Save to update the analytic workspace and all objects in the current schema.
All cubes must have a default aggregation map, which is used initially to guarantee that all queries are answered by fully solved measures. An aggregation map (or aggmap object in the language of the OLAP DML) contains all of the rules for aggregation.
To create a default aggregation map for a cube, take these steps:
In the Object View, expand the Aggregation Maps folder.
Right-click the default aggregation for a similar cube and choose Create Like from the menu.
A default aggregation map has a name such as UNITS_CUBE_AGGMAP_AWCREATEDDEFAULT_1.
Give the new aggregation map a similar name, such as FORECAST_CUBE_AGGMAP_AWCREATEDDEFAULT_1.
Select the new aggregation map and make these changes:
Properties page: Change the value of AW$PARENT_NAME to the name of the new cube.
Aggmap page: Verify that there is exactly one RELATION command for each dimension of the new cube. If changes are needed, make them and then click Compile to check the syntax of your changes.
Click Apply to save these changes in your session.
From the File menu, choose Save to save these changes for future sessions.
Registering a cube is very similar to registering a measure, as described in "Registering a New Measure". It involves most of the same catalogs. You can examine their property sheets in Analytic Workspace Manager, or you can issue this command in OLAP Worksheet to see their definitions:
DESCRIBE all_cubes all_descriptions aw_names cube_measures
For more information about these catalogs, refer to "Standard Form Catalogs".
The ALL_CUBES dimension is a list of all cubes in the analytic workspace. To see its contents, issue this OLAP DML command:
REPORT W 40 all_cubes
The names of the cubes have this detailed format:
schema.cube.CUBE
To add a new cube to ALL_CUBES, use this command syntax:
MAINTAIN all_cubes ADD detailed_cube_name
For example:
MAINTAIN all_cubes ADD 'GLOBAL.ANALYTICS_CUBE.CUBE'
Issue another REPORT command to make sure that the change was made correctly, then issue these commands to save it:
UPDATE; COMMIT
The ALL_DESCRIPTIONS variable stores the short, long, and plural descriptions of each object, as described in "ALL_DESCRIPTIONS Variable".
Use commands such as these to add descriptions of your new cube:
LIMIT all_languages TO 'AMERICAN.AMERICA' LIMIT all_cubes TO 'detailed_mcubename' LIMIT all_objects TO all_cubes all_descriptions(all_desctypes, 'SHORT')= 'short description' all_descriptions(all_desctypes, 'LONG')= 'long description' all_descriptions(all_desctypes, 'PLURAL')= 'plural description'
Issue another REPORT command to make sure that the changes were made correctly, then issue these commands to save them:
UPDATE; COMMIT
The AW_NAMES variable identifies the full name of objects in the analytic workspace, as described in "AW_NAMES Variable".
Use commands such as these to add the workspace name of a new cube:
LIMIT all_cubes TO 'detailed_cube_name' LIMIT all_objects TO all_cubes aw_names = 'full workspace object name'
Issue another REPORT command to make sure that the changes were made correctly, then issue these commands to save them:
UPDATE; COMMIT
The CUBE_MEASURES valueset identifies the measures in each cube, as described in "CUBE_MEASURES Valueset".
Use commands such as these to add measures to the new cube:
LIMIT all_cubes TO cube LIMIT cube_measures ADD 'detailed measure name . . .'
For example:
LIMIT all_cubes TO 'ANALYTICS_CUBE' LIMIT cube_measures ADD 'GLOBAL.ANALYTICS_CUBE.PROFIT.MEASURE' - 'GLOBAL.ANALYTICS_CUBE.SALES_PCTCHG.MEASURE'
Issue another REPORT command to make sure that the changes were made correctly, then issue these commands to save them:
UPDATE; COMMIT
If you made errors in creating a cube, then errors will occur when you try to aggregate the cube, or refresh or enable your analytic workspace. Follow this check list to identify the cause of failure.
Check the properties of the cube dimension against those listed in "Standard Form Cubes". If you copied another cube, make sure that you made all of the appropriate changes to the property values.
Verify that you populated the cube dimension.
REPORT forecast_cube FORECAST_CUBE -------------- CHANNEL CUSTOMER PRODUCT TIME
Verify that you added the cube to the ALL_CUBES dimension.
REPORT W 30 all_cubes ALL_CUBES ------------------------------ GLOBAL_AW.PRICE_CUBE.CUBE GLOBAL_AW.UNITS_CUBE.CUBE GLOBAL_AW.FORECAST_CUBE.CUBE
Verify that you added the cube to the AW_NAMES variable.
LIMIT all_objects TO all_cubes REPORT W 42 DOWN all_objects W 35 aw_names ALL_OBJECTS AW_NAMES ------------------------------------------ ----------------------------------- <ALL_CUBES: GLOBAL_AW.PRICE_CUBE.CUBE> GLOBAL_AW.GLOBAL!PRICE_CUBE <ALL_CUBES: GLOBAL_AW.UNITS_CUBE.CUBE> GLOBAL_AW.GLOBAL!UNITS_CUBE <ALL_CUBES: GLOBAL_AW.FORECAST_CUBE.CUBE> GLOBAL_AW.GLOBAL!FORECAST_CUBE
While you could add the forecast measure to the Units cube, which contains the actual data, this example will create a new cube for it named FORECAST_CUBE. FORECAST_CUBE has the same dimensions as UNITS_CUBE, so the two cubes will share a composite dimension, UNITS_CUBE_COMPOSITE. The forecast will populate a single measure in the Forecast cube.
This example assumes that you have created the SALES measure, as described in Chapter 9.
These are the basic steps to create a new cube named FORECAST_CUBE:
In the Object View of Analytic Workspace Manager, copy UNITS_CUBE as FORECAST_CUBE using Create Like. This step copies the object definition, but not the contents, of UNITS_CUBE.
On the Properties page for FORECAST_CUBE, change the following properties, then click Apply:
AW$LOADPRGS, LOAD_TYPE, SOURCE_NAME, SOURCE_OWNER: Delete so that cube will be ignored by the Refresh wizard.
AW$LOGICAL_NAME: Set to FORECAST_CUBE
AGGMAPLIST: Set to FORECAST_CUBE_AGGMAP_AWCREATEDDEFAULT_1
DISPLAY_NAME: Set to Sales Forecast Cube
Right-click UNITS_CUBE_AGGMAP_AWCREATEDDEFAULT_1, and choose Create Like to create a default aggregation map named FORECAST_CUBE_AGGMAP_AWCREATEDDEFAULT_1.
On the Properties page, change the value of AW$PARENT_NAME to FORECAST_CUBE.
The new cube has the same dimensions as the Units cube. Otherwise, you would need to edit the aggregation map.
To save the new definitions, choose Save from the File menu.
To add the dimensions of the Forecast cube, issue this command:
MAINTAIN forecast_cube ADD 'CHANNEL' 'CUSTOMER' 'PRODUCT' 'TIME'
To register the FORECAST_CUBE cube, open OLAP Worksheet and issue the following commands:
" Add FORECAST_CUBE to the ALL_CUBES dimension MAINTAIN ALL_CUBES ADD 'GLOBAL_AW.FORECAST_CUBE.CUBE' " Add descriptions to the ALL_DESCRIPTIONS variable LIMIT all_cubes TO 'GLOBAL_AW.FORECAST_CUBE.CUBE' LIMIT all_objects TO all_cubes LIMIT all_languages TO 1 all_descriptions(all_desctypes, 'SHORT')= 'Sales Fcast' all_descriptions(all_desctypes, 'LONG')= 'Sales Forecast all_descriptions(all_desctypes, 'PLURAL')= 'Sales Forecasts' " Add cube name to the AW_NAMES variable aw_names = 'GLOBAL_AW.GLOBAL!FORECAST_CUBE' " Save these changes UPDATE COMMIT
The results of this forecast are stored in three variables. Only one is of interest to analysts; the other two hold the seasonal and smoothing adjustment factors used to create the forecast.
The quickest way to define the standard form measure is using the CREATE_MEASURE program shown in Example 9-3, "DML Program for Adding Measures to UNITS_CUBE". Take these steps:
In the Object View of Analytic Workspace Manager, attach GLOBAL in read/write mode. If the program is in a separate workspace, then it must be attached also, in either read-only or read/write mode.
Open OLAP Worksheet and issue the following command:
AW LIST
The GLOBAL analytic workspace must be listed first because the new workspace objects will be created in the first one listed. If GLOBAL is not first, then issue this command:
AW ATTACH global FIRST
Issue this command to create a standard form measure for the forecast results:
CALL create_measure('sales_fcast', na, 'forecast_cube')
The arguments specify a new measure named SALES_FCAST, a new variable whose name is constructed from the measure name, and a cube named FORECAST_CUBE.
Create SALES_FCAST_SEASONAL by taking these steps:
In the Object View, right-click Create Variable.
The Create Variable dialog is displayed.
On the Basic page, define SALES_FCAST_SEASONAL with a DECIMAL data type.
On the Dimensions page, list TIME first, then UNITS_CUBE_COMPOSITE.
Click Create to save this variable definition in the current session.
Create SALES_FCAST_SMOOTHED by right-clicking SALES_FCAST_SEASONAL and choosing Create Like from the menu.
From the File menu, choose Save.
Example 10-2 shows a program named FORECAST_SALES, which forecasts sales in the GLOBAL analytic workspace. You can use it as the basis of forecast programs in your analytic workspace.
The forecast itself requires only four commands. The default forecast method is AUTOMATIC, which permits the OLAP engine to select the best method based on the data. Seasonality is also specified, and both seasonal and smoothed seasonal variables are targeted.
In the GLOBAL analytic workspace, there are 65 historical periods (Jan-98 to May-03) and 12 forecast periods (Jun-03 to May-04). Because the base time period is a month, seasonal adjustments are based on a 12-period cycle. The program uses the INTEGER argument of the LIMIT function to obtain the numeric position of the last historical time period, and sets the status of TIME relative to that position.
The FORECAST_SALES program takes five arguments:
The forecasting method (AUTOMATIC, LINREF, NLREL1 to NLREG5, SESMOOTH, DESMOOTH, or HOLT/WINTERS). These methods are described in the Oracle OLAP DML Reference.
The long description of the last time period for which there is data.
The number of historical periods to be used in the forecast.
The number of periods to forecast.
The number of periods in a seasonal cycle.
Default values are set for these arguments so that they can be omitted from the command line. These are some of the ways you can run this program:
CALL forecast_sales
CALL forecast_sales('holt/winters')
CALL forecast_sales(na, na, 36, 6)
Because arguments are passed sequentially to the program, you may need to pass an NA as a placeholder value for some arguments, as shown in the third example. Later arguments can simply be omitted.
The program arguments, along with some preset local variables, are used to select the dimension members in the status. All dimensions are limited to the base level, so that precalculated aggregates will not be used in the forecast. In addition, the TIME dimension must be limited so that only the source historical periods and the target forecast periods are in status.
Example 10-2 Forecasting Program for Global Sales
DEFINE FORECAST_SALES PROGRAM
PROGRAM
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 = 'May-03'
if _histperiods eq na
then _histperiods = 48
if _fcast_periods eq na
then _fcast_periods = 12
if _periodicity eq na
then _periodicity = 12
" Identify base levels of dimensions
_time_level='MONTH'
_channel_level='CHANNEL'
_product_level= 'ITEM'
_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 product_levelrel EQ _product_level
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 sales_fcast_variable -
SEASONAL sales_fcast_seasonal_variable -
SMSEASONAL sales_fcast_smoothed_variable sales
FCCLOSE _handle
POP time channel product customer
RETURN
OOPS:
SHOW 'Program ended in an error.'
END
Example 10-3 shows partial results from running the FORECAST_SALES program with the default settings. The SALES measure has data only for historical time periods (May-03 and earlier), and the SALES_FCAST measure has data only for forecast time periods (Jun-03 and later). SALES_FCAST_SEASONAL and SALES_FCAST_SMOOTHED store the factors in the cells for the first seasonal cycle (12 months).
Locating data in a very sparse measure can be a challenge. Limit the time dimension to the periods of interest for the forecast, and limit all of the other dimensions to one member that you know has data. Example 10-3 also shows how to limit a dimension by level, attribute value, position, or value.
Example 10-3 Viewing Forecast Results for Global Sales
LIMIT time TO time_levelrel EQ 'MONTH' "Select base level time periods
"Remove periods not used in forecast
LIMIT time REMOVE time_end_date LT '30JUN00'
"Select base level channels and products
LIMIT channel TO channel_long_description EQ 'Direct Sales'
LIMIT product TO product_levelrel EQ 'ITEM'
LIMIT product KEEP FIRST 1 "Keep just the first product
LIMIT customer TO '51' "Select customer 51
REPORT W 5 DOWN time W 12 <time_long_description sales -
sales_fcast sales_fcast_seasonal sales_fcast_smoothed>
CHANNEL: 2
PRODUCT: 13
CUSTOMER: 51
ALL_LANGUAGES: AMERICAN_AMERICA
-------------------------TIME_HIERLIST--------------------------
----------------------------CALENDAR----------------------------
TIME_LONG_DE SALES_FCAST_ SALES_FCAST_
TIME SCRIPTION SALES SALES_FCAST SEASONAL SMOOTHED
----- ------------ ------------ ------------ ------------ ------------
48 Jun-00 2,893.68 NA 0.32 0.70
49 Jul-00 2,840.35 NA 0.78 0.70
50 Aug-00 5,739.92 NA 1.16 0.70
51 Sep-00 5,821.08 NA 0.74 0.70
52 Oct-00 5,034.92 NA 0.32 0.69
53 Nov-00 2,488.27 NA 0.74 1.37
54 Dec-00 5,100.34 NA 1.36 1.22
55 Jan-01 NA NA 0.70 1.24
56 Feb-01 4,903.58 NA 1.35 1.28
57 Mar-01 4,893.34 NA 1.39 1.32
58 Apr-01 4,824.84 NA 1.49 1.37
59 May-01 4,791.26 NA 1.65 0.70
.
.
.
91 Jun-03 NA 0.98 NA NA
92 Jul-03 NA 1.01 NA NA
93 Aug-03 NA 1.21 NA NA
94 Sep-03 NA 1.12 NA NA
95 Oct-03 NA 1.07 NA NA
96 Nov-03 NA 1.05 NA NA
97 Dec-03 NA 1.06 NA NA
103 Jan-04 NA 1.79 NA NA
104 Feb-04 NA 1.84 NA NA
105 Mar-04 NA 1.89 NA NA
106 Apr-04 NA 1.93 NA NA
107 May-04 NA 1.96 NA NA
108 Jun-04 NA NA NA NA
You can create and deploy an aggregation plan for the new Forecast cube the same as any other cube:
In the OLAP Catalog View, expand the GLOBAL analytic workspace folder.
Right-click FORECAST_CUBE and choose Create Aggregation Plan Using Wizard from the menu. Follow the steps of the wizard.
After creating the aggregation plan, expand the Aggregation Plans folder.
Right-click the name of the aggregation plan and choose Deploy Aggregation Plan Using Wizard.
To make the forecast available to applications, re-enable the GLOBAL analytic workspace.
If you experience problems with running any of these wizards, refer to "Troubleshooting a Hand-Crafted Cube".
|
 Copyright © 2002, 2003 Oracle Corporation All Rights Reserved. |
|