5 Data Validation Framework
The foundation file interfaces (such as product and organization hierarchies) have a set of validations and error checking jobs that execute with them to ensure the data is accurate, complete, and follows all basic requirements for RAP application usage. Review the contents of this chapter to understand what validations exist and how to reconfigure them per your implementation needs.
Architecture Overview
The validation framework consists of POM batch jobs that execute the validations, and database tables that control the types of validation rules and what happens when the rule is triggered. Some validation rules may cause the POM job to fail, which means the data has a critical issue that needs to be corrected before the batch process can continue. Other rules will simply write warnings to the database but allow the batch to proceed. In both cases, there are tables that can be queried to check the validation results and determine what actions need to be taken.
The table below summarizes the POM jobs that execute the validations:
Table 5-1 Data Validation POM Jobs
Job Name | Summary |
---|---|
DIM_ORG_VALIDATOR_JOB |
Executes validations on the Organization Hierarchy data |
DIM_PROD_VALIDATOR_JOB |
Executes validations on the Product Hierarchy and Product dimension data |
DIM_CALENDAR_VALIDATOR_JOB |
Executes validations on the Calendar Hierarchy staging data |
DIM_CALENDAR_LOAD_VALIDATOR_JOB |
Executes validations on the Calendar Period data after the load has been run |
The jobs are included both in the nightly batch process flow and in separate ad hoc processes that can be executed as part of your historical data loads.
The configuration table for the validation rules is called C_DIM_RULE_LIST
. You can access this table
from the Control & Tactical Center’s Manage System Configurations screen. This table allows you to edit the following
fields:
-
Set the error message resulting from a validation rule (
ERROR_DESC
) -
Set whether the POM job should have a hard failure or only capture a warning message (
ERROR_TYPE
) with a value ofF
orW
-
Set whether it is turned on or off (
ON_IND
) with a value ofY
orN
The other important field in this table is the BAD_TBL_NAME
, which tells you where the results of the
validations will be written in the case of any errors or warnings. If a failure or warning does occur, you can directly query
the database table listed in BAD_TBL_NAME
using Data Visualizer or APEX.
Any time you execute one or more of the validation jobs, there is also a database view that summarizes the results from
the job executions. This view is RI_DIM_VALIDATION_V
and can also be queried from DV as needed. An example
of the data in this view is shown below:
![Validation Database View Validation Database View](img/validation_db_view.png)
Using a combination of the data in RI_DIM_VALIDATION_V
and the specified BAD_TBL_NAME
table data, you will be able to identify the issues and take corrective action on the source data. In the case of job failures,
you will need to reload the data file to proceed. It is also possible to skip the failed validation job in POM, but this should
only be done if you have carefully reviewed the validation results and are confident the data will not cause any problems
in your target applications.
Resolving Validation Issues
The validation rules scan your input data for a variety of common problems that may result in failures or inconsistencies in downstream applications such as AI Foundation or Planning modules. The table below describes what the rules are checking for and how to resolve the issues.
Table 5-2 Validation Rule Details
Rule ID | Explanation | Resolution |
---|---|---|
CAL_R1 |
The |
Reload a corrected data file after reviewing the contents. All start/end date fields must be populated and all other fields should exactly match the file requirements as documented. |
CAL_R2 |
The start and end dates for the fiscal periods are overlapping which will result in an invalid calendar. |
Create and load a new calendar file where the period start and end dates are exactly aligned and don’t overlap or have gaps. |
CAL_R3 |
The |
Update the |
CAL_R4 |
Your calendar file does not contain at least 2 years prior to the current system date. Many applications on the platform require at least 2 years before and after the current calendar year (5 years total). |
Load a new calendar file having at least 2 years of fiscal periods prior to the current year. |
PROD_R1 |
Many-to-many relationships exist in your product hierarchy, which is not allowed. This is generally due to the same child ID appearing below multiple parent IDs. |
Review all hierarchy levels for instances of the same ID appearing under multiple parents (such as a department belonging to two different divisions or groups) and modify the data to remove the multi-parent issues. |
PROD_R2 |
The same product hierarchy node has multiple descriptions on different rows of the input file. |
Modify your product hierarchy file such that any given hierarchy ID has the same description on all rows. |
PROD_R3 |
A node of the product hierarchy has no children under it. This could be due to a reclass that didn’t delete the old nodes, or when a new node is added but no items were created yet. |
If possible, remove all cases of nodes having no children (for example, if all items are reclassed out of a subclass, delete the old subclass). Some AI Foundation functionality will fail if you attempt to run it on empty nodes. |
PROD_R4 |
Your product hierarchy levels use alphanumeric characters for the level IDs. This is not allowed if you are implementing Retail Insights; all levels must be numbers. |
If you are implementing RI, you must alter your hierarchy to only use numbers for every level above item. Other characters are not allowed. |
PROD_R5 |
You are attempting to delete an item while also sending data for that item in other files on the same batch run. You cannot delete an item if it is still actively sending data on other input interfaces. |
Re-send the deleted item file, removing any items that are still active or posting new data to RI. If the item should be deleted, then re-send the other files having that item’s data to remove the item from all other files. |
PROD_R6 |
Null values are present on product attribute columns that are critical to the operation of multiple RAP applications. This includes the item level, tran level, diff aggregate, and item parent levels. |
Fill in the null values on the specified columns with non-null values wherever possible and re-send the product file. If you are okay with the null values and understand the impact then this may be ignored. |
ORG_R1 |
Many-to-many relationships exist in your organization hierarchy, which is not allowed. This is generally due to the same child ID appearing below multiple parent IDs. |
Review all hierarchy levels for instances of the same ID appearing under multiple parents (such as a district belonging to two different regions or areas) and modify the data to remove the multi-parent issues. |
ORG_R2 |
The same organization hierarchy node has multiple descriptions on different rows of the input file. |
Modify your organization hierarchy file such that any given hierarchy ID has the same description on all rows. |
ORG_R3 |
A node of the organization hierarchy has no children under it. This could be due to a reclass that didn’t delete the old nodes, or when a new node is added but no stores were created yet. |
If possible, remove all cases of nodes having no children (for example, if all stores are reclassed out of a district, delete the old district). Some AI Foundation functionality will fail if you attempt to run it on empty nodes. |
ORG_R4 |
Your organization hierarchy levels use alphanumeric characters for the level IDs. This is not allowed if you are implementing Retail Insights; all levels must be numbers. |
If you are implementing RI, you must alter your hierarchy to only use numbers for every level of the organization hierarchy. Other characters are not allowed. |