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
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.

For more information, see:

• Global Library
  
• Definition
  

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.

For more information, see:

• About View Templates
  
• About Key Templates
  
• About Extract Macros
  
• About Complex and Simple Questions
  
• About Extended Question Attributes
  
• Example of Associating Extract Macros to Questions
  

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.

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.

  1. If at least one of the component views is qualified, a column is included according to regular view rules.

  2. If all the qualified DCMs are based on the same Qualifying Question, a column and attributes are created, according to regular view rules.

  3. If two or more component views are based on different Qualifying Questions, the view is omitted, with an error message.

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.

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.

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.

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.

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

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.

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.

Example of Associating Extract Macros to Questions

The process of associating extract macros to Questions breaks down into four steps:

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)

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)

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.

Step 4

Reference the complex Question BPCOMPLEX in the View Template form. Collect the simple Question COLLECTION_DATE in the DCMs form and accept the default view definition; or you can explicitly reference the Question in a custom view by mapping in the View Definition form.

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.