Forecast Logging


Forecast Logging is a functionality that gives consultants the ability to log scheduled forecasts, both what is stored in dbo.forecasts and what is stored in dbo.forecast_values each time a month or week forecast runs according to schedule. The logging mechanism stores the date which the forecasting took place, called forc_date in the log tables. The forecast logs and historical sales are then used to calculate several forecast accuracy metrics, e.g. MAPE and RMSE. The forecast accuracy is tracked and calculated down to each item and is available in a workspace called Forecast Log Accuracy Tracking which is accessible to the system user.

All views, tables, procedures and data element objects in the database related to forecast logging can be found with the prefix ‘forecast_log’.


Enable or Disable Logging

By default, the forecast logging is disabled. Firstly, it has to be enabled in core.setting. The following setting keys must be updated (changing the setting_value), either only one of them or both, depending on if monthly, weekly or both forecast types are being used:

  • forecast_log_monthly: By default set to 0 which means that no months are being logged. If set to 6, then forecast 6 months ahead for all items with forc_type = 1 are logged each time the monthly forecast runs according to schedule. It is not recommended to log much more than 6 months ahead, but this can vary depending on how far ahead the customers are using the forecast.
  • forecast_log_weekly: By default set to 0 which means that no weeks are being logged. If set to 12, then forecast 12 weeks ahead for all items with forc_type = 2 are logged each time the weekly forecast runs according to schedule. It is not recommended to log much more than 16 to 20 weeks ahead, but this can vary depending on how far ahead the customers are using the forecast.

Additionally, monthly and/or weekly forecast jobs must be scheduled through the scheduler in the system since the forecastg logging procedures are linked with those jobs. The tasks are named Inventory: Forecast monthly items and Inventory: Forecast weekly items.

To disable the logging again it is enough to update the setting values of forecast_log_monthly and/or forecast_log_weekly to 0 again in core.setting.



Logging Tables

There are three logging tables for monthly and weekly forecast logging.

Forecast Log

Mainly depends on what is in dbo.forecasts. For monthly forecasts, the main logging table is called dbo.forecast_log_monthly and for weekly forecasts, the main logging table is called dbo.forecast_log_weekly. The tables consist of the following columns:

  • log_id: It is the primary key of the table and is a combination of item_id and forc_date.
  • forc_date: The date that the forecasting took place. This is always either first day of month or first day of week in the month/week that the forecast is scheduled.
  • update_count_interval: Is counting the number of times the forecast changes over the month. This column is also now visible in dbo.forecasts table. When the scheduled job runs, the count becomes one. If someone changes e.g. the confidence factor a new forecast will run and this count will change to 2. The initial parameters from when the schedule took place will still remain the same. This column is only available to keep track of how many changes are being made.
  • skip_forc_to_date: If skip forc to date is mapped through item_details, that date will appear in the logging table. If no date is in dbo.item_details, this column will be NULL.
  • The rest of the columns are known and being read from the dbo.forecasts when the forecast takes place.

Forecast Log Values

Mainly depends on what is in dbo.forecast_values. For monthly forecasts, the value log table is called dbo.forecast_log_monthly_values and for weekly forecasts, the value log table is called dbo.forecast_log_weekly_values. The tables consist of the following columns:

  • log_id: Same log_id as in the forecast log tables.
  • demand_date: ‘Aggregated’ demand date from dbo.forecast_values, i.e. this is always either first day of month or first day of week.
  • value: ‘Aggregated’ value from dbo.forecast_values, i.e. the forecast value over a whole month or a whole week is logged.

Forecast Log Waterfall

The main forecast accuracy calculation tables. The logging data is grouped together and several accuracy metrics are calculated. For monthly forecasts, the forecast accuracy table is called dbo.forecast_log_monthly_waterfall and for weekly forecasts, the forecast accuracy table is called dbo.forecast_log_weekly_waterfall. The waterfall title originates from the waterfall model, which is a common concept used to analyse forecast accuracy.

The waterfall tables will be empty until historical sales are available for the logged forecasts to calculate accuracy metrics.

The tables consist of the following columns:

  • waterfall_id: The primary key of the table and is a combination of item_id and either lead_months or lead_weeks.
  • lead_months/lead_weeks: Represents the months or weeks ahead of when the forecast is made (forc_date). E.g. if a forecast is made on 01.01.2021 for January, February and March of 2021, the forecast for January represents one month ahead while the February forecast is two months ahead and the March forecast is three months ahead.
  • lead_months_name/lead_weeks_name: Computed column from lead_months/lead_weeks- only used for caption purposes.
  • no_obs: Counting the number of forecasts that are being used for each item and lead_months/lead_weeks in order to make accuracy calculations. E.g. if a forecast is made for a specific item on 01.01.2021 for January, February and March of 2021, there is available one observation for that specific item, 1,2 and 3 months ahead. If, then again for that specific item, a forecast is made on 01.02.2021 for February, March and April, we now have 2 observations for 1,2 and 3 months ahead. With more logging, more observations will be available and used for accuracy calculations which leads to more reliable results.
  • avg_forecast: The average forecasted value for each item and lead_months/lead_weeks. Not precisely a accuracy metric. This column is mostly valuable if there is no seasonality and/or trend in the data, i.e. it is excepected to sell similar amount whole year around.
  • avg_error: The average value difference between the forecast and actual sale (historical sales minus the forecast).
  • avg_pct_error: The average percentage difference between the forecast and actual sale (historical sales minus the forecast, divided by the historical sales).
  • mad: Mean absolute deviation which measures the accuracy of the forecast by averaging the absolute value of each error, i.e. this is calculated by subtracting forecasted sales from actual sales and then that error is converted to absolute error and finally the average of the absolute errors is calculated. This forecast accuracy metric depends on the scale and is in the same unit as the data (in this case ‘sold units’). Keep in mind that you can’t really calculate MAD for a whole item group or compare MAD between items that are sold in different volumes, because a error of 500 units might look very large when the sales are around 2000 on average but the same error would be minuscule if the actual sales were around 20000. MAD is sometimes referred to as MAE (mean absolute error).
  • mape: Mean absolute percentage error which is one of the most widely used measure for checking forecast accuracy. This is calculated by subtracting forecasted sales from actual sales and then that error is absoluted and divided by actual sales. Finally a average of those errors is calculated and multipled by 100 to convert from decimal to percentage. It comes under percentage errors which are scale independent and can be used for comparing series on different scales. The disadvantage of MAPE is that it becomes undefined if the value for acutal value is 0 for any observation in the data.
  • rms_err: Root mean square error which is calculated by subtracting forecasted sales from actual sales and then that errors are squared and finally the squared root of the averaged squared errors is calculated. This measure is scale dependent and is in the same unit as the data. Keep in mind that you can’t really calculate RMSE for a whole item group or compare RMSE between items that are sold in different volumes, because a error of 500 units might look very large when the sales are, on average, around 2000 but the same error would be minuscule if the actual sales were around 20000. RMSE can be more desirable (compared to MAD) when large error values are particulary undesirable as RMSE gives a relatively high weight to large errors.



Logging Procedures

The procedures that store the forecast logging are:

  • forecast_log_monthly_save
  • forecast_log_weekly_save

It is possible to run them manually - however if the forecast jobs/tasks listed above are scheduled, the forecast log procedures are automatically part of that schedule so it shouldn’t be necessary to run these procedures individually.



Forecast Log Accuracy Tracking Workspace

The workspace is available to system user only and can be found in the system under ‘workspaces’. It consists of two types of views:

  • Forecast Percentage Error - Both available on weekly and monthly level where forecast values and historical values are shown along with percentage error. The percentage error is a client calculated series. inst
  • Forecast Lead Time Analysis - Both available on weekly and monthly level. The forecast logging data is grouped together depending on how long is between the forecast date and demand date. The data shown in this view mainly comes from the forecast log waterfall tables, read in more detail here. inst

Try to avoid removing the drill-down setting as this can easily become performance heavy with many item id’s.



Logging Clean-up

By default, the logging mechanism only stores 24 months of forecast log data. This means that if the forc_date is older than 24 months, all logging related to that forecast date is deleted as part of the logging job. To change how much logging is stored, the setting value of the setting key called forecast_log_cleanup_keep_months in core.setting must be updated.

For information, there is also a setting key called forecast_log_cleanup_loop_delete_rows in core.setting which is used if more than x number of rows must be deleted at a time as part of clean-up. By default, this is set as 1000000 rows which means that the logging rows will be deleted in a loop where 1000000 are deleted at each iteration of the loop. This setting key is mainly set for performance purposes.



Daily Logging

The daily logging comes as a bonus for those that are interested and might be further developed in the near future. The concept is that the daily logging is mainly keeping track of how many times per day a forecast for an item is being changed. This could give a valuable idea of how common it is that a forecast is being changed per day. Similarly to monthly/weekly logging, it is enabled through core.setting. There are 4 different setting keys in core.setting that should be considered:

  • forecast_log_daily: By default set to false. If set to true then daily forecast data will be saved to the forecast_log_daily concept in the daily run process. A procedure called forecast_log_daily_save is part of the daily run but it is ignored if daily logging is not turned on.
  • forecast_log_daily_cleanup_on_months: By default set to *. It means that daily log tables will be cleansed every month. This can also be set as a comma separated list of month numbers to execute forecast log daily clean-up.
  • forecast_log_daily_cleanup_on_monthweek: By default set to 3. It means that the forecast log daily clean-up takes place in the third week of the month/months where clean-up should take place. This setting value can be a integer from 1-4.
  • forecast_log_daily_cleanup_on_weekday: By default set to 6. It means that forecast log daily clean-up takes place on the sixth day of the week in a specificed week and month depending on the other setting keys. 1 is monday and 7 is sunday.

There is only one table associated with the daily logging and the table is called dbo.forecast_log_daily. The only difference from dbo.forecast_log_monthly and dbo.forecast_log_weekly is that it has two update count columns:

  • update_count_daily: Counts how many times over the day a forecast for a particular item is being changed. The changes can be something such as updating confidence factor, skip forc to date, forc type etc.
  • update_count_interval: This shows the count over the whole month/week at that particular day depending on if the item has weekly or monthly forecast.