Setting Table Descriptor Attributes

Table Descriptors may have the following attributes:

Is Target: Select Yes to create a target Table Descriptor. Select No to create a source Table Descriptor.

Target As Dataset: (Available only in SAS Programs, and only when Is Target is set to Yes.) Select Yes if you are using a legacy SAS program that uses data statements to write to data sets. Because Program source code must write to Table Descriptors, and Table Descriptors are views, you should use Proc SQL statements to write data to tables in Oracle Life Sciences Data Hub. However, if you set this attribute to Yes, Oracle Life Sciences Data Hub adds a processing step to enable SAS data statements to write to Oracle Life Sciences Data Hub Table Descriptors. This extra processing step results in slower performance but allows you to use existing programs.

Select No if the Program's source code uses Proc SQL statements to write to Table Descriptors. This results in optimal performance.

Reveal Audit: (Available only for source Table Descriptors; when Is Target is set to No.) By default Reveal Audit is set to No and the Program, Data Mart, or Load Set can read data from the Table instance mapped to this Table Descriptor that is or was current at a particular point in time. By default it sees only current data, but it is possible to specify a data snapshot at a past point in time in the Execution Setup of the Program, Data Mart, or Load Set.

Each time a data record is changed in Oracle Life Sciences Data Hub, the system creates a new row with the updated information. The system sets the end timestamp of the old record so that it is no longer current. When a record is deleted, the system sets its end timestamp and also adds a row explicitly recording the deletion. The set of rows for a single data record constitutes its audit trail.

If you set Reveal Audit to Yes the system exposes all data in the Table instance at all points in time to the Program, Data Mart, or Load Set reading from the Table instance, and exposes the predefined audit columns CDR$CREATION_TS (creation timestamp), CDR$CREATED_BY (creator username), CDR$MODIFICATION_TS (modification timestamp), and CDR$MODIFIED_BY (modifier username).

Note:

Because SAS does not support the $ special character, the SAS Name of each of these internal column names has an underscore (_) instead of a dollar sign ($) as follows: CDR_CREATION_TS, CDR_CREATED_BY, CDR_MODIFICATION_TS, CDR_MODIFIED_BY.

You can use this functionality as follows:

• Data Marts. If you set Reveal Audit to Yes for all the Table Descriptors in a Data Mart, the resulting Data Mart output contains the complete audit trail of every record that the mapped Table instances ever contained.
• Programs. If you set Reveal Audit to Yes for all the Table Descriptors in a Program, you can write source code that reads data from all points in time and that references the audit columns.
• Load Sets. Because the source Table Descriptors of most types of Load Sets point to data files or tables outside of Oracle LSH that do not contain audit information, it does not make sense to use the Reveal Audit feature with SAS, text, or most Oracle Load Sets. However, Oracle Clinical uses a similar system for maintaining an audit trail so you may want to use Reveal Audit with some Oracle Clinical tables.

  Oracle Clinical inserts a row each time a record is modified and sets the end timestamp of the previous row for the same record. However, when a record is deleted Oracle Clinical sets its end timestamp but does not add a row explicitly recording the deletion.

• Note:

  The Execution Setup user interface cannot detect if a Table instance is mapped to a Table Descriptor whose Reveal Audit flag is set to Yes, so it is possible for a user to specify a snapshot for such a Table instance at execution time. However, during execution the system ignores any snapshots set for Table instances mapped to a source Table Descriptor whose Reveal Audit flag is set to Yes.