1. Study Setup Guide
  2. Advanced topics
  3. Required syntax for metadata files

Required syntax for metadata files

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. This is the only automatic way you can create tables with all constraints and blinding attribute values.

Tip:

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

Note:

DMW supports a limited number of columns in clinical data model tables. The limit varies depending on whether the model is an inForm input model and on how many columns in a table are blinded.

For file-based input data models and target models:

  • 260 if all columns are blinded

  • 370 if the table is not blinded, has only row blinding, or is completely blinded

For InForm data models:

  • 339 regardless of how many columns are blinded

The limit is due to the system's use of internal columns and the Oracle Database 11 limit of 1000 columns per table. Although it is possible to create tables with more columns, having too many columns may cause issues while loading InForm data, loading text files, transforming data, and displaying data in the Listings pages.

Delimiter: (Optional) Must begin lsh_delimiter= . If you do not specify a delimiter, the default delimiter is a comma (,).

Table: (Optional) Must begin 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 the 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. 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. See syntax below.

Comments: A row beginning with two dashes is treated as a comment.

Syntax:

--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 8-1 and Table 8-2.

Note:

If you are using a metadata file to create a table in LSH, set attributes Duplicate_PK_Support_Flag and Surrogate_Key_Flag to NO or installation will fail. They are relevant only to DMW.

Table 8-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 8-2 Column Attributes with Reference Codelist Values

Attribute Valid Values

Data Type

VARCHAR2, NUMBER, DATE

Nullable

YES, NO

SDTM Identifier

For valid values, see Use SDTM identifiers to support important functionality.

Blinding Type

TABLE, COLUMN, ROW

Masking Level

COLUMN, CELL

Example 8-1 Metadata file

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 8-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}

Parent topic: Advanced topics