Set up clinical data models

2 Set up clinical data models

A clinical data model is a set of logically related tables. You need one input model for each data source. You create target models to review, analyze, or report data. You use transformations to standardize and merge data from one or more source models into a target model.

Figure 2-1 Example of clinical data model use in a study

Description of "Figure 2-1 Example of clinical data model use in a study"

The following table lists the tasks you can perform to set up clinical data models.

Table 2-1 High-level steps

Task	More Information
Create an input clinical data model for every lab.	See Create a file input clinical data model.
Create an input clinical data model for your clinical data system.	If you are using InForm, set up a connection to the InForm study development database and load metadata from there to create the clinical data model. See Create a clinical data model for InForm data. If you are using a different clinical data capture system, see Create a file input clinical data model.
Create target clinical data models and tables as needed.	Create models with tables in the format you need for review and analysis, and any intermediate models required. See Create a target clinical data model for transformed data. You can load metadata from a file using a required syntax, copy a model from another study, create tables manually, or create a study model from a library model, which allows you to update the study model when the library model is updated. To add individual tables, see Add tables.
For each table, define additional attributes and columns.	Add constraints to tables. A primary key is required. Set up data blinding in tables. Add columns to support filtering. See Use SDTM identifiers to support important functionality. Set up Unit of Work data processing for tables (optional). Configure table display in the Listings page for columns on the Listings pages (optional). Map columns to data to be derived from TMS (in target models, if you are using TMS).
Add a Subject Visit table.	Add Subject Visit and Subject tables. Each study must have a Subject Visit table with SDTM identifiers in one model.
Install each model.	Install a clinical data model.
Upgrade each model to Quality Control (optional) and then Production.	Validation status and lifecycle stages.

Other Tasks

More information on the tasks in this chapter

Create a file input clinical data model

Show me how!

On the Home page, select the study and the Development lifecycle stage.
Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Click the Add icon for Clinical Data Models at the top of the left pane.
Enter a name and description. See Naming restrictions for details.
For model type, select Input.
For Input Type, select:
- File to load data files from labs.
- Name_of_System to load data files from a clinical data capture system other than InForm.
For Input Data File Type select either:
- Text
- SAS
For Metadata Source select:
- None to define tables and columns manually.
- Load from file. The system can create tables from:
  - A .zip file that contains one metadata (.mdd) file per table. See Required syntax for metadata files.
  - SAS Transport (CPort or XPort) files, a SAS dataset, or a .zip file that contains SAS datasets or text metadata (.mdd) files (not both).
    Tables created by uploading SAS datasets are created as nonblinded. If data should be blinded, you must define blinding attributes manually for tables in the input data model.
  Note:
  - You can create a table from a metadata file and load data into it from a SAS file. Metadata files are the only automated way to create tables with blinding attributes set.
  - Tables with a large number of columns may cause problems with data loading, transformations, and display in the Listings pages. The supported maximum ranges from 260 columns if all columns are individually blinded to 370 columns if the table is either not blinded, has only row blinding, or is completely blinded. See My Oracle Support article 2298558.1 for the latest information.
- Library Data Model to create tables from a standard library model. If the library model is updated, you can update the study model.
  
  Tip:
  Library models must be checked in to appear in the list. You can only see models you have access to.
- Study Data Model to copy tables from a model in another study. Select its project (or other grouping), then its study, and then the model.
  The Copy operation includes transformations that write to the model and validation checks that read from the model.
To allow visualization tools access to the data contained in the clinical data model, select the Business Area checkbox.

You can change the default schema name, BA_model_name. This is what users of the visualization tool will see. Limitation: Use a maximum of 19 characters for the schema name if you plan to create custom listings on the model. Do not use spaces or special characters other than underscore (_).
Data Mart: A data mart file contains all current data in a clinical data model for export to an external system. Select data mart type(s) to make available.

Only the types that are available in your environment are displayed.
- Oracle Export
- Text Export
- SAS Export
Click OK.

Parent topic: Set up clinical data models

Configure File Watcher data loading

Prerequisite

Your administrator must set up a File Watcher for this study.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select Development from the Lifecycle drop-down at the top of the page.

Tip:

In the Development lifecycle context you can configure File Watcher for any lifecycle stage.
Select the model and click Check Out.
Go to the clinical data model's Watcher Configuration tab.
In the Data Source field, select the name of the lab.

Note:

If this model is for data from an EDC system other than InForm, this field does not appear.
If data will be loaded in SAS files, enter SAS parameter values:
- Upload File Type: Either CPORT, XPORT, or SAS Dataset.
  
  Datasets must be contained in a .zip file and each have the same name as one target table.
- Reported Errors: The number of errors allowed per dataset before the load fails. No records are loaded if the load fails. See Format checks on files being loaded for more information.
Click the Save icon.

Next: See "Set text data load parameters" if you will load data in text files. Otherwise, see "Specify a file name for each lifecycle stage."

Parent topic: Create a file input clinical data model

Set text data load parameters

Prerequisite

Select a data source and save. See Configure File Watcher data loading.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select Development from the Lifecycle drop-down at the top of the page.

Tip:

In the Development lifecycle context you can configure File Watcher for any lifecycle stage.
Select the model and click Check Out.
In the Watcher Configuration tab, click the Data Load Parameters icon.
Specify the format to be used in all data files:
- Fixed: The system uses the target table column length to determine the length of each data value. The file must contain the correct number of characters for each value in each record, in column order.
- Delimited: The system uses a delimiter character you specify to determine when one column value ends and the next one begins.
  - DelimiterChar: Enter the character to separate column values, for example, a comma (,) or a pipe (|).
  - Enclosing Character: If any data value may contain the delimiter character, another character must be added before and after each data value. Enter the character.
Fill in the following fields:
- Skip Records: To prevent loading records at the beginning of the file, enter the number of records you want the system to skip. The default value is zero (0).
- Reported Errors: The number of errors allowed per file before the load fails. No records are loaded if the load fails. See Format checks on files being loaded for more information.
- Rows Before Commit: The number of rows you want the system to process before committing processed rows to the database.
- Date Format: The exact date format used in the data file, if any. Do not enter a value here if the data file does not contain a date field.
  Tips:
  - The examples show mm for minutes, but it should be mi.
  - Use mm for a month number (for example, 11) or mon for a three-letter month (for example, NOV). Use yyyy for a 4-digit year.
  - If you use a 24-hour clock, enter hh24 for the hour. If you use a 12-hour clock, use hh and enter am after a space following the time string.
  For more examples, see: https://docs.oracle.com/database/121/TDDDG/tdddg_globalization.htm#TDDDG255.
Click OK.

Next: Specify a file name convention for each lifecycle stage

Parent topic: Configure File Watcher data loading

Specify a file name convention for each lifecycle stage

This procedures describes how to select the clinical model, name a file specification, set the lifecycle stage, and select the submission mode.

Important:

Make sure you have the privileges to load data in the specified lifecycle stage. If not, data loads will fail. When the data load runs, it uses the account that defined or most recently updated the File Specification.

Click the Study Configuration icon at the top of any page and then click the Clinical Data Models tab.
Select Development from the Lifecycle drop-down at the top of the page.

Tip:

In the Development lifecycle context you can configure File Watcher for any lifecycle stage.
Select the model and click Check Out.
In the Watcher Configuration tab, under File Specifications, click the Add icon.
Enter a name and description for the File Specification.

Tip:

Include the lifecycle stage in the name to make it visible in the Detected Files tab.
File Name: Enter a regular expression for the names of files to be loaded in each lifecycle stage.

File name patterns use the POSIX standard Extended Regular Expression syntax. An asterisk (*) in POSIX syntax matches zero or more occurrences of the preceding character. A dot (.) means "any single character." You can use .* to mean "any character or no characters."

For example, in a study with file input models for three labs, you could use the following file specifications that allow for a date and the lab name:
- CentralLab_.*.zip
- SpecialLab_.*.cport
- LocalLab_.*.zip
Tips:

File names must be unique across all models in the study.

Be careful about case sensitivity. Include the lab or source name.

For more information, see https://docs.oracle.com/cd/B28359_01/server.111/b28286/ap_posix001.htm.
Lifecycle: Select Development, Quality Control, or Production.
Select the Submission Mode:
- Incremental: The system loads all data in the file, inserting new records, updating records with changes, and refreshing the timestamps of records that are reloaded without change. It does not delete any records.
- Full: The system inserts, updates, and refreshes reloaded records as in incremental processing and in addition, compares the unique keys of records in the file to existing records and deletes any records that are not included in the file.
- UOW Load: Within each Unit of Work (subject or subject visit) that has any new or changed records, the system processes all records, inserting new records, updating records with changes, and refreshing the timestamps of records that are reloaded without change. The system does not process records for units of work that have no new or changed records. The system deletes any existing records that are not reloaded within processed units of work. It does not delete records that are not present in the source if no other records from the same unit of work are reloaded.
  
  Tip:
  Create two File Specifications, one for a frequent incremental or UOW load and another for a less frequent full load. Incremental loads are faster and can be run on a subset of data. Full loads are more time-consuming but they detect when to delete data. Be careful to always load the complete set of current data when you use full processing; see Data processing types and modes.
  Use a different file name for incremental/UOW and full loads.
Execution Priority: The priority for loading data with this File Specification relative to others: Low, Normal, or High.
Select the Dataload Type:
- Immediate: The Watcher searches for files continuously.
- Scheduled: The Watcher searches at the interval you specify.
Frequency: If you selected Scheduled, select a frequency for the Watcher to look for a new data file in days, hours, or minutes.
Start Date: If you selected Scheduled, enter the date and time for file watching to begin.
End Date: Enter the date and time for file watching to end. You can enter a date far in the future and change it at any time.
Click OK.

Next: Install a clinical data model or, if the data source is a clinical data capture system other than InForm, Configure the web service location for a source data system.

Parent topic: Configure File Watcher data loading

Configure the web service location for a source data system

If you are using a clinical data capture system other than InForm, you must define its web service location so that Oracle DMW can send discrepancies to it and so that data reviewers can open the other system to view the discrepancy or data there.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select Development from the Lifecycle drop-down at the top of the page.

Tip:

In the Development lifecycle context you can configure File Watcher for any lifecycle stage.
Select the model and click Check Out.
Go to the System Configuration tab.
For each lifecycle stage, click the Modify icon and enter:
1. System Lifecycle: Select the lifecycle stage in the external system that should exchange data and discrepancies with the selected DMW lifecycle stage.
2. Output Location: Select the name of the web service location for the external system, defined by your administrator.
3. Base URL: Enter the URL to use to view discrepancies and data in the external system from the DMW UI.
4. URL Listing Prefix: If required, enter a string to add to the beginning of the base URL to view data in the external system.
5. URL Listing Suffix: If required, enter a string to append to the end of the base URL to view data in the external system.
6. URL Discrepancy Prefix: If required, enter a string to add to the beginning of the base URL to view discrepancies in the external system.
7. URL Discrepancy Suffix: If required, enter a string to append to the end of the base URL to view discrepancies in the external system.
Click OK.

Back to Set up clinical data models

Parent topic: Create a file input clinical data model

Create a clinical data model for InForm data

Show me how!

On the Home page, select the study and the Development lifecycle stage.
Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Click the Add icon for Clinical Data Models at the top of the left pane.
Enter a name and description. See Naming restrictions for details.
For the model type, select Input.
For Input Type, select InForm.

Tip:
Don't create an InForm input clinical data model by copying it. There can be only one input InForm model in a study and its metadata must be imported from InForm. However, you can create a target model by copying an input InForm model and modifying it as necessary.
To allow visualization tools access to the data contained in the clinical data model, select the Business Area checkbox.

You can change the default schema name, BA_model_name. This is what users of the visualization tool will see. Limitation: Use a maximum of 19 characters for the schema name if you plan to create custom listings on the model. Do not use spaces or special characters other than underscore (_).

For InForm models, business areas for InForm metadata and operational data models are also created, with "M" and "O" in their names, respectively. For example, STUDY12345_M_QC is the name of the metadata model's business area in the quality control lifecycle.
Data Mart: A data mart file contains all current data in a clinical data model for export to an external system. Select data mart type(s) to make available:

Only the types that are available in your environment are displayed.
- Oracle Export
- Text Export
- SAS Export
Click OK.

Note:

Tables with more than 339 columns may cause problems with data loading, transformations, and display in the Listings pages. See My Oracle Support article 2298558.1 for the latest information.

Parent topic: Set up clinical data models

Configure the InForm Connector

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select Development from the Lifecycle drop-down at the top of the page.
Open the InForm clinical data model that you have created. (If necessary, see Create a clinical data model for InForm data for details on creating data models.)
If you haven't already checked out the InForm clinical data model, click Check Out.
In the InForm Configuration tab, for each lifecycle stage, in the Remote Location field, specify the InForm reporting database from which to load data.
Remote Study Account Name: Enter the name of the database account that owns the study's InForm reporting database and RDE views for the appropriate DMW lifecycle stage.

Note:

You can change the InForm configuration for any lifecycle to use a different remote location and/or remote study account name. If you do, the system runs a metadata comparison between the InForm model in DMW and the InForm metadata at the new location. If there are differences, the metadata comparison report is displayed. If you accept the changes, data loading is suspended. Reload metadata before resuming data loading.

When you reload metadata, you must redo any blinding and masking you've done in DMW.
InForm LifeCycle: Select the InForm lifecycle stage of the study account name.
Webservice Location: Specify the web service location.
InForm URL: Enter the URL for the study's InForm website.

http://your_InForm_server.your_company.com/your_trial/pfts.dll
Click the Test URL icon on the far right. If the InForm login page opens, the URL is correct. The new window may open behind the current one.
Click the Save icon.

Next: Select internal InForm tables and views for data transformations.

Parent topic: Create a clinical data model for InForm data

Select internal InForm tables and views for data transformations

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select Development from the Lifecycle drop-down at the top of the page.
Select the clinical data model and click Check Out.
Click the Select InForm Operational Data and Metadata Tables icon.

The system displays all InForm tables and views alphabetically. To sort by type, click the heading of the Internal Data Model column. Your administrator makes the default selections. Gray tables and views are required.
Select and deselect until you have selected the tables and views you want for this study.
Save.

After you load metadata and data and install, you can see the internal tables and views in the Default Listings page.

Next: Load InForm metadata.

Parent topic: Create a clinical data model for InForm data

Load InForm metadata

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select Development from the Lifecycle drop-down at the top of the page.
Select the InForm clinical data model and click Check Out.
In the InForm Configuration tab, click the Suspend Data Load icon.
Click the Load InForm Metadata icon. In the Development lifecycle, this process includes installing the model.
Click the Refresh icon and check the Status column.
Click the Resume Data Load icon.

To load data, see:

Parent topic: Create a clinical data model for InForm data

Compare DMW and InForm Metadata

You can compare the metadata—table and column structure—in any DMW InForm model lifecycle stage to any InForm lifecycle database for the same study.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
In the clinical data model InForm Configuration tab, click the Compare DMW and InForm Metadata icon.
In the Metadata Comparison window, select the metadata to compare in DMW and InForm.
Click Compare. The report appears on screen.

To save the report, click the Export All to Excel icon.

If the system finds no differences, a message appears.

For more information, see InForm metadata change detection and synchronization.

Back to Set up clinical data models

Parent topic: Create a clinical data model for InForm data

Create a target clinical data model for transformed data

Show me how!

On the Home page, select the study and the Development lifecycle stage.
Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Click the Add icon for Clinical Data Models at the top of the left pane.
Enter a name and description. See Naming restrictions for details.
For model type, select Target.
For Metadata Source select:
- None to define tables and columns manually.
- Load from file.The system can create tables from a .zip file that contains one (.mdd) file per table. See Required syntax for metadata files.
  
  SAS Transport (CPort or XPort) files, a SAS dataset, or a .zip file that contains SAS datasets or text metadata (.mdd) files (not both).
  
  Tables created by uploading SAS datasets are created as nonblinded. If data should be blinded, you must define blinding attributes manually for tables in the input data model.
  Note:
  - You can create a table from a metadata file and load data into it from a SAS file. Metadata files are the only automated way to create tables with blinding attributes set.
  - Tables with a large number of columns may cause problems with data loading, transformations, and display in the Listings pages. The supported maximum ranges from 260 columns if all columns are individually blinded to 370 columns if the table is either not blinded, has only row blinding, or is completely blinded. See My Oracle Support article 2298558.1 for the latest information.
- Library Data Model to create a study model from a standard library model. If the library model is updated, you can update the study model.
- Study Data Model to copy a model from another study. Select its project (or other grouping), then its study, and then the model.
  
  The Copy operation includes transformations that write to the model and validation checks that read from the model.
You can also Add tables manually.
To allow visualization tools access to the data contained in the clinical data model, select the Business Area checkbox.

You can change the default schema name, BA_model_name. This is what users of the visualization tool will see. Limitation: Use a maximum of 19 characters for the schema name if you plan to create custom listings on the model. Do not use spaces or special characters other than underscore (_).
Data Mart: A data mart file contains all current data in a clinical data model for export to an external system. Select data mart type(s) to make available:

Only the types that are available in your environment are displayed.
- Oracle Export
- Text Export
- SAS Export
Click OK.

Back to Set up clinical data models

Parent topic: Set up clinical data models

Add tables

The following procedures describe how to copy tables, create tables from a file, and add or modify a table.

Parent topic: Set up clinical data models

Copy tables

Copied tables include:

Columns, constraints, and blinding and data processing attribute values.
Validation checks that read from the selected tables, if all the source tables are included in the Copy operation.
Public custom listings that read from the selected tables, if all the source tables are included in the Copy operation.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the model into which you want to copy tables and click Check Out.
In the Tables tab, click the Copy Tables icon.
Select the source: either Study Data Model or Library Data Model.
To specify the tables to copy, start by selecting the study category, then the study (if you selected Study Data Model), then the model.
- To help find any of these, type part or all of their name in the field above.
- Click Clear Filters to remove all typed text and revert to the full list.
Click to select the table or tables to copy.
Specify how to handle any copied tables with the same name as existing tables in the model:
- To replace tables with the same name that already exist in the current model, select Overwrite the same table names.
- If you leave it deselected, the system leaves the existing tables as they are, copies the selected tables and adds _1 (or an increment of 1) to the name of each copied table that has the same name as an existing table. Any validation checks and custom listings copied with the table are mapped to the copied table.
Click OK.

Back to Set up clinical data models

Parent topic: Add tables

Create tables from a file

You can create tables from a ZIP file that contains one or more text metadata (.mdd) files, one for each table. For the required syntax, see Required syntax for metadata files.

SAS Transport (CPort or XPort) files.
A ZIP file that contains one SAS dataset.
A ZIP file that contains one or more text metadata (.mdd) files, one for each table. Metadata files are the only way to create tables with all blinding attributes set; see Required syntax for metadata files.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the model and click Check Out.

Tip:
If the Check Out option is not active, you cannot check out the model because it is already checked out or you do not have the required privileges. To see who checked it out, look at the Checked Out By value at the left of the gray values near the top of the page. You may need to click the >> icon.
Click the Modify icon in the Data Model pane.
For Metadata Source, select Load from File.
Click Choose File.
Click Open.

The system creates one table per .mdd file.

Parent topic: Add tables

Add or modify a table manually

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Model tab is selected.
Select the Development lifecycle.
Select the model and click Check Out.

If the Check Out option is not active, either the model is already checked out or you do not have the required privileges. To see who checked it out, see the Checked Out By value near the top right of the page. You may need to click the >> double arrow icon.

Note:
Do not make structural changes to tables in InForm models; see Modify an InForm input model for information on the changes that are and are not allowed.
In the Tables tab, click the Add Table icon or select a table and click the Modify Table icon.
Enter values in the following fields:
- Enter a name and description for the column; see Naming restrictions for restrictions.
- Oracle Name: The system enters the value you entered for the name, truncated at 30 characters. See Automatic name truncation.
- SAS Name: The system enters the value you entered for the name, truncated at 32 characters.
- SAS Label: (Optional) The system enters the value you entered for the name. It can be up to 256 characters.
- Aliases: Enter one or more aliases, or alternate names for the table. If you want more than one alias, enter a comma-separated list with no spaces—for example: dm,demo,demog,demography.
  
  The system uses these in automapping transformations.
- SDTM Identifier: If this table corresponds to an SDTM standard Subject or Subject Visit table, select its identifier from the list. See Add Subject Visit and Subject tables for information about requirements. Using SDTM identifiers makes automapping more accurate.
  
  Tip:
  
  The SDTM Identifier field is available only when modifying a table.
- UOW (Unit of Work) Processing Type: See Set up Unit of Work data processing for tables.
Set blinding attributes; see Set up data blinding in tables.
Click OK.
Finish table details:
- All clinical data tables must have columns and a primary key. See Add columns to a table manually and Add constraints to tables.
- You must select a data processing type. See Set up Unit of Work data processing for tables.
- You can change the way the table columns are displayed in the Listings pages.
- To mask data, see Set up data blinding in tables.

Back to Set up clinical data models

Parent topic: Add tables

Add columns to a table manually

You can add columns to a table as part of creating the table itself when you upload a file; see Create tables from a file.

Tip:

Be sure to add columns with the following SDTM IDs to support filtering in the Listings and Discrepancies windows: SUBJID, USUBJID, VISIT and VISITNUM. See Use SDTM identifiers to support important functionality.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the model and click Check Out.

Caution: Do not make structural changes to tables in InForm models; see Modify an InForm input model for information on the changes that are and are not allowed.
Select the table.
In the Column tab, click the Add icon. The Create Clinical Data Model Column window opens. The system checks out the table if it is not already checked out.

Note:

There is a limit on the number of columns a table can have and still be displayed in the DMW Listings pages. See the note in Required syntax for metadata files for more information.
Fill in the following fields:
- Enter a Name and Description for the column; see Naming restrictions for restrictions.
- Oracle Data Type: Select the appropriate data type: Varchar2, Number, or Date. All standard rules for Oracle data types apply.
  - DATE: For each Date value, Oracle stores the following information: century, year, month, date, hour, minute, and second. Although date and time information can be represented in both character and number datatypes, the Date datatype has special associated properties.
  - NUMBER: Stores zero, positive, and negative fixed and floating-point numbers. A Number column can contain a number with or without a decimal marker and/or a sign (-).
  - VARCHAR2: Specifies a variable-length character string. For each row, the system stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case the system returns an error.
- Length: The requirements vary according to the data type:
  - DATE: No length required.
  - VARCHAR2: (Required) The default value is 50. The value must be between 1 and 4000.
  - NUMBER: (Required) The default value is 10. The maximum value is 38.
  If the data type is Number you can also enter a value for Precision, which is the total number of digits allowed to the right of the decimal point. For example, if Precision is set to 2 and a data value of 34.333 is entered in this column, the system stores the data value as 34.33. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.
- Map to Filter: If the column has the same function and data type as one of the SDTM column identifiers, it is good practice to select it from the list because the system uses this information in several ways; see Use SDTM identifiers to support important functionality.
- Oracle Name: By default, the system populates this with the value you entered for the name, truncated at 30 characters.
- SAS Name: By default, the system populates this with the value you entered for the name, truncated at 32 characters.
- SAS Label: (Optional) By default, the system populates this with the value you entered for the name. It can be up to 256 characters.
- SAS Format: By default, the system enters a dollar sign ($) followed by the value you entered in the Length field.
- Default Value: (Optional). Enter a default data value.
- Aliases for Automapping: Enter one or more aliases, or alternate names for the column. If you want more than one alias, enter a comma-separated list with no spaces; for example: dm,demo,demog,demography
- Nullable: If selected, having a value in this column is not required. If not selected, all rows must have a value in this column.
- Codelist (Optional): If the column should be populated with a limited set of values that are defined in a codelist, select the appropriate therapeutic area (or other category) and then the codelist. You can apply a codelist only to columns with a data type of varchar2.
  
  Note:
  
  If the table may need to be pivoted from a horizontal (short fat) structure to a vertical (tall skinny) structure—or the reverse—during a transformation, the pivot column must be associated with a codelist; see Pivot.
Enter blinding attributes; available only if the table has a blinding type of Column; see Specify masking attributes for a column.
Click OK.

Back to Set up clinical data models

Parent topic: Set up clinical data models

Add constraints to tables

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the model and click Check Out.

If the Check Out option is not active, you cannot check out the model because it is already checked out or you do not have the required privileges. To see who checked it out, look at the Checked Out By value at the left of the gray values near the top of the page. You may need to click the >> icon.
Select the table.
In the Constraints tab, click the Add icon.
Enter values:
- Constraint: Enter a name for the constraint. It must be unique among constraints for the table and must not contain special characters or Oracle or SQL reserved words.
- Description: (Optional)
- Constraint Type: Select one:
  - Check: The check constraint allows you to specify allowable values for a particular column. Enter one allowed value in the Add Value field and click the arrow icon to move the value into the right-hand column; repeat for each value.
    
    If any row contains a different value for the column, the system does not insert the record but generates an error to the program writing to the table instance. If the program does not handle the error, the job fails.
  - Primary Key: (Required) A primary key is a column or set of columns whose values identify a row in a table as unique. The system uses the primary key to trace data lineage; see How the system tracks data lineage.
    
    Primary key columns cannot have a null value in any row.
    
    The system creates an index based on the primary key, which it uses to enforce a unique constraint and to speed up queries on the table.
  - Unique Key: A unique key is similar to a primary key in that it can include one or more columns whose values identify a row as unique. The difference is that the system allows null values in the columns that are part of a unique key.
    
    Any number of rows can include null (empty) values. A null in a column (or even all nullable columns in a composite unique key) satisfies the unique key constraint. However, you cannot have identical non-null values in the columns of a partially null composite unique key constraint.
  - Non-Unique Index: A non-unique index keeps rows sorted on the specified column or columns to speed up queries.
  - Bitmap Index: A bitmap index stores rowids (row IDs) associated with a key value as a bitmap. Each bit in the bitmap corresponds to a possible rowid. If a particular bit is set, the row with the corresponding rowid contains the key value.
  Note:
  
  The Not Null constraint is handled as an attribute called Nullable for each column.
Columns: Specify the columns in the constraint by selecting them from the list on the left and using an Arrow icon to move them to the right.
Select Supports Duplicate to support inserting records with the same primary key value within a single data load, which may be required in a few cases. Selecting this option ensures that all records are loaded and not deleted but requires careful checking of the data. See Supporting duplicate primary key values in a load.
Click OK.

Tip:

If you create tables by uploading text files you can define constraints at the same time; see Required syntax for metadata files. InForm tables' constraints are imported as part of a metadata load and cannot be modified in the input model.

Back to Set up clinical data models

Parent topic: Set up clinical data models

Set up Unit of Work data processing for tables

Using Unit of Work (UOW) processing can speed up data loading and transformation execution. See Data processing types and modes for more information.

Tip:

We recommend always defining tables as Subject Visit UOW if both Subject and Visit are part of the primary key and as Subject UOW if the Subject column is part of the primary key but Visit is not.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the model and click Check Out.
Select the table.
Define a primary key in the Columns pane, Constraints tab, if there is not already a primary key defined. See Add constraints to tables for details.

Tip:

To quickly see if there is a primary key defined, check the value of the Process Type (not UOW Process Type) attribute in the Tables pane. If there is no primary key, the value is Staging with Audit. When you define a primary key, the value changes to Reload.
Click the Modify Table icon to modify the UOW Processing Type attribute.
In the Modify Clinical Data Model Table pop-up, select one of the following values from the UOW Processing Type drop-down:
- Non UOW: Jobs writing to the table will use Reload processing.
- Subject: Jobs writing to the table will use try to use UOW processing with Subject as the unit of work.
  
  The table must have a column designated with the USUBJID (Unique Subject ID) SDTM Identifier, and must have a primary key that includes that column.
- Subject Visit: Jobs writing to the table will try to use UOW processing with Subject Visit as the unit of work.
  
  The table must have one column designated with the USUBJID (Unique Subject ID) SDTM Identifier and another with the VISITNUM (Visit Number) SDTM Identifier, and both columns must be included in the primary key.
Click OK.

Back to Set up clinical data models

Parent topic: Set up clinical data models

Configure table display in the Listings page

You can change the way table columns are displayed in the Default Listings page.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the model and click Check Out.
Select the table.
Go to the Display Characteristics tab. Each column name is displayed. If the column has been marked Not in Use in the transformation that writes to the table, the Not in Use column displays a Y. These columns are not displayed in the Listings pages.
Click the Modify icon.
For each displayed column, specify the following:
- Displayed?: Deselect if you do not want to display the column on the Listings pages.
- Display Header: Enter the column header to display.
- Display Hover Text: Enter hover text to display for the column.
To change the column order, click the Reorder icon. Select a column and then use the arrows to change its order relative to other columns. Columns at the top are displayed on the left.

Back to Set up clinical data models

Parent topic: Set up clinical data models

Set up data blinding in tables

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the model and click Check Out.
Select the table.
Set blinding attributes:
- Blinded: Select if the table may ever contain any sensitive data that should be hidden.
- Blinding Type (available only if Blinded is selected): Select one:
  - Table: Select to hide all data in the table, then click OK.
  - Column: Select to mask all values in one or more columns, or in cells where data in the row meets conditions you specify, then click OK.
    
    Then select one column, click the Modify icon and specify the masking value.
    
    Tip:
    
    Enclose the value in single quotes.
    
    The default masking values are xxxxx for character data, 99999 for numeric data, and 15-AUG-3501 for dates.
    
    To blind data only in cells that meet certain conditions, specify the conditions; see Specify masking attributes for a column.
  - Row: Select to hide certain rows in their entirety.
    
    Blinding Criteria: Click the Modify Blinding Criteria icon to specify which rows should be hidden. See Use the Expression Builder for details.

Back to Set up clinical data models

Specify masking attributes for a column

Parent topic: Set up clinical data models

Specify masking attributes for a column

If you selected a blinding type of Column for a table, at least one of the columns in the table must be masked.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the model and click Check Out.
Select the table.
Select the column and click the Modify icon. In the Modify Clinical Data Model Column window, select the Blinding Attributes tab.
Select a Masking Level:
- Cell: Masks the real data only in certain rows in this column.
- Column: Masks the real data in this column in every row.
- None: No rows are masked in this column.
Masking Value: Do one of the following to specify what values to display instead of the real values:
- Enter a constant value to be displayed in every row.
  
  Tip:
  
  Enclose the value in single quotes.
  
  The default masking values are: xxxxx for text, 99999 for numbers, and 15 aug 3501 for dates.
- Click the Modify Masking Value icon to create an expression to generate multiple values for the system to display; see Use the Expression Builder for details.
Masking Criteria (for cell-level masking): Click the Modify Masking Criteria icon to specify the criteria for blinding cells in the column; see Use the Expression Builder for details.

Back to Set up clinical data models

Parent topic: Set up data blinding in tables

Add Subject Visit and Subject tables

Include a Subject Visit table in at least one clinical data model in each study. Associate one and only one Subject Visit table per study with the SUBJECTVISIT table SDTM identifier. The system uses only the Subject Visit table to support filters.

This is required to support filtering data and discrepancies by subject and visit and to support tracking subject visit completeness using flags. See How subject and visit filters work.

Tip:

DMW includes an SDTM-compatible Subject Visit table and Subject table that you can copy. Alternatively, you can add an SDTM identifier to an existing table; see Use an existing table as the Subject Visit or Subject table.

Parent topic: Set up clinical data models

Copy the default Subject Visit or Subject table

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the model and click Check Out.

If the Check Out option is not active, you cannot check out the model because it is already checked out or you do not have the required privileges. To see who checked it out, look at the Checked Out By value at the left of the gray values near the top of the page. You may need to click the >> icon.
In the Actions drop-down, select Copy Subject/Visit Table.
Select one:
- Copy from Library: Allows you to copy a Subject, Subject Visit, or both tables created in a library clinical data model by your company.
  
  Then select the study type—therapeutic area or other category—that contains the library model whose tables you want to copy, and then select the library model.
- Default Structure: Allows you to copy the Subject, Subject Visit, or both tables that are shipped with DMW. The table and columns are already associated with the appropriate SDTM identifiers; see Use SDTM identifiers to support important functionality.
Select which tables to copy: Subject, Subject Visit, or Both.
Click OK.

Back to Set up clinical data models

Parent topic: Add Subject Visit and Subject tables

Use an existing table as the Subject Visit or Subject table

Existing tables must comply with the Subject Visit table requirements or Subject table requirements to be designated as the Subject Visit or Subject table.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the model and click Check Out.
Select the table and click the Modify icon.
In the Modify Clinical Data Model Table window, select a value from the SDTM Identifier drop-down list:
- SUBJECT
- SUBJECTVISIT
Click OK.

Parent topic: Add Subject Visit and Subject tables

Subject Visit table requirements

If you designate another table as the SDTM Subject table:

Its primary key must include the Unique Subject ID and Visit Number columns in that order, and no other columns.
These columns must be linked to USUBJID and VISITNUM SDTM identifiers.
The table must be linked to the SDTM SUBJECTVISIT identifier.

Parent topic: Use an existing table as the Subject Visit or Subject table

Subject table requirements

If you designate another table as the SDTM Subject table:

Its primary key must include the Subject ID and no other columns.
The Subject ID column must be linked to the SUBJID SDTM identifiers.

Back to Set up clinical data models

Parent topic: Use an existing table as the Subject Visit or Subject table

Map columns to data to be derived from TMS

Show me how!

Prerequisites: The study must be assigned to a TMS dictionary domain and your administrator must have defined a TMS Set for the dictionary.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select a target model and click Check Out.
Select the target table that contains data you want to code in TMS.
Click the TMS tab, then click the Add icon. The Add TMS Column Association window appears.
Select the TMS Set to use. The system displays the TMS Set description, base dictionary, and primary column name, which is the name of the dictionary level defined as the coding level in your TMS installation.
Select the primary column. The system displays all VARCHAR2 data type columns in the clinical data model table. Select the one whose value you want to have coded in TMS.

The system displays the derived columns defined for the selected TMS Set.
In the Column Name field, select the table column to map to each TMS Set derived column that you need in your study. You do not need to map all the TMS Set columns.

If you have not yet added columns to the table to receive the derived data from TMS, click OK, then add the columns in the Columns tab. Then click the Edit icon in the TMS tab and map the new columns to the TMS Set derived columns.
Click OK.

Back to Set up clinical data models

Parent topic: Set up clinical data models

Extract data from a clinical data model

If a model has been set up to support data extraction, you can extract all current data in the model into a file. The following sections describe how to view existing data marts, set up data extraction (by generating a data mart program), and run a data mart (by running the data extract job).

Parent topic: Set up clinical data models

View existing data marts and their run history

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the model.
Click the Data Extraction tab.
Select a data mart. Its run history appears in the lower pane.
In the Run History pane, click the link in the relevant column to:
- View the output.
- Download the output.
- View the log file.

Back to Set up clinical data models

Parent topic: Extract data from a clinical data model

Generate a data mart program

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the model and click Check Out.
Click the Modify icon.
Select one or more data mart types to generate.
Install the model.

After installation completes, go to the Data Extraction tab to run the data mart.

Back to Set up clinical data models

Parent topic: Extract data from a clinical data model

Run the data extract job

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select a model.
In the Data Extraction tab, select a data mart to run.
Click the Execute icon.
Enter values:
- For a text data mart:
  - Separator: Select the character to use to separate column values in the output file.
  - Enclosing Character: Select the character to use to separate records in the output file. You can select None.
- For an Oracle data mart:
  - Compress: Specifies how Export and Import manage the initial extent for table data.
    
    If set to Y, data is flagged for consolidation into one initial extent upon import. If extent sizes are large (for example, because of the PCTINCREASE parameter), then the allocated space will be larger than the space required to hold the data. This is the default value.
    
    If set to N, the export utility uses the current storage parameters, including the values of initial extent size and next extent size. The values of the parameters may be the values specified in the CREATE TABLE or ALTER TABLE statements or the values modified by the database system. For example, the NEXT extent size value may be modified if the table grows and if the PCTINCREASE parameter is nonzero.
  - Statistics: Select the type of database optimizer statistics to generate when the exported data is imported. Options are Estimate, Compute, and None. The default value is None.
Select the appropriate Blind Break option.
- Not Applicable: None of the tables are blinded. All data will be included.
- Real (Blind Break): If selected, the real, blinded data will be included and the action will be audited. This option is available only if you have special privileges.
  
  Note:
  
  In this release it is not possible to extract masking values. If any data is blinded in the model, you can only extract it if you perform an audited blind break, extracting the real, sensitive data.
- Real (Unblinded): If available, the real data has been unblinded. If selected, the real, unblinded data will be included. This option is available only if you have special privileges.
Select an execution type:
- Immediate to submit the job immediately.
- Deferred to schedule a single execution. Click the Start Date icon and select a date and time.
- Scheduled to create a regular execution schedule. Select a start and end date and time and a frequency number and unit (hours, days, weeks, or months).
  
  For example, with a frequency unit of months and a frequency of 1, the job will run once a month from the specified start date and time to the specified end date and time.

Back to Set up clinical data models

Parent topic: Extract data from a clinical data model

Modify a clinical data model

You can modify an Inform or non-Inform clinical data model. (To add or modify a table manually, see Add or modify a table manually.)

Parent topic: Set up clinical data models

Modify a non-InForm model

Go to the model in the Study Configuration or Library page.
Select the model and click Check Out.

Tip:
To modify a target data model, do not check it out directly. Instead, check out the transformation that writes to it, which also checks out the model. Then return to the model and make your changes, which are then synchronized with the transformation.
Click the Modify icon in the Data Model pane on the left. In the Description field, describe the changes you are making.
Make changes. If you have the required privileges, you can:
- Add, modify, or delete tables by clicking the appropriate icon and making the changes; see Add tables.
  
  Note:
  
  You can add tables or modify existing ones using a metadata file, but you cannot remove tables using a file.
- Add, modify, or delete columns in a table by selecting the table in the upper pane, clicking the appropriate icon in the Columns tab, and making the changes; see Add columns to a table manually.
- Add, modify, or delete constraints in a table by selecting the table in the upper pane, clicking the appropriate icon in the Constraints tab, and making the changes; see Add constraints to tables for details.
- Update to Current Library Version If the study model was created from a library model and the library model has been updated, the Upgrade to Latest Version button appears. Click it to update your study model to the new library version.
  
  Note:
  
  Any changes that have been made to the study model will be lost.
- Update Validation Status Select this option from the Actions drop-down list to change the validation status or to upload a supporting document for the validation status change; see Validation status and lifecycle stages. The system displays this button only if you have the privileges required.
Install the model. The system uses the old version until the new one is installed. The model must have a status of Installable.

A model is not installable if it does not have any tables or if any of its tables are not installable. A table is not installable if it has no columns.

Back to Set up clinical data models

Parent topic: Modify a clinical data model

Modify an InForm input model

The InForm model must have exactly the same table structures as in InForm. You cannot make any structural changes.

Tip:

You can change attributes that apply only in DMW, but if metadata is reloaded from InForm either manually or automatically, your manual changes are lost. See Load InForm metadata for more information.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the model and the Development lifecycle.
In the InForm Configuration tab, click the Suspend Data Loading icon for the Development lifecycle.
Check out the model.
Make your changes. See:
- Add constraints to tables
- Set up data blinding in tables
- Set up Unit of Work data processing for tables
- Add or modify a table manually for information on adding tables aliases and SDTM identifiers. But do not add or remove tables.
- Add columns to a table manually for information on adding column aliases, codelists, and SDTM identifiers. But do not add or remove columns.
Install the model. The system uses the old version until the new one is installed. The model must have a status of Installable.

A model is not installable if it does not have any tables or if any of its tables are not installable. A table is not installable if it has no columns.

Back to Set up clinical data models

Parent topic: Modify a clinical data model

Upgrade a clinical data model to the latest library version

If a clinical data model was created from a library model, and a new version of the library model exists, the Upgrade from Library Model icon appears. Click it to synchronize the study model with the library model.

Note:

Any changes you have made to the study model are lost if you upgrade.

Parent topic: Modify a clinical data model

Roll back changes to the last production version

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Click the Roll Back Clinical Data Models to Production Version icon in the Data Model pane on the left of the Clinical Data Model page. A window appears.
Select one or more clinical data models to roll back.

Note:

InForm models cannot be rolled back.
Click Rollback.
Click the Refresh icon periodically to see the Job ID, Log, and Job Status.

Parent topic: Modify a clinical data model

Install a clinical data model

After creating or modifying a clinical data model, you must install it to make it usable. See What happens during installation? and Installation requirements.

To install a clinical data model:

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Select the lifecycle in which you want to install the model.
If it is an input model, click the Suspend Data Load icon.
Select the model and click Check Out.
Select one option from the Install drop-down:
- Install upgrades all tables and does not delete any data.
- Full Install drops and replaces all tables, deleting all data. Full installation is not available in the Production lifecycle stage.
  Note:
  These options are available only if the object is installable:
  - The Version and the Installable Version must be the same.
  - See Installation requirements.
To see the updated job status in the Install Status field, click the Refresh icon. The final status doesn't appear until the job completes and you click Refresh.
To see the log file, select Get Install Log from the Actions drop-down list.

Back to Set up clinical data models

Parent topic: Set up clinical data models

Unblind and reblind data

A person with the required privileges can unblind the data in a table so that users with Read Unblind privileges can see the sensitive data, normally at the end of a study. Unblinding undoes all types of blinding and masking: whole table, whole column, or row or cell values meeting specified criteria.

A table can be reblinded after being unblinded. Both actions are audited and can be viewed.

The Unblind and Reblind actions are audited.

Click the Study Configuration icon at the top of the page. Make sure the Clinical Data Models tab is selected.
Go to the Data Models page and select the data model, then the table to unblind.
Select Unblind from the Actions drop-down list.

If the table is currently unblinded, the Reblind option appears instead.

Parent topic: Set up clinical data models

FAQs

Parent topic: Set up clinical data models

What happens the first time I run the Load Metadata job?

The Load Metadata job creates one table for each view in the InForm reporting database that is registered in the RD_DATADICTIONARY table and installs the model. The tables have the same structure as the InForm views plus SKEY columns required in DMW.

Parent topic: FAQs

What happens if there's a protocol change?

When metadata changes in InForm, DMW detects it:

During data loading.
When you promote a model to a higher validation status.
When you save changes to a remote location or study account.

The load, promotion, or save is not allowed and a message appears that you need to reload metadata.

You can also run the Compare Metadata report by clicking its icon in the InForm configuration tab, and then import metadata.

Parent topic: FAQs

How do InForm and DMW exchange data?

Data and metadata are imported from the study's InForm reporting database to DMW using a database connection.

Discrepancies are sent almost immediately to InForm as queries, using a web service.

Parent topic: FAQs

When I copy a table, sometimes validation checks and custom listings are included in the copy and sometimes not included. Why?

When you copy a table, the system checks if you have included all source tables for the validation checks and custom listings in the copy operation. If you have, the validation checks and custom listings are also copied.

If you have not included all the source tables:

If a validation check is in an ordered batch, you receive a warning.
For validation checks that are in an unordered batch, and for any public custom listing, the system does not give a warning and does not copy the validation check or custom listing.

If you need the validation check or custom listing, you can copy it separately.

Parent topic: FAQs

Can I use Oracle Thesaurus Management System in all three lifecycle stages?

In Development and QC you can send data to TMS, derive data for terms that can be automatically coded, and create DMW discrepancies for terms that cannot be automatically coded. These discrepancies will close only if the data is updated in such a way that it can be coded automatically.

Only Production has complete coding functionality. In addition to deriving data:

TMS creates omissions for terms that cannot be automatically coded. TMS users can code these terms manually.
DMW creates discrepancies that correspond to the omissions and sends them to InForm. You can export discrepancies on lab data to Excel and send the spreadsheet to the lab.

Parent topic: FAQs

If I create a new version of a clinical data model in the Development lifecycle, can I revert to the previous Production version?

Yes. If, for example, a protocol amendment is canceled after you created a new version of the clinical data model in the Development lifecycle, you can undo all changes and revert to the version being used in the Production lifecycle.

For InForm models, the rolled-back Development model is installed as part of the process. Non-InForm models are not installed during the rollback. The Production version is checked out and made the current Development version.

If the older Production version has fewer columns or shorter ones, for example, the rollback is "destructive" and all data is deleted from the model in Development.

Parent topic: FAQs

Is it possible to get back data that has been deleted?

Deleted data is no longer available in the system. However, it remains in the database with an end timestamp equal to the date and time of the job when it was deleted.

Parent topic: FAQs

Why am I getting error "ORA-00955: name is already used by an existing object"?

If you perform an upgrade installation on a clinical data model after modifying a table's primary key (PK) or unique index, you may get the above error from Oracle Warehouse Builder (OWB).

This is because when you create a primary key on a table, the system generates a unique index on the PK columns and maintains a connection between the PK and the uinque index. If you drop the primary key, the system drops the unique index. However, if you have manually created a unique index on the same columns, the system does not drop the index and the PK and index are no longer connected.

If you subsequently modify a PK column or manually create a unique index, the system generates and runs reorg.sql with the following steps:

Drops the PK.
Alters the column (or whatever change you have made).
Recreates the unique index on the PK columns.
Recreates the PK.

This works, but the connection between the primary key and unique index is lost. If you make another manual change, reorg.sql fails with the above error message because it does not drop the unique index in Step 1 due to the disconnect, but does try to create the unique index in Step 3, causing the error.

If you do a Full installation, the system recreates the primary key, the unique index, and a connection between them. However, it also deletes all data in the model's Development lifecycle stage.

Parent topic: FAQs