Customizing Data Extract

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.

Parent topic: Customizing Data Extract

Create Extract Macros

You can create SQL and SAS macros in the Global Library, and then associate them with complex Questions.

For more information, see:

Parent topic: Customizing Data Extract

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.

Parent topic: Create Extract Macros

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.

Parent topic: Create Extract Macros

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.

Item	LPARM	LVALUE 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.

Parent topic: Create Extract Macros

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

Parent topic: Create Extract Macros

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	LOWER (\0)	Lowercase.
INITCAP	CHAR	INITCAP (\0)	First letter in each word in uppercase; all else in lowercase.
LPAD_1_BLANK	CHAR	LPAD (\0, LENGTH(\0)+1)	Left-pad with one blank.
LENGTH	CHAR	SUBSTR(TO_CHAR(LENGTH(\0)), 1, LENGTH(\0))	Length of input value.
PREFIX	CHAR	'x'\|\|\0	Prefix the character 'x'.
SUFFIX	CHAR	\0\|\|'x'	Suffix the character 'x'.
ABS	NUMBER	ABS(TO_NUMBER(\0))	Absolute value.
CEIL	NUMBER	CEIL(TO_NUMBER(\0))	Smallest integer greater than or equal to input value.
FLOOR	NUMBER	FLOOR(TO_NUMBER(\0))	Largest integer equal to or less than input value.
EXP	NUMBER	EXP(TO_NUMBER(\0))	E (=2.71828183) raised to the power of the input value.
LN	NUMBER	LN(TO_NUMBER(\0))	Natural logarithm.
LOG10	NUMBER	LOG(10, TO_NUMBER(\0))	Logarithm, base 10.
SIGN	NUMBER	SIGN(TO_NUMBER(\0))	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	SQRT (TO_NUMBER(\0))	Square root.
TRUNC_0	NUMBER	TRUNC(TO_NUMBER(\0))	Truncate to 0 decimal places.

Parent topic: Create Extract Macros

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	TO_DATE (DECODE(LENGTH(\0), 6, \0\|\|'01', 4, \0\|\|'0701', 8, \0, NULL), 'YYYYMMDD' )	-	DATE9.	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	SUBSTR (TO_CHAR (DECODE (LENGTH (\0), 8, \0, NULL)), 1, 8)	INPUT (SUBSTR (\0, 1, 8), YYMMDD8.)	DATE9.	SAS date format DATE9.
COMP_TM	TIME	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)	-	$6.	Complete a partial time and display as HHMMSS.
TM8	TIME	SUBSTR (TO_CHAR (DECODE (LENGTH (\0), 4, \0, NULL)), 1, 4)	HMS (INPUT (SUBSTR (\0, 1, 2), 2.), INPUT (SUBSTR (\0, 3, 2), 2.), 0)	TIME8.	SAS time format TIME8.

Parent topic: Create Extract Macros

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.

Parent topic: Create Extract Macros

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.

Parent topic: Create Extract Macros

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.

Parent topic: Create Extract Macros

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.

Parent topic: Create Extract Macros

Creating Key Templates

In this section:

Parent topic: Customizing Data Extract

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.

Parent topic: Creating Key Templates

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.

Parent topic: Creating Key Templates

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.

Parent topic: Creating Key Templates

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.

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.

Parent topic: Creating Key Templates

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.

In this section:

Associating Questions with Views

Parent topic: Customizing Data Extract

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.

Parent topic: Create Questions and View Templates

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.

For more information, see:

Parent topic: Customizing Data Extract

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.

Parent topic: Maintaining View Definitions

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.

Parent topic: Maintaining View Definitions

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.

Parent topic: Maintaining View Definitions

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.

Parent topic: Maintaining View Definitions

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.

Parent topic: Maintaining View Definitions

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)

Parent topic: Maintaining View Definitions

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.

Parent topic: Maintaining View Definitions

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.

Parent topic: Customizing Data Extract

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

For more information, see:

Parent topic: Customizing Data Extract

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.

Parent topic: Creating Union Views

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.

Parent topic: Creating Union Views

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.

Parent topic: Creating Union Views

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.

Parent topic: Creating Union Views

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

For more information, see:

Copying Where Clauses

Parent topic: Customizing Data Extract

Copying Where Clauses

When you copy a Where clause, you need to be sure, as always when copying from the Global Library, that the domain of the object you want to copy is in your user domain searchlist. If the domain is not in this searchlist, you can add it.

Parent topic: Creating Where Clauses

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.

Parent topic: Customizing Data Extract

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;

Parent topic: Customizing Data Extract

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.

Parent topic: Customizing Data Extract