| Oracle® Health Sciences Data Management Workbench User's Guide Release 2.4 E52292-02 |
|
|
PDF · Mobi · ePub |
Home > User's Guide > Reference Information
| Oracle® Health Sciences Data Management Workbench User's Guide Release 2.4 E52292-02 |
|
|
PDF · Mobi · ePub |
Home > User's Guide > Reference Information
This section contains reference information.
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".
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.
Note:
The system uses the value you enter in the Name field for the default Oracle name.For information on 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.
Note:
In addition, DUPLICATE is a reserved word in Oracle DMW for columns. It is used in the Default Listings page to allow filtering on duplicate records.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".
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 Oracle DMW_DOMAIN itself, the study, and any libraries or other subdomains you create. (The maximum number of subdomains is 9. This number is configurable using a profile in Oracle LSH; see the Oracle Life Sciences Data Hub System Administrator's Guide.) See also "Object Ownership."
Source Code definitions path: username>cdrwork>Domain_name>Subdomain_name(s)>Application_Area_name>Program_definition_name>Source_Code_definition_name>version_number>fileref>source_code_filename
Table instances path: username>cdrwork>Domain_name>Subdomain_name(s)>Application_ Area_name>Work_Area_name>program_ instance_name>program_ version>Table_Descriptor_libname> Table_Descriptor_SAS_name>
SAS runtime script path: username>cdrwork>Domain_name>Subdomain_name(s)>Application_ Area_name>Work_Area_name>Program_instance_name>version_number>setup
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 sequential number (in this case, 02).
The system enforces unique naming for each object type in the same container. The system enforces unique names only within the immediate container. For example, you cannot have two tables with the same name in the same model, but you can have tables with the same name in different models in the study.
If you try to create a second object of the same type and name in the same container, the system creates the object but appends an underscore and the number one (_1) to the name. If you add a third object of the same type and name, the system increments the number (_2).
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.
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.
You can define tables in a data model by uploading a .zip file that contains one .mdd file per table. Each .mdd file must have the syntax described here, in the order given. 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 then load data into it from a SAS file.Delimiter (Optional) Must be lsh_table= delimiter. If you do not specify a delimiter, the default delimiter is a comma (,).
Table (Optional) Must begin with lsh_table= . If you do not specify a table name in the file, the system uses the file name (without the extension) as the table name and follows Oracle LSH default behavior for the attribute values.
Columns The system expects a set of column attribute values, one column per row in the file, optionally preceded by a row identifying the delimiter and a row defining Table attribute values, each of which must begin with a key word. Column position is determined by the order in which the column rows in the file are processed. Oracle LSH default behavior for the attribute values applies.
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.
Comments A row beginning with two dashes is treated as a comment.
--This is a comment. lsh_delimiter = , lsh_table= Name, Description, Oracle Name, SAS Name, SAS Label, Process Type, Allow Snapshot?, Blinding Flag?, Blinding Status, SAS Library Name, Is Target?, Target as Dataset?, SDTM Identifier (SUBJECT/SUBJECTVISIT), Table Alias, Blinding Type (TABLE/COLUMN/ROW), Blinding Criteria --Column details: Name, Data Type, Length, Precision, Oracle Name, SAS Name, SAS Format, Description, SAS Label, Nullable?, Default Value, Date Format, SDTM Identifier, Column Alias, Masking Level (COLUMN/CELL), Masking Value, Masking Criteria --Constraints must start with string "CONSTRAINTS". Requirements for each type: CONSTRAINT,Name,Description,PRIMARYKEY,Duplicate_PK_Support_Flag (YES/NO), Surrogate_Key_Flag (YES/NO),{delimited_list_of_columns_in_key] CONSTRAINT,Name,Description,UNIQUE,,,[delimited_list_of_columns_in_key] CONSTRAINT,Name,Description,NONUNIQUE,,,[delimited_list_of_columns_in_key] CONSTRAINT,Name,Description,BITMAP,,,[delimited_list_of_columns_in_key] CONSTRAINT,Name,Description,CHECK,,,[column_name],{delimited_list_of_values}
Note that all constraints require square brackets ([]) around the column name(s). In addition, the check constraint requires curly brackets ({}) around the list of values.
See Table A-1, "Table Attributes with Reference Codelist Values" andTable A-2, "Column Attributes with Reference Codelist Values".
lsh_delimiter = | --This section is for 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?, SDTM Identifer (SUBJECT/SUBJECTVISIT), Table Alias, Blinding Type (TABLE/COLUMN/ROW), Blinding Criteria lsh_table=S_QS|S_QS Table|S_QS|S_QS|S_QS|Staging with Audit|Yes|Yes|Blinded|Target|Yes|Yes||qs|ROW|(VISITDY < 100) -- --This section is for columns --Name, Data Type, Length, Precision, Oracle Name, SAS Name, SAS Format, Description, SAS Label, Nullable, Default Value, Date Format, SDTM Identifier, Column Alias, Masking Level(COLUMN/CELL), Masking Value, Masking Criteria -- STUDYID|VARCHAR2|12||STUDYID|STUDYID|$12.||Study Identifier|Yes|| USUBJID|VARCHAR2|11||USUBJID|USUBJID|$11.||Unique Subject Identifier|Yes|| QSTESTCD|VARCHAR2|7||QSTESTCD|QSTESTCD|$7.||Question Short Name|Yes|| VISITNUM|NUMBER|||VISITNUM|VISITNUM|8.||Visit Number|Yes|| DOMAIN|VARCHAR2|2||DOMAIN|DOMAIN|$2.||Domain Abbreviation|Yes|| QSSEQ|NUMBER|||QSSEQ|QSSEQ|8.||Sequence Number|Yes|| QSTEST|VARCHAR2|40||QSTEST|QSTEST|$40.||Question Name|Yes|| QSCAT|VARCHAR2|70||QSCAT|QSCAT|$70.||Category for Question|Yes|| QSSCAT|VARCHAR2|26||QSSCAT|QSSCAT|$26.||Sub-Category for Question|Yes|| QSORRES|VARCHAR2|20||QSORRES|QSORRES|$20.||Finding in Original Units|Yes|| QSORRESU|VARCHAR2|7||QSORRESU|QSORRESU|$7.||Original Units|Yes|| QSSTRESC|VARCHAR2|4||QSSTRESC|QSSTRESC|$4.||Character Result/Finding in Std Format|Yes|| QSSTRESN|NUMBER|||QSSTRESN|QSSTRESN|8.||Numeric Finding in Standard Units|Yes|| QSSTRESU|VARCHAR2|7||QSSTRESU|QSSTRESU|$7.||Standard Units|Yes|| QSBLFL|VARCHAR2|1||QSBLFL|QSBLFL|$1.||Baseline Flag|Yes|| QSDRVFL|VARCHAR2|1||QSDRVFL|QSDRVFL|$1.||Derived Flag|Yes|| VISIT|VARCHAR2|19||VISIT|VISIT|$19.||Visit Name|Yes|| VISITDY|NUMBER|||VISITDY|VISITDY|8.||Planned Study Day of Visit|Yes|| QSDTC|VARCHAR2|10||QSDTC|QSDTC|$10.||Date/Time of Finding|Yes|| QSDY|NUMBER|||QSDY|QSDY|8.||Study Day of Finding|Yes|| -- --This section is for constraints -- CONSTRAINT|pk_1|pk|PRIMARYKEY|Yes|YES|[STUDYID|USUBJID]
Example A-2 Constraint Metadata
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}
Table A-1 Table Attributes with Reference Codelist Values
| Attribute | Valid Values |
|---|---|
|
Processing Type |
UOW, Reload |
|
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. You can safely use the default value (YES). |
|
Target as dataset |
YES, NO. You can safely use the default value (NO). |
|
SDTM Identifier |
For tables: SUBJECT, SUBJECTVISIT |
|
Blinding Type |
TABLE, COLUMN, ROW |
Table A-2 Column Attributes with Reference Codelist Values
| Attribute | Valid Values |
|---|---|
|
Data Type |
VARCHAR2, NUMBER, DATE |
|
Nullable |
YES, NO |
|
SDTM Identifier |
For valid values, see "Using SDTM Identifiers for Columns". |
|
Blinding Type |
TABLE, COLUMN, ROW |
|
Masking Level |
COLUMN, CELL |
In order to understand object security, you must understand the object ownership hierarchy. The following section describes the hierarchy and Figure A-1, "Object Ownership" represents it graphically.
Oracle DMW uses Oracle LSH object types and a few additional object types created for use in Oracle DMW. The following diagram shows only primary and instance 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 shown in Figure A-1, "Object Ownership", is described in the following sections.
The DMW_DOMAIN domain is an LSH container object of type Domain. It contains all user-created objects in Oracle DMW.
DMW_DOMAIN is created within the Oracle LSH instance domain during product installation.
The DMW_UTILS domain is created during product installation inside the Oracle DMW domain.
The DMW_UTILS domain is designed to hold user-defined custom programs, functions, and reference tables for use with the programs. You can create application areas within it to organize the programs you create, using the Oracle LSH user interface or public APIs. Create programs and tables in the Oracle LSH user interface or with public APIs.
Note:
For performance reasons, Oracle recommends creating application areas instead of domains inside DMW_UTILS to organize programs and other objects.Define additional domains in the Oracle DMW domain to organize studies into categories and to hold library clinical data models and code lists for each category. By default the categories are called therapeutic areas in the user interface, 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.
A 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.
When you create a study, the system 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.
Each lifecycle area has a work area for every installed clinical data model, validation check batch, and custom listing. Transformation maps are installed in their target clinical data model's work area.
When you create a clinical data model, the system puts the model's metadata directly in the study domain. When you install the model, the system:
Creates a work area object in the current lifecycle area.
Creates a database schema for the model containing a database table for each table definition in the model definition.
When you create an input clinical data model of type File, the system 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 in the input model's work area.
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 in the study domain and installed in the relevant work area.
Transformation map programs are installed in the target model's work area.
Transformation map metadata is contained 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.
Validation check batches are created internally as model-level transformation maps, and validation checks are created as child table-level transformation maps. They in turn contain validation check details.
When you create a study, if you select the option to create a business area, the system does so. 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"
A user group assigned to an object is 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 definitions.
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 program contained in the transformation or validation check batch.
Only after installation can tables actually hold data, and transformations and validation checks read data from tables and write data to tables.
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".
|
 Copyright © 2013, 2015, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
