6 Creating Validation Checks

See "Viewing and Running Validation Check Batches".

Validation checksâalso called edit checksâare programs designed to identify flawed data: discrepancies (called queries in InForm). 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âincluding lab and InForm data and data from different CRFsâcontained in a single data model.

The same flawed data point can have several discrepancies created against it raised by different validation checks. For example, if the value is both out of range and does not make sense compared to a related data point.

The first time a validation check is executed, 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 VC (Validation Check) Listings page.

Validation checks are part of a study or study template. When you apply a study template to a study, the validation checks from the study template are copied to the target study.

Automated Discrepancy Closure You can set up a validation check so that it closes any discrepancies it previously created if the underlying data point has been updated in such a way that it is no longer discrepant.

Manual Discrepancy Closure Alternatively, you can set up a validation check so that, when the underlying data point is no longer discrepant, the validation check marks the discrepancy as "Answered" but a data manager must review the discrepancy and data change and manually close it.

Custom Programs You can define most validation checks, including comparing related data points across tests, visits, or sources, directly in the Validation Check window. However, if you need a more complex program, you can write a program in SAS or PL/SQL and upload it to Oracle Life Sciences Data Hub (Oracle LSH) and reference the program from a validation check; see "Creating a Custom Validation Check" for more information.

Creating a Validation Check Batch

Validation checks are executed in batches of one or more. Before you create a validation check you must create the batch in which to execute it. You can use batches to group validation checks in logical waysâfor example:

Checks that have dependencies on each other so they must be executed in a particular order.
Standard checks kept in a set for ease of reuse in many studies.
Checks that should all be executed manually, or triggered by the same event, or scheduled for the same frequency.

To create a validation check batch:

Select your study in the Home page and click Study Configuration.
Select Validation Checks at the bottom of the left pane.
Select the clinical data model whose data you want to check.
Click the Create Batch icon.
Enter a name for the batch. If it is not unique within the study, the system appends "_1" or the next higher number to its name.
Enter a description (optional). The description is displayed in the places where you can run the batch.
If the validation checks in the batch should be run in a particular order select the Ordered Execution? check box. You specify the order when you create the validation checks.

Validation checks in unordered batches run in parallel.
Check Can be Triggered to allow the successful completion of a transformation or data load writing to the clinical data model that this batch runs against to trigger the execution of this batch.
Click OK.
Create the validation checks to run in the batch; see "Creating a Validation Check".

Copying Validation Checks

You can copy validation checks from another study or from a different clinical data model in the same study. The system checks if the required source tables are available in the current model.

Tip:

Copy validation checks after completing the transformation that writes to the model, so that you know which tables are used in the model. If you copy validation checks that read from tables or columns marked Not Used in the transformation, the system copies them as disabled. If the tables or columns are later marked Used, you must manually enable the validation checks.

Select the validation check batch into which to copy validation checks. In the Study Configuration page, navigate to Validation Checks, then to the clinical data model, and then to the batch.
Click the Copy Checks icon in the Validation Checks for batch_name pane. The Copy Validation Checks window opens.
The system displays all validation checks to which you have access. You can type part or all of the name of the Therapeutic Area (or other category), Study, and Model to filter the list.

Click Clear Filters to remove all typed text and revert to the full list.
Select a model. The system displays all the validation checks associated with the model, with their validation check batch. You can type all or part of the batch or check name to filter.
Select one or more validation checks. Use Ctrl+click or Shift+click to select multiple checks.
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 the tables or columns do not exist, the Copy operation fails with an error message.
- If they exist but are marked Not Used in the transformation that writes to the model, the system copies the validation checks as disabled and displays the reason they are disabled on the Validation Checks page.
- If the tables and columns exist and are used, the system copies the validation checks and links them to the appropriate tables and columns.

Creating a Validation Check

Before you create a validation check, you must create a batch in which to execute it; see "Creating a Validation Check Batch".
Select the batch. If it is not already checked out, click Check Out.
Click Add Check.

Building the Validation Check Query

To create the query for a validation check:

Enter a name and description for the query. The name is displayed on the VC Listings page; the description is not. The Listings page can display about 25 characters of the name without requiring scrolling to see the whole name. You may want to use a naming convention to make it easier to find queries later.
Select Authorize access to this listing for users without Blind Break rights if you know that only nonblinded data will be displayed in the listing. This option is available only if at least one source table contains blinded data and if you have the required blinding-related privileges.

If any source table is blinded at any level and this attribute is not selected, the system blinds the entire target table, so that by default no data is displayed on the VC Listings page for this validation check. Only a user with the required Blind Break privileges can view the data.
Select Create VC Using a Custom Program if you need more complex logic than you can create here and you have already created the program in Oracle LSH. See "Creating a Custom Program in Oracle Life Sciences Data Hub". Then click the Select a Program icon to select the custom program.

If you select Create VC Using a Custom Program, select the source tables and skip the next steps. Your program must take care of selecting columns for display in the target table on the VC Listings page, creating joins, and specifying criteria if required.

Note:
The custom program's source tables' Oracle name cannot be more than 25 characters long. This is because the target table must contain a column for surrogate key information that contains the source table name.
Specify the columns to display; see "Selecting Columns to Display in VC Listings". The system creates a SELECT clause based on your specifications.
If you need to use a function from a library in the SELECT or WHERE clause, and you plan to write the expression using the function in free text, open the Select Packages tab and check the packages you will use. This enables the system to generate the query code and supports the Test function.

If you use the Expression Builder (reached by clicking the Modify Expression icon), you do not need to use the Select Packages tab.
If you need to use a self join in the SELECT clause, open the Define Table Alias tab and click the Add icon to add another row for the same table. Specify a different aliases for the table in each row. Use the aliases to create a self join in the Criteria pane.

Note:
The table alias must be three characters or less. If it is longer it causes problems.
Specify query criteria; see "Specifying Criteria". The system creates a WHERE clause based on your specifications.

Selecting Columns to Display in VC Listings

In the Select Columns tab, identify the columns you want to display for each record retrieved by the query. You can give columns a different header for display and combine or write expressions on one or more columns as required. The system creates a SELECT clause for the query based on your specifications.

In the Source pane, expand the node for the table or tables whose data you want to display in the VC Listings page for each record retrieved.

Note:
Tables and columns marked Not Used in the transformation are not displayed here, nor are uninstalled tables.
Select the columns you want to display. You can use Ctrl+click and Shift+click to select multiple columns at a time. You can also select a table to add all its columns and then remove the ones you do not want to display.
Click the Add icon. The system displays the columns you selected under Selected Columns, each in its own row.

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 using the Add icon and highlight it there.
2. Select the additional columns in the Source pane and click the Arrow icon in the Source pane. The system adds them to the same row.
Table Alias: If you have defined table aliases for a table in a self join in the Define Table Alias tab, select a column and click the Select Table Alias icon to specify which table alias the selected column belongs to.
Alias: To display a heading for the column different from the column name, enter it in the Alias field. This heading appears in the VC Listings page.

Note:
This is different from giving a column or table a database alias, which you do in the data model under Study Configuration.
Expression: Add the expression, if any, to operate on the column in the SELECT clause. Enter free text or click the Modify Expression icon; see "Using the Expression Builder in Validation Checks and Custom Listings". You can edit code generated by the Expression Builder in this field afterward.

Selecting Packages

If you plan to create expressions in the query using free text, first use the Select Packages tab to identify packages whose functions you will use. This enables the system to generate the query code and supports the test function. The system displays all packages in the DMW_UTILS domain that meet certain criteria; see "Developing a Library of SQL Functions".

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

Adding Table Aliases for a Self-Join

To create a self-join:

Add the table and column(s) to the Selected Columns tab.
Open the Define Table Alias tab and click the Add icon to add the table as many times as required.
Specify a different alias for the table in each row.

Note:
The table alias must be three characters or less. If it is longer it causes problems.
In the Selected Columns tab, select a column and click the Select Table Alias icon to specify which table alias the selected column belongs to.
Specify the join criteria in the Criteria pane. See "Using the Expression Builder in Validation Checks and Custom Listings".

Specifying Criteria

In the Criteria pane, build the WHERE clause.

Click the Add or Modify Criteria icon to open the Expression Builder. See "Using the Expression Builder in Validation Checks and Custom Listings".

You can edit code generated by the Expression Builder or enter code directly in the Criteria pane.

Note:

Oracle recommends using the Expression Builder to add columns so that Oracle DMW "knows" what you are using in the expression and can use table and column aliases defined in the clinical data model to help determine if source tables and columns exist when you copy validation checks.

Defining Validation Check Details

Validation checks must be associated with a batch of validation checks that are executed together.

Select the batch. If it is not already checked out, click the Check Out icon.
Click Create a New VC.
Enter a name for the validation check that is unique within the batch. If it is not unique within the batch, the system appends "_1" to its name. Avoid using special characters except underscore (_) and reserved words; see "Naming Objects".
All the other fields affect the discrepancies identified by the validation check:
- Allow Auto Close: If selected, rerunning the validation check closes any discrepancy it created if the underlying data point is modified so that it no longer satisfies the criteria of the validation check.
  
  If not selected, rerunning the validation check changes the state of discrepancies not satisfying the criteria to Answered, so that they can be manually closed after review.
- Category: Select a category for the validation check. The validation check applies this category to each discrepancy it creates. Users can filter and count discrepancies by category.
  
  See "Viewing and Creating Categories".
- Discrepancy Initial State: Select a state to be applied to discrepancies created by this validation check: Open or Candidate. You can require review in Oracle DMW or send discrepancies to InForm immediately in either state.
- Discrepancy Text: Enter the text to be displayed for the discrepancy. This is the message that will be used for communication with InForm, lab, or Oracle DMW users. Describe the problem with the data and/or the action required.
- Initial Discrepancy Action: Select the action you want the validation check to immediately perform on the new discrepancies, if any; see "Actions".
- Primary Source Table: You must designate one data point as the primary one against which discrepancies are created. Select the table that contains the primary data point. If the validation check logic processes only one data point, select its table. If the logic processes two or more data points, you must designate one of them as primary.
- Primary Source Column: Select the column that contains the primary data point.
  
  Note:
  You can ensure that all data points are displayed with the discrepancy in the VC Listings page by adding their columns in the Selected Columns tab.
- Execution Order: 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 highest, 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. This setting is available only in batches with ordered execution. The default value is 100. Up to 6 digits are allowed.
- Continue on Error: If selected, the batch continues to execute subsequent validation checks even after one fails. This setting is available only in batches with ordered execution. Unordered batches always run all validation checks.

Installing Validation Checks

Before you can run a validation check, you must select its batch and select Install. This creates database packages for the generated program. If any source tables are not installed, the Installation job tries to install the whole clinical data model.

The batch must be Installable to be installed. It is not Installable if:

It does not have any validation checks.
One or more source tables are not installable.
If it has a custom program, the program is not installable, which may be due to not having source code or table descriptors.

If the batch does not install successfully, the system displays an error message with a Job ID. You can view the log file on the Home page's Validation Checks tab.

Using the Expression Builder in Validation Checks and Custom Listings

You build an expression gradually, clicking Add after defining each part. The system then generates corresponding code and displays it in the Expression Text pane.

You can enter free text or edit generated code in the Expression Text pane.

Note:

In the Expression Criteria pane, select the following as needed to build the expression from left to right.
- Add Group to add the parentheses () that surround a string in an expression or group smaller units of logic.
- Add Item to add a unit of logic smaller than a group.
When you add an item, in the Expression Item pane select either Column, Function, or Standard Function.

To create an expression using columns:
1. For Item Type, select Column.
2. Click the Select Column icon. The Select Column window appears, displaying all available columns. You can query (see "Querying By Example") above any of the attribute columns to find the table column you want. Select a column, then click OK.
3. If needed, select an operator from the list.
4. If needed, enter a constant value. The system encloses the value you enter in single quotes.
5. If needed, select a conjunction from the list.
6. Click Add. The system generates and displays the SQL expression in the Expression Text pane. You can edit it there.
  
  Click Validate to check the generated code.
7. Define additional groups and items to complete the expression as necessary.
To reference a function in your library:
1. For Item Type, select Function. The Select Function window appears, displaying a list of packages created for this purpose; see "Developing a Library of SQL Functions".
2. Select the package that contains the function you need.
3. Select the function and click OK.
To use a SQL function:
1. For Item Type, select Standard Function.
2. Click the Select Standard Function icon. A search window appears. To filter, enter all or part of the name in the field above. You can use the wildcard %.
3. Select a function and click OK.
To make a correction:
1. Select the faulty item in the Expression Criteria pane. An Update button appears in the Expression Item pane.
2. Make your changes in the Expression Item pane and click Update.

For more information about expressions, see:

Also see the OracleÂ® Database SQL Language Reference 11g Release 2 (11.2).

If you need to perform a more complex operation on the source dataâfor example using a lookup table or populating staging tablesâsee "Creating a Custom Program".

Running a Validation Check Batch

After you have installed the validation check batch, you can run it in the Home page, Validation Check tab.

You can run the job at any time on demand or schedule it to run on a regular basis or link it to other jobs as part of a scheduled or triggered forward-chained execution; see "Viewing and Running Validation Check Batches".

The resulting data is displayed in the VC (Validation Checks) Listings page.

Modifying a Validation Check

To make any change to a validation check other than enabling or disabling it, you must check out the batch. To make most changes, click the Modify Batch icon and see instructions for "Creating a Validation Check".

Reordering Validation Checks

To change the execution order of an ordered batch, click Re-order. 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.

Disabling and Enabling a Validation Check

To prevent a validation check from being executed when the batch is executed, select it and then click the Disable Check icon. To include it in the batch execution after disabling it, select it and then click the Enable Check icon.

Note:

The system disables validation checks if their source tables or columns are marked Not Used in the transformation that writes to this clinical data model. The system prevents you from enabling a check whose source tables or columns are marked Not Used.

You can change this setting whether the validation check batch is checked in or out. If a check is disabled, the system records who or what disabled it in the Disabled Reason column. Hover over the value to see the complete text.

Upgrading a Validation Check Batch

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 batch becomes upgradable.

Go to the Validation Check Batch list in the Study Configuration page.
Select the batch and click the Upgrade Batch icon. The system updates the table and column mappings behind the scenes.

Creating a Custom Validation Check

To write a program that is too complex for the user interface, you can write a PL/SQL or SAS program in an editor and upload it to an Oracle Life Sciences Data Hub (Oracle LSH) program, then associate it with a validation check. For example, if you need to refer to a table outside the data model such as shared codelist tables, lab reference ranges, or SI conversion factors, you must create a custom program and use a static reference within it.

You also need a custom program to call APIs for assigning flags to records or applying actions to records.

If you have already written an appropriate SAS or PL/SQL program outside of Oracle LSH, you can upload it to an Oracle LSH program. However, to support data lineage tracing, you will need to add columns to the target table and populate them as described below.

Note:

To create and use a SAS program, you must purchase SAS separately and integrate it with Oracle LSH. See the Oracle Life Sciences Data Hub Installation Guide for instructions.

Create the program the same way you do for a transformation; see "Creating a Custom Program in Oracle Life Sciences Data Hub" except for this additional task:

Each validation check should have a single target table with the following requirements:
- The table name should be no longer than 25 characters.
- The table must have one column per source table to store the surrogate key for the source table, which is a concatenation of the primary key values for each record; see "How the System Tracks Data Lineage".
1. In the Oracle LSH Program page, create a new Table Descriptor and Table instance for the target table.
2. In addition to the columns you need to display the results of the validation check, add one column for each source source table with a name like source_table_SKEY.
3. In the source code, populate each of these columns with the value from the CDR$SKEY column of each source table.
Define the validation check in a batch and enter values for all required fields.
Select Create VC using a Custom Program. The system displays a window where you can select the source tables for the custom validation check program.
When the Select a Program icon appears, click it and select the program. You can use the Query By Example fields above any column to search for all or part of a value in that column. For example, enter %cardio% to search for a program in the Cardiology category your company has set up; see "Setting Up Custom Program and Function Categories".