Data Validation

What is it and why use it?

It answers the question ‘Is my data valid and could its relationships be improved?’. It can check and report on:

• Invalid User defined soft links.

• Invalid data in the database.

• Foreign keys that could be missing in the database.

It will also look for new rules and categorise the database entities.

In order to use it, you first need to create the data activity.

Create a Validate Database Data activity

On the Activity Explorer page (1):

• choose the appropriate directory (2)

• Click Add Activity (3)

• Select the Validate Database Data activity (4)

This will display the Validate Database Data dialog, where you can name and describe your activity and choose the server to use.

The next step, will display the summary page that will allow you to finish creating the activity and then open it.

When you open the activity, you will need to configure it.

Configure the Validate Database Data activity

You need to attach a Database connection (1) and a Definition version (2)

Attach Default Database connection

This will present a dialog box, for you to select the database connection

Note: the details on creating a database connection are: Create a new database connection

Attach Definition Version

This will present a dialog box so you can select the database definition and version.

Note: the details on creating a database definition are: Register a definition

Having attached the connection and definition, these will now be visible in the components tab for the activity.

The next step will be to perform a validation.

Perform a Validation

Once you have added a definition (1), then you can perform some validations on the database.

Select the Actions drop down list (2) for the definition

Once you have chosen the Run Database Validation action, then click the execute button (3).  Some of these available actions are listed below in the Validation Actions section.

Validation Actions

Run Database Validation

On selecting this action you are presented with a dialog box where you can select and add the tables that you want validated.  In the following example I selected all by checking the box in the header (1).  However if you want to only select a few, check the relevant boxes (2).  Then click Add tables (3) (which will un-check the tables, but add them to the internal list of tables to be validated).  Finally click Execute (4).

This will bring up the ‘Data Activity - Run Database Validation - Job Parameters’ dialog box, where you can set the database (1) and the number of rows to sample (2) before clicking  executing to start the job.

The job details window for the validation process will open in a new browser window, so you can examine the logs and results.

If you refresh the data activity view, then in the Activity section there will be a new record for the validation (1), and if you click to download the attachments (2), then you will download the validation report, which you can check.

On the the validation report you can see details like the number of invalid foreign keys on the FK results tab and the number of errors inside a table on the summary tab.

Look for Potential New Relationships

As for Run Database Validation, you need to select the tables.

Once the tables are selected you are presented with the ‘Data Activity - Look for potential new relationships - Job Parameters’ dialog box.

Executing this will open a new browser tab showing the job details.  Once the job finishes and the data activity window is refreshed, then the relationship activity will be added to the list in the Activity panel (1). For this activity there are multiple attachments created, which you can download by clicking the attachment buttons (2).

If there are potential foreign keys then one of the reports will detail them which can be helpful when generating data. It will highlight soft foreign keys that you may consider adding.