5 Create validation checks

Validation checks, also called edit checks, are programs designed to identify flawed data, or discrepancies (also called queries). Each one must check for a single problem and apply the same text, state, and action to each discrepancy created. Validation checks can test any combination of data that is contained in a single clinical data model. To run a validation check comparing data that originated in both InForm and a lab, create a transformation to put InForm and lab data in one clinical data model.

When you save the validation check, it creates a target table with columns corresponding to source table columns you select and a row for each discrepancy identified. Each time it runs it updates the table with new or changed data. The data in this table is displayed in the Validation Checks Listings page.

You specify whether or not a validation check automatically closes discrepancies it created when their underlying data item is updated to be valid.

• Create a validation check batch
  
• Validation checks
  
• Create a validation check
  
• Complete a validation check without a custom program
  
• Complete a validation check using a custom program
  
• Copy a validation check batch
  
• Copy a validation check
  
• Disable or enable a validation check
  
• Reorder validation checks within an ordered batch
  
• Install a validation check batch
  
• Run validation check batch and view run history
  
• View discrepancies created by a validation check
  
• Upgrade validation checks to synchronize with models
  
• View audited modifications to a validation check or batch
  
• FAQs
  

Create a validation check batch

Show me how!

Validation checks are executed in batches. Before you create a validation check you must create a batch for it. Use batches to group validation checks in logical ways:

• Checks that have dependencies on each other so they must be executed in a particular order.

• Standard checks kept together for reuse in many studies.

• Checks on the same clinical data model that should all be triggered by the same event or scheduled for the same frequency.

1. Click the Study Configuration icon at the top of any page and then click the Validation Checks tab.
2. Select the clinical data model containing the data you want to check.

   Note:

   • Models are displayed only if they are installed.

   • The validation checks in the batch reference the latest installed version of the model. If the model is updated after the batch is created, the Upgradeable status changes to Required and you must upgrade the check to reference the latest version of the model. See Why does it say Upgrade Required?.

3. Click the Add icon.
4. Enter a name for the batch. See Naming restrictions.
5. Enter a description (optional). The description is displayed where you run the batch.
6. Select Ordered Execution? if the validation checks in the batch should be run in a particular order. Validation checks in unordered batches run in parallel.
7. Select Can be Triggered to allow the successful completion of a transformation or data load writing to the clinical data model to trigger the execution of the batch.
8. Click OK.

Next: Create a validation check.

Validation checks

This window shows any validation checks that have already been created for the selected validation check batch. To make any changes, check out the batch.

• To create a new validation check, click the Add icon. See Create a validation check.

• To copy a validation check from another study, select Copy Checks from the Checks drop-down list. See Copy a validation check.

• To modify an existing validation check, selecti it and click the Modify icon. For an explanation of the fields, see Create a validation check or Complete a validation check using a custom program.

• To delete a validation check, select it and click the Delete icon.

Create a validation check

Before you create a validation check, you must create a batch in which to execute it; see Create a validation check batch.

Show me how!

1. Click the Study Configuration icon at the top of any page, then click the Validation Checks tab.
2. Select the validation check batch you want the new validation check to be executed with. If necessary, Create a validation check batch.
3. Click the Add icon in the lower pane.
4. Enter a name and description for the validation check. See Naming restrictions for details. The name is displayed on the Validation Checks Listings page; the description is not.

   The remaining details affect the discrepancies created by the validation check:

5. Select Authorize access to this listing for users without Blind Break rights if you know that only nonblinded columns will be displayed in the listing. This option is available only if at least one source table contains blinded data and if you have special privileges.

   If you do not select this option and one of the source tables is blinded, the system completely blinds the listing. To see any data, a user with Blind Break privileges must break the blind.

6. Discrepancy Text: Describe the problem with the data and/or the action required. Maximum 255 characters.
7. Discrepancy Initial State: Select a state to be applied to discrepancies created by this validation check: Open or Candidate. Use Candidate to require manual review for setting the discrepancies to Open.
8. (Optional) Select a Category. The validation check applies this category to each discrepancy it creates. Users can filter discrepancies by category.

   Note:

   If you change the category, all new discrepancies created by the check have the new category, but the category of existing discrepancies remains the same.

9. (Optional) Select the Initial Discrepancy Action you want the validation check to immediately perform on new discrepancies, if any.

   • Needs DM Review adds this tag to discrepancies with either initial state. Discrepancies must be reviewed in DMW before being sent to InForm or a lab.

   • Open InForm immediately sends the discrepancy to InForm as an open query. Available only for data in an InForm clinical data model and only if you selected Open as the initial state.

     Note:

     Come back and select the Discrepant Table and Discrepant Column after selecting the tables and columns to be displayed. See Select columns to display in the validation checks listings page.

10. Execution Order: (Available only in ordered validation check batches.) Enter a number to indicate the order in which this validation check should be run, in relation to other validation checks in the same batch. The system runs the lowest numbered check first, then the next lowest, and so on. The numbers do not need to be consecutive, and you may want to use numbers divisible by 10, for example, so that you can add a new validation check at any point.
11. Continue on Error: (Available only in ordered validation check batches.)

    • If set to Yes, the batch continues to execute subsequent validation checks even after one fails.

    • If set to No, execution stops if one of the checks fails.

    Unordered batches always run all validation checks.

12. Allow Auto Close: If selected, rerunning the validation check closes any discrepancy it created if the underlying data item is modified so that it no longer meets the criteria of the validation check.

    If not selected, rerunning the validation check changes the state of such discrepancies to Answered so that they can be manually closed after review.

Next: Complete the validation check:

• Complete a validation check without a custom program
• Complete a validation check using a custom program

Complete a validation check without a custom program

Prerequisites: Create a validation check batch and Create a validation check.

• Select columns to display in the validation checks listings page
  
• Select packages
  
• Add table aliases
  
• Specify validation check criteria
  
• Generate, test, view, and save source code
  

Select columns to display in the validation checks listings page

Identify the columns you want to display in the Validation Check Listings page. Include any columns the validation check will operate on. The system creates a SELECT clause for the query based on these selections.

1. Continuing in the Add Validation Check window, in the Source pane, expand the node for the table or tables with data you want to display in the Validation Checks Listings page.

   Note:

   Tables and columns marked Not Used in the transformation are not displayed here, nor are uninstalled tables.

2. Select columns by moving them into the Selected Columns tab. You can work several ways:

   • Drag and drop selected columns or tables.

   • Select columns or tables and click the Add to Select statement icon.

   • To write an expression that operates on multiple columns, add all columns in the expression to the same row:

     1. Move one column into Selected Columns and highlight it there.

     2. Select the additional columns and click the Use in Expression arrow icon.

3. Select a Table Alias for each column used in an expression. If there is only one defined for the table, the system adds it automatically.

   To add an alias, go to the Table Alias tab. See Add table aliases.

4. Enter the Column Header for Display. This header is displayed in the Validation Checks Listings page.

5. Expression: Add an expression, if needed, to change data display, for example, to mask blinded data, or to append a string to all values in the column, or to concatenate two column values in a single column. Either:

   For example, to prefix ‘Test' before the SUBJID, write: Select ‘Test'+SUBJID.

   • Enter free text.

     Important:

     • If you reference a static package or function in free text, you must select it in the Selected Packages tab.

     • In free text, use just the column name, not the table.column format, unless you need to use an alias, as in a self-join. In that case the alias.column format is required.

   • Click the Modify Expression icon; see Use the Expression Builder for details. You can edit code generated by the Expression Builder in this field afterward.

     If you use the Expression Builder, you do not need to use the Select Packages tab.

     Note:

     If you select a column that contains masked data, write an expression for the column to display masking values.

     Validation checks can evaluate real data that is masked and create a discrepancy on it.

     Target table is blinded if source table supports table/column/cell/row level blinding. Since target tables are auto generated and they are not accessible in DMW other than see the data in listing. So, we cannot define row/column/cell level blinding on custom listing and VC's target table. Target table is authorized when authorize attribute is set for custom listing and VC.

6. Discrepant Table and Column: In the upper portion of the window, designate one data item as the one against which discrepancies are created. If the validation check logic processes two or more data items, select one of them.

   Tip:

   You must specify Selected Columns before you can specify the primary table or column.
   1. In the Discrepant Table field, select the table that contains the discrepant data.
   2. In the Discrepant Column field, select the column that contains the discrepant data.

Next: Specify validation check criteria.

Select packages

If you use free text to write an expression and the expression references a static package or function, you must select the package or function in the Select Packages tab.

1. Use the Query By Example fields above the columns to search for the package.
2. Select the package(s) you are using in the validation check.
3. Click OK.

Next: Add table aliases.

Add table aliases

Columns used in an expression must have a 3-character table alias.

1. In the Define Table Alias tab, select the table from the drop-down list.
2. Enter the alias. The alias cannot be longer than three characters.
3. If you need an additional alias, click the Add Table Alias icon.

   Tip:

   If you modify an alias later, any expressions or criteria that refer to the old alias are updated during the Save operation. If an expression or criteria already saved refers to a deleted alias, update it manually. To work with the expression or criteria before saving, update it manually.

Next: Specify validation check criteria.

Specify validation check criteria

In the Criteria tab, specify the data condition the validation check is looking for. This becomes the WHERE clause.

Either:

• Enter the code directly in the Criteria pane.
• Click the Add or Modify Criteria icon to open the Expression Builder. See Use the Expression Builder for details.

  Important:

  • If you reference a static package or function in free text, you must select it in the Selected Packages tab.

  • In free text, use just the column name, not the table.column format, unless you need to use an alias, as in a self-join. In that case the alias.column format is required.

Next: Generate, test, view, and save source code.

Generate, test, view, and save source code

1. Click Test. The system generates the code, runs the validation check and displays the records retrieved.
2. To view the generated PL/SQL source code, click View Source. The button is inactive if the validation check uses a custom program.
3. Click OK.

Next: Install a validation check batch.

Complete a validation check using a custom program

Prerequisites: Create a validation check batch and Create a validation check.

1. Continuing in the Add Validation Check window, select Create VC using a Custom Program.

2. Click the Select a Program icon.

   1. Use the Query By Example fields above the columns to search for the package.

   2. Select the package.

   3. Click OK.

3. In the Select Source Tables tab, move the tables that contain data you want to display, or that your program operates on, to the Select edTables box.

4. Discrepant Table and Column: In the upper portion of the window, designate one data item as the one against which discrepancies are created. If the validation check logic processes two or more data items, select one of them.

   Tip:

   You must specify Selected Columns before you can specify the primary table or column.

   1. In the Discrepant Table field, select the table that contains the discrepant data.

   2. In the Discrepant Column field, select the column that contains the discrepant data.

5. Click OK.

Next: Install a validation check batch.

Copy a validation check batch

You can copy a validation check batch from another study or from a different clinical data model in the same study. Disabled validation checks, if any, are included in the copy. No source tables are copied.

Tip:

Copy validation check batches after completing the transformation that writes to the model, so that the system can handle them appropriately.

1. In the Study Configuration page, navigate to Validation Checks, and select the clinical data model.
2. Click Copy Batches in the Actions drop-down list.
3. Select the project (or other study category), then the study, then the model.
4. Select one or more batches.
5. Click OK.

   The system searches the current model for the tables and columns that the validation checks read from, first by Oracle name and then by alias.

   • If any of the tables or columns do not exist, the Copy operation fails.

   • If any of the tables are marked Not Used in the transformation that writes to the model, the system copies the validation checks as disabled. If the tables or columns are later marked Used, you can manually enable the validation checks.

   • If the tables and columns exist and are used, the system copies the validation checks and links them to the appropriate tables and columns.

Next: Install a validation check batch.

Copy a validation check

1. Click the Study Configuration icon at the top of any page and then click the Validation Checks tab.
2. Select the batch into which you want to copy a validation check.
3. Select Copy Checks from the Checks drop-down.
4. Select the project (or other study category), then the study, then the model.
5. Select the batch and validation check.
6. Click OK.

Next: Install a validation check batch.

Disable or enable a validation check

1. Click the Study Configuration icon at the top of any page and then click the Validation Checks tab.
2. Select the batch that contains the check.

   • To prevent a validation check from being executed when the batch is executed, select it and then select Disable Check from the Checks drop-down list.

   • To re-activate it, select it and then select Enable Check from the Checks drop-down list.

     The system prevents you from enabling a check whose source tables or columns are marked Not Used.

Reorder validation checks within an ordered batch

1. Click the Study Configuration icon at the top of any page and then click the Validation Checks tab.
2. Select the batch.
3. Select a check.
4. Select Reorder Checks from the Checks drop-down list.

   The system displays all validation checks in the batch with their Execution Order number. Edit the numbers as required. They do not need to be consecutive. The system runs the validation check with the lowest number first, then the next higher number, and so on. For example, it would run checks with these numbers in the following order: 20, 30, 35, 36, 40, 100, 200.

Note:

If a validation check has Enabled set to No, the system ignores its execution order number and does not run it.

Install a validation check batch

After creating or modifying a validation check batch, you must install it to make it usable. See What happens during installation? for details.

Validation check batch installation fails if there are destructive changes in the model such as the removal of a column or table that the validation check reads. See Why does it say Upgrade Required? for details.

1. Click the Study Configuration icon at the top of any page and then click the Validation Checks tab.

2. Select one or more validation check batches, then select one of the following from the Install drop-down list:

   • Install Batch upgrades all tables and programs and does not delete any data.

   • Full Install drops and replaces all tables and programs, deleting all data. Full installation is not available in the Production lifecycle.

     Note:

     The installation options are available only if all selected batches are installable:

     • The Version and the Installable Version must be the same.
     • See Validation check batch installation requirements for details.

3. To see the updated job status in the Install Status field, click the Refresh icon.

4. To see the log file:

   1. Go to the Home page, Validation Checks tab.

   2. From the Model drop-down list, select the source clinical data model.

   3. In the VC Batch Name column, select the batch.

   4. Click the icon in the Install Job Log column in the same row.

Run validation check batch and view run history

• Run a validation check batch

• View run history

• Run a validation check batch
  
• View run history
  

Run a validation check batch

You must run validation checks as a batch.

1. Go to the Home page, Validation Checks tab.
2. Select a validation check batch and click the Submit Job icon.

   Tip:

   The Submit Job icon does not appear if the validation check batch is not installed. Check the Installed Status.

   If the installation status of a validation check batch is Warning, you may still be able to run the batch. Check the installation log file. If the status is Warning because one of the source tables is not used in the current study, the batch runs without input from that table.

3. Enter values:

   • Submission Mode: Select one:

     • Full mode processes all records.

     • Incremental mode processes only new and changed records.

   • Force Execution: Select to run the job even though the source data currency, parameter values, and the version number of the program(s) have not changed since the last run. The system uses Full mode regardless of the Submission Mode setting. Full mode includes data deletion.

     If not selected and all the conditions are the same as the last run, the system does not execute the job and returns a status of Success.

   • Submission Type: Select:

     • Immediate to run the job once, as soon as possible.

     • Scheduled to set up a regular schedule.

     • Deferred to run the job once, at a future time.

   • Trigger Downstream Transformations and Validation Checks: Select this checkbox if you want the system to detect all transformations and validation checks set up for this data model and all others that come after it, and submit them sequentially.

     Note:

     This option appears only if the validation check batch is set up to allow it.

   • Click the Refresh icon at any time for an update.

   • To check the log file, click the icon in the Log column.

View run history

1. Go to the Home page, Validation Checks tab.
2. From the Model drop-down list, select the clinical data model.
3. Select a validation check batch in the upper pane.

   The system displays information about its validation checks in the middle pane and information about its run history in the lower pane.

4. To view all jobs, click the View Full Job History icon.

   To view only recent jobs again, click the View Recent Jobs Only icon.

   To view log files, click the icon in the column for the type of job:

   • Log (Run History pane) The most recent manually submitted job.

   • Triggered Job Log (Run History pane) The most recent triggered job.

   • Install Job Log (Validation Check Batch pane) The most recent installation of the validation check batch.

   Note:

   A validation check can be disabled so that it is not included in the batch execution. To find out if a check was included in the run, check the log file.

View discrepancies created by a validation check

1. Click the Data Management icon at the top of any page to reach the Listings page.
2. Select the source clinical data model.
3. Select Validation Checks from the bottom of the left pane.
4. Expand the node for the batch containing the validation check.
5. Select the validation check.

Upgrade validation checks to synchronize with models

If there have been metadata changes in a clinical data model—for example, an increase of column length—that affect a transformation or validation check, the system sets the transformation or validation check to Upgrade Required. You must run the upgrade job to synchronize the transformation or validation check with the model.

• Select one or more batches and click the Upgrade Batch icon or reinstall the batch(es).

• If columns or tables have been removed, mappings may be broken. You must fix these manually.

• When you next install the batch(es), installation ends with a warning if a validation check refers to a table or column that no longer exists in the source model. You must do the synchronization manually.

View audited modifications to a validation check or batch

1. Click the Study Configuration icon at the top of any page and then click the Validation Checks tab.
2. Select the lifecycle.
3. Select the target model.
4. Select the batch or validation check you want to see audit history for and either:

   • To see the audit trail for the batch, select Audit Trail from the Actions drop-down list.

   • To see the audit trail for a validation check, select the check and click the Audit Trail icon.

To export the change records to Excel, click the Export to Excel button. A maximum of 1000 records can be exported. They will be the 1000 most recent records.

FAQs

• Why can't I install my validation check batch?

• Why are some validation checks disabled?

• Do I have to check out the batch to disable a validation check?

• Why does it say Upgrade Required?

• Why isn't the transformation triggering my validation check batch job?

• Where are custom programs stored?

• Why can't I install my validation check batch?
  
• Why are some validation checks disabled?
  
• Do I have to check out the batch to disable a validation check?
  
• Why does it say Upgrade Required?
  
• Why isn't the transformation triggering my validation check batch job?
  
• Where are custom programs stored?
  

Why can't I install my validation check batch?

It may be because the clinical data model is not installed. Install the model and try again.

Why are some validation checks disabled?

Validation checks that read from a table or column marked Not Used are automatically disabled. If the table or column is later marked as Used, you must manually reenable the validation checks to run them.

A person may have manually disabled a validation check to prevent it from running.

To find out why a particular validation check is disabled, hover over the value in the Disabled Reason column.

Do I have to check out the batch to disable a validation check?

No. You can disable or enable checks whether the validation check batch is checked in or out.

Why does it say Upgrade Required?

If there have been metadata changes in the source clinical data model—for example, change of column length—that affect any validation check in a batch, the system sets the value of the Upgradable column to Required. Select the batch, then select Upgrade Batch from the Actions drop-down, then install the batch.

See Upgrade validation checks to synchronize with models.

Why isn't the transformation triggering my validation check batch job?

Set this up in two places:

1. In the transformation that writes to the clinical data model that the validation check reads from, set Can Trigger to Yes in Add or Remove Source Models.
2. In the validation check batch, select Can Be Triggered.

Where are custom programs stored?

All custom programs for both validation checks and transformations are stored in the DMW_UTILS domain/namespace. Any other locations listed are subdomains inside that domain.