Skip to main content

Use Field Validations in the Jet Data Manager

Overview

The Jet Data Manager has built-in functionality that allows users to validate data as it comes over from the data source. This will allow the user to see which records in the source database are not conforming to applicable business rules and allows the users the option of whether these records should be flagged for further review or excluded from the data warehouse altogether.

Process

Field validation is set on the field itself within the desired table.

In this example, there is a business rule in place that stipulates that all customers need to be associated with a salesperson code on the Customer card. If a customer is not associated with a salesperson code then a violation of the rule has take place and the user should be notified.

There are currently 3 customers that are not associated with a salesperson code:

To add a Field Validation for this, locate the field in the desired table, right click the field, as select Field Validations. A window will appear on the right-hand side of the screen that allows the user to define the Field Validation rule.

The Field Validation rule that is specified will determine which values are valid, NOT which values are invalid.

In this example, valid customers are represented by any customer record for which the Salesperson Code field is not empty. To define this, the Operator is changed to Not Empty The severity of this rule violation also needs to be selected by the user. The Severity options are:

Warning: All records that do not meet the validation rule will appear in the Warning tab in the Jet Data Manager. These records will continue to be inserted into the staging or data warehouse table.

Error: All records that do not meet the validation rule will appear in the Errors tab in the Jet Data Manager. During the data cleansing process, any records flagged as errors will not be moved to the data warehouse table.

Once the selections have been made and the Severity level has been chosen the user will click Add to insert the rule.

The Field Validation can be viewed, modified, or deleted under the Salesperson Code field once it has been added:

In this selection the 3 customers without a Salesperson Code associated with them will appear in the Warnings tab but the records will continue to exist in the Customer table, as illustrated below once the table has been deployed and executed.

Warnings Tab:

The violation that causes each record to be recorded as a Warning is also shown at the bottom of the screen.

Because the Severity was set to warning, t he customers will still appear in the Customer table.

When the Severity is set to Error the customers that violate the rule will be visible in the Errors tab and the customer will be excluded from the Customer table.

Field Validation can play a very important role in the data cleansing process. In addition to giving the user control over what is or is not included in the tables based on rules that the user specifies it also allows users to see what data is not matching business rule practices in the data source itself. This allows the data to then be properly changed in the ERP system so that future transactions are not associated with incorrect data.

Was this article helpful?

We're sorry to hear that.