9 Generating Forecasts

Time Series

Time series forecasting methods are based on the premise that you can predict future performance of a measure simply by analyzing its past results. These methods identify a pattern in the historical data and use that pattern to extrapolate future values.

Past results can, in fact, be a very reliable predictor for a short period into the future. You can generate this type of forecast very quickly and easily, and you do not need either forecasting expertise or an in-depth knowledge of your data. The modeling techniques used by the time-series methods are relatively simple and run very fast. Time-series forecasting is extremely useful when hundreds or thousands of items must be forecast.

You may also use time-series methods to generate forecast data further into the future. However, the results will not be as accurate, because factors other than past performance have a greater impact over time. For example, you or your competitors may change the pricing structure or run advertising campaigns, competitive products may come onto the market, or shifts in the economy or political events may affect performance. You should consider the forecast data generated by time series methods to be one component of a medium- or long-range forecast, which may be adjusted by expert opinion and other factors.

Analytic Workspace Manager provides access to a time-series forecasting engine, which is described in this chapter.

Causal Analysis

Causal analysis takes into consideration the external factors (the causes) that can affect a forecast, as described under "Time Series". Statistical regression methods are the basis for causal analysis. They use the forecasts for several independent measures to forecast a dependent measure. This type of forecast requires considerable skill and understanding of forecasting methodology and the relationships between independent and dependent variables. A good regression model will produce the best results for medium-range forecasts.

However, because of the time, expense, and expertise needed to develop a model, most businesses restrict regression analysis to a few key business measures. For the other measures, they use a combination of methods including time-series and expert opinion.

The forecasting engine used by Analytic Workspace Manager does not support causal analysis. The linear and nonlinear regression methods in the forecasting engine are time-series regression methods that use historical data from a single measure.

Expert Opinion

As the time horizon for the forecast moves further out into the future, expert opinion becomes the most reliable predictor. The experts, who are usually corporate executives, have their fingers on the pulse of myriad factors that may influence future performance, such as the general direction of the market and plans for new products. Customer surveys also provide input to long-term forecasts. An equivalent computer model to rival expert opinion for long-term forecasts would be too complex to generate within a usable time frame.

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 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.

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

Creating a Forecast Measure

You can store the forecast data in the same measure as the actual data, or you can store it in a separate measure. If you store the forecast in the same measure, then the actual data will eventually overwrite it. This prevents you from monitoring the accuracy of the forecast. For this reason, you should create a separate forecast measure in the same cube as the source measure.

To create a forecast measure:

1. In the navigation tree, expand the cube for the actual data.

2. Right-click Measures and choose Create Measure.

3. Complete the Create Measure property sheet. Do not map the measure to a data source.

Selecting the Historical Data

The forecasting engine needs only a year of data to detect trends and seasonality. Business cycles may take two or three years of data to detect.

If your business has experienced a paradigm shift, then you should exclude previous data from your forecast as irrelevant. The following are examples of events that might cause a paradigm shift:

• Cellular telephones on the telecommunications industry.

• Digital cameras on the photography industry.

• The Internet on the book and music publishing industries.

You will select the historical data when creating the forecast step.

Identifying the Levels for the Forecast

To generate consistent data at all levels of a hierarchy, you must generate the forecast data at a single level and use it to populate the other forecast levels by aggregation or allocation. If you generate a forecast from multiple levels, then the aggregate forecast data may be inconsistent with the lower levels of forecast data.

The "correct" levels are determined by the time frame of your forecast and by your reasons for making the forecast. For example, you may forecast Customers at the Total level for manufacturing, but at a lower level for marketing. Table 9-2 shows the recommended dimension levels for forecasting products over various time frames.

If you set the levels too low, then large variations in the data may decrease accuracy. These inaccuracies may be magnified in the aggregated forecasts. If you set the levels too high, then the aggregated forecasts may smooth out localized trends and allocate them incorrectly.

You will select the levels when creating the forecast step.

Creating a Forecast Step

To create a forecast step in Analytic Workspace Manager:

1. In the navigation tree, create a new Calculation Plan or open an existing plan.

2. On the General tab of the Calculation Plan, click New Step, then select New Forecast Step.

   The New Forecast Step property pages are displayed.

3. Complete the General page. For the forecast method, select Automatic.

   For information about using other methods, refer to "Designing Your Own Forecast". For information about completing the other fields, click Help.

4. Keep the default values on the Advanced Settings page unless you have expertise in time-series forecasting.

5. On the Status page, select the historical time periods and other dimension values that will be used as the basis for the forecast. Select only one level for each dimension.

6. Save the forecast step, then save the Calculation Plan.

Generating the Forecast

To generate the forecast data:

If all the time periods and data are already loaded into the analytic workspace, then right-click the Calculation Plan and choose Execute Calculation Plan.

or

If you need to load new data, then include the Calculation Plan in the regular maintenance process using the Maintenance Wizard.

Afterward, you can view the forecast data in the Measure Viewer.

Evaluating the Forecast Results

If the forecast does not initially look plausible to you, then check that there are no errors in the design of the forecast:

• Compare the first few forecast periods to the last few historical periods to verify that a discrepancy exists.

• Use the forecast step editor to check the number of forecast periods against the status of the Time dimension. The forecast periods are the last ones in status. For example, if the Time dimension has dimension members defined through the next five months and you designed a 4-month forecast, then you must remove the last month from status. Otherwise, the forecast will be based on a month of null historical data.

• Use the Measure Viewer to verify that all of the historical data has been loaded in the source measure. If several periods immediately prior to the forecast period have not been loaded, then the forecast will be 0.

• If you used a specific forecasting method (not Automatic):

  • Compare its results with those of the Automatic option.

  • Verify that you set Forecast Approach to Manual and Data Filter to the appropriate choice.

• If you set any of the advanced parameters, then compare the results against a forecast that uses the default settings.

A standard part of forecasting is to continually monitor the accuracy of the forecast data. The easiest way to compare the forecast data with the actual data is to set up a standard report that includes a line graph. Then you can see how closely the forecast data converges with the actual data.

Short-term forecasts should be fairly precise, with only a small difference between forecast and actual data. If this is not the case, then you should consider modifying the forecast using some of the suggestions listed previously. You may even want to create several forecasts and compare their results over time.

Medium- and long-range forecasts generated by time-series forecasting methods should be qualified by other input, such as expert opinion, because external factors will affect performance in these time frames.

What is the Expert System?

The Expert System supports the Automatic method by identifying the best statistical method and selecting the best parameter settings for your data. It also distinguishes outliers from factors like trend and seasonality.

The Expert System separates the data into seasonal effects and trend effects. It then uses an iterative approximation method to forecast the seasonal component of the data. After completing the trend forecast, it factors the seasonality portion into the trend forecast for all methods except Holt-Winters, which calculates its own seasonal factors.

The Expert System represents a type of artificial intelligence for statistical forecasting that has been in common use ever since computers took over the task of performing complex and lengthy numerical calculations. Instead of the analyst's having to evaluate the data and make an educated guess as to the best method, the software can quickly try all methods and select the best one based on the results.

You can override the Expert System by setting the Forecast Approach[APPROACH] parameter to Manual. The default value of Automatic gives the Expert System the most control in overriding your choices. This is the appropriate setting when using the Automatic method, but it will invalidate your attempt to design a forecast.

What is the Verification Window?

The Expert System always tests the accuracy of a forecast method using a portion of the historical data called a verification window. For the Automatic method, the Expert System uses this window to select the best statistical method. For the other methods, it verifies that your selection of a method and the parameter settings provide a good fit to the historical data.

For this test, the Expert System divides the historical time periods into two groups. The older time periods retain their role as historical data. The newer historical time periods become the "forecast" periods and form the verification window. The Expert System generates forecast data for the newer time periods, using the older time periods as the basis for the forecast.

The Expert System calculates the precision of the method by comparing the forecast data to the actual data in the verification window. The precision is the distance between the forecast data and the actual data.

The Expert System uses several standard calculations to compare the precision of different forecasting methods: Mean Absolute Deviation (MAD), Mean Absolute Percentage Error (MAPE), and Root Mean Square Error (RMSE).

When Should You Design a Forecast?

You may want to control the forecast when you have special knowledge that future performance will deviate from past results.

For example, you may recently have entered an agreement for a major national chain of stores to carry your products, so you anticipate a dramatic increase in sales. Or your company might have been an innovator in developing a new product line, but your competitors are about to introduce rival products. In this case, you expect sales to level off. You or your competitors might also be negotiating a corporate merger, and you expect that transaction to affect performance.

Under circumstances like these, your special knowledge may enable you to design a more accurate forecast than the Expert System.

Overriding the Expert System

To override the Expert System, take these steps:

1. Create or edit a forecast step, as described in "Creating a Forecast Step".

2. On the General page, select the method that best describes the future performance that you expect, based on your expert knowledge.

3. On the Advanced Settings page, set Forecast Approach to Manual.

4. Set the Data Filter parameter to an appropriate setting for your data.

5. Change the Verification Window Size parameter as desired.

6. Make whatever other changes to the parameter settings are appropriate.

7. Complete the definition of the forecast, and run it as described in "Creating a Forecast".

Automatic

The Expert System identifies the best fit by quickly testing each statistical method against the portion of historical data specified by the Verification Window Size parameter. The Expert System selects the method and the parameter settings that would have generated the most accurate forecast in the past. It automatically detects and handles outliers, removing noise so that it can better detect trends and seasonality.

The forecasting engine generates a forecast for every combination of dimension members. The Expert System evaluates each forecast separately and picks the best method and parameter settings for each one.

In general, Automatic is the best choice unless you have knowledge that future performance will deviate from the past. Under these special circumstances, you can substitute your own expert judgment for the Expert System.

"What is the Verification Window?" provides more information about how the Expert System selects a method.

Regressions

Time series regression methods relate a variable (measure) to functions of time describing trend and seasonal components. Regression generates the most reliable forecasts when the trend or seasonal components remain constant.

OLAP forecasting provides both linear and nonlinear regression models.

Exponential Smoothing

The exponential smoothing methods weight the historical data using exponentially decreasing weights. The prior period has the most weight and each period prior to it has comparatively less weight. The decline in weight is expressed mathematically as an exponential function. The smoothing parameters determine the weights.

Setup Parameters

These parameters provide the forecasting engine with basic information about how you want it to approach a forecast. Always set the Forecast Approach and Data Filter parameters when using a specific forecasting method.

• Forecast Approach[APPROACH]: Specifies whether the forecasting engine gives control to the Expert System.

  • Automatic: Give control to the Expert System. Use this setting with the Automatic method.

  • Manual: Give control to the user. It enables you to choose a method and set the parameters that are appropriate for the historical data. Use this setting with all methods other than Automatic.

• Data Filter[TRANSFORM]: Identifies a basic characteristic of the data.

  • Non-Seasonal Data:[TRNOSEA]: No seasonality.

  • Seasonal Data [TRSEA]: Adjust for seasonal patterns in the data. You can use this filter with Double Exponential Smoothing to get a more accurate forecast than Holt-Winters.

  • Intermittent Data [TRMPT]: Adjusts for sporadic or intermittent data and, if appropriate, seasonal patterns. Intermittent data has null or zero for over 50% of the values. Do not use median smoothing with this filter, because smoothing eliminates the intermittent characteristic of the data. The purpose of the intermittent data filter is to forecast intermittent demand.

    Set the Moving Periodic Total Decay parameter when using this filter.

• Verification Window Size[RATIO]: The Expert System uses the verification window to determine the best method and parameter settings, as described in "What is the Verification Window?".

  The verification window is specified as a fraction of the total number of historical periods. For example, assume that you have three years of historical data for 2004, 2005, and 2006. The default window size is .3333, so the Expert System will use 1/3 of the historical data for the verification window. Thus, the data for 2004 and 2005 will be used to generate a "forecast" for 2006. The difference between the forecast data and the actual data for 2006 indicates the precision of the method.

  You may want to adjust the window size, depending on the granularity of the data. For monthly data, use a window size of 20% (1/5) or more. For weekly data, use a window size of 12.5% (1/8) or more. For daily or hourly data, you can use a window size of 11.1% (1/9) or less.

General Parameters

These parameters apply to all of the specific forecasting methods.

• Allocate Last Cycle[ALLOCLAST]: Controls whether the last cycle is calculated by forecasting alone or with allocation. Allocation may reduce the risk of overadjustment for trend or seasonality.

  Allocation forecasts an average value for one period of the last cycle. That average value is then multiplied by factors to give the remaining points in that period. For example, a forecast at the day level would calculate an average for all days in the last week rather than forecasting individual days.

  Set Periodicity to a value greater than 1 when using this parameter.

• Boundary Maximum and Minimum[MAXFACTOR, MAXFCFACTOR]: Boundaries constrain the forecasting engine from occasionally generating unreasonably high or low values. The upper boundary is calculated by multiplying Boundary Maximum by the largest value in the historical series. The lower boundary is calculated by multiplying Boundary Minimum by the smallest value in the historical series.

  For example, if the Boundary Maximum parameter is 100.0 and the largest historical value 5,600, then no forecast value can be greater than 560,000. If the Boundary Minimum parameter is 0.5 and the smallest historical value 300, then no forecast value can be less than 150.

• Moving Periodic Total Decay[MPTDECAY]: The maximum value of a decay constant that is inversely related to noise, random deviation, and stability in the history of intermittent data. Set this value higher when the history is evolving rapidly from one cycle to the next or when the noise level is low. This parameter is used only with the Intermittent Data filter. The difference between the maximum and the minimum must be evenly divisible by 0.4.

• Periodicity[PERIODICITY]: The number of periods in a single cycle or the number of periods in each set of nested cycles. The default value of 1 does not group the periods at all, so each period is logically independent.

  For example, if you are using Month as the base level for the forecast, and the time hierarchy has levels for Month, Quarter, and Year, then the cycles are 12 months in a year and 3 months in a quarter. For a single cycle, enter the number of periods. For nested cycles, list the cycles in parentheses from the most aggregate to the least aggregate, separated by commas, such as (12,3).

• Trials[TRIALS]: The number of trials that are run to determine the best method and combination of parameter settings.

Historical Data Smoothing Parameters

These parameters help generate a smoother forecast from intermittent historical data. Alternatively, you can use the intermittent data filter to forecast intermittent demand. Do not combine the smoothing parameters with the intermittent demand filter, because these adjustments are contradictory.

• Use Smoothed Historical Data[COMPSMOOTH]: Controls whether the historical data is smoothed. Smoothing is typically used for weekly or finer-grained data that has many missing values. Smoothing the historical data produces a smoother baseline forecast.

• Interpolate Missing Values[SMOOTHING]: Specifies whether you want to smooth the data by inserting estimates for missing values instead of by averaging. This parameter is useful when missing values indicate incomplete data instead of a lack of activity.

• Median Smoothing Window [WINDOWLEN]: The number of time periods used in a median smoothing window to identify outliers and replace them with adjusted data values. 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. This setting must be an odd number, so that the current time period is in the center of the window.

  The larger the window, the smoother the data. If the window is too large, smoothing may eliminate important data patterns. If the window is too small, then smoothing may include outliers that could not be filtered out. As a rule, you should not set this parameter below 3; setting it to 1 has the effect of turning off smoothing.

  For monthly data, use a maximum value of 5 to prevent excessive flattening of the data. For weekly data, use a maximum of 13. Use a longer window (15 or more) for daily or hourly data.

Creating the Sales Forecast Target Measure

This example stores the forecast data in a separate measure from the historical data so that the results of the forecast can be evaluated more easily.

To create the target measure:

1. In the UNITS_CUBE folder, right-click Measures and select Create Measure.

   The Create Measure dialog box opens.

2. On the General page, create a measure named SALES_FORECAST.

3. Select Override the Aggregation Specification of the Cube.

4. On the Summarize To page, deselect all levels for all dimensions.

5. Click Create.

The measure is not mapped to a data source, so no aggregation needs to be done during regular builds. Instead, aggregation will be defined in the Calculation Plan.

Creating the Calculation Plan

Sales Plan will have a forecast step, an allocation step, and an aggregation step.

To create a new Calculation Plan:

1. Right-click Calculation Plans and select Create Calculation Plan.

   The Create Calculation Plan dialog box opens.

2. Create a new plan named SALES_PLAN. Click Create.

   SALES_PLAN appears as a new item in the Calculation Plans folder. It does not yet contain any steps.

Creating the Sales Forecast Step

To create the forecast step:

1. On the General page of SALES_PLAN, click New Step, then select New Forecast Step.

   The Create Forecast Step dialog box opens.

2. Complete the General page with these values, as shown in Figure 9-1

   • Name: forecast_sales_step

   • Cube: UNITS_CUBE

   • Source Measure: SALES

   • Target Measure: SALES_FORECAST

   • Time Dimension: TIME

   • Forecast Method: Automatic

   • Number of Forecast Periods: 5

   Figure 9-1 Forecasting Global Sales

   
   Description of "Figure 9-1 Forecasting Global Sales"
   
   

3. Keep the default settings on the Advanced Parameters page.

4. On the Status page, set the Time dimension:

   1. On the Selected Steps tab, click All Levels and select Month from the drop-down list.

   2. On the Available Conditions tab, expand the Hierarchy folder. Select Children of Jan-98 and click the Edit Step icon.

      The Edit Step dialog box opens, as shown in Figure 9-2.

   3. Set Action to Remove, and set Relation to Descendants.

   4. Click Member and choose More from the list.

      The Select Members dialog box opens.

   5. Select 2005.

   6. Click OK to close the Select Members dialog box, then click OK to close the Edit Step dialog box.

   7. Add this condition to the Selected Steps.

   8. On the Members tab, verify that only months are in the list and Dec-04 is the last value.

   Figure 9-2 Selecting Time Dimension Members

   
   Description of "Figure 9-2 Selecting Time Dimension Members"
   
   

5. Set the status of the Customer dimension:

   1. On the Steps tab, click All Levels and choose Total Customer.

   2. On the Members tab, verify that All Customers is the only value.

6. Set the status of the Product dimension:

   1. On the Steps tab, remove the initial selection.

   2. On the Conditions tab, expand the Hierarchy folder. Add the Children of Total Product condition to the Selected Steps.

   3. On the Members tab, verify that Hardware and Software/Other are the only values.

7. Set the status of the Channel dimension:

   1. On the Steps tab, click All Levels and choose Total Channel.

   2. On the Members tab, verify that All Channels is the only value.

8. Click Create to save the forecast step.

9. Click Apply to save the Calculation Plan.

Generating and Validating the Forecast

You do not need to generate the forecast now. You can wait until you have created all the steps of the Calculation Plan. However, this step-by-step approach simplifies troubleshooting.

To generate the forecast:

1. Expand the Calculation Plans folder. Right-click SALES_PLAN and choose Execute Calculation Plan SALES_PLAN.

   The Maintenance Wizard opens, and SALES_PLAN is a selected target object.

2. Click Finish.

   The build log is displayed when the Calculation Plan is done executing.

To view the forecast results, take these steps:

1. Fully expand the UNITS_CUBE folder, right-click the SALES_FORECAST measure, and choose View Data SALES_FORECAST.

   The Measure Data Viewer opens. No data is displayed, because the base levels for Product, Customer, and Channel are NA.

2. From the File menu, choose Query Builder.

   The Query Builder opens.

3. On the Layout tab, switch Product and Customer. Click Help for instructions.

4. On the Dimensions tab, set the status of Time:

   1. On the Steps tab, remove the initial selection.

   2. On the Conditions tab, expand the Hierarchy folder.

   3. Change Children of 1998 to Children of Q3-04, Q4-04, and add this condition to the Selected Steps.

   4. On the Members tab, verify that only months are in the list from Jul-04 to Dec-04.

5. Set the status of Product:

   1. On the Steps tab, remove the initial selection.

   2. On the Available Members tab, expand Total Product.

   3. Select Hardware and Software/Other, and add them to the Selected list.

6. Click OK to close the Query Builder.

Figure 9-3 shows the results of the forecast, which are displayed in the Measure Viewer. Notice the empty cells in the crosstab that need to be populated by allocation.

Figure 9-3 Forecast Data Displayed in the Measure Viewer

Description of "Figure 9-3 Forecast Data Displayed in the Measure Viewer"

Creating an Allocation Basis Measure

This example uses the Proportional method to distribute the values based on the sales performance for the previous year. Before creating the allocation step, you must create a calculated measure for sales values for the prior year to use as the basis measure.

To create the basis measure if you did not already create SALES_PY in Chapter 8:

1. Expand the UNITS_CUBE folder, right-click Calculated Measures, and choose Create Calculated Measure.

   The Calculation Wizard opens.

2. Complete the Name and Type page with these values:

   • Name: sales_py

   • Calculation Type: Prior Value (under Prior/Future Comparison)

3. Complete the Prior Value page with these values, as shown in Figure 9-4.

   • Measure: Sales

   • Over Time in: Calendar Year

   • Go back by: 1 Year

Figure 9-4 Creating a Calculated Measure

Description of "Figure 9-4 Creating a Calculated Measure"

Creating the Allocate Sales Forecast Step

The forecast created the data only for a single level of each dimension. Only Time is populated at the base level. The data must be allocated to the base levels of the other dimensions before it can be aggregated.

To create an allocation step:

1. On the General page of Sales Plan, click New Step, then select New Allocation Step.

   The Create Allocation Step dialog box opens.

2. Complete the General page with these values:

   • Name: allocate_sales_forecast_step

   • Cube: UNITS_CUBE

   • Source Measure: SALES_FORECAST

   • Target Measure: SALES_FORECAST

   • Basis Measure: SALES_PY

3. On the Rules page, select None for the Time operator. For the other dimensions, select Proportional.

4. On the Status page, set the dimension status using conditions:

   • Time: Start with Month

     On the Members tab, verify that only months are listed.

   • Customer: Start with All Levels

     On the Members tab, verify that all members are listed.

   • Product: Start with Descendants of Total Product

     On the Members tab, verify that all members except Total Products are listed.

   • Channel: Start with All Levels

     On the Members tab, verify that all members are listed.

5. Click Create to save the allocation step.

6. Click Apply to save the Calculation Plan.

Generating and Validating the Allocation

Rerun the Calculation Plan, as described in "Generating and Validating the Forecast". Both the forecast step and the allocation step will be executed.

To view the allocation results, use the Measure Viewer to see the data in the Sales Forecast measure. The allocation populated the base levels of Product, Customer, and Time.

Creating the Sales Forecast Aggregation Step

As the previous display of the allocated measure shows, the aggregated data is available without an aggregation step. All aggregated values are generated on the fly. The aggregation step simply generates and stores some of the aggregate values to improve runtime performance.

To create a new aggregation step:

1. On the General page of Sales Plan, click New Step, then select New Aggregation Step.

   The Create Aggregation Step dialog box opens.

2. Complete the General page with these values:

   • Name: aggregate_sales_forecast_step

   • Cube: UNITS_CUBE

   • Selected Measures: SALES_FORECAST

3. On the Summarize To page, select the levels the same as for UNITS_CUBE, that is, select all levels for Time, Product, and Channel, and skip levels for Customer. Refer to "Case Study: Aggregating a Moderately Sparse or Dense Cube".

4. On the Status page, keep the default status of All Levels for all dimensions. Keep the default settings for Rules and Cache also.

5. Click Create to save the aggregation step.

6. Click Apply to save the Calculation Plan.

Generating the Aggregation

Rerun the Calculation Plan, as described in "Generating and Validating the Forecast". The forecast step, the allocation step, and the aggregation step will be executed. You can view the data in the Measure Viewer.