A Reference Information

This section contains the following topics:

• "Naming Objects"

• "Required Syntax for Table Metadata Text Files"

• "Object Ownership"

Naming Objects

This section contains the following topics:

• "Avoid Special Characters and Reserved Words"

• "Name Length: Keep It Short"

• "Handling of Duplicate Names"

• "Naming Studies and Libraries"

• "Customizable Naming Validation Package"

Make an object's name descriptive to help other users understand its purpose, but keep it short. Develop naming conventions; see "Customizable Naming Validation Package".

Avoid Special Characters and Reserved Words

To avoid problems, do not use special characters such as ( & @ * $ | % ~ ) in object names, except for underscore (_). Also do not use Oracle SQL or PL/SQL reserved words in object names, especially the Oracle name, which defaults from the generic name. For lists of reserved words, see:

Oracle® DatabaseSQL Language Reference at http://download.oracle.com/docs/cd/E11882_01/server.112/e17118.pdf

Oracle® Database PL/SQL Language Reference at http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126.pdf

For the latest information, you can generate a list of all keywords and reserved words with the V$RESERVED_WORDS view, described in the Oracle® Database Reference at http://download.oracle.com/docs/cd/E11882_01/server.112/e17110.pdf.

Name Length: Keep It Short

Although many name fields allow 200 characters, short names work better both for display in the user interface and for technical reasons. For example, the Validation Check (VC) Listings page displays columns using the format data_model_name>table_name>column_name in a single UI column. The shorter the name of the data model, table, and column, the easier it is for the user to see the whole value.

In addition, Windows has a maximum length of a file path of 256 characters that may be a problem for users who develop custom programs in Oracle LSH; see "Keep Container and Object Names Short for Integrated Development Environments".

Oracle and SAS names also have much smaller limits; see "Automatic Name Truncation".

Keep Container and Object Names Short for Integrated Development Environments

Although names can contain up to 200 characters, the maximum length of a file path is 256 characters in Windows. When you open an integrated development environment (IDE) such as SAS or run a SAS program on your personal computer, the system uses the actual full file path for source code definitions, table instances, and the SAS runtime script. If the full file path exceeds this length you get an error and cannot open the IDE or run the program. You can use a package to limit object name size or display an error when an object's file path is too long; see "Customizable Naming Validation Package".

The full file path begins with the username of the person who opens the IDE followed by the directory name cdrwork. It also includes the DMW_DOMAIN itself, the study, and any libraries or other subdomains you create. (The maximum number of subdomains is 9. This number is configurable in Oracle LSH; see the Oracle Life Sciences Data Hub System Administrator's Guide.)

• Source Code definitions. The path for Source Code definitions is: username>cdrwork>Domain_name>Subdomain_name(s) (0-9)>Application_ Area_name>Program_definition_name>Source_Code_definition_nameversion_number>fileref>source_code_filename

• Table instances. The path for Table instances is: username>cdrwork>Domain_name>Subdomain_name(s) (0-9)>Application_ Area_name>Work_Area_name>program instance name>program version>Table_Descriptor_libname> Table_Descriptor_SAS_name>

• SAS runtime script. The path for x is: username>cdrwork>Domain_name>Subdomain_name(s) (0-9)>Application_ Area_name>Work_Area_name>Program_instance_name>version_number>setup

Automatic Name Truncation

Names can contain up to 200 characters. However, the system populates the values for Oracle Name and SAS Name with the value you enter for Name stored in uppercase, truncating the Oracle Name to 30 characters and the SAS Name to 32 characters. You can change the Oracle and SAS Names.

When you create a new object by uploading a table, view, data set, or column, the system truncates the Oracle Name and also replaces the last two characters with the number 01 or, if an object of the same type with the same name already exists in the same container, the next highest number (in this case, 02).

Handling of Duplicate Names

The system enforces unique naming for each object type in the same container, whether the container is an organizational object—Domain, Application Area or Work Area—or a Report Set or Workflow. (Report Sets contain Programs. Workflows may contain Programs, Load Sets, Report Sets, and/or Data Marts.) The system enforces unique names only within the immediate container; for example, you cannot have two Program instances named Program_A in the same Workflow, but you can have a Program_A in a Workflow and another Program_A directly in the Work Area that contains the Workflow.

If you try to create a second object of the same type and name in the same container, the system creates the object but automatically appends an underscore number to the name. If you add a third object of the same type and name, the system increments the number. For example, if you create a Program called Merge in Application Area 12345, and then create another Program called merge in the same Application Area, the system names the new Program Merge_1. If you create a third Program called Merge in the same Application Area, the system names the new Program Merge_2.

Naming Studies and Libraries

Studies and Libraries in Oracle DMW are Oracle LSH domain objects. If you plan to export a domain to another Oracle DMW instance, you may want to avoid using spaces in its name. Domain names with spaces must be entered with escape characters surrounding it in the Import Export Utility; for example: \" domain name\". See the Oracle Life Sciences Data Hub System Administrator's Guide for more information.

Customizable Naming Validation Package

Object creation and modification code includes a call to a predefined validation package from every object name field. By default, this package performs no validation and returns a value of TRUE, allowing users to enter any name in the field. However, you can customize the package to enforce your own naming conventions, full path length, or other object attribute standards. See "Customizing Object Validation Requirements" in the Oracle Life Sciences Data Hub System Administrator's Guide.

Required Syntax for Table Metadata Text Files

You can define tables in a data model by uploading a .zip file that contains one .mdd file per table, each of which must have the required syntax described here. This is the only way you can automatically create tables that include all constraints and blinding attribute values.

Note:

You can create a table initially from a metadata file and subsequently load data into it from a SAS file.

The system uses the file name (without the extension) as the table name.

The system expects a set of column attribute values, optionally preceded by a row identifying the delimiter and a row defining Table attribute values, each of which must begin with a key word. A row beginning with dashes is treated as a comment. For example:

--This is a comment.

Additional information on each section of the required syntax follows Example A-1, "Metadata File".

Example A-1 Metadata File

lsh_delimiter = |
--This section is for the Table attributes
--Name, Description, Oracle Name, SAS Name, SAS Label, Process Type, Allow Snapshot ?, Blinding Flag ?, Blinding Status, SAS Library Name, Is Target?, Target as Dataset ?
lsh_table= AE|Adverse Events|AE|AE|AE|Reload|Yes|Yes|Blinded|target|yes|yes

--This section is for columns
--Name, Data Type, Length, Precision, Oracle Name, SAS Name, SAS Format, Description, SAS Label, Nullable, Default Value,date format
Study|varchar2|15||Study|Study|$15.||Study|yes||
DCMNAME|varchar2|16||DCMNAME|DCMNAME|$16.||DCM Name|yes||
        DCMSUBNM|varchar2|18||DCMSUBNM|DCMSUBNM|$18.||DCM Subset Name|yes||
SUBSETSN|number|3||SUBSETSN|SUBSETSN|3.||DCM Subset Number|yes||
DOCNUM|varchar2|20||DOCNUM|DOCNUM|$20.||Document Number|yes||
INVSITE|varchar2|10||INVSITE|INVSITE|$10.||Site|yes||
INV|varchar2|10||INV|INV|$10.||Investigator|yes||
PT|varchar2|10||PT|PT|$10.||Patient|yes||
ACCESSTS|date|||ACCESSTS|ACCESSTS|$datetime20.||Accessible TS|yes|1|MM-DD-YYYY
LOGINTS|date|||LOGINTS|LOGINTS|$datetime20.||Login TS|yes|1|DD-MON-YYYY
LSTCHGTS|date|||LSTCHGTS|LSTCHGTS|$datetime20.||Last Change TS|yes|1|MON-DD-YYYY
VISIT_NUMBER|number|10||VISIT_NUMBER|VISIT|10.||Visit|yes||
QUALIFYING_VALUE|varchar2|70||QUALIFYING_VALUE|QUALIFYINGV|$10.||Qualifying Value|yes||
 
--This section is for constraints
--Name, Description,Constraint Type,Comma separated list of columns,Comma separated list of values for check const,duplicate support for PK flag ,surrogate key flag
CONSTRAINT|pk_1|pk|PRIMARYKEY|YES|YES|[Study]
CONSTRAINT|uk|uniq|UNIQUE|||[DCMNAME]
CONSTRAINT|pk_22|nuq|NONUNIQUE|||[DOCNUM]
CONSTRAINT|bmap_invsite|bitmap on INVSITE|BITMAP|||[INVSITE]
CONSTRAINT|check_inv|check on INV|CHECK|||[INV]|{1|2|3|4}

Delimiter  The first row defines the delimiter used in the file. If not specified, Oracle LSH treats it as a comma delimited file. The delimiter row must begin with: lsh_delimiter=

Table Attributes The second row lists the table attributes required in the file. The Table attribute row must begin with: lsh_table=

If the second row is not present or contains null values, the system assumes that the file name (without extension) is the Table Name and follows the normal Oracle LSH default behavior for the attribute values. The attributes and their required order in the file are: Name, Description, Oracle Name, SAS Name, SAS Label, Process Type, Allow Snapshot?, Blinding Flag?, Blinding Status, SAS Library Name, Is Target?, Target as Dataset?

Some attributes have associated reference codelists and allow either the actual values for the associated reference codelist (RC) columns or the decode values defined in the "Meaning" attribute of the _RC lookup. For example, "select meaning from cdr_lookups where lookup_code='< RC>' so that YES or Yes or $YESNO$YES are acceptable values.

The table below outlines the applicable values for each attribute that has an associated reference codelist. For more information, see the Oracle Life Sciences Data Hub Application Developer's Guide.

Note:

Processing types that require audit keys are not supported.

Table A-1 Attributes with Reference Codelist Values

Attribute	Values
Processing Type	UOW, Reload, Staging with Audit, Staging without Audit, Transactional High Throughput, Transactional without Audit
Allow Snapshot	Yes, No
Blinding Flag	Yes: The table may contain sensitive data at some point in time. No: The table will never contain blinded data.
Blinding Status	If Blinding Flag is set to Yes, the data may have a status of either Blinded or Unblinded. (Users can set Blinding Status to Authorized but not in the table itself.) If Blinding Flag is set to No, the data must have a Blinding Status of Not Applicable.
Is Target	Yes, No
Target as dataset	Yes, No
Data Type	Date, Number, Varchar2
Nullable	Yes, No

Columns  Subsequent rows must contain the column, or variable, attributes with each represented by a new row in the text file with attributes. The position is determined by the order in which the column /variable rows are processed. For example, in a comma delimited file: Name, Data Type, Length, Precision, Oracle Name, SAS Name, SAS Format, Description, SAS Label, Nullable, Default Value, Date Format

Oracle LSH validation rules apply to the column or variable attributes. The operation uses Oracle LSH default values if invalid values are provided for any of the attributes.

Constraints The final section is for constraints. Each constraint must have its own row starting with the string CONSTRAINT followed by values you supply for the constraint name, description, and constraint type, followed by other values depending on the constraint type.

The required values for Constraint Type are, with the remaining values required for each type:

• PRIMARYKEY|Duplicate PK Support Flag (YES or NO)|Surrogate Key Flag (YES or NO)|Comma-separated list of columns in key

  For example: CONSTRAINT|pk_1|pk|PRIMARYKEY|YES|YES|[SUBJID

• UNIQUE|Comma-separated list of columns in key

• NONUNIQUE|Comma-separated list of columns in key

• BITMAP|Comma-separated list of columns in key

• CHECK|Comma-separated list of allowed values

Example A-2 Constraint Metadata

--Name, Description,Constraint Type,Comma separated list of columns,Comma separated list of values for check const,duplicate support for PK flag ,surrogate key flag
CONSTRAINT|pk_1|pk|PRIMARYKEY|YES|YES|[Study]
CONSTRAINT|uk|uniq|UNIQUE|||[DCMNAME]
CONSTRAINT|pk_22|nuq|NONUNIQUE|||[DOCNUM]
CONSTRAINT|bmap_invsite|bitmap on INVSITE|BITMAP|||[INVSITE]
CONSTRAINT|check_inv|check on INV|CHECK|||[INV]|{1|2|3|4}

Object Ownership

Figure A-1, "Oracle DMW Object Ownership" shows the hierarchy of object containership in Oracle DMW.

Effects of User Group Assignment to Objects

A user group assigned to an object is automatically assigned by inheritance to all objects contained in it. You can explicitly revoke the user group from any owned object, which also revokes the assignment from objects contained in that object.

When you assign a user group to an object, you must select either Metadata, Development, QC, or Production. The last three choices provide access to the three lifecycle areas, shown as their actual object names in the diagram: DEV_APP_AREA, QC_APP_AREA, and PROD_APP_AREA or to an object within the selected lifecycle area. Metadata provides access to object definition(s).

As in Oracle LSH, object definitions are pure metadata. They must be installed as object instances in a Work Area schema to be used:

• When you install a clinical data model in the Development lifecycle area, the system creates a database schema for the model and creates a database table for each table defined in the model.

• When you install a transformation or a validation check batch in the Development lifecycle area, the system creates a PL/SQL package for each programs contained in the transformation or validation check batch.

Only then can tables actually hold data, transformations and validation checks read and write data.

Users must have access to the objects in the lifecycle Work Areas to install and execute objects. To modify objects such as models, transformations, and validation checks, they must have access to the appropriate lifecycle Work Area and the object metadata.

Note:

The actions a user in a user group can take on an object to which the user group is assigned depend on the privileges defined for the role the user has in the group. See "Predefined Object Security Roles".

Figure A-1 Oracle DMW Object Ownership

Oracle DMW Object Hierarchy

Oracle DMW uses Oracle LSH object types and a few new object types created for use in Oracle DMW. The following diagram shows only primary objects; the relationships with secondary, or component, objects such as parameters, source code, variables, and columns is the same as in Oracle LSH and is documented in the Oracle Life Sciences Data Hub Application Developer's Guide.

The hierarchy used in Oracle DMW, shown in Figure A-1, "Oracle DMW Object Ownership", is described in the following sections.

DMW Domain

Within the Oracle LSH instance domain, one of which is automatically created in each installation of Oracle LSH, the DMW domain is also automatically created during product installation. It contains all user-created objects in Oracle DMW.

The DMW domain is an LSH object of type Domain.

DMW Utilities

The DMW_Utils domain is automatically created during product installation inside the DMW domain. It is designed to hold all custom programs and functions created for use with Oracle DMW by customers. You can create domains within it to organize the programs you create, using the Oracle LSH user interface or public APIs.

You create programs in the Oracle LSH user interface or with public APIs.

Study Category Domains

You define additional domains in the DMW domain to categorize studies and to hold library clinical data models and code lists for each study category. By default the categories are called therapeutic areas, but you can change this; see Setting Up Library and Study Categories.

You create these domains in the Oracle LSH user interface or with public APIs.

Study Domains

An Oracle DMW study is a domain. When you create a study in the Oracle DMW user interface, the system creates a domain inside the study category domain you indicate.

Lifecycle Areas

When you create a study, the system automatically creates three lifecycle areas; one each for Development, Quality Control, and Production. These are LSH objects of type Application Area and their names are DEV_APP_AREA, QC_APP_AREA, and PROD_APP_AREA respectively.

Clinical Data Models

When you create a clinical data model, the system puts its object definitional metadata directly in the study domain. When you install the model, the system creates a Work Area object in the current lifecycle area and a database schema for it containing a database table for each table definition in the model definition.

Load Sets

When you create an input clinical data model of type File, the system automatically creates an Oracle LSH Load Set object which it then uses to actually load data from a file to the model in Oracle DMW. When you install the model, the system also installs the Load Set.

Programs

When you create a custom program for a transformation or validation check, or when the system generates a program for a transformation or validation check, the Program definition is contained directly in the study and installed in the relevant clinical data model work area:

Transformation Maps

Transformation map metadata is contained directly within the study domain. Each transformation map has three levels: the model level contains all table-level maps, and the table-level maps contain column-level maps.

Business Areas

If you want to use a data visualization tool to view Oracle DMW data, create an Oracle LSH Business Area object and link it to the tables you want to view. See "Setting Up a Data Visualization Tool"

Data Marts

If you want to use export Oracle DMW data to a file, create an Oracle LSH Data Mart object and link it to the tables whose data you want to export. See the Oracle Life Sciences Data Hub Application Developer's Guide chapter on data marts for information.