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.

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.

Customer Table:

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.

Errors Tab: