This chapter lists the most commonly needed database procedures.
This chapter covers the following topics:
The following table lists the most important predefined procedures provided by Demantra. You should schedule all these procedures to run periodically from workflows.
Oracle only. Analyzes all the tables in the schema. By default, this procedure runs once a week as part of the Scheduled Cleanup Tasks workflow.
DSM associates settlements with an item and a location level and is shipped pre-configured to an existing item and location level. If you want to change this information, run the procedure API_CONFIG_SETTLEMENT.
This procedure:
Updates the item and location levels with which settlements should be associated
Upgrades the Settlement Location alias level to point to the new location level
Ensures that all standard procedures reflect the new settlement levels
For details, see "Setting up Database Structures" in the Configuring DSM chapter.
The APPPROC_REBUILD_CONTEXT_CACHES stored procedure automates the creation of caches for worksheets that use the Open With functionality and where the Open With Context for the worksheet is set to Selected Member.
This procedure simulates the user open with action which allows worksheet caches to be created in a batch process thereby eliminating the need to build the cache the first time the user opens the worksheets.
Note: This procedure improves the efficiency of building common caches by creating a cache for one user and copying it to other users instead of creating the cache for each user. This is accomplished by specifying a User Group. When common caches are detected, the cache is created for one of the Users in the User Group and copied to other Users in the User Group that require the same cache.
This procedure checks User security only on the Open With Level when creating and copying caches. It is important to structure the User Groups to be used by this procedure so that all Users have common security for all levels other than the Open With Level of the Worksheet that the cache is being built for.
This procedure must be incorporated into a workflow that also runs the Worksheet Cache Step. See Worksheet Caching > Creating a Workflow to Build Worksheet Caches Automatically.
The APPPROC_REBUILD_CONTEXT_CACHES stored procedure has the following requirements:
Worksheet
Level
User Group
The worksheet to be cached:
Must be a public worksheet
Must have caching enabled
Must be selected as a Worksheet for Levels in the Component definition in the Business Modeler
The level
Must be selected for Open With on the worksheet
Must contain an attribute named CAN_CREATE_CW which must be set to 1 for all members to build caches for. For details, see Identifying the Members of the Open With Level to Cache in this section.
In addition, a User Group must exist that contains all of the Users for which caches are to be built.
Following are the parameters for the APPPROC_REBUILD_CONTEXT_CACHES stored procedure. You set these parameters in the Stored Procedure Step Properties dialog in Workflow Manager:
WORKSHEET_ID: The internal ID of the worksheet to be cached
LEVEL_ID: The internal ID of the Open With level for the worksheet
GROUP_ID: The internal ID of the User Group containing the Users for which caches will be created
RUN_MODE:
1: Pre-process called before the Worksheet Cache Step to create master caches for the first User in the User Group and Level members
2: Post-process called after the Worksheet Cache Step to create copies of master caches for remaining Users in the User Group
Identifying the Members of the Open With Level to Cache
For each Member of the Open With Level for the Worksheet being cached, the APPPROC_REBUILD_CONTEXT_CACHES stored procedure requires a flag to indicate whether or not the member should be cached.
To set this up, perform the following:
In Business Modeler (Configure > Configure Levels), on the Open With level for the worksheet that you want to cache, create an attribute named CAN_CREATE_CW.
Set the value of the CAN_CREATE_CW attribute to 1 for all Level Members that you want to build caches for. You can set this value through any method you select such as a worksheet, data load process, or database procedure.
This setting applies to all worksheets that are cached using the APPPROC_REBUILD_CONTEXT_CACHES stored procedure for the particular Open With level.
Checks the chaining queue for any pending chaining operations; performs those operations.By default, this procedure runs once a week as part of the Scheduled Cleanup Tasks workflow.
Removes old data from the db_exception_log and audit_trail tables. To determine which data to keep, this procedure checks the value of the audit_history_length parameter, which you can access in the Business Modeler. By default, this procedure runs once a week as part of the Scheduled Cleanup Tasks workflow.
Performs any pending standard error calculations.
Creates the default database structures needed for the Promotion Optimization module. The installer runs this procedure, if you choose to set up a default Promotion Optimization environment.
For details, see “Other Configuration for PTP”.
Creates the default database structures needed for Promotion Effectiveness.
Demantra provides the Promotion Effectiveness (PE) structures by default. Therefore, the CREATE_PE_STRUCT procedure should be run only after creating a new or custom data model from scratch (this is not common or recommended); only in this scenario would these structures not already be in place. To determine whether PE structures exist, search the DB for table names containing %PROMOTION%.
For details, see “Configuring Promotion Effectiveness”.
Deletes all the history data from the current forecast column, for all combinations for which prediction_status equals 1. The procedure deletes history, starting at the date given by max_sales_date - ForecastGenerationHorizon.
ForecastGenerationHorizon is an engine parameter; see “Engine Parameters”.
Checks the promotion_data table for invalid records and deletes them.
Deletes temporary database tables that are created, for example, when modifying worksheets, launching integration profiles, or running the Analytical Engine.
By default, this procedure runs once a week as part of the Scheduled Cleanup Tasks workflow.
Performs the data loading described in the Data Model Wizard. Specifically, this procedure loads data from the staging tables specified within the Data Model Wizard and writes records in sales_data and other internal tables as needed.
This procedure is created by the Data Model Wizard and thus is different in different implementations.
EP_LOAD_MDP_LEVEL is the main method of populating matrix levels. During data load, information from sales master that is not time dependant can be loaded to these levels.
The following parameters are used with the EP_LOAD_MDP_LEVEL procedure:
| Description | If you run DML statements, any objects with a parallel degree configured will be run in parallel.Individual parallel hints setting will supersede this setting. |
| Values | TRUE - LOAD_MDP_LEVEL should use PARALLEL DML on objects that are set as parallel.FALSE - LOAD_MDP_LEVEL should not use PARALLEL DML on objects that are set as parallel. |
| Default Value | FALSE |
| Description | Set this parameter to FALSE when you do not want to use parallel hints in the EP_LOAD_MDP_LEVEL script.Should be set to FALSE when database resources may be limited. |
| Values | TRUE - MDP_ADD will use PARALLEL hints.FALSE - MDP_ADD will not use PARALLEL hints. |
| Default Value | TRUE |
| Description | Set this parameter to FALSE if the database is relatively small and has smaller rollback segments. If your database is powerful and thus sufficient resources are available, then set this parameter to TRUE. |
| Values | TRUE - EP_LOAD_MDP_LEVEL executes only one commit.FALSE - EP_LOAD_MDP_LEVEL executes multiple commits. |
| Default Value | FALSE |
| Description | This parameter determines whether the MDP_ADD process will set PROP_CHANGES to 1. PROP_CHANGES are used to calculate status and proportions of MDP_MATRIX combinations.This parameter should be set to FALSE if another process calls the PROPORT procedure or status and proportions are defined during a different process. |
| Values | TRUE - Set PROP_CHANGES = 1 during EP_LOAD_MDP_LEVEL.FALSE - Do not set PROP_CHANGES = 1 during EP_LOAD_MDP_LEVEL. |
| Default Value | FALSE |
| Description | This parameter determines whether the MDP_ADD process will set PROP_CHANGES to 1. PROP_CHANGES are used to calculate status and proportions of MDP_MATRIX combinations.This parameter should be set to FALSE if another process calls the PROPORT procedure or status and proportions are defined during a different process. |
| Values | TRUE - Set PROP_CHANGES = 1 during EP_LOAD_MDP_LEVEL.FALSE - Do not set PROP_CHANGES = 1 during EP_LOAD_MDP_LEVEL. |
| Default Value | FALSE |
| Description | Controls whether to drop indexes on a table when loading MDP_LEVEL information.. MDP indexes are rebuilt after execution. |
| Values | TRUE - Disable and then enable (rebuild) indexes.FALSE - Indexes remain in place during the updates. |
| Default Value | FALSE |
| Description | If rebuilding indexes using Ep_Load_Mdp_LevelDisableEnableIndexes, this parameter specifies which degree to use. |
| Values | --- |
| Default Value | 4 |
| Description | This parameter controls the MDP_MATRIX update method. Choose either Merge or Cursor.For larger data sets, with appropriate database resources, MERGE should be faster. |
| Values | TRUE - Use the MERGE update method.FALSE - Use the CURSOR update method. |
| Default Value | TRUE |
| Details | DELETE mdp_matrix WHERE NOT EXISTS (SELECT ... SALES_DATA s) AND NOT EXISTS (SELECT hint PROMOTION_DATA s)) |
| Description | SQL hint to use when Ep_Load_Mdp_LevelUseMerge is set to MERGE.This parameter defines the degree used during the Merge. |
| Values | TRUE - Use the MERGE update for the MDP_MATRIX cache columns - FROM_DATE and UNTIL_DATE.FALSE - Use a cursor loop. |
| Default Value | + full (a) parallel (a 20) |
| Details | Example:MERGE hint UPDATE INTO MDP_MATRIX |
The EP_LOAD_ITEMS procedure is the main method by which items are loaded into Demantra internal tables. The procedure validates item hierarchy compliance and uploads all item dimension information into internal tables.
The following parameters can be used with the EP_LOAD_ITEMS procedure:
| Description | If you run DML statements, any objects with a parallel degree configured will be run in parallel. Individual parallel hints setting will supersede this setting. |
| Values | |
| Default Value | FALSE |
| Description | Set this parameter to FALSE when you do not want to use parallel hints in the EP_LOAD_ITEMS script.This parameter should be set to FALSE if your database has limited resources. |
| Values | TRUE - EP_LOAD_ITEMS will use parallel hints. FALSE - EP_LOAD_ITEMS does will not use parallel hints. |
| Default Value | TRUE |
| Description | Set this parameter to FALSE if your database is relatively small and has smaller rollback segments.If your database is powerful and thus sufficient resources are available, then set this parameter to TRUE. |
| Values | TRUE - EP_LOAD_ITEMS executes only one commit.FALSE - EP_LOAD_ITEMS may execute more than one commit. |
| Default Value | FALSE |
| Description | Hint for sub-select in merge statement with MDP_MATRIX. Used in the same statement with hint DBHintEp_Load_ItemsMergeMDP.The total degree of both hints should not exceed number of database CPUs. |
| Values | --- |
| Default Value | + parallel (a4) |
| Details | MERGE INTO mdp_matrix (SELECT hint ) |
| Description | This parameter disables indexes before performing the update on MDP_MATRIX and then rebuilds them when the update is complete.This parameter should be used only if the customer data set is very large and rebuilding indexes is faster than updates with indexes enabled.Note: This parameter is not currently used. |
| Values | TRUE - Indexes on columns updated in the MDP_MATRIX MERGE update are disabled before the update and are rebuilt after the update.FALSE - Indexes remain during the update. |
| Default Value | FALSE |
| Details | This parameter is not currently used. |
| Description | If EP_LOAD_ITEMS rebuilds indexes, this parameter controls what parallel degree to use. The value of this parameter should not exceed the number of available CPUs. |
| Values | --- |
| Default Value | 4 |
| Details | This parameter is not currently used. |
The EP_LOAD_LOCATIONS procedure is the main method by which locations are loaded into Demantra internal tables. The procedure validates location hierarchy compliance and uploads all Location Dimension information into internal tables.
The following parameters can be used with the EP_LOAD_LOCATIONS procedure:
| Description | If you run DML statements, any objects with a parallel degree configured will be run in parallel. Individual parallel hints setting will supersede this setting. |
| Values | TRUE - EP_LOAD_LOCATIONS should use PARALLEL DML on objects that are set as parallel. FALSE - EP_LOAD_LOCATIONS should not use PARALLEL DML on objects that are set as parallel. |
| Default Value | FALSE |
| Description | Set this parameter to FALSE when you do not want to use parallel hints in the EP_LOAD_LOCATIONS script. Set this parameter to FALSE if your database has limited resources. |
| Values | TRUE - EP_LOAD_LOCATIONS will use parallel hints. FALSE - EP_LOAD_LOCATIONS will not use parallel hints. |
| Default Value | TRUE |
| Description | Set this parameter to FALSE if your database is relatively small and has smaller rollback segments. If your database is powerful and thus sufficient resources are available, then set this parameter to TRUE. |
| Values | TRUE - EP_LOAD_LOCATIONS executes only one commit. FALSE - EP_LOAD_LOCATIONS may execute more than one commit. |
| Default Value | FALSE |
| Description | Hint for merge statement with MDP_MATRIX. Used in the same statement with hint DBHintEp_Load_Locations_MergeMDP_GLOB. The total degree of both hints should not exceed number of database CPUs. |
| Values | --- |
| Default Value | + parallel (m4) |
| Details | MERGE (hint) INTO mdp_matrix (SELECT) |
| Description | Hint for sub-select in merge statement with MDP_MATRIX. Used in the same statement with hint DBHintEp_Load_LocationsMergeMDP.The total degree of both hints should not exceed number of database CPUs. |
| Values | --- |
| Default Value | + parallel (a4) |
| Details | MERGE INTO mdp_matrix (SELECT hint) |
| Description | This parameter disables indexes before performing the update on MDP_MATRIX and then rebuilds them when the update is complete.This parameter should be used only if your data set is very large and rebuilding indexes is faster than updates with indexes enabled.Note: This parameter is not currently used. |
| Values | TRUE - EP_LOAD_LOCATIONS disables indexes on columns updated in the MDP_MATRIX MERGE update.FALSE - EP_LOAD_LOCATIONS does not disable indexes on columns updated in the MDP_MATRIX MERGE update. |
| Default Value | FALSE |
| Description | If EP_LOAD_LOCATIONS rebuilds indexes, this parameter controls what parallel degree to use. The value of this parameter should not exceed the number of available CPUs.Note: This parameter is not currently used. |
| Values | --- |
| Default Value | 4 |
The EP_LOAD_SALES procedure loads lowest level time-dependant information into the Demantra SALES_DATA table. This information includes historical data as well as other time-varying information such as price, booking, and shipments.
The following parameters can be used with the EP_LOAD_SALES procedure.
| Description | If you run DML statements, any objects with a parallel degree configured will be run in parallel.Individual parallel hints setting will supersede this setting. |
| Values | TRUE - LOAD_MDP_LEVEL will use PARALLEL DML on objects that are set as parallel.FALSE - LOAD_MDP_LEVEL will not use PARALLEL DML on objects that are set as parallel. |
| Default Value | FALSE |
| Description | Set this parameter to FALSE when you do not want to use parallel hints in the EP_LOAD_SALES script.Should be set to FALSE when database resources are limited. |
| Values | TRUE - EP_LOAD_SALES will use PARALLEL hints. FALSE - EP_LOAD_SALES will not use PARALLEL hints. |
| Default Value | TRUE |
| Description | When checking data for errors. EP_LOAD_SALES will send invalid rows to error tables.This parameter enables hints on the SELECT statement to determine if there is an error.If loading very large source tables, providing a hint is recommended. |
| Values | --- |
| Default Value | + parallel (@T_SRC_SALES@ 4) full(@T_SRC_SALES@) |
| Details | Example:INSERT INTO ... SELECT hint ... MINUS SELECT hint ... Note: Leave the token @T_SRC_SALES@ to be used for the real T_SRC sales table. |
| Description | Use this parameter when data from a staging table is aggregated to match internal time resolution. The hint defines the degree used during insert for aggregation. |
| Values | --- |
| Default Value | + parallel (@T_SRC_SALES@ 4) |
| Details | Example Usage: INSERT EP_(t_src_sales)_LD... . This is the insert statement from the T_SRC_SALES table to the EP_T_SRC_SALES_LD table. Note: Leave the token @T_SRC_SALES@ to be used for the real T_SRC sales table. |
| Description | When inserting information into MDP_MATRIX_LOAD_ASSIST, this hint defines the degree used in the subselect statement. |
| Values | --- |
| Default Value | + parallel (@T_SRC_SALES_LD@ 4) full (@T_SRC_SALES_LD@) |
| Details | Example Usage: MERGE INTO mdp_load_assist USING (SELECT hint ... FROM EP_ (t_src_sales)LD ....Note: Leave the token @T_SRC_SALES_LD@ to be used for the real T_SRC Load sales table, |
| Description | When merging data into SALES_DATA table, this hint defines the degree used in the sub-select from intermediate data table. |
| Values | --- |
| Default Value | + parallel (@T_SRC_SALES_LD@ 4) full (@T_SRC_SALES_LD@) |
| Details | Example:MERGE INTO SALES_DATA USING (SELECT hint ... FROM EP_ (t_src_sales)LD ....Note: Leave the token @T_SRC_SALES_LD@ to be used for the real T_SRC Load sales table. |
| Description | This parameter is used when inserting a row into the MDP_MATRIX table based on historical demand records.Set this parameter to FALSE if you are loading all rows in sales history. Set this parameter to True if you want to ignore rows where historical demand is null.Setting of FALSE is typically faster. |
| Values | TRUE -Ignore/do not process for NULL values.FALSE - Process NULL values. |
| Default Value | FALSE |
| Details | --- |
| Description | Gathering statistics can be very slow in large environments.Initially, you would want to gather statistics but subsequently you may not want to gather statistics to improve performance. Parameter controls whether statistics are gathered on the MDP_LOAD_ASSIST table. When set to FALSE, statistics are always gathered. When set to TRUE statistics are gathered only if none exist. |
| Values | TRUE - Gather statistics ONLY if MDP_LOAD_ASSIST has no table statistics.FALSE - Always gather statistics for MDP_LOAD_ASSIST. |
| Default Value | FALSE |
| Details | --- |
| Description | Gather statistics for MDP_LOAD_ASSIST. Gathering executed first merge into MDP_LOAD_ASSIST. |
| Values | TRUE - Gather statistics after the first merge into MDP_LOAD_ASSIST if no statistics are available.FALSE - Always gather statistics for MDP_LOAD_ASSIST after the first merge. |
| Default Value | TRUE |
| Description | Gather statistics for MDP_LOAD_ASSIST. Will be executed after second merge into MDP_LOAD_ASSIST. |
| Values | TRUE - Gather statistics after the second merge into MDP_LOAD_ASSIST if no statistics are available.FALSE - Always gather statistics for MDP_LOAD_ASSIST after the second merge. |
| Default Value | TRUE |
| Description | Gather statistics for MDP_LOAD_ASSIST. Will be executed later in code, after delete from MDP_LOAD_ASSIST.This parameter will not be used if delete duplicates is not used (see Ep_Load_Sales_MDP_LOAD_ASSIST_DelDuplicates ). |
| Values | TRUE - Gather statistics after deleting duplicates from MDP_LOAD_ASSIST if no statistics are available.FALSE - Always gather statistics for MDP_LOAD_ASSIST after deleting duplicates. |
| Default Value | TRUE |
| Description | Controls whether MDP_LOAD_ASSIST deletes any duplicates found in MDP_LOAD_ASSIST.Should be enabled if rows frequently are added to MDP_LOAD_ASSIST from a variety of sources. |
| Values | TRUE - Delete duplicates from MDP_LOAD_ASSIST.FALSE - Do not delete duplicates from MDP_LOAD_ASSIST. |
| Default Value | TRUE |
| Description | Controls whether the procedure CHANGE_IS_FICTIVE is executed.This parameter must be set to TRUE if you are using Members Management or Chaining. |
| Values | TRUE - Run CHANGE_IS_FICTIVE.FALSE - Do not run CHANGE_IS_FICTIVE. |
| Default Value | TRUE |
| Description | Controls whether rows are inserted into SALES_DATA from staging table in one MERGE or using a loop.Should be set to FALSE for smaller environments, with smaller database and rollback settings.For more powerful environments, setting this parameter to TRUE will improve performance. |
| Values | TRUE - Use a single MERGE into SALES_DATA.FALSE - Merge is run in a location level loop. |
| Default Value | FALSE |
| Description | If Ep_Load_Sales_SALES_DATA_Merge_InOneMerge is set to FALSE, then this level will define the commit points. The name of a suitable T_SRC_SALES_TMPL location column should be entered as an input.If left null, the generated default is the one of the first Location level columns available. Level chosen should have an average number of combinations matching desired commit point. |
| Values | --- |
| Default Value | --- |
Executes all the active rolling data profiles in the system. These are defined through the Business Modeler and are stored in the rolling_profiles table. A profile is active if it is selected in the Configure Rolling Session dialog box.
For each active profile, Business Modeler copies the source data into the target series. Data for any given time bucket is copied into the same time bucket in the target series.
The profiles are executed in the order in which they are listed in the Configure Rolling Session dialog box.
Iterates through the promotions listed in the promotion table, checks the status of each (the status field), and does the following:
If the current status is 3 (planned) and if the current date is after the from_date of the promotion, change the status to 4 (committed).
If the current status is 4 (committed) and if the current date is after the until_date of the promotion, change the status to 5 (executed).
For details, see “Configuring Promotion Effectiveness”.
INSERT_UNITS is the mechanism by which future rows are inserted into the SALES_DATA table. Before each engine task is executed, all combinations belonging to the task which will receive a forecast are evaluated and future rows are inserted where appropriate. Ensuring the appropriate rows are available enables the engine output to be written quickly and efficiently.
The Analytical Engine calls this procedure at the start of an engine run. For performance reasons, Insert_Units automatically runs as a distributed process. No setup is required.
This procedure is controlled by the RunInsertUnits parameter and can do several things, depending on the value of that parameter:
Makes sure the engine has rows to write into when generating the forecast. In particular, for all non-dead combinations, this procedure does the following:
Checks to see if the database contains records for this combination for all dates in the span of time from max_sales_date to max_sales_date + lead.
For any dates when the combination does not have records, this procedure inserts records with zero sales, into which the Analytical Engine can then write the forecast.
Records with dates in the past are ignored.
Runs the EXECUTE_PROFILES procedure, which executes the active rolling data profiles.
RunInsertUnits and lead are engine parameters; see “Engine Parameters”.
The following parameters can be used with both the INSERT_UNITS and the DYN_INSERT_UNITS_BRANCH_ID procedures:
| Description | Should be set to TRUE for smaller databases or those with limited resources and smaller rollback segments. For larger databases with sufficient resources, set to FALSE. |
| Values | TRUE – The EP_LOAD procedure will do commits as needed.FALSE – Use only one commit per run of a DYN_INSERT_UNITS_BRANCH_ID procedure. |
| Default Value | TRUE |
| Description | Used with the EP_LOAD_SALES procedure. Defines whether the procedure will commit as needed or wait until the entire process is complete. When DB resources may be limited, set this parameter to False. |
| Values | TRUE - EP_LOAD will use the commit control and issue commits per n transitions.(Where n = SYS_PARAMS setting max_records_for_commit) FALSE - Use only one commit should be used per ep_load procedure. |
| Default Value | TRUE |
Updates all the appropriate database tables after a user makes a change in a worksheet.
Note that the update_dead_comb parameter controls whether this procedure considers dead combinations.
You use this procedure only if you are using the desktop product (Demand Planner and Demand Replenisher) rather than the Web products. The Web products (Demand Planner Web, Promotion Effectiveness, and Settlement Management) use a Java-based mechanism to update the same tables.
update_dead_comb is an engine parameter; “Engine Parameters”.
Updates all the appropriate database tables after data has been imported. Make sure you run this procedure after running an import integration interface.
Performs a recovery if MANUALS_INS or MANUALS_POPULATE_INS has failed. This procedure retrieves the temporary records that were written to the update_batch_trai_err and update_batch_values_err tables and completes the processing.
The MDP_ADD procedure is the main method by which Item/Location combinations are created in Demantra. Combinations are created and maintained based on Item/Location combinations existing in the SALES_DATA table. MDP_ADD also populates and maintains hierarchy information which is stored on MDP_MATRIX and executes the PROPORT procedure.
This procedure is run automatically when needed. It is responsible for maintaining the mdp_matrix table.
If the Analytical Engine fails with an error (“node not found in map”), you can correct the error by setting the align_sales_data_levels_in_loading parameter to true and then manually running the MDP_ADD procedure.
Depending on the setting of the RunProportInMdp_add parameter, this procedure may or may not call the proport mechanism when it runs.
The following parameters can be used with the MDP_ADD procedure:
| Description | Defines whether the Is_fictive column is to be maintained.Is_fictive is used only in Chaining and Members Management. If you are using Chaining and Members Management, this parameter should be set to TRUE. If you are not using these features, performance may improve by setting this parameter to FALSE. |
| Values | TRUE - MDP_ADD should update IS_FICTIVE. FALSE - MDP_ADD should not update IS_FICTIVE. |
| Default Value | TRUE |
| Description | Defines whether the Is_linked column is to be maintained.Is_fictive is used only in Chaining and Members Management. If you are using Chaining and Members Management, this parameter should be set to TRUE. If you are not using these features, performance may improve by setting this parameter to FALSE. |
| Values | TRUE - MDP_ADD should update IS_LINKED. FALSE - MDP_ADD should not update IS_LINKED. |
| Default Value | TRUE |
| Description | This parameter controls whether MDP_MATRIX combinations not found in historical data tables are removed from the matrix.Setting depends on functionality. In large implementations substantial saving can be achieved by setting this parameter to FALSE. |
| Values | TRUE - MDP_ADD should DELETE MDP_MATRIX rows not linked with SALES_DATA. In a PE schema, the delete also includes rows not linked with PROMOTION_DATA.FALSE - MDP_ADD should not DELETE MDP_MATRIX rows not linked with SALES_DATA. |
| Default Value | FALSE |
| Description | If you run DML statements, any objects with a parallel degree configured will be run in parallel.Individual parallel hints setting will supersede this setting. |
| Values | TRUE - Use PARALLEL DML on objects that are set as parallel.FALSE - Do not use PARALLEL DML on objects that are set as parallel. |
| Default Value | FALSE |
| Description | Set this parameter to FALSE if you want to use parallel hints in the MDP_ADD script. This parameter should be set to FALSE when DB resources may be limited. |
| Values | TRUE - EP_LOAD_ITEMS will use parallel hints.FALSE - EP_LOAD_ITEMS will not use parallel hints. |
| Default Value | TRUE |
| Description | Controls the degree used during the MDP_ADD insert statement when inserting rows into MDP_MATRIX. Used in the same statement as hint DBHintMdp_AddInsertSelect .The combined value of both parameters should not exceed the number of available CPUs. |
| Values | + parallel(mdp_matrix 4) append |
| Default Value | --- |
| Details | INSERT hint INTO mdp_matrix |
| Description | Controls the degree used during the internal select of the MDP_ADD insert statement when inserting rows into MDP_MATRIX. Used in the same statement as hint DBHintMdp_AddInsert.The combined value of both parameters should not exceed the number of available CPUs. |
| Values | --- |
| Default Value | + parallel (@TBL_NAME@ 2) parallel (location 2) parallel (items 2) |
| Details | INSERT INTO mdp_matrix SELECT hint.Note: Leave the token @TBL_NAME@ unchanged as it is to be used for the real table, usually MDP_LOAD_ASSIST. |
| Description | If this parameter is set to TRUE, the MDP_ADD procedure checks if a row already exists before inserting to MDP_MATRIX.This parameter also controls whether the statement uses IN or NOT EXISTS.If set to TRUE the statement uses IN; otherwise it uses NOT EXISTS. |
| Values | --- |
| Default Value | TRUE |
| Details | The default setting (TRUE) was chosen for backwards compatibility. However, setting this parameter to FALSE may improve performance. |
| Description | SQL hint for inserting into MDP_MATRIX when choosing the IN mechanism via DBHintMdp_AddMdpInsInMdpUseIn. Used with hint DBHintMdp_AddMdpInsInMdp.The combined value of both parameters should not exceed the number of available CPUs. |
| Values | TRUE - EP_LOAD_LOCATIONS should use PARALLEL DML on objects that are set as parallel.FALSE - EP_LOAD_LOCATIONS should use PARALLEL DML on objects that are set as parallel. |
| Default Value | + parallel (a 2 ) |
| Details | INSERT INTO mdp_matrix SELECT ... WHERE ... IN (SELECT hint mdp_load_assist ...) |
| Description | Controls minus sub-select of insert into MDP_MATRIX statement when choosing the IN mechanism. Used with DBHintMdp_AddMdpInsInTbl.The combined value of both parameters should not exceed the number of available CPUs. |
| Values | --- |
| Default Value | + parallel (b 2) |
| Details | INSERT into mdp_matrix SELECT ... WHERE ... IN (SELECT mdp_load_assist MINUS SELECT hint mdp_matrix) |
| Description | SQL hint on select count from MDP. This hint should be used in environments that have a large amount of data.This parameter should not be set to a value that exceeds the number of available CPUs. |
| Values | --- |
| Default Value | + full (MDP_MATRIX) parallel (MDP_MATRIX 4) |
| Details | SELECT hint COUNT (*) FROM mdp_matrix |
| Description | If updating Is_fictive, set degree parallel when updating MDP_MATRIX IS_FICTIVE value to 0.If you are not using Is_fictive, then this parameter is not used. |
| Values | --- |
| Default Value | --- |
| Details | UPDATE hint mdp_matrix SET is_fictive =... |
| Description | This parameter is used in the subselect for updating Is_fictive value in MDP_MATRIX to 0.The degree set for this parameter, combined with DBHintMdp_AddUpdateIs_Fictive0MDP, should not exceed the number of available CPUs. |
| Values | --- |
| Default Value | + parallel(s 2 ) |
| Details | UPDATE mdp_matrix SET is_fictive = 0 EXISTS (SELECT hint FROM sales_data ...) AND is_fictive = 2 |
| Description | Used in the subselect for updating Is_fictive value in MDP_MATRIX to 0.The degree set in this parameter combined with DBHintMdp_AddUpdateIs_Fictive0MDP should not exceed the number of available CPUs. |
| Values | --- |
| Default Value | + parallel(s 2 ) |
| Details | UPDATE mdp_matrix SET is_fictive = 2 NOT EXISTS (SELECT hint FROM sales_data ...) AND is_fictive = 0 |
| Description | If deleting MDP_MATRIX rows, use this parameter to set the degree used in delete.Used together with hints DBHintMdp_AddDeleteMdpNotInSD and DBHintMdp_AddDeleteMdpNotInPD. Total values of hints should not exceed number of available CPUs. |
| Values | --- |
| Default Value | + parallel(s 2) |
| Description | Hint used in the sub-select of delete population when viewing SALES_DATA.Used together with hints DBHintMdp_AddDeleteMdp and DBHintMdp_AddDeleteMdpNotInPD. Total values of hints should not exceed number of available CPUs. |
| Values | --- |
| Default Value | + parallel(s 2) |
| Details | DELETE mdp_matrix WHERE NOT EXISTS (SELECT hint FROM SALES_DATA s...)) |
| Description | Hint on the subselect of delete population when viewing promotion_data. Only used in environments that support promotions.Used together with hints DBHintMdp_AddDeleteMdp and DBHintMdp_AddDeleteMdpNotInSD. The total value of hints should not exceed the number of available CPUs |
| Values | --- |
| Default Value | + parallel (p 2) |
| Details | DELETE mdp_matrix WHERE NOT EXISTS (SELECT ... SALES_DATA s) AND NOT EXISTS (SELECT hint PROMOTION_DATA s)) |
| Description | When updating from_date and until_date in MDP_MATRIX, the process be done via a single merge or a cursor loop.The Merge statement should be faster if there is a large amount of data with sufficient database capacity. |
| Values | TRUE - Use the MERGE update for the MDP_MATRIX cache columns - FROM_DATE and UNTIL_DATE.FALSE - Use a cursor loop. |
| Default Value | TRUE |
Only for DSM. This procedure iterates through all settlements (except for off-invoice settlements), finds promotions that meet all the match criteria, and writes a record into the proposed_match table for each match.
This procedure performs the following comparisons, which are controlled by parameters:
It compares the promotion date (DSMPEShipDateSeries) to the settlement date. Only promotions with close enough dates are considered possible matches.
The DSMAllShipDateDifference parameter specifies the window of time that Demantra uses to search for a promotion that matches a given settlement. Express this as the number of time buckets between the promotion end date and the deduction date.
It compares the promotion budget (DSMPromotionBudgetSeries) to the monetary settlement amount. A promotion is a possible match only if its remaining budget is at least as large as the settlement amount.
Only for DSM. This procedure iterates through all off-invoice settlements, finds promotions that meet all the match criteria, and writes a record into the proposed_match table for each match.
This procedure performs the following comparisons, which are controlled by parameters:
It compares the promotion budget (DSMPromotionBudgetSeries) to the off-invoice amount. For this comparison, the DSMOIPercentDifference parameter specifies the maximum percent difference (of monetary amount) permitted when matching an off-invoice settlement to possible promotions.
It compares the promotion date (DSMPEShipDateSerie) to the off-invoice date. Only promotions with close enough dates are considered possible matches. You use the DSMOIShipDateDifference parameter to specify the closeness of these dates.
It can also check that the off-invoice settlement and the possible promotions use the same product. To control this check, you use the DSMOICheckProduct parameter.
Sets the database date and time formats.
Many other predefined procedures automatically call this procedure.
Oracle only. Rebuilds table indexes, a necessary maintenance task for the database.
Note: This procedure requires additional space (equal to the current tablespace) and can take a long time.
Rebuilds all tables, a necessary maintenance task for Oracle databases.
Note: This procedure requires additional space (equal to the current tablespace) and can take a long time.
Oracle only. Rebuilds the sales_data and mdp_matrix tables, a necessary maintenance task for Oracle databases.
Note: This procedure requires additional space (equal to the current tablespace) and can take a long time.
Arguments
This procedure has the following optional positional arguments:
The first argument indirectly specifies which tables to rebuild. If null, the procedure rebuilds tables according to the Rebuild_Sales_Table parameter. If this argument is 1, the procedure rebuilds the sales_data table. If this parameter is 0, the procedure skips the sales_data.
If the second argument is 0, the procedure rebuilds the sales_data (if permitted by the previous argument), mdp_matrix, items and location tables. If this parameter is 1, the procedure rebuilds all tables listed in user_tables that need to be rebuilt.
Iterates through the level tables and replaces any apostrophes in the column names with underscore characters.
Runs the Analyze Schema, Drop Temp Tables, Clean Log Tables, and Rebuild Tables workflows. By default this procedure runs once a week, on Saturdays.
Creates samples for activity shape modeling. Specifically this procedure does the following:
Creates two sample activity causal factors: Product_launch and Price_change.
It creates four editable series for the benefit of end users, described in the following table.
| Series Name | Data Association | Series Purpose |
|---|---|---|
| Price_change | Sales | Lets the user indicate the start and duration of the price change shape associated with a specific combination. Within this series, for each date, the user chooses “Start” or “Active” from a drop-down menu to specify the promotion start and continuation dates. The default is “None,” meaning no promotion. The user identifies past activities and marks where future activities will occur. |
| Price_change_QAD | Combination | Controls whether the Analytical Engine re-scales the generated shape to align with the amplitude of the most recent observed instance of this shape, for a given combination. Specify the number of buckets for which the shape alignment should occur, starting with the beginning of the shape. Typically you use either 0 or the length of the shape. |
| Product_launch | Sales | Like Price_change, but applies to the product launch shape instead of the price change shape. |
| Product_launch_QAD | Combination | Like Price_change_QAD, but applies to the product launch shape instead of the price change shape. |
See “About Activity Shape Modeling”.