14 Data Extract
Oracle Clinical's Data Extract subsystem is a set of facilities to present patient and clinical study data that meets the demands of external applications related to statistical analysis and reporting. Oracle Clinical presents the data for external access through standard and custom database views created in study access accounts.
Oracle Clinical stores data in tables that are normalized and generic, in that the underlying table structure of the data is the same from one study to another.
This chapter describes the design side of these data extract features and processes. For runtime data extract tasks see the Oracle Clinical Conducting a Study manual. See the Oracle Clinical Administrator's Guide for information on regenerating and updating views. This chapter includes the following topics:
Views created before Oracle Clinical Release 3.1 ("pre-3.1 views") continue to be supported, although you cannot have pre-3.1 views in the same study as views created in Release 3.1 and later. As of Release 3.1, Oracle Clinical creates views in the View Builder.
The View Builder is a toolkit that provides the means to perform the following tasks:
-
Customize and interactively view default, DCM-based views.
-
Create Questions derived at the time of data extract.
-
Customize how views are created by, for example:
-
controlling the view mode
-
controlling the view granted for view access
-
controlling the default attributes of the view's Questions.
-
-
Create Cross-study views by program, project, or user-defined study set.
View data can be transferred into database tables for better system performance during user analysis, reporting, and browsing operations.
14.1 The View Builder's Structure
The diagram illustrates the relationship of the structural elements in the View Builder. These elements are described in this section.
Description of the illustration ''vb_structure.gif''
When you invoke Maintain Study View Definitions (from the Definition menu, select Data Extract View Builder) or Maintain Library View Definitions (from the Glib menu, select Data Extract View Builder), the components of a view definition are displayed.
The following components make up a view definition, which itself is the basic building block for data extract view creation in Oracle Clinical:
| Component | Function |
|---|---|
| Component | Function |
| View Template | Defines the columns for the view based on simple and complex Questions. |
| Key Template | Defines the set of keys and key expressions for the view based on default and user-defined extract macros. |
| Mapping | Maps DCM Questions to View Template Questions. |
| Having Clause | Restricts data retrieved by response values to Questions (optional). |
| Where Clause | Restricts data retrieved by key values (optional). |
A view definition always exists in the context of a mapping. In the context of a study set or a single study, you can query view definitions in an ad hoc fashion; they may or may not become an Oracle view in a study (set) access account.
Actions bearing on a view definition occur in the Global Library, and in the subsystems Definition and Conduct. For tasks in Conduct and data extract context in Conduct, see the manual Oracle Clinical Conducting a Study.
14.1.1 Global Library
In the Global Library, you define View Templates, Key Templates, and view definitions. You can also extend column definition, by means of extract macros, to include multivariate expression definition. For the purposes of data extract only, you can also extend Questions or create complex Questions.
14.1.1.1 About View Templates
View templates define the columns for the view, based on response values and expressions. They are defined, created, and stored by the Global Librarian.
View templates are mapped to the source DCM Question. For example, a View Template might specify a view consisting of three numeric columns whose sources are inputs N1 and N2. The columns correspond to N1, N2, and (N1+N2)/2. This format can then be used in multiple actual view definitions by mapping N1 and N2 to source DCM Questions in a particular study.
For how to customize View Templates by means of complex Questions, see "About Complex and Simple Questions".
View templates can exist at the Global Library level as standard structures defining the Question columns for standard views. These View Templates can be copied as part of a local study view definition, or they can be incorporated by reference as part of a definition repeated over many studies.
View templates can be created in several ways:
-
You can create default View Templates as part of default view definitions for a DCM. These templates, if unmodified, produce views just like the standard views of earlier versions of Oracle Clinical.
-
You can produce View Templates as if you were using the Oracle Clinical V3.0 Query Builder—that is, by selecting particular DCM Questions to include in a view definition and its default format.
-
You can create View Templates explicitly by specifying the column structure and characteristics. For standard views you can also determine column ordering, which applies to all views created from the same template and is consistent for all access accounts.
Note:
Avoid using "CON" as a View Template name. If you are using Windows 2000 or NT, the SAS view creation file con.sas will fail as "CON" is a reserved device name.
14.1.1.2 About Key Templates
Key templates define the keys and key expressions for the view, based on default and user-defined key values obtained from default or user-defined extract macros.
Key columns are the fixed part of the view definition structure and enable the non-aggregation of data as they set the key variables for a view. Key templates also enable you to specify the SAS names and labels of the key columns, and to order the view data. "Customizing Data Extract" summarizes how aggregation of data works, and the keys needed for non-aggregation of data.
Before Oracle Clinical 3.1.1, a Qualifying Question defined at DCM level was extracted as an additional column/variable. As of 3.1.1, a Key Template had the columns Qualifying Value and Qualifying Question. The Qualifying Question key includes the Qualifying Question ID from the DCM; the Qualifying Value key, regardless of whether the underlying DCM is qualified, includes the RDCM Qualifying Value column in a view with the generic column name of Qualifying Value and the attributes associated with the Qualifying Value key extract macro.
The data extract key DYNA_QUAL_VALUE allows dynamic Qualifying Questions to be part of views created with the View Builder. Behavior is as follows:
-
In regular data extract views that reference just one qualified DCM, the new key includes a column containing the RDCM qualifying value. The name of the column, as well as Oracle and SAS attributes, comes from the Qualifying Question. When the DCM is not qualified or the view references more than one DCM, the column is omitted. This scenario is the same as for pre-V3.1.1 views.
-
In Union views, both In-study and Cross-study, there are three possible outcomes.
-
If at least one of the component views is qualified, a column is included according to regular view rules.
-
If all the qualified DCMs are based on the same Qualifying Question, a column and attributes are created, according to regular view rules.
-
If two or more component views are based on different Qualifying Questions, the view is omitted, with an error message.
-
14.1.1.3 About Extract Macros
With extract macros, you customize the way your data is formatted in Oracle and SAS views, because extract macros provide the keys for Key Templates. Default extract macros exist for standard Key Templates—the default templates for view definitions.
You specify an extract macro—SQL or SAS—when defining Questions or Question Group Questions so that the macro is incorporated in SQL and SAS view creation text, respectively.
Before you can make an extract macro active, which is required before it can reference Questions, you must assign a name for it, make sure the Question and the extract macro share the same Question data type, and validate the SQL code.
You can modify views by defining derived columns with any parsable SQL expression that references 0 or more Questions. Embedded function calls may also be used.
14.1.1.4 About Complex and Simple Questions
Complex Questions are not DCM Questions; they are created for the purpose of data extract. They are not part of default definition, so they have to be defined in the Global Library. Once a complex Question is defined, it references other Questions as well as the extract macro that combines them into a single value.
When you write an extract macro with more than one variable, you accommodate that extract macro by creating a complex Question, which can be broken down into simple Questions, each of which can then be associated with one of the variables.
Simple Questions are Questions that are collected and that are called "simple" to distinguish them from complex Questions.
So for a complex Question with extract macro parameters defined for it as N1 and N2, you can map these two parameters to two simple Questions, Q1 and Q2, respectively. You then map these simple Library (or study) Questions to DCM Questions.
In practice, this process of definition and mapping is largely automated in default cases, but you can control the structure if you want to customize the views.
14.1.1.4.1 Maintaining Question Details
Complex Questions combine information collected by multiple simple Questions. The Details button in the Questions window accesses the form used to associate the variables of the complex Question with the simple Question that collects the data. COMPLEX Questions are associated with extract macros and QUESTION_SET Questions are associated with Question Sets.
To access the Question Details window, from the Glib menu, select Questions, choose Prov Questions, and click the Details button. The Details button activates only for complex Questions, therefore only for Questions of type COMPLEX or QUESTION_SET. The extract macros and Question Sets must be defined before you try to associate them with the Question. Use this window to associate extract macros and Question Sets with provisional Questions.
14.1.1.5 About Extended Question Attributes
You can extend simple Questions for the purpose of data extract, so that you can choose among attributes when selecting a Question for use in data extract and map the selected attributes to extract macros; these extract macros can then be used to customize views.
Extended attributes, as attributes of the RESPONSES table, allow you to extend data extracted from the value text and validation status as well as from several additional attributes. When you create a simple Question—that is, not a complex Question—in the Global Library, default extended attributes are created. See the Oracle Clinical Administrator's Guide for a description of these extended attributes.
The following diagram shows the relationship between Question attributes, complex Questions, and extract macros.
14.1.1.5.1 Maintaining Extended Attributes
From the Glib menu, select Questions, then choose Prov Questions, and click the Extended Attributes button. Oracle Clinical provides additional attributes that can be associated with Questions to increase the information provided in extract views. Use this window to associate the desired extended attributes with a specific provisional Question.
Description of the illustration ''extending_questions.gif''
14.1.1.5.2 Discrete Value Group Translation for Applicable Variables
You can determine on a view- by-view, Question-by-Question basis whether to expand the view definition to include discrete value group sequence numbers and/or long values, in addition to the data values for DVG Questions.
14.1.1.5.3 Specifying Explicit Access to Other Response Information
You can specify whether to base a column on various attributes of a response. These include handling of exception values, access to data comments, and access to validation status.
In the Data Extract Installation Configuration window (from the Admin menu, select DX Installation Configuration), you can decide whether to include Question attributes when defining view definitions from DCMs.
When you create new Questions, attributes with LONG_VALUE set to Y in the reference codelist DX_EXTENDED_ATTRIBUTES are created automatically. Two extended attributes set by default are VALIDATION_STATUS and DVG_SHORT_VALUE.
To change the extended attributes for existing Questions, you need to run a SQL script. For instructions, see the Oracle Clinical Administrator's Guide.
14.1.1.6 Example of Associating Extract Macros to Questions
The process of associating extract macros to Questions breaks down into four steps:
14.1.1.6.1 Step 1
Create the following two expressions, where q1, q2, q3, and q4 are Questions, while fDate is a database procedure:
| Expression Description | Expression Alias |
|---|---|
| Simple Numeric Expression | 0.3333 * (q1 + q2 + q3) |
| Database Procedure | fDate(q4) |
14.1.1.6.2 Step 2
For the simple numeric expression, create an extract macro named BPFUNCTION, where the corresponding SQL macro would be:
0.333 * (<VALUE_TEXT>N1 + <VALUE_TEXT>N2 + <VALUE_TEXT>N3)
For the database procedure, create an extract macro named PROCESSED_DATE, where the corresponding SQL macro would be:
fdate (<VALUE_TEXT>D1)
14.1.1.6.3 Step 3
In the Questions form of the Global Library, create a Question named BPCOMPLEX, with a Question type of COMPLEX, a datatype of NUMBER, and an associated extract macro named BPFUNCTION.
To continue working with the BPCOMPLEX Question and the BPFUNCTION extract macro, click the Details button, which shows you a window where the SQL macro for this extract macro is displayed, and where you enter the name of a simple Question against each variable listed (N1, N2, and N3 in this case). The simple Questions are available from a list of values. This procedure has broken down the complex Question into three simple Questions.
For the database procedure, create a Question, COLLECTION_DATE, in the Global Library Questions form, with a Question type of anything but COMPLEX, a data type DATE, and an associated extract macro, PROCESSED_DATE.
14.1.2 Definition
As part of Definition, you can create a study set, which includes several studies for either a project, a program, or a selection of individual studies. Creating a study set allows you to create Union views across studies.
You can define view definitions and View Templates in Definition, and in Glib. View definitions can reference both study- and Global Library-level View Templates.
Although indexing may also be thought of as a Definition task, the menu entry is under Conduct, so the description of data extract indexing is in Oracle Clinical Conducting a Study.
14.2 A Sample Workflow
Figure 14-1 shows a sample workflow for data extract activities.
Figure 14-1 Simple Workflow for Data Extract
Description of ''Figure 14-1 Simple Workflow for Data Extract''
Before starting data extract you need to identify what data to access—a decision depending on the design of the clinical study, on possible problems encountered during study conduct, or on the expected analysis of the data extracted. These factors may also determine whether you accept system defaults and omit the optional steps in the data extract process.
For information on the DX Installation Configuration settings, see the Oracle Clinical Administrator's Guide.
Reviewing the Default View Definition
If the View Builder is enabled for your study, when you make a DCM active in Definition and save it, you can choose to say Yes when the Alert box asks if you want to create a default view definition. Accepting a default view definition results in a further Alert box message that a view definition has been created.
If you want to create a default view definition for an existing active DCM, select Default View Def from the Special menu. You can also update a view definition from the Special menu. View definitions created this way can then be submitted to PSUB so that you can obtain your views.
14.3 Customizing Data Extract
This section describes the various ways you can customize your data extract process. Many customizations begin with copying, so a section on that feature starts off this section.
14.3.1 Copying
As in other subsystems in Oracle Clinical, copying can play a large part in data extract tasks. The special points in copying for data extract are that:
-
You can copy active objects that are in the user domain searchlist. These are displayed by the List function. For Key Templates, the Special menu does not allow copying if you have your cursor on a provisional object.
-
You can add the domain of the object you want to copy to your user domain searchlist, if you need to.
14.3.2 Create Extract Macros
You can create SQL and SAS macros in the Global Library, and then associate them with complex Questions.
14.3.2.1 Simplified Extract Macro Workflow
A typical workflow might follow this scenario:
-
A programmer writes an extract macro, which may be multi-variable.
-
A Global Librarian creates a complex Question and references each of its component Questions to a variable of the extract macro created in step 1.
-
A study data coordinator adds the complex Question referenced in step 2 to the View Template of a view definition, and then maps each Question in the complex Question to a DCM Question.
From the Glib menu, select Data Extract View Builder to access the Maintain Extract Macros form. In this form, you can:
-
Activate or delete a provisional extract macro.
-
Retire an active extract macro.
-
Create and validate the SQL of an extract macro.
Before creating a macro, you should know about the macro's structure. The following sections describe this structure.
14.3.2.2 Attributes of a SQL Macro
A SQL extract macro is valid SQL text containing an embedded reference to variables. The variables might be patient data collected in the response table, such as Value Text, Exception Text, Data Comment Text, Data Change Reason Type Code, Audit Comment Text, Discrepancy Indicator, or Validation Status.
Keys may also be referenced in the SQL macro text.
For all response value text variables, you must also specify the Oracle Clinical data type—CHAR, NUMBER, DATE, or TIME.
The SQL macro may reference database functions. If you provide the SQL definition, it is validated for correct syntax.
14.3.2.3 Example
The kind of problem that would lend itself to solution by a SQL macro might be:
Problem and Goal
The response table contains the following four pairs of values and exception text for the Questions LPARM and LVALUE, where LPARM is a character parameter and LVALUE its numeric component.
| LVALUE | |||
|---|---|---|---|
| Item | LPARM | Value | Exception Text |
| 1 | HCT | 89 | |
| 2 | HCT | 92 | |
| 3 | 90 | ||
| 4 | HCT | +++ | |
The object is to retrieve the lab data in one column that contains the value text when the value is valid, and the exception text when the value is invalid.
The resulting data should look like this:
| Item | Value |
|---|---|
| 1 | HCT:89 |
| 2 | HCT:92 |
| 3 | 90 |
| 4 | HCT:+++ |
Solution
To achieve this display, define a SQL extract macro with the following variables:
c1 for the first character variable
n2 for the second numeric variable
The SQL macro HTPARM would be:
<VALUE_TEXT>C1||':'||NVL(TO_CHAR(<VALUE_TEXT>N2),<EXCEPTION_VALUE_TEXT>N2)
Another alternative is:
<VALUE_TEXT>C1||<FULL_VALUE_TEXT>N2
To create a complex Question with this extract macro in the Global Library:
-
Call the Question HTCPARM and define it as complex with a data type of CHARACTER.
-
Select the extract macro HTPARM and reference the Questions LPARM and LVALUE.
-
Map the Question LPARM to the variable C1 and the Question LVALUE to the variable N2.
To define a view with the complex Question HTCPARM:
-
Include the Question, HTCPARM, as an output column in a View Template.
-
Map the Question LPARM to a DCM_QUESTION for LPARM, and the Question LVALUE to a DCM_QUESTION for LVALUE.
14.3.2.4 Macro Data Type
The SQL macro itself has an Oracle data type, which is derived at compilation. (The Compilation field has a value of N when the SQL has not been validated, and a value of Y when the SQL has been validated.) When a SAS macro is defined as well as a SQL macro, the reference variable is always the same type in both cases (i.e., DATE, CHAR, DATETIME, or NUMBER).
14.3.2.5 Some SQL*Plus Functions
The following table lists some SQL*Plus functions you can use with extract macros.
Table 14-1 Extract Macro SQL*Plus Functions
| Macro Name | Question Data Type | SQL Macro, Description | |
|---|---|---|---|
|
LOWER |
CHAR |
SQL macro: |
LOWER (\0) |
|
Description: |
Lowercase. |
||
|
INITCAP |
CHAR |
SQL macro: |
INITCAP (\0) |
|
Description: |
First letter in each word in uppercase; all else in lowercase. |
||
|
LPAD_1_BLANK |
CHAR |
SQL macro: |
LPAD (\0, LENGTH(\0)+1) |
|
Description: |
Left-pad with one blank. |
||
|
LENGTH |
CHAR |
SQL macro: |
SUBSTR(TO_CHAR(LENGTH(\0)), 1, LENGTH(\0)) |
|
Description: |
Length of input value. |
||
|
PREFIX |
CHAR |
SQL macro: |
'x'||\0 |
|
Description: |
Prefix the character 'x'. |
||
|
SUFFIX |
CHAR |
SQL macro: |
\0||'x' |
|
Description: |
Suffix the character 'x'. |
||
|
ABS |
NUMBER |
SQL macro: |
ABS(TO_NUMBER(\0)) |
|
Description: |
Absolute value. |
||
|
CEIL |
NUMBER |
SQL macro: |
CEIL(TO_NUMBER(\0)) |
|
Description: |
Smallest integer greater than or equal to input value. |
||
|
FLOOR |
NUMBER |
SQL macro: |
FLOOR(TO_NUMBER(\0)) |
|
Description: |
Largest integer equal to or less than input value. |
||
|
EXP |
NUMBER |
SQL macro: |
EXP(TO_NUMBER(\0)) |
|
Description: |
E (=2.71828183) raised to the power of the input value. |
||
|
LN |
NUMBER |
SQL macro: |
LN(TO_NUMBER(\0)) |
|
Description: |
Natural logarithm. |
||
|
LOG10 |
NUMBER |
SQL macro: |
LOG(10, TO_NUMBER(\0)) |
|
Description: |
Logarithm, base 10. |
||
|
SIGN |
NUMBER |
SQL macro: |
SIGN(TO_NUMBER(\0)) |
|
Description: |
Sign: -1 if input value is less than 0; 0 if input value is 0; 1 if input value is greater than 0. |
||
|
SQRT |
NUMBER |
SQL macro: |
SQRT (TO_NUMBER(\0)) |
|
Description: |
Square root. |
||
|
TRUNC_0 |
NUMBER |
SQL macro: |
TRUNC(TO_NUMBER(\0)) |
|
Description: |
Truncate to 0 decimal places. |
||
14.3.2.6 Some Examples of SQL and SAS Macros
The following table lists and compares SQL macros, SAS macros, and SAS formats.
| Macro Name | Question Data Type | SQL Macro, SAS Macro, SAS Format, Description | |
|---|---|---|---|
| COMP_DT9 | DATE | SQL macro: | TO_DATE (DECODE(LENGTH(\0), 6, \0||'01', 4, \0||'0701', 8, \0, NULL), 'YYYYMMDD' ) |
| SAS format: | DATE9. | ||
| Description: | Complete a partial date and display, using DD-MON-YY format. When no day is specified, the first day of the month is assumed. When no month is specified, July 1 is assumed. | ||
| DT9 | DATE | SQL macro: | SUBSTR (TO_CHAR (DECODE (LENGTH (\0), 8, \0, NULL)), 1, 8) |
| SAS macro: | INPUT (SUBSTR (\0, 1, 8), YYMMDD8.) | ||
| SAS format: | DATE9. | ||
| Description: | SAS date format DATE9. | ||
| COMP_TM | TIME | SQL macro: | SUBSTR (DECODE (SUBSTR (\0, 1, 2), NULL, '00', SUBSTR(\0,1,2)) ||DECODE (SUBSTR (\0, 3, 2), NULL, '00', SUBSTR (\0, 3, 2)) ||DECODE (SUBSTR (\0, 5, 2), NULL, '00', SUBSTR (\0, 5, 2)), 1, 6) |
| SAS format: | $6. | ||
| Description: | Complete a partial time and display as HHMMSS. | ||
| TM8 | TIME | SQL macro: | SUBSTR (TO_CHAR (DECODE (LENGTH (\0), 4, \0, NULL)), 1, 4) |
| SAS macro: | HMS (INPUT (SUBSTR (\0, 1, 2), 2.), INPUT (SUBSTR (\0, 3, 2), 2.), 0) | ||
| SAS format: | TIME8. | ||
| Description: | SAS time format TIME8. | ||
14.3.2.7 Some Hints for SAS Users
In current SAS views produced by Oracle Clinical, Questions of data type TIME and DATE are viewed respectively as SAS variables of CHAR$6 (character of length 6) and CHAR$8 (character of length 8). In the SAS view, the TIME data is seen as HHMMSS, and the DATE data as YYYYMMDD.
SAS programmers and statisticians may prefer to have TIME and DATE Questions as true SAS TIME and DATE variables. Moreover, it may not be necessary, or possible, to complete a partial date or time according to a standardized algorithm. For example, data may require a sensitivity analysis with dates and times completed according to a variety of algorithms. The following approach supports these objectives.
-
Define two DATE Questions: one entered, called COLLD, and one derived, called COLLDT.
The derived Question has an extract macro, DT9, which formats the data as a SAS date variable, provided the date is already complete.
-
Enter data into COLLD, with the derivation expression for the derived Question, COLLDT, being the value of COLLD.
In the SAS extract view, COLLD is a CHAR8 variable that contains the entered data according to a YYYYMMDD character display, and COLLDT is a true SAS DATE variable. This translates into:
-
If the value of COLLD is a complete date, then COLLDT contains the corresponding data.
-
If the value of COLLD is a partial date, then the value of COLLDT is missing.
14.3.2.8 Defining Extract Macros Using the Maintain Extract Macros Window
Fields in the Maintain Extract Macros window are mandatory, unless stated otherwise.
If you want to edit an active extract macro, you must have selected the preference Enable Edit of Active Extract Macros?, which is displayed in the Data Extract Installation Configuration window (from the Admin menu, select DX Installation Configuration).
To define an extract macro:
-
Navigate to Glib, then Data Extract View Builder. The Maintain Extract Macros window opens.
-
Enter a name for the macro. This name must be unique in the Global Library.
-
Choose a status for the macro: Active (A), Provisional (P), or Retired (R). You can activate or delete Provisional macros, and retire Active macros.
-
Choose the expected data type of a response to a Question. Acceptable values are CHAR, DATE, NUMBER, or TIME.
-
Enter the SQL statement for this macro. Double-click in the SQL Macro field or click its LOV button to invoke the Editor.
SQL text can contain an embedded reference to database functions, keys, or variables. The variables may be patient data collected in the Response table--such as, Value Text, Exception Text, Data Comment Text, Data Change Reason Code, Audit Comment Text, Discrepancy Indicator, or Validation Status.You can choose components of a macro using the list of values for the SQL Macro item.
-
Enter a description of this extract macro. Entering descriptions for Global Library objects can be helpful to other users who want to use the objects you define. Use the following descriptions to complete the fields:
In the Oracle Name field, choose the Oracle name of the key columns in the view.
Length: Lists the maximum number of allowable characters for a response to the Question. This value cannot exceed: 200 for type CHAR; 40 for type NUMBER; 8 for type DATE; and 6 for type TIME. For a Question of type NUMBER, do not count a negative sign or a decimal point toward the length.
SAS Macro: Unvalidated SAS text referenced during generation of the SAS Pass Through view definition. For example, to apply the SAS function DATE1 to a Question value text, enter the expression DATE1(1) in the SAS Macro field. When associated with the Question ONSDATE, for example, the following appears in the.SAS file: DATE1 (ONSDATE).
A SAS macro references only one variable: the Oracle Extract View column. If you define a SAS macro as well as a SQL macro, the reference variable is always the same in both cases.
SAS Name: Mandatory when the macro references no response variables. Based on the Oracle name of the extract macro. Defined when you create a Question. The name cannot end with a number, and can be comprised only of uppercase letters, numbers, underscores, or hashes. It can be up to 8 characters in length, unless the Question has multiple occurrences in a single Question Group, in which case it can be only up to 7 characters.
SAS Label: Mandatory when the macro references no response variables. Free form text supplied to the SAS Label field in the SAS views and SAS datasets to describe the purpose of a Question.
SAS Format: Information for the format portion of the SAS Pass Through definition file.
A list of values is available, with values from the installation reference codelist SAS_FORMATS. You can use this codelist to store all the formats you might want to use for the creation of extract macros. Oracle formats are listed in the list of values with their equivalent SAS formats.
-
Save. Oracle Clinical validates the extract macro and adds it to the Global Library.
14.3.2.9 Changing a Macro Definition
When you change the status of a macro, either by activating or retiring it, you can enter more information about this change in the Status Comment field.
14.3.2.10 Listing Available Functions in the Database
Clicking the List Function button launches the Function Specification window, from which you can examine details about the functions to which you have access. These details include the function name, its owner, and the function's attribute names and data types. Use the Down and Up arrow keys to see different functions.
14.3.3 Creating Key Templates
This section includes the following topics:
14.3.3.1 Maintain/Query Key Templates
You can select which keys to include in extract views by defining Key Templates. You define a Key Template by picking from previously defined key extract macros (from the Glib menu, select Data Extract View Builder, then Extract Macros) and then specifying how to name and display the keys in the template. You can control both the Oracle and SAS names for a column.
A predefined set of key extract macros is installed with Oracle Clinical. These extract macros form the basis of the standard Key Template provided with the application. You can define alternative Key Templates and instruct Oracle Clinical to use them by changing the name of the default Key Template in the Data Extract Installation Configuration form (from the Admin menu, select DX Installation Configuration) or by supplying a study-specific Key Template in the Clinical Study States form (from the Conduct menu, select Security, then choose Clinical Study States). If you want to be able to choose alternative Key Templates for different view definitions within a study, you can select Enable Selection of Nonaggregate, Nondefault Key Template? in the Data Extract Installation Configuration form.
14.3.3.2 Customize/Query Key Template Columns
By clicking the Key Columns button in the Key Template window, you can select the keys to include in the Key Template. You can use the Up and Down buttons to change the order in which the key columns are placed in views created with the Key Template. The keys are ordered in the view in the same order as they are displayed in the window.
14.3.3.3 Order Specification for Data Viewing
You can specify how to order data that is queried in the View Data window in the View Definitions form. Click the Order By button in the View Templates window. In the Data Order By Columns window, select from key columns that have already been added to the view, and specify the order in which the key values are used to order the query. The criteria you enter for ordering in this window have no effect on data extract views: they affect the order in which data is returned in the View Data window.
To navigate with the arrows and buttons on the Data Order By Columns window, follow these instructions:
Table 14-2 Buttons and Actions in the Data Order By Columns window
| Clicking... | Moves the Selected Column... |
|---|---|
|
Up |
Up one row |
|
Down |
Down one row |
|
-> |
From Column Picklist to Selected Order By Data Columns |
|
<- |
From Selected Order By Data Columns to Column Picklist |
Clicking the <<< button moves all columns in Selected Order By Data Columns to Column Picklist.
14.3.3.4 Aggregation
If you have added key columns to the Key Template, the Aggregate? box is automatically selected when the keys do not completely define a unique repeat of data. The set of keys that uniquely define one row of data in a view are as follows. If you choose the key DOCNUM, it includes PATIENT_ID and VISIT; if you choose the key ACTUAL_EVENT, it includes VISIT and SUBEVENT.
| PATIENT_ID | VISIT | SUBEVENT | QUALIFYING_VALUE | REPEAT_SEQUENCE_NUMBER |
|---|---|---|---|---|
| DOCNUM | ||||
| ACTUAL_EVENT | ||||
Only nonaggregate Key Template can be used for view definitions intended to create data extract views.
Note:
If you are using Fast Views and an aggregate key template, you must enter the text "AGGREGATE" somewhere in the template's Status Comment field. Fast View structure is incompatible with aggregate key templates. If you enter the word "AGGREGATE" in the Status Comment, the system recognizes it and creates a regular view structure. See the Oracle Clinical Administrator's Guide chapter on Data Extract configuration for information on Fast Views.14.3.4 Create Questions and View Templates
Creating Questions is of itself not a data extract task. However, customizing data extract can include creating complex Questions and mapping their child Questions to extract macro variables, or extending attributes on simple Questions and mapping selected attributes to extract macros. For more information, see these sections in this chapter: "About Extended Question Attributes", "About Extract Macros", "About Complex and Simple Questions", and "Example of Associating Extract Macros to Questions".
14.3.4.1 Associating Questions with Views
Navigate to the View Definitions window (from the Definition menu, select Data Extract View Builder, then choose View Definitions), then follow these steps:
-
Query for the view definition that you want to work on.
-
Select its template and either double-click on it in the View Template field or click the Details button. Oracle Clinical opens the View Template Details window, which lists Question names associated with the selected View Template, the response attributes for the Question, and whether they are complex.
-
The upper part of the View Template Details window serves as a tool with which you can restrict the possible Questions that you are going to map to the View Template.
In this upper part of the window, choose either the Pick by Question radio button or the Pick by DCM Question radio button. The Question referred to in Pick by Question is any library Question, simple or complex.
-
Go back to the View Definitions window to map the Questions of the complex Question to DCM Questions. Select a view definition and click the Map button. The Maintain View Question Mappings window appears.
-
The upper part of the View Mappings window serves as a tool with which you can restrict the possible DCMs and DCM Question Groups you are going to map to the DCMs and DCM Question Groups that are accessible to the study. If you select the radio button DCM Filter ON, a table is displayed with the fields where you can enter your restrictions. Selecting the radio button Clear Filter erases what you enter.
14.3.5 Maintaining View Definitions
The Maintain View Definitions window enables you to:
-
Review and modify an existing view definition, created by defaulting or copying.
-
Create a new view definition, using an existing study or library View Template.
-
Create a new view definition and its View Template at the same time.
-
View data from a new or existing view definition.
If you create a new view definition and reference an existing View Template, you can invoke the Map window to associate, or map, the columns that are specified in the View Template to specific DCM Questions in particular DCMs.
If you want to add columns to a view definition, or modify the specified Question attributes, you can invoke the View Template form by double-clicking on the View Template field. The View Template form behaves the same way as when you call it from the menu, with one important difference: Questions you add to the View Template by selecting from DCMs are automatically mapped to that DCM in the calling view definition.
You can create a View Template and view definition simultaneously by double-clicking an empty View Template field. Oracle Clinical automatically creates a new View Template with the same name as your current view definition and then places you in the Template Questions definitions window. As you add columns to the View Template by selecting from DCM Questions, the Questions are automatically mapped to the DCM in your view definition.
You can add Questions from either the DCM or the Global Library. You cannot add columns directly to the View Template, but when you add Questions to it, the system automatically maps the Questions to the DCM in your view definition. For example, if you add a DVG Question, Oracle Clinical automatically adds the columns for value_text or dvg_short_name for each Question. The automatic mapping of View Template Questions with the DCM Question works only if you add the Questions using the DCM Questions filter.
You can add more attribute columns by clicking the Attribute boxes for each View Template Question, provided they are defined in the Questions form (Global Library).
When you create or modify a view definition, you must also specify which pre-existing Key Template to use. You can also optionally use a previously created Where clause to restrict the view.When you batch load views, the display-only field Source File, in the View Definitions form, reflects the fully specified path of the operating system source file for the batch load.
14.3.5.1 Linking a View Definition to a DCM
Link modes govern the way that DCMs are tied to View Definitions for update purposes and how re-defaulting View Definitions affects these relationships. DEFAULT and LINKED Link Modes apply only to View Definitions that reference exactly one DCM. The link modes are:
LINKED Changes to the DCM definition will also update the view definition.
NONE Changes to the DCM will not affect or update the View Definition in any way. This mode can provide a useful method of preventing a cascade effect in a Cross-study data extract.
DEFAULT Oracle Clinical refers to the default linking behavior defined in the DX Installation Configuration window accessed from the Admin menu. In this window, if the DCM Default Views Are Linked to Source DCM as Default Condition? setting is enabled, a view definition will be linked to the DCM; if this setting is clear, no linkage will exist. You cannot choose DEFAULT as the link mode once you save a view definition with either of the other two modes.
If a view definition's link mode is either DEFAULT or LINKED, and you add a Question to the DCM, you must either update the view definition manually (from the Special menu, select Update View Definition) or perform a View Maintenance. INCREMENTAL View Maintenance detects only the changes that are made since the last maintenance and synchronizes them with the DCM definition. FULL View Maintenance recreates the study access account.
14.3.5.2 Viewing the Data for a View Definition
When you click the View Data button in the Study View Definition window, you can see the data that the selected view definition would output. The window is divided into the keys that make up the view definition you selected and the Questions that make up the View Template of that view definition.
This button works only if you have chosen a Key Template and a View Template, and if you have mapped the Questions to the View Template. You see an alert specifying the problem if you have not completed the view definition. The button will also not work if you do not have a user account for the default study access account.
You can print from this window, and you can save the SQL view for the view definition in the USER_QUERIES table, provided you have this table set up for you by the system administrator.
14.3.5.3 View Model
When you click the View Model button in the View Definition window, you can see the components of the selected view definition: the key or Question name; the optional SQL macro; the attributes of Questions; sort order (for ad hoc data retrieval only); the names of the tables from which data is extracted; and any optional Having and Where clauses.
14.3.5.4 Creating View Templates
You can create View Templates in any of the following ways:
-
Accept a default view definition by choosing Default View Def from the Special menu of the DCM form or by clicking the Create Default View Definition? button in the Alert box that comes up when you activate a DCM.
-
Associate View Templates in the View Templates form, at the Library and study levels, with DCM Questions that you select.
-
In the View Definitions window, query for the View Definition you want to work on, then select its template and either double-click on it in the View Template field or click the Details button.
In addition, to add a complex Question, you should select the Global Library Filter.
14.3.5.5 Mapping DCM Questions to View Template Questions
In the upper part of this window, when the button labeled DCM Filter ON is selected, list the DCM Question Groups that contain the Questions you want to include in the View Template. When the button labeled DCM Filter OFF is selected, which is the default, you do not see the fields to the right of the buttons and there are no restrictions on which Questions can be associated with the View Template. You can choose the Clear button to erase what you enter. The Clear button is not visible when the filter is off. The lower part of the window lists the Questions available to a View Template in the selected study, given any choices you may have made in the filter area.
14.3.5.6 Having Clauses
Having clauses restrict data. You can create these clauses from the View Definitions window in the Global Library or at the study level by pressing the heading on the column Having, which brings up the Having window. Having clauses have no status and can be changed at will.
When there is more than one argument, each argument is enclosed in parentheses. Functions available include GREATEST, LEAST, UPPER, and LOWER. The Questions made available by the List function are Questions referenced in the View Template. The expression choices are LIKE, NOT LIKE, IN, NOT IN, =, <>, >, and <. You add arguments with the AND/OR option.
An example: (RACE+BLACK) OR (GREATEST AGE>65)
14.3.5.7 Copying a View Definition
When you copy a view definition, the elements of the view definition behave differently, depending on where they were created—at a study level or at the Global Library level—and the source/target relationship.
A view definition comprises a View Template, a Key Template, and optional Having and Where clauses. Having clauses are never copied with the view definition. Key Templates are always created in the Global Library, so you copy the reference to the Key Template, not the Key Template itself. View templates and Where clauses follow these rules:
-
Copying a Library view definition into a study means that you reference the View Template and Where clause.
-
Copying a study view definition into a study or the Global Library means that you copy the View Template and the Key Template as well as the view definition.
Copying a view definition would create two view definitions with the same name in the same study; to address this naming issue, Oracle Clinical automatically gives the copied view definition the name COPY_OF_name, where name is the name of the source view definition. The system alerts you to change this name in the View Definition window, which appears with the copied view definition when you click OK in the Copy View Definition window. For a Library view definition, the name must be unique in the first domain found in the user domain searchlist or in the default domain searchlist. When the name is not unique, the system alerts you.
14.3.6 Defining Study Sets
To create a study set, from the Definition menu, select Data Extract View Builder, then Study Sets. First give the study set a name, assign it an account type, and write in a description, then use the Define button to select the studies you want to include in the set. You must use the Define button if you choose STUDY SET or if you want to limit the studies available when you choose PROGRAM or PROJECT.
The order in which you select studies to form the study set has no effect on which study access account is associated with the Cross-study Union.
The name of the Cross-study View is associated with the name of the study you were working in when you opened the Maintain Study Sets form. The study name listed in the blue banner across the top of the window disappears once you give the study set a name.
14.3.7 Creating Union Views
A Union is a vertical grouping of multiple views. You follow a similar process in creating Union views whether they are within a study (In-study Union) or in more than one study (Cross-study Union). However, studies chosen for Cross-study Union must be in a study set, and views showing data across studies must share the same type of:
-
View Template
-
Key Template
-
Study Account
Only views built with Global Library-defined View Templates can be unioned for a study set; whereas views built with either Global Library- or study-defined View Templates can be unioned within a study. You can interactively query view definitions both in the study and in the study set.
Note:
Union views cannot be represented as tables (see the "Data Extract" chapter of Oracle Clinical Conducting a Study for details on table views).14.3.7.1 Creating a Cross-study Union
To define a Cross-study union:
-
Define a View Template with Questions in the STANDARD domain of the Global Library.
-
Create a DCM that includes all of the Questions from the first instruction, for each study.
-
Copy the same view template from the Global Library for each study.
-
Create a view definition of the same name in each study, from the template you created in the first step.
-
Perform Initial Log-in and data entry for each study.
-
Run Data Extract View for each study: Navigate to Conduct, then Data Extract, and finally Data Extract Views.
-
Create a study set by navigating to Definition, then Data Extract View Builder, and finally Study Sets, first giving the study set a name, assigning an account type, and writing in a description, then using the Define button to select the studies you want to include in the set. You must use the Define button if you choose STUDY SET or if you want to limit the studies available when you choose PROGRAM or PROJECT.
The order in which you select studies to form the study set has no effect on which study access account is associated with the Cross-study Union.
-
Check the access account for the study set by navigating to Conduct, Data Extract, then Study Set Access Accounts, remembering to make any TABLE types of view into VIEW types.
See the "Data Extract" chapter of Oracle Clinical Conducting a Study for details on table views.
-
Define a cross-study view by navigating to Definition, then DX View Builder, and finally Cross Study Union.
-
Create the cross-study union view by navigating to Conduct, Data Extract, then Data Extract View for Study Sets, and submitting it.
-
Query the view in SQL*Plus.
Snapshot account naming becomes important for Cross-study Union views. For example, if you name a snapshot account BASEA in STUDY1 and you want to compare the same period that the snapshot covers in another study, STUDY2, then you need a snapshot account in STUDY2 with the same name as the snapshot account in STUDY1. The two snapshot accounts may have different batch validation timestamps, but because of the naming, in a Cross-study Union their data will be treated as if it were processed at the same time.
14.3.7.2 Troubleshooting Cross-study Union Views
An error may arise if you do not use the same Key Template and Global Library View Template, and in the same domain, for the study level views and the Union view. The error follows:
Update_view_account_statisitics invoked Acct: XXXVB$CURRENT, View_id: 10101, status: INVALID Inserting view_acct_stat Update_view_account_statistics returned success Invoking get_union_view_text with 10101,2001,82201 ERR level 1 get_union_view_text Error: unable to get union text ERR level 1 create_cross_study_views Error when calling update_view_acct_statistics ERR level 1 maintain_account Error when calling create_cross_study_views Connect prvl user. Connected to ORACLE as user /. Connected to ORACLE as user RXC_MAA. RXCDXBVB EXIT WITH FAILURE
This error will appear in the .out file that Oracle Clinical creates when you run the Cross-study Unions Maintenance batch job. This batch job is available under the Conduct menu, by selecting Data Extract, and then Data Extract Views for Study Sets.
14.3.7.3 Creating In-Study Union Views
In-study Unions are vertical groupings of multiple views within a study. To create an In-study view properly, start by performing the following steps:
-
Create and activate the two DCMs. Do not create a Default View Definition for either one.
-
Create and activate a View Template for the Union view that contains all the required Questions. These Questions must exist in both source DCMs.
-
Create two or more view definitions using this newly created View Template. The data for all the view definitions using this common View Template is displayed in the in-study union view.
-
Map each view definition to the respective DCM and activate the view definitions.
-
Confirm that the Key and View Templates are the same for all the view definitions that are being unioned.
-
Log in data for both DCMs.
-
Run the Data Extract View.
When these steps are complete, create the In-study Union view by following these steps:
-
Navigate to Definition, Data Extract View Builder, and In-study Unions. If you have not selected a study during your session, Oracle Clinical prompts you to choose one now.
The Maintain In-study Unions window opens.
-
Enter a name and description for this Union view.
Create an In-study Union by navigating to Definition, Data Extract View Builder, In-study Unions, and giving the Union a name and description.
-
Select a View Template that was used in the underlying view definitions of the Union view.
-
Select a Key Template that is nonaggregate and was used in the underlying view definitions of the Union view.
-
In the Role Access field, choose RXCLIN_MOD or RXCLIN_READ, which determines your role access.
-
Save.
-
Generate the data extract views.
You may want to run the Data Extract View Maintenance batch job at this point. See "Troubleshooting Cross-study Union Views".
14.3.7.4 Example of Union Views
A Global Library View Template, AE, has the columns AE, STARDT, STOPDT, and SEVERITY. Two studies, OCLINI and OCLIN2, share the View Template AE and the Key Template STANDARD.
In tabular form, these statements can be represented in more detail, as follows:
| Study | DCM | View | View Template | Key Template |
|---|---|---|---|---|
| OCLIN1 | SIGNSYMP | SIGNSYMP | AE | STANDARD |
| OCLIN1 | AE | AE_OCLIN1 | AE | STANDARD |
| OCLIN2 | AE | AE_OCLIN2 | AE | STANDARD |
In-study Union for study OCLIN1, from a Union of views AE_OCLIN1 and SIGNSYMP is ALL_AES.
Cross-study Union of views AE_OCLIN1 and SIGNSYMP from study OCLIN1 and the view AE_OCLIN2 from OCLIN2 is ALL_OCLIN_AES.
14.3.8 Creating Where Clauses
Where clauses are optional methods by which you can restrict the data retrieved from DCMs/RDCMs by key values--that is, the key columns. These restrictions parallel those imposed by Having clauses, which allow you to restrict the data retrieved by response values to Questions.
Where clauses can be created at the study level or in the Global Library by navigating to either:
-
Glib, then Data Extract View Builder, then Where Clauses
-
Definition, then Data Extract View Builder, then Where Clauses
They are created with a status of Provisional and can be tested in a view definition with that status. In the Where clause window you can choose available key values via the List function.
The list of values for expressions includes LIKE, NOT LIKE, IN, NOT IN, =, <>, >, and <. In the Value field, you can enter an appropriate value or you can select a value from the list of values. If you have more than one argument, enclose each argument in parentheses. You add arguments by means of AND/OR.
An example of a Where clause:
(RDCM.PATIENT=5) AND (RDCM.VISIT_NUMBER=1)
You can modify and delete Where clauses, and you can change their status from Provisional (P) to Active (A) and from Active to Provisional or Retired (R).
14.3.9 Adding DISTINCT terms
You can generate the extra "distinct" term in each column in the extract views. This change can decrease the performance of the views, so you should only use this approach if you experience the "sort key too long" error and you cannot increase your database blocksize.
To adjust the setting, navigate to Definition, then Data Extract View Builder, and finally View Definitions and include the text DISTINCT in the Status Comment for each view that requires a distinct clause. For instance, change max(decode... to max(distinct decode…. When you have made your changes, rebuild existing extract views in FULL mode.
14.3.10 About Joining Fast Extract Views
When you join fast extract views to each other, it is important to include an optimizer hint specifying a hash join. Joins without optimizer hints may be up to ten times slower than joins with optimizer hints, and may even be slower than for joins between "slow" views. The following example shows a join between PHYSICAL EXAM and VITAL SIGNS, based on a common patient visit:
select /*+ ordered use_hash(v) no_merge(v) */ pe.pt, pe.actevent, pe.repeatsn, pe.exam, pe.result, v.blood_pressure_syst from oraclin$current.pepe pe, oraclin$current.vit v where v.pt=pe.pt and v.actevent=pe.actevent order by 1,2,3;
14.3.11 General Example
The goal of this example is to perform the following tasks:
-
create a patient-level view.
-
create one row per patient, with enrollment date and sex from DEMOGRAPHICS, and termination date from the Termination form.
The challenge is to create an appropriate Key Template. Oracle Clinical requires that a Key Template include the following columns to be considered nonaggregate:
Patient
Qualifying Value
Visit Number
Subevent Number
Repeat Number
An aggregate key can be used interactively when you click the View Data button in the View Definitions window, but not for a permanent view. Therefore, a Key Template to be used for a permanent view needs to include all these columns.
However, if a Key Template were created with these required columns, it is not possible to obtain one row per patient, since the DEMOGRAPHICS DCM and the TERMINATION DCM have different visit numbers.
Solution
Create an extract macro that uses each of these key columns, but collapses them to the same value. The extract macro is then added to the Key Template, which means that the extract macro contains the required columns and the Aggregate flag is therefore turned off. Now the Key Template can be used.
The steps might be as follows:
-
Create the following CHAR extract macro named PATIENT_SUMMARY:
decode(<QUALIFYING_VALUE>,'?',null,null)|| decode(<REPEATSN>+ <VISIT_NUMBER>+ <SUBEVENT_NUMBER>,0,'N/A','N/A')
This always collapses to a value of N/A.
-
Create the Key Template to include PATIENT and PATIENT_SUMMARY, and make it active.
-
Create a View Template with the required Questions (ENROL_DATE, SEX, and TERM_DATE).
-
Create a view definition that links to this View Template and uses the newly created Key Template.
-
Map the Questions to the appropriate DCM Questions.
-
Make the view definition active.
-
Generate the extract views.
If there is more than one value for a DCM Question, the View Builder applies a MAX function. Therefore, if the view also needs to include minimum dosing date and maximum dosing date from a dosing DCM, there is no way to do this entirely within the View Builder (since there is no way to get the minimum dosing date). You need to create minimum dosing date and maximum dosing date as derived Questions in the DEMOGRAPHICS DCM, populate them in an appropriate Derivation Procedure, then include the derived Questions in the View Template.