| Oracle® Clinical Conducting a Study Release 4.6 Part Number A85201-06 |
|
|
View PDF |
| Oracle® Clinical Conducting a Study Release 4.6 Part Number A85201-06 |
|
|
View PDF |
This chapter contains the following topics:
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. Data is presented for external access via standard and custom views (that is, 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 those data extract features and processes that fall under the heading "Conducting a Study". For data extract tasks performed in the Global Library and Definition subsystems, see "Data Extract" in the manual Oracle Clinical Creating a Study. See the "Utilities" chapter of the Oracle Clinical Administrator's Guide for information on regenerating and updating views.
Views created before Oracle Clinical V3.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 V3.1 and later. As of V3.1, views are created by the View Builder, which is a toolkit that provides the means to:
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. You also can create in-study unions within a study; these union all the data extract views that use the same key and view templates across the study.
This section contains the following topics:
To create a structure more conducive to data analysis, the Data Extract facility allows data to be accessed or extracted via Oracle or SAS views.
De-normalized Oracle views, termed data extract views, are created with SQL statements; these views join together and organize the study data so that responses to DCM questions are presented in the context of study, Investigator, patient, and RDCM.
For every Oracle view created, a corresponding SAS view is also created. Data extract views correspond to DCMs, and there is at least one view for every DCM; a DCM with repeating questions may have several.
Data extract is always done at the study level, either within a single study or across multiple studies. You can perform data extract for all DCMs, though it is not done for all the DCMs by default. For View Builder-enabled studies, you can default the View Definitions from DCMs. You can perform this default when the DCM is activated, or on an ad-hoc basis by choosing Default View Definition from the Special menu.
Data extract views are created as follows:
For every DCM, one view is created to contain the data for all non-repeating DCM question groups in the DCM. The name of the data extract view is the DCM short name. For each repeating question group in a DCM, a view is created whose name is the DCM short name plus the DCM question group short name.
For example: in the study ORACLIN, a DCM called ET, with the short name ET, has three DCM question groups: SD_DOSAGE, TREADMILL_INFO, AND EXERCISE_STAGE, with the short names SD, TI, and ES, respectively. SD_DOSAGE and TREADMILL_INFO are non-repeating question groups, while EXERCISE_STAGE is repeating. These views are created for this DCM:
ET: Contains the data for the two non-repeating question groups, SD_DOSAGE and TREADMILL_INFO.
ETES: Contains the data for the repeating question group, EXERCISE_STAGE.
If a third non-repeating question group is added to this DCM, the data is included in the existing ET view; but if another repeating group is added to the DCM, another view is created.
Data extract views fall under the following categories:
Current views display all data, including data not yet validated. The account type TEST is the same as Current, except that it uses the T test tables. Test type accounts create data extract views for provisional view definitions, while the Current account creates data extract views for active view definitions only for View Builder-enabled studies.
Stable views display only data that has been made accessible through data entry and has been through Batch Validation. A Stable view looks the way it did after completing its last Batch Validation.
Snapshot views display data as it looked at a particular moment in time during the data collection process. You can pick any Batch Validation run time (stability point) as the base date for a snapshot.
Rolling Snapshots are refreshed on demand to reflect the current Stable view of the data.
Once you have defined and created the data extract views, you can submit batch jobs to create SAS datasets and SAS Proc Prints, and then analyze the data.
The Rolling Snapshot—or Rollsnap—account is a study access account for holding data extract tables equivalent to Stable accounts, except that data extraction has to be explicitly run to update its contents. A system-defined snapshot, Rollsnap, is created and automatically updated to reflect the Batch Validation timestamp at the last table refresh. Maintenance of this account can therefore be scheduled asynchronously with Batch Validation.
For instance, if Batch Validation runs nightly, the Rollsnap account could be refreshed on a weekly basis. If required, the Rollsnap account could be refreshed after each Batch Validation and always reflect the latest Stable account.
View definitions created as views and not tables in this account view their data as of the Snapshot timestamp and thus are consistent with the data extracted to tables. Under this structure, with appropriate privileges, database objects in this account can be accessed with the prefix Study$ROLLSNAP.
In creating a clinical study state for a study (from the Conduct menu, select Security, then select Clinical Study States), you automatically create access accounts. These accounts are owned by one of the following kinds of account — Current, Stable, Rollsnap or Test. The only accounts you can create are Snapshot accounts.
The Oracle account name is the name of the study concatenated with $, concatenated with the account type. Snapshot accounts follow the naming convention of Study$your_choice_of_name. When you run the PSUB form Maintain Data Extract Views, you run your job in one of the account types described in order to create the view.
You cannot log on to these accounts, but you can describe and select from the views, if you have the appropriate security, by logging on to SQL*Plus in your own account, and typing — for example:
desc ORACLIN$CURRENT.ET
SAS views are created on the server, in the RXC_SAS_VIEW directory, with this structure: a subdirectory for each study, and under each study, subdirectories for each account type — for example:
RXC_SAS_VIEW/oraclin/current
A subdirectory is created for each new Snapshot or Rollsnap account — for example:
RXC_SAS_VIEW/oraclin/snap
The RXC_SAS_VIEW path is set by the Administrator as an Oracle Clinical environment variable.
You can remove access account s from a study by running the Purge Study Access Accounts batch job. The batch job allows you to remove one access account at a time; you cannot remove all the accounts from a particular study.
To run this job:
From the Conduct menu, select Data Extract, then select Remove Account. The PSUB window for this job opens.
In the Name of Study Access Account field, enter the account you want to remove or choose one from the LOV.
Run or schedule the job.
The Table-Based Views option is available for all accounts, and enables you to specify that particular access accounts will contain Oracle tables of extracted data instead of views. Queries against table-based extracted data will reduce activity against the RESPONSES and RECEIVED_DCMS tables. You can also control indexing of these tables.
Despite its name, the Table-based Views option still enables you to see views with the names of DCMs or View Definitions that are based on the data extract tables.
When you create a table-based view, you need to choose a tablespace for tables and one for indexes that is large enough to meet your needs. The Maintain Study Access Accounts window provides fields for you to enter these tablespaces.
Some relevant information to help in estimating tablespace may be found in the table VIEW_ACCOUNT_STATISTICS in the RXC account. See the OracleAS10gR2 documentation for information about calculating the space needed.
Note:
Union views cannot be represented as tables.Although indexing is a task that could be considered better covered under the Definition subsystem in Oracle Clinical, it is treated here because the menu entry is under Conduct.
Follow these steps:
Set up additional tablespaces to store the extract data and extract indexes, calling them, for example, RXC_EXTRACT_DATA and RXC_EXTRACT_IDX, respectively.
Locate the installation reference codelist DX_VIEW_TABLESPACE, and enter the name(s) of the tablespace(s) allotted to extract data (for this example, RXC_EXTRACT_DATA) in the Long Value column. The Short Value column will just be a sequential number. Each tablespace name requires a row.
Locate the installation reference codelist DX_INDEX_TABLESPACE, and enter the name(s) of the tablespace(s) allotted to the extract index(es) (for this example, RXC_EXTRACT_IDX) in the Long Value column. The Short Value column will just be a sequential number. Each tablespace name requires a row.
From the Conduct menu, select Data Extract, then select Study Access Accounts, set the View/Table column to TABLE for study access accounts where you want table views.
Study access accounts of types Snapshot and Rollsnap (see "View Types" and "Rollsnaps") can be made into table views.
For the same study you chose in Step 4, from the Definition menu, select Data Extract View Builder, then select View Definitions.
Change the Type field to TABLE from VIEW for each definition where you want a table view.
Both the view definition and the study access account must be set to TABLE for table views to occur.
In the Indexes window (from the Conduct menu, select Data Extract, then select Indexes), specify the indexes required for the underlying tables. For example, most tables need to be indexed on patient and visit, which are common search fields.
In the View Name to Index field, invoke the LOV and choose a view name from the list.
Enter a description for the view name in the Index Description field.
In each Template Columns to Index Column Name field, invoke the LOV to see the view columns, and choose the ones you want to use as index columns.
Save, then exit the Maintain Indexes window.
In the Data Extract Views window (from the Conduct menu, select Data Extract, then select Data Extract Views), choose the type and name of the study access account you want, and run in full mode.
This section contains the following topics:
Once choices in the DX Installation Configuration form are set, with View Builder enabled, and you have accepted the default view definition after making a DCM active, from the Conduct menu, select Data Extract, then select Data Extract Views. In the PSUB window Maintain Data Extract Views, submit a job for the study access account for which you want to extract data.
If you want to take advantage of the flexibility of the Oracle Clinical data extract process, you can follow various paths to customize your views.
Note:
Tasks not covered in this workflow, or in this manual, are described in the manual Oracle Clinical Creating a Study.See "Data Extract" in the manual Oracle Clinical Creating a Study for data extract tasks that precede the tasks you perform in the Conduct subsystem.
After you create a view definition (see the Data Extract chapter) you must generate the views in the Maintain Data Extract Views window; from the Conduct menu, select Data Extract, then select Data Extract Views. This PSUB job generates Oracle and SAS views from the same definitions.
To create the data extract views (or tables) in the database, Navigate to Conduct, then Data Extract, and finally Data Extract Views. The Data Extract Views window opens. Select the account type of the view and the account that matches the view. The account type is optional, but the account must match the account type. For all types except Snapshot, the account is study$type. For example: study1$CURRENT.
For all the values you provide in the form, there is a LOV. The system fills in the default for the SAS queue name with SYS$BATCH, the View Creation Mode with data_only, and the default for View Maintenance Mode with incremental; see "Full and Incremental Modes". The View Creation Mode parameter applies only to studies where the View Builder is not enabled, and Oracle Clinical ignores the entry in this field unless it is relevant.
The SAS queue name determines how a job is submitted. Oracle Clinical submits one job to the CPU running Oracle Clinical—the batch queue name, which can be seen in the Submission Details window. Oracle Clinical then writes a program to create the corresponding SAS views and submits this program to a potentially different queue, named Queue Name to Submit SAS, of the CPU running SAS.
View Maintenance, like any PSUB job, is automated, occurring as a batch job that can be scheduled.
The two modes handle view maintenance as follows:
Updating the View Templates linked to DCMs. Incremental mode updates only views that have been modified since the last job. Full mode updates all views. They both report the total number of linked DCMs fetched.
Generating view text from view structures created using the View Builder. Only full mode does view text generation. Incremental mode does not do text generation at all.
Maintains Study Access Accounts. This step includes activities such as creating, deleting accounts, and assigning privileges to the roles set under View Definition. Both modes loop through all accounts being maintained. Full mode drops and recreates the account, but incremental mode only grants Select, Connect, and Create type privileges to the account.
Both modes do the following tasks for all records:
Creating views when view creation is specified for the View Definition and Access Account
Creating tables when table creation is specified for the View Definition and Access Account
Creating union views in Study Set Access Accounts
Creating union views in Study Access Accounts
In Conduct, you can specify view definitions that then become the basis for creating Oracle views or Oracle tables in the access accounts, which are new Oracle accounts made for views. You can also create Snapshot views. Finally, you can submit account names to the PSUB utility to extract the data.
You can also create SAS datasets and SAS Proc Reports in Conduct. For these two functions, you can reduce overhead and decrease confusion by restricting view creation to meaningful views. For example, for a default view definition for a DCM named CONMED, assume a custom view that includes the CONMED data, some derived information, and selected information from a demographic DCM. In this case preventing generation of CONMED can avoid problems.
This section includes the following topics:
The View Creation Mode field in the PSUB window allows you to specify whether and how the validation status of the data should be included in the views.
When you specify in this field that response status information should be included in view creation, this applies to all views except externally loaded views.
You can create views in three modes:
with (response) data only
with both response and validation status data in the same view
with validation status and response data in separate views
Study views created with the View Builder are restricted to data-only mode. You can include the validation status in a view of this kind by adding the information as an extended attribute of a question (see Chapter 7, "Utilities", in the Oracle Clinical Administrator's Guide for instructions). You can choose to default the validation status by triggering a setting in the DX Installation Configuration window. In View Template Details, you can choose to include validation status in any type of view definition.
Oracle Clinical maintains a Status field that reflects the validation status of each Response field. This field validation status consists of three characters: the first represents univariate validation; the second, multivariate; and the third, manual comments.
The rules for computing the univariate, multivariate, and manual validation statuses are listed below. These are listed in descending order of priority. Higher priority indicates that a rule is applied before another with lower priority. As soon as a rule for status determination is satisfied, the process of computing the validation status ends.
Indicates that the response has at least one unresolved discrepancy still under review. This condition applies to all three status variables.
Indicates that the response does not have any outstanding system-generated or manual discrepancies, but has at least one resolved discrepancy with a status of Irresolvable. This condition applies to all three status variables.
Indicates that the response does not have any outstanding discrepancies, but has at least one resolved discrepancy with a status of Confirmed, that is, confirmed as a true verified value by a reviewer. This condition applies to all three status variables.
Indicates that the response has discrepancies that were resolved or made obsolete by either a data change or change in an edit check.
Univariate status is C when a univariate discrepancy is eliminated by a change to the data or a change to an edit check so that the discrepancy is now Obsolete.
Multivariate status is C when a multivariate discrepancy is eliminated by a change to the data or a change to the validation procedure so that the discrepancy is now Obsolete.
Manual status is C when the response has at least one manual discrepancy that is manually resolved with a resolution status code having superseded long_value in the codelist.
Indicates no discrepancy ever existed for the response.
Univariate status is N when a response has no univariate discrepancies.
Multivariate status is N when a response has no multivariate discrepancies.
Manual status is N when a response has no manual discrepancies, excluding those with a resolution status code having NON DISCREPANT long_value in the codelist.
To implement view selectivity—that is, to submit only those views you want—mark study (set) access accounts as Create or Drop in either the Maintain Study Access Accounts or the Maintain Study Set Access Accounts window. Open these windows by selecting Data Extract from the Conduct menu. These windows enable you to view existing study access accounts. You can also create Snapshot accounts.
When the fields in the Maintain Study Access Accounts window are completed the way you want them, you can submit the access account to the PSUB form, Maintain Data Extract Views, to obtain views. You need only create access accounts once; you do not need to create them at all if you are satisfied with the default views.
The Maintain Study Set Access Accounts window has the same fields as the Maintain Study Access Accounts form. The available studies however, are sets of studies.
At least two of the studies in a cross-study view must belong to a study set for an account to be created.
The Query Data Extract View module lets you view existing data extract views, whether they are standard Oracle Clinical-generated views, user-defined views created via View Builder, or externally loaded views.
You can query existing data extract views; from the Conduct menu, select Data Extract, then select Qry Data Extract Views. All active views are listed in the Query Data Extract Views window. The Query Data Extract Views window is read-only; you cannot create, update, or delete a data extract view from this window. In addition, the module does not display the text of a data extract view. Its function is simply to display all existing data extract views.
When the Qy? box is selected, the view is a custom view created through the View Builder. When the Source field contains a value, the view was created from an externally loaded file. When neither of these fields has a value, the view is a standard data extract view defaulted from a DCM.
SAS datasets are true extracts, in that these datasets are removed and held in an unchanging and final form for eventual analysis. If you want to see new data, you have to create a new SAS dataset.
Oracle views are dynamic: the data they retrieve changes as the data in Oracle Clinical changes, although they change in different ways, according to the account type. So the data in a Current Oracle view may change as new data is entered into the system; a Stable Oracle view may reflect new data each time Batch Validation is run; while the data in an Oracle Snapshot view does not change, by definition.
To create an SAS dataset, from the Conduct menu, select Data Extract, then select SAS Datasets. The Create SAS Datasets PSUB window appears. Enter the account type and the name of the data extract view for which you want to create the SAS dataset. Then choose the queue name to submit SAS, which may be different from the batch queue name in the Submission Details window.
For more information on Oracle Clinical and SAS data, see "Data Extract" in the manual Oracle Clinical Creating a Study.
This section contains the following topics:
The Data Extract View Loader allows you to load an externally created data extract view definition statement into the Oracle Clinical database. Once it is loaded, the view definition statement is treated like other Oracle Clinical data extract views: it is used to create data extract views and SAS views specified for the study.
The Data Extract View Loader has a limited purpose: it allows you to load data extract view definition statements, including those you cannot create through the View Builder. It is not an alternative to the View Builder.
Oracle Clinical simply inserts the SQL view definition statement and the SAS view definition statement into a table and acts on them at data extract view maintenance time. At that time, both the data extract view and the SAS view are created from user-provided statements in the data files.
Do not load an extract view you can build through the View Builder, for the following reasons:
No definitional information is maintained for externally loaded views. Oracle Clinical does not keep track of which DCMs, DCM question groups, or questions are involved in the loaded view.
Consequently, if an Oracle Clinical element changes, for example, a question referred to in the loaded view is retired, Oracle Clinical does not automatically recognize that the view is no longer valid.
No validation is performed on externally loaded views.
Question misspellings or syntactic errors are not detected at load time, and result in a failure status at data extract view maintenance time.
Maintaining externally loaded views is a manual process.
If you update your externally loaded view definition statement, Oracle Clinical does not automatically update the corresponding SAS view. You are responsible for updating both the data extract view definition and the SAS view manually.
To load external views, your files must have SQL text specifications for queries or views so that the data extract view text for both the Oracle and SAS view is created.
To reach the PSUB form you need for loading, from the Conduct menu, select Data Extract, then select Load External View.
Enter the study, view name, and name of the file to load.
The file names containing the Oracle and the SAS text must have the same name and the file extensions .SQL and .SAS, respectively. Enter the file name of the .SQL file in the submission window. The module determines the .SAS file name from the .SQL file name.
Click the Submit Job button.
The batch job loads the text of the SQL and SAS views into the Data Extract Views table. When the job has completed, continue data extract view maintenance for the view(s) you want to create, which are created in the study access accounts selected.
File extensions for externally loaded views must be .SQL for Oracle views and .SAS for SAS views. An SAS and SQL load file must exist for each view loaded. The Load External Views function does not do error checking, so if an invalid syntax is specified, view maintenance fails.
select /* + ORDERED USE_MERGE (cpe) INDEX (r RESPONSE_UK_IDX) INDEX (cpe CLIN_PLAN_EVE_UK2_IDX) */ substr(rdcm.document_number, 1 0) ||'WK14338' study, rdcm.patient pt, rdcm.clin_plan_eve_name cpevent, r.repeat_sn repeatsn, max(decode( r.dcm_question_id, 123, substr(r.value_text,1,8), 456, substr(r.value_text,1,8), 789, substr(r.vaue_text,1,8) )) var1 from clinical_planned_events cpe, rdcms_view rdcm, responses_view r where rdcm.clinical_study_id = 100 and rdcm.dcm_id in (003,004) and r.dcm_question_id in (123,456,789) and r.received_dcm_id = rdcm.received_dcm_id group by substr(rdcm.document_number, 1 0) ||'WK14338' study, rdcm.patient, rdcm.clin_plan_eve_name, r.repeat_sn
DEFINE S_Study_ID = 100
DEFINE DCM_1 = 003
DEFINE DCM_2 = 004
DEFINE DCMQ_1 = 123
DEFINE DCMQ_2 = 456
DEFINE DCMQ_3 = 789
select /* + ORDERED USE_MERGE (cpe)
INDEX (r RESPONSE_UK_IDX)
INDEX (cpe CLIN_PLAN_EVE_UK2_IDX) */
substr(rdcm.document_number, 1 0) ||'WK14338' study,
rdcm.patient pt,
rdcm.clin_plan_eve_name cpevent,
r.repeat_sn repeatsn,
max(decode(r.dcm_question_id, &DCMQ_1, substr(r.value_text,1,8),
&DCMQ_2, substr(r.value_text,1,8), &DCMQ_3, substr(r.value_text,1,8))) var1
from clinical_planned_events cpe,
rdcms_view rdcm,
responses_view r
where rdcm.clinical_study_id = &S_study_ID and
rdcm.dcm_id in (&DCM_1, &DCM_2) and
r.dcm_question_id in (&DCMQ_1, &DCMQ_2, &DCMQ_3) and
r.received_dcm_id = rdcm.received_dcm_id
group by substr(rdcm.document_number, 1 0) ||'WK14338' study,
rdcm.patient,
rdcm.clin_plan_eve_name,
r.repeat_sn
The following restrictions apply:
View names may not exceed 8 characters.
View names cannot be the same as those of existing DCMs or Oracle Clinical-created data extract views.
For substitution parameters, it is recommended that all numbers be of the same length. So if your largest number is 981, then 10 should be represented as 010.
An external view can be updated by reloading a view file and using the same view name as an existing externally loaded view. The file name can be different from the file name used to originally create the view. The new view definition replaces the previous view definition with the same view name. No history is kept within Oracle Clinical on the previous version of the view.
To delete an externally loaded view, from the Conduct menu, select Data Extract, then select Delete External View. A PSUB window called Mark an Externally Loaded View for Deletion appears. Enter the name of the view you want to delete. Click the Submit Job button. The view is marked for deletion. The next time data extract view maintenance is run, this view is deleted.
|
 Copyright © 1996, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|