Allocation

Technical documentation about how to setup and configure the AGR Allocation Module.

Note when updating Allocation 6.1.0->6.1.1. or 6.1->6.2

  • Workspace views: When updating Allocation module from v.6.1 to v.6.2 or from v.6.1.0 to 6.1.1, you need to be aware that all workspace views will break, as the names for all data_element_ref_columns have been changed. This is because there was added a prefix “alloc_” at the beginning of all names. And the views will not automatically update, so you need to do that manually.
  • If you have customized a view in Allocation, before updating, the best way to keep those changes is to take a backup of the dbo.de_workspace_views table and also take a screenshot of the views. Then after updating, you can set up the view by comparing with the screenshot and use the backup of dbo.de_workspace_views to get any changes in column or view config.
  • If you have not customized or changed the workspace views, the easiest way is to get the new views from the daily build (since the workspace views are not updated throught patches you need to get the latest views from daily build)

Allocation module in standard since v.6.2-r11

The Allocation module is part of the standard AGR system with version 6.2-r11. If you have installed version 6.2-r11 or newer, you only need two steps to add it to the system.
The Allocation module is turned off by default, but all procedures/setup/tables/views are installed, you only have to turn it on.

On STG, you need to add allocation data transfer to the daily job.
Go to table core.stg_run_group and change active=0 to active=1 for all stg_group_name that start with ‘alloc_’.

ON PROD, you only need to make the workspace visible in the system.
Go to table dbo.features and change from enabled=0 to enabled=1.


Run Installation Scripts

(The following instructions are for versions older than 6.2-r11).
The release files can be retrieved from http://releases.local/ by downloading the release package Allocation_6.x-x.zip. The package includes the db scripts for prod and stg2. If you are setting the module up against stg1, you need to make sure to run the stg1 script instead of the stg2 script. The stg1 script is not a part of the zip file but can be retrieved from the release page.

STG

Step 1 - Start by running the stg db script (for stg1 run here the stg1 script)

Step 2- Run the alloc.initial_load procedure on the stg database

PROD

Step 1 - Start by running the prod db script

Step 2- Run the alloc.initial_load procedure on the prod database

Make sure that you are not already using the data_element ids (8000 is schema range for allocation)



Data mapping on stg

(The following instructions are for versions older than 6.2-r11).

STG1

If you are using stg1 you need to go into all alloc views on stg and adjust the data mapping according to your customer. Run stg_to_ready and ready_to_prod procedures and make sure all data is flowing correctly into the alloc tables on prod.

STG2

If you are using stg2 you should double check the raw views and run the allocation stg_run to make sure that all data is transferred correctly from there to the alloc layer on prod (through prep and alloc on stg).



Add allocation to the daily run

(The following instructions are for versions older than 6.2-r11).

STG1

Add alloc.all_stg_to_ready to the end of rep.all_stg_to_ready.

Add alloc.all_ready_to_prod to the end of stg_prod.all_ready_to_prod.

Add to job.all_daily_run ; “EXEC alloc.[merge_allocation_mix_forecasts] @l” and there after add prod_alloc.daily_update (those two can e.g. be executed before job.run_orders).

STG2

Make sure to execute ctr.stg_run id 8000 in your daily job.



Dynamic forecast on Product Group Location level

Make sure all product group per location data is correct.

The dynamic forecast for the allocation model is based on sale per product group per location. In standard setup, the product group level is set to level=1, meaning the highest product group level.
This level can be changed for each customer. Go to table [core].[setting] on STG and change the setting alloc_product_group_location_level. (Note, you need the run the allocation_daily_build stg-run to get the new product group level set up).

View [alloc].[v_product_group_per_location_ids] is an important view in Allocation because it creates an id for all sets of product groups and locations.
View [alloc].[v_product_group_per_location_histories_sale] aggregates the sale history for each product_group_per_location_id and is used by the forecasting engine.

So the input into dynamic forecast is values(sale history) from table [alloc].[product_group_per_location_histories_sale].

The output from dynamic forecast is found in table [alloc].[product_group_per_location_forecast_values].

How the dynamic forecast is set up is found in table [dbo].[data_element_dynamic_forecast] and for Allocation it has id = 8000.

data_element_dynamic_forecast


Run forecast

To run forecast for allocation you need to create a scheduled task in “Scheduler” in the system. It is up to your customer whether the forecast needs to be updated weekly or monthly, you choose the schedule.

allocation_forecast_run

The stg_element behind the dynamic forecast run is alloc_forecast_run in [core].[stg_element]. Note, you can’t manually run this stg_element in the database (you get the error “Can’t do server side executions with this procedure”).

stg_element_alloc_forecast_run

The stg_element alloc_forecast_run belongs to stg_group = 'allocation_forecast_run'.
Note that we are also running two other stg_elements in this stg_group, that is to have the allocation mix tables populated.

stg_group_alloc_forecast


Outdated way to run the dynamic forecast

For version 6.2.r-8 and older, to run the dynamic forecast you can call the procedure [alloc].[run_forecast] like so:

EXEC alloc.run_forecast @dynamic_forecast_id = 8000

The number of days parameter can be found in the core.setting table with a setting name of alloc_forecast_number_of_days

Then, you need to create a job for dynamic forecasts so it updates regulary:

STG1 To update the forecast regularly, create a job which calls the [alloc].[run_forecast] procedure like above. (e.g. a monthly job).

STG2 Create a job which executes core.execute_stg_run=‘allocation_forecast_run’

It is up to your customer whether this needs to be done weekly or monthly.



Configurations

Warehouse filter

Filter the WH Stock and Create Allocation Proposal views to show only your customer’s warehouse. If your customer has multiple warehouses, you should create a drilldown for those warehouses so that the user only looks at one at a time.


Allocation mix calculations - Adjust the length of the forecast period used

By default, the length of the forecast period used for Product Group allocation-mix calculations is the average of 1 order frequency period for all locations within a product group. The order frequency that is used is stored down to product group per location level, taking the max() of all products belonging to the product group and location. This can be found in view alloc.v_product_group_per_location_ids.
Then in view alloc.v_allocation_mix_product_group_forecast, the average order frequency is calculated per product group.
This means that the system takes into account forecast per location for the length of average order frequency period and calculates the locations’ allocation-mix based on that forecast. To make changes to this setup, please take a look at these two views mentioned.

For Item Forecast Allocation-mix, there is used forecast from 8 weeks ahead in time by default. Please look at alloc.v_allocation_mix_item_forecast to make any changes.


Execution of custom allocation procedures

If you need to create custom allocation procedures and want to execute them daily, you can add such to the procedure alloc.daily_update on prod.


Customising Views

Standard views are setup in the module. In case you need to change the views to better fit the needs of your customer, please save a new version of the view with your changes instead of overwriting the standard views, otherwise your changes might get overwritten when the module gets updated or patched in the future.


Allow subset of items into Allocation

From verions 6.2-r16 it is possible to add a subset of items into Allocation. E.g. in the case where customer doesn’t want certain items to be available for allocation.
Follow the following steps.

  1. Go into view prep.v_product_item. There is column [include_in_allocation] (set default as 1). Populate that column with the column found in ERP system that specifies if item should be included or not in Allocation. Please create a prep_cus.v_product_item version of the view.

  2. Run the daily build or those stg_elements needed to map this data into core.product_item (note, the system will automatically pick up the prep_cus view so no need to change the stg_elements). Make sure that the [include_in_allocation] column is correctly populated to core.product_item.

  3. Next, you need to decide if those excluded items should be taken into account when calculating the allocation product group mix. Go to core.setting and find setting_key 'alloc_include_inactive_items_in_product_group_mix'. It is set to default 1, meaning it will include the excluded items into the product group mix calculations. If the setting is set to 0, then the excluded items will not be taken into consideration in the calculation for the product group mix.

  4. Run the allocation daily build, e.g. like this: EXEC [core].[execute_stg_run] @stg_run_name = 'allocation_daily_run'.
    If you changed the setting 'alloc_include_inactive_items_in_product_group_mix' you also need to run the dynamic forecast again, see chapter Run forecasts here above.

Now, everything should be set for having a subset of items into Allocation.