Dynamic Forecast


Dynamic Forecasting is functionality that gives consultants the ability to create statistical forecasts on any level in the data structure hirarchy. Historically, all forecasts in the Inventory module utilize sku level forecasting but the Allocation module uses the Dynamic Forecasting component to create forecasts on item category level to calculate the buy mix percentages to allocate by. For fashion businesses it might make sense to create forecasts on article level and distribute down to sizes using size profiles or for Wholesale customers, it might make sense to create their forecasts on customer level.


Setup

  • Create tables for the historical data, details table and forecast outputs​
  • Data map the historical data ​
  • Create data series for the histories table and the output table in data_element_series ​
  • Add a new line to data_element_dynamic_forecast​
  • Add lines into stg_run and stg_element tables and their underlying tables
  • Run the forecast (and schedule them) through the scheduler in the system

create




Create table for historical data

IF OBJECT_ID('[dbo].[item_group_histories_sale]', 'U') IS NOT NULL
DROP TABLE [dbo].[item_group_histories_sale]

CREATE TABLE [dbo].[item_group_histories_sale](
	[id] [INT] NOT NULL,
	[date] [DATE] NOT NULL,
	[value] [DECIMAL](18, 4) NOT NULL
 CONSTRAINT [PK_item_group_histories_sale] PRIMARY KEY CLUSTERED
(
	[id] ASC,
	[date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Inserting data into the table:

INSERT INTO [dbo].[item_group_histories_sale]
SELECT i.item_group_id AS id
      ,[history_date] AS [date]
      ,SUM([value]) AS [value]
  FROM [dbo].[histories_sale] hs
  INNER JOIN [dbo].[items] i ON hs.item_id = i.id
  GROUP BY item_group_id, [history_date]



Create details table used by the forecast for the unit ids

IF OBJECT_ID('[dbo].[item_group_forecast_details]', 'U') IS NOT NULL
DROP TABLE [dbo].[item_group_forecast_details]

CREATE TABLE [dbo].[item_group_forecast_details](
	[id] [INT] NOT NULL,
	[confidence_factor]  AS (ISNULL([confidence_factor_overwrite],[confidence_factor_default])),
	[skip_forc_to_date]  AS (ISNULL([skip_forc_to_date_overwrite],[skip_forc_to_date_default])),
	[forecast_from_date]  AS (ISNULL([forecast_from_date_overwrite],[forecast_from_date_default])),
	[forecast_to_date]  AS (ISNULL([forecast_to_date_overwrite],[forecast_to_date_default])),
	[confidence_factor_default] [DECIMAL](18, 4) NULL,
	[confidence_factor_overwrite] [DECIMAL](18, 4) NULL,
	[skip_forc_to_date_default] [DATE] NULL,
	[skip_forc_to_date_overwrite] [DATE] NULL,
	[forecast_from_date_default] [DATE] NULL,
	[forecast_from_date_overwrite] [DATE] NULL,
	[forecast_to_date_default] [DATE] NULL,
	[forecast_to_date_overwrite] [DATE] NULL,
	[forc_type] [INT] NULL,
	[forecast_event_header_id] [INT] NULL,
	[forecast_index_header_id] [INT] NULL,
 CONSTRAINT [PK_item_group_forecast_details] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO



Create Forecast header table

IF OBJECT_ID('[dbo].[item_group_forecasts]', 'U') IS NOT NULL
DROP TABLE[dbo].[item_group_forecasts]

CREATE TABLE [dbo].[item_group_forecasts](
	[id] [INT] NOT NULL,
	[valid] [BIT] NOT NULL,
	[forc_type] [SMALLINT] NOT NULL,
	[date_created] [DATETIME] NOT NULL,
	[model_family] [INT] NULL,
	[y_mean] [DECIMAL](28, 4) NULL,
	[y_sdev] [DECIMAL](28, 4) NULL,
	[sigma] [DECIMAL](28, 4) NULL,
	[rms_err] [DECIMAL](28, 4) NULL,
	[rsq] [DECIMAL](28, 4) NULL,
	[adj_rsq] [DECIMAL](28, 4) NULL,
	[complexity] [DECIMAL](28, 4) NULL,
	[aic] [DECIMAL](28, 4) NULL,
	[bic] [DECIMAL](28, 4) NULL,
	[mape] [DECIMAL](28, 4) NULL,
	[raw_err] [DECIMAL](28, 4) NULL,
	[mad] [DECIMAL](28, 4) NULL,
	[n] [INT] NULL,
	[d_error_mean] [DECIMAL](28, 4) NULL,
	[d_error_std_dev] [DECIMAL](28, 4) NULL,
	[forc_state] [SMALLINT] NULL,
	[safety_stock_next_7_days] [DECIMAL](28, 4) NULL,
	[safety_stock_next_14_days] [DECIMAL](28, 4) NULL,
	[safety_stock_next_30_days] [DECIMAL](28, 4) NULL,
	[safety_stock_next_60_days] [DECIMAL](28, 4) NULL,
	[seasonality] [SMALLINT] NULL,
    [confidence_factor] [DECIMAL](28, 4) NULL,
	[trend] [SMALLINT] NULL,
	[model_description] varchar(200) NULL,
 CONSTRAINT [PK_item_group_forecasts] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



Table for the forecast values

IF OBJECT_ID('[dbo].[item_group_forecast_values]', 'U') IS NOT NULL
DROP TABLE [dbo].[item_group_forecast_values]

CREATE TABLE [dbo].[item_group_forecast_values](
	[id] [INT] NOT NULL,
	[date] [DATE] NOT NULL,
	[value] [DECIMAL](18, 4) NOT NULL,
 CONSTRAINT [PK_item_group_forecast_values] PRIMARY KEY CLUSTERED
(
	[id] ASC,
	[date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

The forecast and the history need to exist as data series

    MERGE INTO [dbo].[data_element_series] AS Target USING
    (VALUES
    (12000,'item_group_sale',2,1,NULL,'{"caption":"ITEM_GROUP_HISTORIES_SALE","description":"DE_i_ITEM_GROUP_HISTORIES_SALE","data_object": {        "name":"item_group_histories_sale","data_element_ref_table_id":3,"id_column":"id","date_column":"date"}}'),
    (12001,'item_group_forecast',2,1,NULL,'{"caption":"ITEM_GROUP_FORECAST","description":"DE_i_ITEM_GROUP_FORECAST","data_object": {          "name":"item_group_forecast_values","data_element_ref_table_id":3,"id_column":"id","date_column":"date"}}')
    )
    AS Source ([id],[name],[group_id],[system_default_element],[client_config],[server_config])
    ON Target.[id] = Source.[id]
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([id],[name],[group_id],[system_default_element],[client_config],[server_config])
    VALUES ([id],[name],[group_id],[system_default_element],[client_config],[server_config]) ;

We need to add a entry into the dynamic forecast table for our new forecast:

	DELETE FROM [dbo].[data_element_dynamic_forecast] WHERE id = 12000
    INSERT INTO [dbo].[data_element_dynamic_forecast]([id], [input_data_element_name], [output_data_element_name], [header_table_name], [details_table_name], [forecast_batches], [safety_stock_output_de_name], [forecast_outliers_output_de_name])
	SELECT 12000,'item_group_histories_sale','item_group_forecast','item_group_forecasts','item_group_forecast_details',1,NULL,NULL

The forecast requires stg_run and stg_element to be able to run the forecast. The following tables have to include the dynamic forecast:

  • core.stg_run
  • core.stg_group
  • core.stg_run_group
  • core.stg_element
  • core.stg_group_element

An dynamic forecast example is part of the standard setup so you should see row for dynamic forecast in all of these tables. The only thing that might require adjustment is what is in the parameters column in core.stg_element table. It must contain the id of the dynamic forecast in dbo.data_element_dynamic_forecast (Based on example above for item group forecast the parameters column should include: /D:12000)

If the dynamic forecast example is missing the following code can be used to create it: 

	-- inserting into core.stg_run
    MERGE INTO [agr_stg].[core].[stg_run] AS Target USING
    (VALUES
    ('dynamic_forecast','Dynamic Forecast: Running for all ids',0,0,1)
    )
    AS Source ([name], [description], [force_run], [run_stg_groups_in_sequence], [include_in_system_health])
    ON (Target.[name] = Source.[name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([name], [description], [force_run], [run_stg_groups_in_sequence], [include_in_system_health])
    VALUES ([name], [description], [force_run], [run_stg_groups_in_sequence], [include_in_system_health]);

	-- inserting into core.stg_group
    MERGE INTO [agr_stg].[core].[stg_group] AS Target USING
    (VALUES
    ('dynamic_forecast','Dynamic Forecast: Running for all ids', NULL)
    )
    AS Source ([name], [description], [system_health_group_id])
    ON (Target.[name] = Source.[name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([name], [description], [system_health_group_id])
    VALUES ([name], [description], [system_health_group_id]);

	-- inserting into core.stg_run_group
    MERGE INTO [agr_stg].[core].[stg_run_group] AS Target USING
    (VALUES
    ('dynamic_forecast', 'dynamic_forecast', 1, 1)
    )
    AS Source ([stg_run_name], [stg_group_name], [run_order], [active])
    ON (Target.[stg_run_name] = Source.[stg_run_name] AND Target.[stg_group_name] = Source.[stg_group_name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([stg_run_name], [stg_group_name], [run_order], [active])
    VALUES ([stg_run_name], [stg_group_name], [run_order], [active]);

	-- inserting into core.stg_element
    MERGE INTO [agr_stg].[core].[stg_element] AS Target USING
    (VALUES
    ('dynamic_forecast_all_ids',	'post_run', '', 'FORECASTS', 3,	'/D:12000', NULL, 1, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
    )
    AS Source ([name], [type_name], [description], [object], [operation_mode], [parameters], [src_data_object], [break_on_error], [add_missing_columns_to_target], [data_merge_column_values], [data_merge_join_columns], [data_merge_compare_columns], [data_merge_update_columns], [data_merge_insert_columns], [data_merge_not_match_source_values], [data_merge_filter], [data_merge_source_only_filter], [data_merge_pre_sql], [data_merge_post_sql], [last_run_time], [last_run_time_successful], [last_run_event_id], [last_run_failed])
    ON (Target.[name] = Source.[name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([name], [type_name], [description], [object], [operation_mode], [parameters], [src_data_object], [break_on_error], [add_missing_columns_to_target], [data_merge_column_values], [data_merge_join_columns], [data_merge_compare_columns], [data_merge_update_columns], [data_merge_insert_columns], [data_merge_not_match_source_values], [data_merge_filter], [data_merge_source_only_filter], [data_merge_pre_sql], [data_merge_post_sql], [last_run_time], [last_run_time_successful], [last_run_event_id], [last_run_failed])
    VALUES ([name], [type_name], [description], [object], [operation_mode], [parameters], [src_data_object], [break_on_error], [add_missing_columns_to_target], [data_merge_column_values], [data_merge_join_columns], [data_merge_compare_columns], [data_merge_update_columns], [data_merge_insert_columns], [data_merge_not_match_source_values], [data_merge_filter], [data_merge_source_only_filter], [data_merge_pre_sql], [data_merge_post_sql], [last_run_time], [last_run_time_successful], [last_run_event_id], [last_run_failed]);

	-- inserting into core.stg_group_element
    MERGE INTO [agr_stg].[core].[stg_group_element] AS Target USING
    (VALUES
    ('dynamic_forecast', 'dynamic_forecast_all_ids', 1, 0, 1)
    )
    AS Source ([stg_group_name], [stg_element_name], [run_order], [force_run], [active])
    ON (Target.[stg_group_name] = Source.[stg_group_name] AND Target.[stg_element_name] = Source.[stg_element_name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([stg_group_name], [stg_element_name], [run_order], [force_run], [active])
    VALUES ([stg_group_name], [stg_element_name], [run_order], [force_run], [active]);

Finally a task must be scheduled through the scheduler in the system or by adding a line to the dbo.scheduled_tasks and you run the forecast through the task in the system.



Run for one id

If you want to be able to run forecast for one id within the dynamic forecasting (in the example used above, for one item group id) you can set it up with the following code snippet below. The only thing that needs to be adjusted for each specific scenario is the parameters column when inserting into core.stg_element. In this specific case below where the parameters has ’/D:12000 /DU:1’, a forecast run is being setup to run dynamic forecast for:

  • /D:12000 : 12000 represents the dynamic forecast id in dbo.data_element_dynamic_forecast
  • /DU:1 : 1 represents the specific id you want to run dynamic forecast for (here item group id = 1).

After this has been setup, you only need to update the id in the parameters column in core.stg_element to run dynamic forecast for another id in the future. 

	-- inserting into core.stg_run
    MERGE INTO [agr_stg].[core].[stg_run] AS Target USING
    (VALUES
    ('dynamic_forecast_by_id','Dynamic Forecast: Running for one id',0,0,1)
    )
    AS Source ([name], [description], [force_run], [run_stg_groups_in_sequence], [include_in_system_health])
    ON (Target.[name] = Source.[name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([name], [description], [force_run], [run_stg_groups_in_sequence], [include_in_system_health])
    VALUES ([name], [description], [force_run], [run_stg_groups_in_sequence], [include_in_system_health]);

	-- inserting into core.stg_group
    MERGE INTO [agr_stg].[core].[stg_group] AS Target USING
    (VALUES
    ('dynamic_forecast_by_id','Dynamic Forecast: Running for one id', NULL)
    )
    AS Source ([name], [description], [system_health_group_id])
    ON (Target.[name] = Source.[name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([name], [description], [system_health_group_id])
    VALUES ([name], [description], [system_health_group_id]);

	-- inserting into core.stg_run_group
    MERGE INTO [agr_stg].[core].[stg_run_group] AS Target USING
    (VALUES
    ('dynamic_forecast_by_id', 'dynamic_forecast_by_id', 1, 1)
    )
    AS Source ([stg_run_name], [stg_group_name], [run_order], [active])
    ON (Target.[stg_run_name] = Source.[stg_run_name] AND Target.[stg_group_name] = Source.[stg_group_name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([stg_run_name], [stg_group_name], [run_order], [active])
    VALUES ([stg_run_name], [stg_group_name], [run_order], [active]);

	-- inserting into core.stg_element
    MERGE INTO [agr_stg].[core].[stg_element] AS Target USING
    (VALUES
    ('dynamic_forecast_one_id',	'post_run', '', 'FORECASTS', 3,	'/D:12000 /DU:1', NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
    )
    AS Source ([name], [type_name], [description], [object], [operation_mode], [parameters], [src_data_object], [break_on_error], [add_missing_columns_to_target], [data_merge_column_values], [data_merge_join_columns], [data_merge_compare_columns], [data_merge_update_columns], [data_merge_insert_columns], [data_merge_not_match_source_values], [data_merge_filter], [data_merge_source_only_filter], [data_merge_pre_sql], [data_merge_post_sql], [last_run_time], [last_run_time_successful], [last_run_event_id], [last_run_failed])
    ON (Target.[name] = Source.[name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([name], [type_name], [description], [object], [operation_mode], [parameters], [src_data_object], [break_on_error], [add_missing_columns_to_target], [data_merge_column_values], [data_merge_join_columns], [data_merge_compare_columns], [data_merge_update_columns], [data_merge_insert_columns], [data_merge_not_match_source_values], [data_merge_filter], [data_merge_source_only_filter], [data_merge_pre_sql], [data_merge_post_sql], [last_run_time], [last_run_time_successful], [last_run_event_id], [last_run_failed])
    VALUES ([name], [type_name], [description], [object], [operation_mode], [parameters], [src_data_object], [break_on_error], [add_missing_columns_to_target], [data_merge_column_values], [data_merge_join_columns], [data_merge_compare_columns], [data_merge_update_columns], [data_merge_insert_columns], [data_merge_not_match_source_values], [data_merge_filter], [data_merge_source_only_filter], [data_merge_pre_sql], [data_merge_post_sql], [last_run_time], [last_run_time_successful], [last_run_event_id], [last_run_failed]);

	-- inserting into core.stg_group_element
    MERGE INTO [agr_stg].[core].[stg_group_element] AS Target USING
    (VALUES
    ('dynamic_forecast_by_id', 'dynamic_forecast_one_id', 1, 0, 1)
    )
    AS Source ([stg_group_name], [stg_element_name], [run_order], [force_run], [active])
    ON (Target.[stg_group_name] = Source.[stg_group_name] AND Target.[stg_element_name] = Source.[stg_element_name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([stg_group_name], [stg_element_name], [run_order], [force_run], [active])
    VALUES ([stg_group_name], [stg_element_name], [run_order], [force_run], [active]);

Now a task can be setup through the scheduler in the system and the forecast can be run for specific id.

Run for 1 batch

If the forecast batches column (forecast_batches) in dbo.data_element_dynamic_forecast is larger than 1 then you can run the dynamic forecast for a specific batch. It can be setup and done with the code snippet below where you only need to change the parameters column in the core.stg_element table based on what batch is being run. In the example given below the parameters show ’/D:12000 /DB:2’ which represents:

  • /D:12000 : 12000 for the dynamic forecast id in dbo.data_element_dynamic_forecast
  • /DB:2 : 2 for the specific number of batch you want to run dynamic forecast for (in this case batch number 2).

First setting number of batches to 10 as shown in code snippet below

UPDATE [dbo].[data_element_dynamic_forecast]
SET forecast_batches = 10
WHERE id = 12000

Then creating new stg_element, stg_run etc. for forecast batch number 2.

	-- inserting into core.stg_run
    MERGE INTO [agr_stg].[core].[stg_run] AS Target USING
    (VALUES
    ('dynamic_forecast_by_batch','Dynamic Forecast: Running for specific batch',0,0,1)
    )
    AS Source ([name], [description], [force_run], [run_stg_groups_in_sequence], [include_in_system_health])
    ON (Target.[name] = Source.[name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([name], [description], [force_run], [run_stg_groups_in_sequence], [include_in_system_health])
    VALUES ([name], [description], [force_run], [run_stg_groups_in_sequence], [include_in_system_health]);

	-- inserting into core.stg_group
    MERGE INTO [agr_stg].[core].[stg_group] AS Target USING
    (VALUES
    ('dynamic_forecast_by_batch','Dynamic Forecast: Running for specific batch', NULL)
    )
    AS Source ([name], [description], [system_health_group_id])
    ON (Target.[name] = Source.[name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([name], [description], [system_health_group_id])
    VALUES ([name], [description], [system_health_group_id]);

	-- inserting into core.stg_run_group
    MERGE INTO [agr_stg].[core].[stg_run_group] AS Target USING
    (VALUES
    ('dynamic_forecast_by_batch', 'dynamic_forecast_by_batch', 1, 1)
    )
    AS Source ([stg_run_name], [stg_group_name], [run_order], [active])
    ON (Target.[stg_run_name] = Source.[stg_run_name] AND Target.[stg_group_name] = Source.[stg_group_name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([stg_run_name], [stg_group_name], [run_order], [active])
    VALUES ([stg_run_name], [stg_group_name], [run_order], [active]);

	-- inserting into core.stg_element
    MERGE INTO [agr_stg].[core].[stg_element] AS Target USING
    (VALUES
    ('dynamic_forecast_one_batch',	'post_run', '', 'FORECASTS', 3,	'/D:12000 /DB:2', NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
    )
    AS Source ([name], [type_name], [description], [object], [operation_mode], [parameters], [src_data_object], [break_on_error], [add_missing_columns_to_target], [data_merge_column_values], [data_merge_join_columns], [data_merge_compare_columns], [data_merge_update_columns], [data_merge_insert_columns], [data_merge_not_match_source_values], [data_merge_filter], [data_merge_source_only_filter], [data_merge_pre_sql], [data_merge_post_sql], [last_run_time], [last_run_time_successful], [last_run_event_id], [last_run_failed])
    ON (Target.[name] = Source.[name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([name], [type_name], [description], [object], [operation_mode], [parameters], [src_data_object], [break_on_error], [add_missing_columns_to_target], [data_merge_column_values], [data_merge_join_columns], [data_merge_compare_columns], [data_merge_update_columns], [data_merge_insert_columns], [data_merge_not_match_source_values], [data_merge_filter], [data_merge_source_only_filter], [data_merge_pre_sql], [data_merge_post_sql], [last_run_time], [last_run_time_successful], [last_run_event_id], [last_run_failed])
    VALUES ([name], [type_name], [description], [object], [operation_mode], [parameters], [src_data_object], [break_on_error], [add_missing_columns_to_target], [data_merge_column_values], [data_merge_join_columns], [data_merge_compare_columns], [data_merge_update_columns], [data_merge_insert_columns], [data_merge_not_match_source_values], [data_merge_filter], [data_merge_source_only_filter], [data_merge_pre_sql], [data_merge_post_sql], [last_run_time], [last_run_time_successful], [last_run_event_id], [last_run_failed]);

	-- inserting into core.stg_group_element
    MERGE INTO [agr_stg].[core].[stg_group_element] AS Target USING
    (VALUES
    ('dynamic_forecast_by_batch', 'dynamic_forecast_one_batch', 1, 0, 1)
    )
    AS Source ([stg_group_name], [stg_element_name], [run_order], [force_run], [active])
    ON (Target.[stg_group_name] = Source.[stg_group_name] AND Target.[stg_element_name] = Source.[stg_element_name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([stg_group_name], [stg_element_name], [run_order], [force_run], [active])
    VALUES ([stg_group_name], [stg_element_name], [run_order], [force_run], [active]);

Now a task can be setup through the scheduler in the system and the forecast can be run for the specific batch.


Run for set of ids

If you want to run forecast for specific set of ids it can be set up almost the same as with the one id example given above. The only difference is the parameters column in core.stg_element. Now the set of ids are listed up with comma between them: /D:12000 /DS:1,2,3.

  • /D:12000: 12000 for the dynamic forecast id in dbo.data_element_dynamic_forecast
  • /DS:1,2,3 : 1,2,3 for running dynamic forecast for id 1, 2 and 3 (In this case item group id 1, 2 and 3).
	-- inserting into core.stg_run
    MERGE INTO [agr_stg].[core].[stg_run] AS Target USING
    (VALUES
    ('dynamic_forecast_by_set_ids','Dynamic Forecast: Running for set of ids',0,0,1)
    )
    AS Source ([name], [description], [force_run], [run_stg_groups_in_sequence], [include_in_system_health])
    ON (Target.[name] = Source.[name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([name], [description], [force_run], [run_stg_groups_in_sequence], [include_in_system_health])
    VALUES ([name], [description], [force_run], [run_stg_groups_in_sequence], [include_in_system_health]);

	-- inserting into core.stg_group
    MERGE INTO [agr_stg].[core].[stg_group] AS Target USING
    (VALUES
    ('dynamic_forecast_by_set_ids','Dynamic Forecast: Running for set of ids', NULL)
    )
    AS Source ([name], [description], [system_health_group_id])
    ON (Target.[name] = Source.[name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([name], [description], [system_health_group_id])
    VALUES ([name], [description], [system_health_group_id]);

	-- inserting into core.stg_run_group
    MERGE INTO [agr_stg].[core].[stg_run_group] AS Target USING
    (VALUES
    ('dynamic_forecast_by_set_ids', 'dynamic_forecast_by_set_ids', 1, 1)
    )
    AS Source ([stg_run_name], [stg_group_name], [run_order], [active])
    ON (Target.[stg_run_name] = Source.[stg_run_name] AND Target.[stg_group_name] = Source.[stg_group_name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([stg_run_name], [stg_group_name], [run_order], [active])
    VALUES ([stg_run_name], [stg_group_name], [run_order], [active]);

	-- inserting into core.stg_element
    MERGE INTO [agr_stg].[core].[stg_element] AS Target USING
    (VALUES
    ('dynamic_forecast_set_ids',	'post_run', '', 'FORECASTS', 3,	'/D:12000 /DS:1,2,3', NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
    )
    AS Source ([name], [type_name], [description], [object], [operation_mode], [parameters], [src_data_object], [break_on_error], [add_missing_columns_to_target], [data_merge_column_values], [data_merge_join_columns], [data_merge_compare_columns], [data_merge_update_columns], [data_merge_insert_columns], [data_merge_not_match_source_values], [data_merge_filter], [data_merge_source_only_filter], [data_merge_pre_sql], [data_merge_post_sql], [last_run_time], [last_run_time_successful], [last_run_event_id], [last_run_failed])
    ON (Target.[name] = Source.[name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([name], [type_name], [description], [object], [operation_mode], [parameters], [src_data_object], [break_on_error], [add_missing_columns_to_target], [data_merge_column_values], [data_merge_join_columns], [data_merge_compare_columns], [data_merge_update_columns], [data_merge_insert_columns], [data_merge_not_match_source_values], [data_merge_filter], [data_merge_source_only_filter], [data_merge_pre_sql], [data_merge_post_sql], [last_run_time], [last_run_time_successful], [last_run_event_id], [last_run_failed])
    VALUES ([name], [type_name], [description], [object], [operation_mode], [parameters], [src_data_object], [break_on_error], [add_missing_columns_to_target], [data_merge_column_values], [data_merge_join_columns], [data_merge_compare_columns], [data_merge_update_columns], [data_merge_insert_columns], [data_merge_not_match_source_values], [data_merge_filter], [data_merge_source_only_filter], [data_merge_pre_sql], [data_merge_post_sql], [last_run_time], [last_run_time_successful], [last_run_event_id], [last_run_failed]);

	-- inserting into core.stg_group_element
    MERGE INTO [agr_stg].[core].[stg_group_element] AS Target USING
    (VALUES
    ('dynamic_forecast_by_set_ids', 'dynamic_forecast_set_ids', 1, 0, 1)
    )
    AS Source ([stg_group_name], [stg_element_name], [run_order], [force_run], [active])
    ON (Target.[stg_group_name] = Source.[stg_group_name] AND Target.[stg_element_name] = Source.[stg_element_name])
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([stg_group_name], [stg_element_name], [run_order], [force_run], [active])
    VALUES ([stg_group_name], [stg_element_name], [run_order], [force_run], [active]);

As before, a task can be setup through the scheduler in the system and the forecast can be run for this specific set of ids in the system.



Optional outputs from dynamic forecasts

Safety stock

IF OBJECT_ID('[dbo].[item_group_safety_stock]', 'U') IS NOT NULL
DROP TABLE [dbo].[item_group_safety_stock]

CREATE TABLE [dbo].[item_group_safety_stock](
	[id] [INT] NOT NULL,
	[days] [SMALLINT] NOT NULL,
	[value] [DECIMAL](18, 4) NOT NULL,
 CONSTRAINT [PK_item_group_safety_stock] PRIMARY KEY CLUSTERED
(
	[id] ASC,
	[days] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
UPDATE [dbo].[data_element_dynamic_forecast]
SET safety_stock_output_de_name = 'item_group_safety_stock'
WHERE id = 12000

Outliers

IF OBJECT_ID('[dbo].[item_group_forecast_outliers]', 'U') IS NOT NULL
DROP TABLE [dbo].[item_group_forecast_outliers]

CREATE TABLE [dbo].[item_group_forecast_outliers](
	[id] [INT] NOT NULL,
	[iteration] [INT] NOT NULL,
	[error_mean] [DECIMAL](28, 4) NOT NULL,
	[error_std_dev] [DECIMAL](28, 4) NOT NULL,
	[value] [DECIMAL](28, 4) NOT NULL,
	[corrected] [DECIMAL](28, 4) NOT NULL,
	[offset] [INT] NOT NULL,
 CONSTRAINT [PK_item_group_safety_forecast_outliers] PRIMARY KEY CLUSTERED
(
	[id] ASC,
	[iteration] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
UPDATE [dbo].[data_element_dynamic_forecast]
SET forecast_outliers_output_de_name = 'item_group_forecast_outliers'
WHERE id = 12000

The outliers table is only written to if any outliers are found!