Oracle® Life Sciences Data Hub Application Developer's Guide Release 2.3.1 E35298-02 |
|
![]() Previous |
![]() Next |
This section contains information on the following topics:
Figure 7-1 Process of Creating a Load Set Definition and Instance
To load data into the Oracle Life Sciences Data Hub (Oracle LSH), you define and run a Load Set.
Oracle LSH allows you to load data from a wide variety of sources. The standard Oracle LSH installation (without any special customizations) allows you to load data from SAS, from any Oracle database, or from a text file. Oracle LSH also has a set of Load Set types developed especially for Oracle Clinical that allow you to load data from data extract views, from the discrepancy management subsystem, and from the global library.
Oracle LSH uses a specialized executable defined object called a Load Set to handle data loading. A Load Set definition identifies the type of source data system and includes Table Descriptors that specify the required structure of one or more Tables into which to load the data in Oracle LSH. For Oracle and SAS Load Sets the system can create Table Descriptors based on tables or data sets in the source data system. For all types, you can create Table instances based on Table Descriptors and map them automatically, using a job in the Actions drop-down list.
For Load Sets that load files—SAS and Text—you should not specify the file during Load Set definition because the system uploads the actual data file at that time. Let the person who executes the Load Set specify the file to get the most current data. For the same reason it does not make sense to set up a repeating schedule for running a SAS or Text Load Set; you would be loading the same data each time.
For Oracle-technology Load Sets you can specify default values in the Load Set definition for the tables to be loaded. The system only creates views during Load Set definition, and always loads the most current data when you run the Load Set. You can set up a repeating execution schedule to update the data as necessary.
At runtime, if the user submitting the Load Set specifies different external tables or files to load from the ones you used to create the Table Descriptors, the Load Set will run successfully if the data structure is compatible; see "Enforced Compatibility at Runtime".
For information on loading blinded data, see "Loading Real and Dummy Data".
Load Set Types The different types of Load Sets are as follows. You see only those types that your company uses and that you have security access for.
Oracle Tables and Views. Oracle LSH loads data and metadata from any Oracle database.
SAS. Oracle LSH loads one SAS data set at a time or, using a CPORT or XPORT file, multiple data sets. The system loads metadata at the time of Load Set definition and data at Load Set runtime.
Text. Oracle LSH loads one or more text files into one or more Table instances.
Oracle Clinical Data Extract Views. Oracle LSH loads patient data from Oracle views defined in Oracle Clinical.
Oracle Clinical Data Extract SAS Views. Oracle LSH loads patient data from SAS views defined in Oracle Clinical.
Oracle Clinical Global Metadata. Oracle LSH loads your Oracle Clinical Global Library definitions and automatically converts them to Oracle LSH definitions as follows: converts Oracle Clinical Questions to Oracle LSH Variables. If a Question is associated with a DVG, Oracle LSH also converts the Question to an Oracle LSH Parameter with the DVG as its list of allowed values. Oracle LSH converts Oracle Clinical Question Groups to Oracle LSH Tables with Columns based on the Variables corresponding to each Question in the Question Group.
Oracle Clinical Labs. Oracle LSH loads Lab definitions, Lab Ranges, and Lab Assignment Criteria, and other Lab-related metadata (see "OC Labs Load Set Table Descriptors" for a complete list).
Oracle Clinical Study Data. Oracle LSH loads study-specific data including discrepancies, Data Clarification Forms (DCFs), page tracking information and patient status information.
Oracle Clinical Stable Interface. Oracle LSH loads tables described in the Oracle Clinical 4.x Stable Interface Guide.
Oracle Clinical Randomization. Oracle LSH loads Oracle Clinical tables related to the Randomization subsystem.
Security Each Load Set type uses a different predefined Oracle LSH adapter to handle the data and metadata exchange between the external system and Oracle LSH. An adapter consists of a group of predefined objects in an Adapter Area, which is contained in an Adapter Domain.
Before you can create a Load Set for any Oracle source data system you must do the following setup: enter connection information for the remote location into Oracle LSH in the Remote Locations subtab of the Administration tab. Instructions are included in the chapter "Registering Locations and Connections" in the Oracle Life Sciences Data Hub System Administrator's Guide.
Create one or more user groups and assign them to Adapter Areas (see "Setting Up Security for Adapters" in the Oracle Life Sciences Data Hub System Administrator's Guide). Each Definer who will need to define Load Sets must belong to a user group that is assigned to the Adapter Area that corresponds to the Load Set type that he or she needs to define.
In addition, you must add an administrator a user group assigned to each Oracle-technology adapter so that the administrator can define remote locations for the Oracle adapters.
An administrator must define remote locations for the Oracle adapters. If you want to use shared connections, the administrator must create them. See the chapter on "Defining Remote Locations" in the Oracle Life Sciences Data Hub System Administrator's Guide for further information.
Individual users with access to external Oracle systems can create their own connections for use in defining and running Load Sets Preferences, accessed from a link on the Oracle LSH My Home screen. See "Getting Started" in the Oracle Life Sciences Data Hub User's Guide for further information.
Note: When you define any Oracle technology-based Load Set you must enter a Remote Location and your own (or shared) connection information in the Attributes section of the Load Set definition in order to create the Load Set's Table Descriptors.However, be careful not to enter your own or shared connection information as a runtime Parameter because this represents a breach of security on the source data system, enabling anyone with security access to the Load Set itself to run the Load Set regardless of whether he or she has the required privileges on the source system. Leave the Parameter value empty so that the user must enter his or her own connection information to run the Load Set. |
Execution Load Sets, like other Oracle LSH executable objects, have Execution Setups that serve as the basis for the submission form for running the job. You can modify the default value of Parameters in the Execution Setup definition, and the user submitting the Load Set can change them at runtime. See "Creating, Modifying, and Submitting Execution Setups".
Oracle LSH uses the Processing Type you define for the target Table instances to determine how to process the data and whether to maintain an internal audit trail of inserts, updates, and deletes (see "Data Processing Types" for further information).
Loading Blinded Data See "Loading Real and Dummy Data".
Reports on Load Set Definitions and Instances From the Actions drop-down list, you can generate reports that provide information on a Load Set definition or instance; see Chapter 14, "System Reports" for information.
When you create a Load Set in a Work Area, you are actually creating an instance of a Load Set definition.
To create a new Load Set instance:
In a Work Area, select Load Set from the Add drop-down list.
Click Go.
The system displays the Create Load Set screen.
Choose one of the following options:
Create a new Load set Definition and Instance. Choose this option if no Load Set definition exists that can meet your needs, either as it is or with some modification.
Create an instance of an existing definition. Choose this option if a Load Set definition already exists that meets your needs.
If you can adapt an existing Load Set definition to make it fit your needs, first copy it into the current Application Area, then choose this option and select the copied definition. See "Finding an Appropriate Definition" and "Reusing Existing Definitions" for further information.
Depending on your choice, follow one of the following sets of instructions:
When you select Create a new Load set Definition and instance in the Create Load Set screen, additional fields appear.
Enter values in the following fields:
Name. See "Naming Objects".
Description. See "Creating and Using Object Descriptions".
Load Set Type. See "Defining Different Load Set Types" for information.
In the Classification section, select the following for both the definition and the instance:
Subtype. Select a subtype according to your company's policies.
Classification Values. See "Classifying Objects and Outputs" for instructions.
Click Apply to save your work and continue defining the Load Set.
The system opens the Properties screen for the new Load Set instance.
See the following sections for general information that applies to most Load Set types:
See "Defining Different Load Set Types" for information about each type of Load Set.
Some Load Set types include attributes. These vary depending on the type of Load Set; see the instructions for each type of Load Set:
These values cannot be changed at runtime.
If you use an existing Load Set as a definition source, all of its Table Descriptors, Parameters and other characteristics are already defined. See "Creating an Instance of an Existing Definition" for instructions.
After you have created the Load Set instance, you must map the Table Descriptors to Table instances; see "Mapping Table Descriptors to Table Instances" for instructions.
This section contains the following topics:
Subtabs:
See also Figure 7-1, "Process of Creating a Load Set Definition and Instance".
See "Modifying Load Sets" for information on modifying Load Sets.
If you are working in a Work Area, you see the properties of both the Load Set instance and the Load Set definition it references. If you are working directly on the definition in an Application Area or Domain, you see only the properties of the definition.
You can see the following instance properties:
Name You can click Update and modify the name. See "Naming Objects" for further information.
Description You can click Update and modify the description. See "Creating and Using Object Descriptions" for further information.
Definition This field specifies the Load Set definition to which this Load Set instance points. For further information, see "Definition Source".
You can upgrade to a new version of the same definition. See "Upgrading to a Different Definition Version from an Instance".
Version Label This field displays the version label, if any, for the current Load Set instance version.
Validation Status This field displays the current validation status of the Load Set instance. If you have the necessary privileges, you can change the validation status by selecting Validation Supporting Information from the Actions drop-down list. See "Validating Objects and Outputs" for further information.
Status This field displays the installable status of the Load Set: Installable or Non Installable. See Appendix A, "Installation Requirements for Each Object Type".
Version This field displays the current version number of the Load Set instance.
For further information on object versions, see "Understanding Object Versions and Checkin/Checkout".
You can see the following definition properties:
Checked Out Status This field displays the status of the definition: either Checked Out or Checked In. You must check out the definition to modify Table Descriptors, Parameters, or Planned Outputs. However, you can change Table Descriptor mappings without checking out the definition. See "Understanding Object Versions and Checkin/Checkout" for further information.
Latest Version If set to Yes, this Load Set instance is pointing to the latest version of the Load Set definition. If set to No, this Load Set instance is pointing to an older version of the Load Set definition.
Checked Out By This field displays the username of the person who has the Load Set definition checked out. See "Understanding Object Versions and Checkin/Checkout" for further information.
Version Label This field displays the version label, if any, for this definition version.
Load Set / Adapter Type This field displays the type of Load Set or Adapter. See "Load Set Types".
Validation Status This field displays the current validation status of the Load Set definition. If you are working directly in the definition in an Application Area or Domain and you have the necessary privileges, you can change the validation status by selecting Validation Supporting Information from the Actions drop-down list. If you are working in an instance of the Load Set in a Work Area, and you want to change the validation status of the definition, you must go to the definition. See "Validating Objects and Outputs" for further information.
Status This field displays the installable status of the Load Set: Installable or Non Installable. See Appendix A, "Installation Requirements for Each Object Type".
This section of the Load Set screen displays attribute values for the type of Load Set you have created. See "Setting Load Set Attributes".
From a Load Set instance in a Work Area, you can use the following buttons:
Install Click Install to install the Load Set instance, including mapping target Table Descriptors and installing mapped target Table instances; see "Installing Load Set Instances". For a list of reasons a Load Set instance may not be installable, see Appendix A, "Installation Requirements for Each Object Type".
Submit Click Submit to run the Load Set instance. Before you can run the Load Set, you must install it and create an Execution Setup for it (select Execution Setups from the Actions drop-down list).
Update Click Update to modify the Load Set instance properties. See "Modifying Load Set Instance Properties". You can also update Load Set Attributes by clicking Update in the Load Set Attributes section of the screen. See "Setting Load Set Attributes".
Check In/Out and Uncheck Click these buttons to check out, check in, or uncheck the Load Set definition. Different buttons are displayed in the Load Set Definition Properties section depending on the Checked Out Status and whether or not you are the person who has the definition checked out. If someone else has checked out the definition, you cannot check it in or uncheck it. The username of the person who has checked it out is displayed. See "Understanding Object Versions and Checkin/Checkout".
Load Sets have only target Table Descriptors. They must be compatible with the structure of the external tables or data sets from which you are loading data and with the Table instances into which you are loading data.
For all Load Set types, Oracle LSH can create the Table Descriptors based on data structures you specify in the source system. You can then create Table instances based on those Table Descriptors, or create Table instances based on standard (or any other compatible Table definitions) and map the two. See "Mapping Columns of Different Data Types and Lengths" for an explanation of compatibility; for example, a source column or variable is compatible with a target column of a longer length, but not with a target column of a shorter length.
The target Table instance must have one of the following process types: For information on the first three processing types, see "Data Processing Types".
Staging with Audit (the default)
Staging Without Audit
Reload
Pass-Through View For Oracle-technology Load Sets, you also have the option to create the target Table instances as pass-through views so that the physical source data remains in the external Oracle system. This approach allows you to minimize data storage space. After you map the Table instance to an Oracle-type Load Set, the system adds this option to the Processing Type drop-down list of the Table instance.
It is not necessary to run Load Sets whose target Table instances are defined as pass-through views. As soon as you install the Load Set, the data in the external Oracle system is available to Oracle LSH.
Programs can use Table instances defined as pass-through views as sources just as they can Table instances that contain data. When you run the Program, it reads data in the source system and can write data to Oracle LSH Table instances.
Mapping Table Descriptors to Table Instances You can do either of the following:
Run the Table Instances from Existing Table Descriptors job from the Actions drop-down list to create a Table instance of the same structure as the Table Descriptor and automatically map the two. See "Creating Table Instances from Table Descriptors and Simultaneously Mapping Them" for instructions.
Create a Table instance—for example, create an instance of a standard CDISC Table definition—and map the Table Descriptor to the Table instance. If they have the same name, you can run the Automatic Mapping by Name job from the Actions drop-down list and it will map any Columns that have the same name. If not, you can map them manually. See Mapping Table Descriptors to Table Instances.
Enforced Compatibility at Runtime At runtime a user can specify a remote data source with a different structure from the Table Descriptor you defined. If the structure are incompatible—the differences are such that they would cause data corruption— the load fails with an error.
The following types of differences cause a failure:
The target Oracle LSH Table Column of a character data type is shorter than the source table column or data set variable.
For number data types, the length and precision combined are smaller in the target than in the source.
The target Table Column has a data type that is incompatible with the source data type. See "Mapping Columns" for information on compatible data types.
The source table or data set includes a column or variable that is not included in the Table Descriptor.
See the section on each type of Load Set for details:
For general instructions see Defining and Mapping Table Descriptors.
Each Load Set type has a different set of runtime Parameters. The system uses these Parameters to create the Execution Setup to be used to submit the Load Set for execution.
Note: Because these are predefined and required Parameters you should not change anything except the default value in the Load Set definition, or the Load Set may not function properly. For Remote Location Parameters you should not even enter a default value for security reasons. |
The predefined Parameters are described for each type of Load Set:
Oracle LSH automatically creates the following Planned Outputs for Load Sets:
Log File. Oracle LSH creates a log file Planned Output for all types of Load Sets. It is the only Planned Output for Load Sets of Oracle Tables and Views and OC adapters.
Error File. Oracle LSH creates an error file Planned Output for SAS and Text Load Sets and saves each rejected row into this error file. The generation of the error file does not indicate a job failure. A SAS or Text Load Set job fails when the number of rejected records exceeds the value of the Maximum Allowed Errors parameter.
Input data file. For SAS and Text Load Sets, if you set the Save Input File attribute to Yes, the system automatically creates a Planned Output and saves the input file as an actual output at runtime. If you classify this Planned Output, the system classifies the saved input file as an output with the classification you specified.
Control File. Oracle LSH creates this Planned Output for Text and SAS Load Sets. This Planned Output is always generated for Text Load Set jobs. For SAS Load Sets, the system generates it only for bulk loads (when you set the parameter Direct to Yes). This Planned Output stores the SQL*Loader control file.
Textload.zip. Oracle LSH creates this Planned Output if you set the Save as Input attribute to Yes while uploading a zipped Load Set file. This is applicable only to Text Load sets.
You can classify a Planned Output in order to classify the actual output when it is generated; see "Classifying Outputs" for further information.
To view the log file or saved input file for a Load Set job, do one of the following:
Use the Search or Advanced Search feature.
Browse for the log file in the Outputs subtab of the Reports tab.
This section includes information on the following topics:
This section contains the following topics:
Oracle Tables and Views Load Sets enable you to access data in Oracle LSH from any Oracle system external to Oracle LSH. The source of the data in the external system can be either a database table or a view with column data types of varchar2, number, and date.
The adapter loads the primary key definitions of the source tables to the target Table instances when you define a Load Set.
You can take two approaches to Oracle data in Oracle LSH:
Load the physical data into Oracle LSH. Rerun the Load Set periodically to refresh the data in Oracle LSH.
Define the Load Set's target Table instances as a pass-through view, so that the physical source data remains in the external Oracle system. This approach allows you to minimize data storage space.
Programs can use Table instances defined as pass-through views as sources just as they can Table instances that contain data. When you run the Program, it reads data in the source system and can write data to Oracle LSH Table instances.
You specify whether to use the Load Set to load or view data as part of the definition of the target Table instance to which you map the Table Descriptor. The system adds the Create Table as a View option to the Process Type drop-down list of the Table instance after you map the Table instance to an Oracle-type Load Set.
Note: All target Table instances of the Load Set must be defined the same way—either as pass-through views or not. If some are defined as pass-through views and others are not, execution of the Load Set fails. |
Click Update and enter values for the remote location and database schema that are the source of the data to be loaded:
Remote Location. Click the Search icon and choose a source remote location/connection combination from the list of values. The system displays only those locations and connections to which you have security access.
If the location you need is not on the list, ask a system administrator to create a defined Remote Location for it in the Administration user interface.
If you have a valid username and password for the required database, you can create a connection in the Preferences hyperlink at the top of most Oracle LSH screens. However, you cannot create a connection until an administrator has created a remote location.
Your company may also use shared connections. If so, you can use those as well.
Schema. Select the database schema on the remote location where the table or view resides.
Use the Upload Table Descriptors function to specify the tables you want to load. The system generates a list of tables in the remote location that you specified in the Attributes section.
See also "Defining Table Descriptors".
The following are Oracle Load Set runtime Parameters:
Remote Location. The user submitting the Load Set for execution must select his or her own remote location/connection combination (or a remote location with a shared connection) at runtime to ensure the proper security for the external database. Do not enter a default value or change any of the other Parameter settings.
Direct. If you set Direct to Yes (default is No), the system uses the direct path INSERT to load data into the target Table instance(s).
Note: When writing to multiple Table instances, if all the Table instances are not of the Transactional High Throughput processing type, the system first inserts data into the Transactional High Throughput processing type Tables using the direct INSERT and subsequently inserts data using the conventional INSERT into Tables of other data processing types. The Load Set job completes with warnings and writes warning messages to the job log. |
Drop and Recreate Indexes. This parameter applies only if you set Direct to Yes.
If set to Yes (default), the system drops all non-unique indexes before running a data-loading job and recreates the non-unique indexes after loading data into the target Table instances. The system does not drop indexes if Direct is set to No.
Logging. This parameter applies only if you set Direct to Yes.
If set to Yes (default), in the event of a failure the system can recover data committed to the database. If set to No, the system does not maintain a redo log file and cannot recover any data when a database failure occurs.
Oracle Load Sets have only one Planned Output: a log file. See "About Load Set Planned Outputs" for further information.
Do not set the Remote Location Parameter in the Execution Setup. See "Oracle Load Set Parameters".
For general information on defining Execution Setups, see "Creating, Modifying, and Submitting Execution Setups"
This section includes the following topics:
A SAS Load Set loads data from a SAS file into Oracle LSH. You can define a SAS Load Set for a single data set or for multiple data sets in a SAS XPORT or CPORT file.
Each time you run a SAS Load Set, you load all the data contained in the source file at runtime. You can load a zipped data set file (but not a zipped XPORT or CPORT file).
To ensure that Oracle LSH stores and displays special characters in your data correctly, start SAS in UTF8 mode.
If you are using SAS 9.2:
In Windows:
Drive:\Program Files\SAS\SASFoundation\9.2\sas.exe" -CONFIG Drive:\Program Files\SAS\SASFoundation\9.2\nls\u8\SASV9.CFG"
In UNIX:
sas -encoding UTF8
If you are using SAS 9.1.3 on UNIX, use sas_dbcs instead of sas to start in unicode mode. For further information, see SAS Paper Paper 1036, Multilingual Computing with the 9.1 SAS Unicode Server at: http://support.sas.com/rnd/papers/sugi28/unicodeserver.pdf
The only attribute is the Save Input File? flag:
If set to Yes, the system saves the uploaded file as an output. See "SAS Load Set Planned Outputs".
If set to No, the uploaded file is deleted after the load completes. This is the default value.
If you are using a transport file as a source, you can load more than one data set with a single Load Set, but you cannot load a zipped file. If you are using a data set file as a source, you can load only that one data set but you can load it as a zipped file.
Oracle LSH automatically creates one Table Descriptor for each data set in the file you specify. If you specify a transport file with data sets that have indexes defined, Oracle LSH creates corresponding indexes in the Table definition as well.
To create Table Descriptors for a SAS Load Set, do the following:
In the Table Descriptors subtab of the Load Set, click Add. The system opens the Create Table Descriptors screen.
Select Create New Table Definition(s) and Descriptor(s) from a SAS file. The system refreshes the screen and adds a field with a Browse button.
Click Browse. The system opens a standard Browse window.
Navigate to the SAS file you want, select it, and click Open. The system returns to the Create Table Descriptors screen and enters the name of the file you selected in the Import From File field.
Click Apply. The system creates one Table Descriptor for each data set in the file you specify.
Note: To create a SAS Load Set definition, Oracle LSH loads the file you specify with all its data. The more data the data set contains, the longer it takes to create the Load Set and the more resources are consumed. Therefore, copy the structure of the data set in SAS and use the empty copy to create the Table Descriptors. |
You can use the Create Table Instances from Existing Table Descriptors job from the Actions drop-down list to create and map all the Table instances you need. Alternatively, you can create instances of any compatible Table definitions and map them. See "Mapping Table Descriptors to Table Instances" for further information.
SAS Load Sets have the following runtime Parameters:
Note: These are predefined, required, runtime Parameters and you should not change any of their properties, or the Load Set may not function properly.These Parameters take meaningful values only at the time of submitting the Load Set's Execution Setup. |
BLOB ID (Temporary). This Parameter is for internal use only. It contains a pointer to the uploaded file.
Dataset File Name. Do not select a file while defining the Load Set. Oracle LSH uploads the file when you submit the Load Set's Execution Setup. If you upload the file during Load Set definition, the data will probably be outdated at the time the Load Set is executed. Therefore, leave this field blank. The user must enter the filename at runtime if Load From Server OS is set to No else the user has to enter the Server OS filename.
Load from Server OS. You have the option to load a data set file from your local computer or from a remote system. Set this parameter to Yes (default is No) if your file is either on the Oracle LSH DP server or on another computer that has its drives NFS-mounted on the DP server computer. If you set this parameter to No, you must upload a data set file from your local computer at the time of submitting the SAS Load Set job.
Note: The system can load remote SAS data set files only from an Oracle LSH DP Server that is running the SAS and the SQL*Loader services. In addition, the SAS Service Location must point to the SQL*Loader executable. Provide the absolute path of the SQL*Loader executable in the Details field of the SAS Service. See the chapter on Setting Up Services in the Oracle Life Sciences Data Hub System Administrator's Guide for more information. |
Server OS Filename. If you set Load From Server OS to Yes, enter the absolute path of the remote data set file at the time of submitting the Load Set's Execution Setup else leave this parameter blank.
Maximum Allowed Errors. Tolerance factor; the maximum number of invalid records you are willing to accept before SQL*Loader stops the loading process and marks the Load Set job as failed. The default value of this parameter is 0.
Note: If you are uploading a SAS CPORT or XPORT file, note that the value you enter here applies to each data set contained within the CPORT or XPORT file. |
Direct. If set to Yes (default is No), the system uses the direct path INSERT to load data from the SAS dataset file into the Oracle LSH target Table instance.
Note: When writing to multiple Table instances, if all the Table instances are not of the Transactional High Throughput processing type, the system uses the direct path INSERT for the Transactional High Throughput processing type Tables and the conventional INSERT for Tables of other data processing types. The Load Set job completes with warnings and writes warning messages to the job log. |
Drop and Recreate Indexes. If set to Yes (default), and if the value of the parameter Direct is Yes, the system drops all non-unique indexes before running a data-loading job and recreates the non-unique indexes after loading data into the target Tables. The system does not drop indexes if Direct is set to No.
Note: See Oracle® Database Utilities athttp://download.oracle.com/docs/cd/E11882_01/server.112/e16536.pdf for more information on SQL*Loader parameters at: |
Oracle LSH automatically creates the following Planned Outputs for SAS Load Sets:
SAS Loading Log File. Oracle LSH writes the job log into this Planned Output for all jobs except bulk loading jobs. Bulk loading jobs write data from SAS data sets into Transactional High Throughput Table instances.
SAS Loading Input Data File. If you set the Save Input File attribute to Yes, the system uses that Planned Output as a placeholder for the input file saved as an output during execution. If you set Save Input File to No, the system still creates the Planned Output but does not create the actual output when you run the Load Set.
You can classify the Planned Output and view the actual output through the Oracle LSH Outputs user interface, subject to normal Oracle LSH security rules. To view the contents of the file, you must download it and view it through SAS.
SAS Bulk Loading Error File. If you set the parameter Direct to Yes, and if the data loading job encounters one or more invalid records, the system generates this file and writes each rejected record into it. For SAS CPORT and XPORT files, Oracle LSH consolidates the error files that SQL*Loader generates for each data set.
SQL*Loader Log File. If you set the parameter Direct to Yes, SQL*Loader generates this log file for each data set when writing to a Table instance and stores in this Planned Output. For SAS CPORT and XPORT files, Oracle LSH consolidates the log files that SQL*Loader generates for each data set.
SQL*Loader Control File. If you set the parameter Direct to Yes, SAS generates the SQL*Loader control file for each data set when you run the Oracle LSH Load Set job. The system stores the control file in this Planned Output. If you use CPORT or XPORT, Oracle LSH consolidates all the control files into this Planned Output.
When you define an Execution Setup for a SAS Load Set, be careful to give it a name and description that will enable a person submitting the Load Set for execution to understand which file should be loaded.
The file must contain data sets that correspond to the Table Descriptors defined for the Load Set, but the Table Descriptors are created as part of the Load Set definition, while the file should only be specified at runtime. Therefore, provide enough information in the Execution Setup Name and Description to make it clear which data sets this Load Set definition is intended to load.
In addition, do not enter a value for Dataset File Name or the Server OS Filename in the Execution Setup definition. The person submitting the Load Set for execution must enter a file name. The system uploads the file at the time that you specify it. If you specify it during Load Set or Execution Setup definition, the data in the file may be out of date by the time the user runs the Load Set.
Note: It does not make sense to schedule a SAS Load Set for repeated execution because it would use the same BLOB ID each time, reloading the same data.If you schedule a single run of a SAS Load Set for a later time, it loads the data that was current at the time you specified the file. |
This section contains the following topics:
A Text Load Set loads data in a text file in either fixed or delimited format into one or more Table instances in Oracle LSH.
During Text Load Set definition, you can upload a metadata file. The system reads the data structure from the file and creates a Table Descriptor with the same structure. You can load multiple metadata files zipped together and create multiple Table Descriptors at the same time.
During Text Load Set execution, you can include metadata files in a zipped file with data files. For each metadata file, the system creates a matching Table Descriptors if none exists or updates and synchronizes a Table Descriptor if it does exist, and loads data from the data file(s). The system identifies files with extension .mdd as a metadata definition file and treats other extensions as data files.
You can specify tolerance factors to determine whether or not to continue loading if errors occur. See "Text Load Set Parameters".
Note: All text files must use carriage returns between records so that each record appears on its own line. |
To ensure that Oracle LSH stores and displays special characters in your data correctly, convert text files that are not already using UTF8 encoding to UTF8 before uploading them to Oracle LSH.
To do this, copy the text in the text file into a a text editor such as Notepad or Wordpad and use the Save As option to select UTF-8 as the Encoding value.
The only attribute is the Save Input File? flag:
If set to Yes, the system saves the uploaded file as an output. This is especially useful when uploading data files with multiple date formats; see "Text Load Set Planned Outputs".
If set to No, and the file was uploaded from the user's desktop, the uploaded file is deleted after the load completes. This is the default value. If the file is uploaded from the operating system, it is not deleted.
Oracle LSH can read the data structure from a metadata (.mdd) file using a specified format (see Example 7-1) and define a Table Descriptor or read multiple metadata files zipped together to create multiple Table Descriptors. You can also define a target Table Descriptor manually; see "Adding a New Target Table Descriptor".
You can create multiple Table Descriptors by uploading a zip file containing multiple metadata files. The system unzips the file, identifies and reads the metadata files (.mdd extension) and ignores any other files. It uses the metadata information to either create new Table Descriptors or update and synchronize existing Table Descriptors as the case may be.
Creating a Metadata File
If the file has an .mdd extension, the system expects a set of Column attribute values, optionally preceded by a row identifying the delimiter and a row defining Table attribute values, each of which must begin with a key word. A row beginning with dashes is treated as a comment. For example:
--This is a comment.
Example 7-1 Text Load Set Metadata File
lsh_delimiter = , --This section is for the Table attributes lsh_table= DM,DEMOG Table,EMP,EMP,EMP,Staging with Audit,Yes,Yes,Blinded,target,yes,yes --The following are columnsComment Line 2 INITS,VARCHAR2,100,,inits,inits,2.,,inits,no,1 AGE,NUMBER,10,,2,age,age,2.,,age,yes,1 DOB,DATE,,,3,dob,dob,datetime.,,dob,yes,1,MM/DD/YY HH24:MI:SS
Delimiter The first row defines the delimiter used in the file. If not specified, Oracle LSH treats it as a comma delimited file. The delimiter row must begin with lsh_delimiter=
Table and Table Descriptor Attributes The second row lists the table attributes required in the file. The Table attribute row must begin with lsh_table=
If the second row is not present or contains null values, the system assumes that the filename (without extension) is the Table Name and follows the normal Oracle LSH default behavior for the attribute values. The attributes and their required order in the file are: Name, Description, Oracle Name, SAS Name, SAS Label, Process Type, Allow Snapshot?, Blinding Flag?, Blinding Status, SAS Library Name, Is Target?, Target as Dataset?
Some attributes have associated reference codelists and allow either the actual values for the associated reference codelist (RC) columns or the decode values defined in the "Meaning" attribute of the _RC lookup. For example, "select meaning from cdr_lookups where lookup_code='< RC>' so that YES or Yes or $YESNO$YES are acceptable values.
The table below outlines the applicable values for each attribute that has an associated reference codelist.
Note: Processing types that require audit keys are not supported. |
Table 7-1 Attributes with Reference Codelist Values
Attribute | Values |
---|---|
Processing Type |
Staging with Audit, Staging without Audit, Transactional High Throughput, Transactional without Audit |
Allow Snapshot |
Yes, No |
Blinding Flag |
Yes, No |
Blinding Status |
Blinded, Unblinded |
Is Target |
Yes, No |
Target as dataset |
Yes, No |
Data Type |
Date, Number, Varchar2 |
Nullable |
Nullable Yes, No |
Columns Subsequent rows must contain the column and variable attributes with each represented by a new row in the text file with attributes. The position is determined by the order in which the column /variable rows are processed. For example, in a comma delimited file: Name, Data Type, Length, Precision, Oracle Name, SAS Name, SAS Format, Description, SAS Label, Nullable, Default Value, Date Format
Normal Oracle LSH validation rules apply to the Column or variable attributes. The operation uses Oracle LSH default values if invalid values are provided for any of the attributes.
Text Load Sets include the Parameters listed below. You can change their default values in the Execution Setup definition. The user can change their values at runtime.
Note: Because these are predefined, required Parameters you should change only the values specified below or the Load Set may not function properly. Do not change any other properties of these parameters. |
Data File Name. Do not select a file when defining the Load Set. Oracle LSH uploads the file when you submit the Load Set's Execution Setup. If you upload the file during Load Set definition, the data will probably be outdated at the time the Load Set is executed. Therefore, leave this field blank. The user must enter the filename at runtime if Load From Server OS is set to No.
Data Format. Choose either Fixed or Delimited:
Fixed. The system uses the Load Set's target Table Descriptor Column definitions to interpret the data type and length of the values in the text file. The file must contain the correct number of characters for each value in each column of each record.
In the case of Table Descriptor Columns with a Number data type (with a fixed format file), if you data includes a sign (+/-), you must increase the length by one to accommodate the positive (+) or negative (-) sign. In addition, if the data includes numbers with decimal places, you must declare the precision and increase the length by one to accommodate the decimal marker. See "Defining Table Columns" for further information.
Delimited. With delimited records, you specify the character used in the source file between each record as the separator character. Different records in the same column can be of different lengths, up to the maximum allowed. The system loads the contents between the separator character into each consecutive Column of the target Table instance.
For example, if you have a Table instance with Columns Patient ID, Patient Initials, and Date of Birth, and the separator character was a comma, and the date format was DDMMYYYY, the first two records might look like this:
54602,EKP,04081949
66781,BAH,22011955
Delimiter Character. (Required for Delimited-format Load Sets only.) Specify the character(s) used as the value delimiter in the source text file. In the example above, the comma (,) is the separator character. The default character is the comma (,). You can use any two unicode characters as a delimiter. For example, to use tab as the delimiter character, enter \t
.
Note: Even if you specify that this is a fixed format Load Set, this Parameter is displayed. Leave the default value as is. |
Enclosing Character. (For Delimited-format Load Sets only; recommended but not required.) If any record value may contain the delimiter character, you need an enclosing character. Specify the character used to enclose each value. The default character is double quotation marks (").
For example, if a double quotation mark (") were the enclosing character, the same two records would look like this in the source text file:
"54602","EKP","04081949"
"66781","BAH","22011955"
You can use any single unicode character as an enclosing character.
Note: Even if you specify that this is a fixed format Load Set, this Parameter is displayed and is required. Leave the default value as is; the system does not use this value. |
Notes: Both delimiter and enclosing characters should be characters that never appear in the data content of the file.The delimiter character and the enclosing character must be different. If they are the same, the Load Set execution will fail. |
Initial Records to Skip. If you want to not load records at the beginning of the file, enter the number of records you want the system to skip. The default value is zero (0).
Maximum Allowed Errors. Tolerance factor; the maximum number of invalid rows you are willing to tolerate before the SQL*Loader stops the load process and marks the Load Set job as failed. The default value is 0.
Temp LOB ID. This Parameter is for internal use only. Do not modify it.
Date Format. Enter the date format used in the source data or .mdd file, if any. The date format you enter here must correspond exactly with the date field in the source text file, else the Load Set cannot execute correctly. Do not enter a value here if the source text file does not contain a date field.
Load From Server OS. You have the option to load a file from your local computer or from a remote system. Set this parameter to Yes (default is No) if your file is either on the Oracle LSH DP server or on another computer that has its drives NFS-mounted on the DP server computer. If you set this parameter to No, you must upload a text file from your local computer when running the Text Load Set job by providing its name in the Data File Name parameter.
Note: The system can load remote text files only from an Oracle LSH DP Server that is running the SQL*Loader service. |
Server OS Filename. If you set Load From Server OS to Yes, enter the absolute path of the remote file with the filename and extension, else leave this parameter blank.
Direct. If set to Yes (default is No), the system uses the direct path INSERT to load data from the text file into the Oracle LSH target Table. If the Table Descriptor mapped to the Text Load Set is not of the Transactional High Throughput processing type, the system loads data using the conventional INSERT and writes warning messages to the job log.
Note: See the Oracle Database Utilities Guide (part number B14215-01) for more information on this SQL*Loader Command-Line Parameter. |
(Optional) Rows. This parameter applies only when you set Direct to Yes.
It indicates the number of rows that SQL*Loader reads together at one time from the datafile before writing them to the target Table and committing to the database. If you leave this parameter blank, by default the system reads all the rows from the datafile and then writes the data to the target Table.
For conventional path loads, (if you set Direct to No), this parameter indicates the number of rows that SQL*Loader assigns to the bind array. If you leave this parameter blank, Oracle LSH calculates the number of rows.
Note: A very high or a very low value for Rows can adversely affect the system's performance. Enter a value only if you are absolutely sure that you need to change this.See the Oracle Database Utilities Guide (part number B14215-01) for more information on this SQL*Loader Command-Line Parameter. |
Drop and Recreate Indexes. If set to Yes (default), and if the value of the parameter Direct is set to Yes, the system drops all non-unique indexes before running a data-loading job and recreates the non-unique indexes after loading data into the target Tables. The system does not drop indexes if Direct is set to No.
Recoverable. This parameter applies only when you set Direct to Yes.
If set to Yes (default), the system can recover data in the event of a failure. If set to No, the data becomes unrecoverable because SQL*Loader does not maintain a redo log file for the data.
Note: For more information on the SQL*Loader UNRECOVERABLE clause at: Oracle® Database Utilities athttp://download.oracle.com/docs/cd/E11882_01/server.112/e16536.pdf |
The system automatically creates four Planned Outputs for Text Load Sets:
Text Loading Log File (.log). The system writes the job log into the log file.
Text Loading Control File (.ctl) The system generates the SQL*Loader control file and writes the SQL*Loader options to this file. This is useful when you use bulk loading.
Text Loading Error File (.err). The system writes each invalid record that got rejected while writing data into a target Table instance into this Planned Output.
Text Loading Input Data File. If you set the Save Input File attribute to Yes, Oracle LSH uses a Planned Output as a placeholder for the input file saved as an output during execution. If you set Save Input File to No, the system still creates the Planned Output but does not create the actual output when you run the Load Set.
If you upload a zipped file with Save as Input set to Yes, the system always saves it as TEXTLOAD.ZIP—it does not have its original name when saved.
Setting Save as Input to Yes when you are loading multiple zipped files is especially useful when uploading data files with multiple date formats. If the date formats are the same, the Save as Input setting is immaterial. If the date formats vary, and the Save as Input option is set to Yes, the system uses the date format specified in each metadata file.
You can classify the Planned Output and view the actual output through the Oracle LSH Outputs user interface, subject to normal Oracle LSH security rules. To view the contents of the file, you must download it and view it through SAS.
When you define an Execution Setup for a Text Load Set, be careful to give it a name and description that will enable a person submitting the Load Set for execution to understand which file should be loaded.
The file must contain data in a format that corresponds to the Table Descriptors defined for the Load Set, but the Table Descriptors are created as part of the Load Set definition, while the file can only be specified at runtime. Therefore, provide enough information in the Execution Setup Name and Description to make it clear which data this Load Set definition is intended to load.
In addition, do not enter a value for Data File Name or Server OS Filename in the Execution Setup definition. The system uploads the file at the time that you specify it. If you specify it during Load Set or Execution Setup definition, the data in the file may be out of date by the time the user runs the Load Set.
Note: It does not make sense to schedule a Text Load Set for repeated execution because it would use the same BLOB ID each time, reloading the same data.If you schedule a single run of a Text Load Set for a later time, it loads the data that was current at the time you specified the file. |
This section contains the following topics:
Oracle Clinical Stable Interface Load Sets give you access to the metadata of all Oracle Clinical tables that are part of Oracle Clinical's stable interface.
For information on Oracle Clinical tables and joins, request a copy of the Oracle Clinical Stable Interface Guide from Oracle Support. This documentation is available only to Oracle Clinical customers.
Click Update and enter a value for the following attribute:
Remote Location. Click the Search icon and select a source remote location/connection combination from the list of values.
Oracle LSH automatically creates Table Descriptors from the Oracle Clinical tables you specify.
To specify the Oracle Clinical tables to load, do the following:
Click Upload Table Descriptors.
Select one or more Oracle Clinical views to load.
Refer to the Oracle Clinical documentation for information on these tables. Functional information is in Creating a Study and Conducting a Study. Table structure information is in the Oracle Clinical Stable Interface Guide.
Click Apply. The system returns you to the Load Set screen and displays the Table Descriptors in the Table Descriptors subtab.
Map the Table Descriptors to Table instances. See "Mapping Table Descriptors to Table Instances" for instructions.
Oracle Clinical Labs Load Sets have the following runtime Parameter:
Remote Location. The user submitting the Load Set for execution must select his or her own remote location/connection combination (or a remote location with a shared connection) at runtime to ensure the proper security for the external database. Do not enter a default value or change any of the other Parameter settings.
The only Planned Output for any Oracle Clinical Load Set is a log file. See "About Load Set Planned Outputs" for further information.
Do not set the Remote Location Parameter in the Execution Setup. See "OC Stable Interface Tables Load Set Parameter".
For general information on defining Execution Setups, see "Creating, Modifying, and Submitting Execution Setups"
This section contains the following topics:
Data Extract (DX) SAS Views Load Sets allow you to make Oracle Clinical patient data available in Oracle LSH using the SAS DX Views your company has already defined in Oracle Clinical. There is no need to redefine the views in Oracle LSH.
You can load views from one Study Access Account or Study Set Access Account at a time.
Click Update and enter values for the following attributes:
Remote Location. Click the Search icon and select a source remote location/connection combination from the list of values.
Study Name. Click the Search icon and select the name of the Study or Study Set whose views you want. The system displays all studies that your connection has access to in Oracle Clinical.
Oracle Clinical Data Extract SAS Views Load Sets have the following runtime Parameters:
Remote Location. The user submitting the Load Set for execution must select this or her own remote location/connection combination (or a remote location with a shared connection) at runtime to ensure the proper security for the external database. Do not enter a default value or change any of the other Parameter settings.
View Type. From the list of values, select the Oracle Clinical Study or Study Set Access Account that maintains the views you want.
Note: Because this is a predefined, required Parameter you should not change anything except the default value in the Load Set definition, or the Load Set may not function properly. |
You can upload views from the Remote Location and study you specified. Oracle LSH uses the structure of the Oracle Clinical views to create the target Table Descriptor and its underlying Table definition.
When you install an OC SAS DX Load Set using the Install button on the Load Set screen, if the Load Set has no Table Descriptors, the system creates a target Table Descriptor for each active SAS DX View at the specified Remote Location for the specified Oracle Clinical study or study set. It also creates a matching Table instance in the current Work Area for each Table Descriptor and maps the matching Table Descriptor and Table instance.
The only Planned Output for any Oracle Clinical Load Set is a log file. See "About Load Set Planned Outputs" for further information.
Do not set the Remote Location Parameter in the Execution Setup. See "OC DX SAS Views Load Set Parameters".
For general information on defining Execution Setups, see "Creating, Modifying, and Submitting Execution Setups"
This section contains the following topics:
Oracle Clinical Data Extract (DX) Views Load Sets allow you to make Oracle Clinical patient data available in Oracle LSH using the Oracle DX Views your company has already defined in Oracle Clinical. There is no need to redefine the views in Oracle LSH.
You can load views, including union views, from one Access Account at a time.
You can either physically load patient records into Oracle LSH or use the Load Set as a pass-through view to Oracle Clinical; see "Oracle Tables and Views".
You can load views of any type—stable, snapshot, or current—that are maintained in the specified access account. However, you can see data in current Oracle Clinical DX Views in Oracle LSH only if you do not physically load the data into Oracle LSH.
Click Update and enter values for the following attributes:
Remote Location. Click the Search icon and choose a source database/connection combination from the list of values.
Study Name. From the list of values, select the name of the Study or Study Set whose views you want. The system displays all studies that your connection has access to in Oracle Clinical.
View Type. From the list of values, select the type of view you want to load; for example, Stable, Snapshot, or Current.
Use the Upload Table Descriptors function to specify the tables you want to load. The system generates a list of tables in the study in the location that you specified in the Attributes section.
When you install an OC DX Load Set using the Install button on the Load Set screen, if the Load Set has no Table Descriptors, the system creates a target Table Descriptor for each active Oracle DX View at the specified Remote Location for the specified Oracle Clinical study or study set. It also creates a matching Table instance in the current Work Area for each Table Descriptor and maps the matching Table Descriptor and Table instance.
Oracle Clinical Data Extract Oracle Views Load Sets have the following runtime Parameters:
Remote Location. The user submitting the Load Set for execution must enter his or her own remote location/connection combination (or a remote location and shared connection) at runtime to ensure the proper security for the external database. Do not enter a default value.
View Type. From the list of values, select the Oracle Clinical Study or Study Set Access Account that maintains the views you want.
Note: Because this is a predefined, required Parameter you should not change anything except the default value in the Load Set definition, or the Load Set may not function properly. |
The only Planned Output for any Oracle Clinical Load Set is a log file. See "About Load Set Planned Outputs" for further information.
Do not set the Remote Location Parameter in the Execution Setup. See "OC Data Extract Views Load Set Parameters".
For general information on defining Execution Setups, see "Creating, Modifying, and Submitting Execution Setups"
This section contains the following topics:
Oracle Clinical Design and Definition Load Sets load the metadata on which CRFs are based, including:
Data Collection Modules (DCMs)
Data Collection Instruments (DCIs)
Procedures
Copy Groups
Data Extract Queries and Templates
Click Update and enter values for the following attributes:
Remote Location. Click the Search icon and choose a source database/connection combination from the list of values.
Design Sub-System. Click the Search icon and select either Study Design or Study Definition. You can load tables from only one subsystem in a single Load Set. For a list of the tables you can load from each subsystem, see "OC Design and Definition Load Set Table Descriptors".
Oracle LSH automatically creates Table Descriptors from the Oracle Clinical tables you specify. The tables available depend on the subsystem you specified.
To specify the Oracle Clinical tables to load, do the following:
Click Upload Table Descriptors to specify the Oracle Clinical tables you want to load. Oracle LSH generates a list of tables in the study in the location that you specified in the Attributes section.
Select one or more Oracle Clinical tables to load. The choices available depend on which subsystem you selected as an attribute; see "Study Design Table Descriptors" and "Study Definition Table Descriptors".
Refer to the Oracle Clinical documentation for information on these tables. Functional information is in Creating a Study. Table structure information is in the Oracle Clinical Stable Interface Guide.
Click Apply. The system returns you to the Load Set screen and displays the Table Descriptors in the Table Descriptors subtab.
Map the Table Descriptors to Table instances. See "Mapping Table Descriptors to Table Instances" for instructions.
Study Design Table Descriptors If you set the Design Sub-System attribute to Study Design, you can load any of the following tables:
Study Definition Table Descriptors If you set the Design Sub-System attribute to Study Definition, you can load any of the following tables:
Oracle Clinical Design and Definition Load Sets have the following runtime Parameters:
Remote Location. The user submitting the Load Set for execution must select his or her own remote location/connection combination (or a remote location with a shared connection) at runtime to ensure the proper security for the external database. Do not enter a default value or change any of the other Parameter settings.
Study. The user submitting the Load Set for execution must specify the study from which to load data.
Note: Because this is a predefined, required Parameter you should not change anything except the default value in the Load Set definition, or the Load Set may not function properly. |
The only Planned Output for any Oracle Clinical Load Set is a log file. See "About Load Set Planned Outputs" for further information.
Do not set the Remote Location Parameter in the Execution Setup. See "OC Design and Definition Load Set Parameters".
For general information on defining Execution Setups, see "Creating, Modifying, and Submitting Execution Setups"
This section contains the following topics:
An Oracle Clinical Global Metadata Load Set loads your Oracle Clinical Global Library definitions and automatically converts them to Oracle LSH definitions as follows:
Oracle LSH loads all Oracle Clinical Questions and converts them to Oracle LSH Variables.
If a Question is associated with a Discrete Value Group (DVG) in Oracle Clinical, Oracle LSH converts the Question to a Parameter and converts its DVG values to a list of allowable values for the Parameter.
Oracle LSH loads all Oracle Clinical Question Groups and converts them to Oracle LSH Table definitions with Columns based on the Variables corresponding to each Question in the Question Group.
The first time you run an Oracle Clinical Global Metadata Load Set, the system creates an Oracle LSH Domain called "Oracle Clinical Global Libraries" and an Application Area for the particular Oracle Clinical Global Library Domain. The first time you run a Load Set for a different OC Global Library Domain, Oracle LSH automatically creates another Application Area for the new OC Global Library Domain.
Note: The use of the term "Domain" is confusing because Oracle LSH and Oracle Clinical use the term differently. The system creates an Oracle LSH Domain to contain all Oracle Clinical Global Library Domains. Within this Domain, Oracle LSH creates an Oracle LSH Application Area for each OC Global Library Domain. |
Each subsequent time you run a Load Set for a Global Library Domain, Oracle LSH add any new definitions and modifies any definitions that have been modified in Oracle Clinical. If an Oracle Clinical Question has been removed from a Question Group in Oracle Clinical, Oracle LSH removes the corresponding Column from the corresponding Table. However, Oracle LSH does not delete or retire any Oracle LSH object definitions if their corresponding Oracle Clinical object is retired.
Oracle Clinical Global Metadata Load Sets never delete any metadata because the definitions may be in use in Oracle LSH. The processing is the same in either Full or Incremental mode.
There are no attributes.
No Table Descriptors are required or allowed.
Oracle Clinical Global Metadata Load Sets include the Parameters listed below.
Remote Location. The user submitting the Load Set for execution must select his or her own remote location/connection combination (or a remote location with a shared connection) at runtime to ensure the proper security for the external database. Do not enter a default value or change any of the other Parameter settings.
Library Domain Name. From the list of values, select the name of the Oracle Clinical Global Library Domain that you want to load. Do not change any of the other Parameter settings.
The only Planned Output for an Oracle Clinical Global Metadata Load Set is a log file. See "About Load Set Planned Outputs" for further information.
Do not set the Remote Location Parameter in the Execution Setup. See "OC Global Metadata Load Set Parameters".
For general information on defining Execution Setups, see "Creating, Modifying, and Submitting Execution Setups"
This section contains the following topics:
Oracle Clinical Labs Load Sets load lab reference ranges and associated information from Oracle Clinical Labs-related tables (see "OC Labs Load Set Table Descriptors" for a complete list).
The Lab Assignment Criteria table is not included. You can load it separately if necessary, using the Oracle tables adapter.
Because Oracle Clinical Lab data tables are closely interrelated and are used in multiple studies, you must load all lab tables each time you run the Load Set. However, you can choose the Labs for which to load information.
Each execution of this type of Load Set retrieves the most recent creation or modification timestamp from the Lab tables in Oracle Clinical.
Click Update and enter a value for the following attribute:
Remote Location. Click the Search icon and choose a source database/connection combination from the list of values.
Do not define any Table Descriptors. The system automatically creates Table Descriptors for Lab tables. The Oracle Clinical tables loaded are:
Note: Do not change any of these Table Descriptors. They belong to the OC Labs adapter and any changes to them will make the adapter invalid causing Load Sets of this type to stop working. |
You must map the Table Descriptors to Table instances.See "Defining Table Descriptors" for instructions.
Oracle Clinical Labs Load Sets have the following runtime Parameters:
Remote Location. The user submitting the Load Set for execution must select his or her own remote location/connection combination (or a remote location with a shared connection) at runtime to ensure the proper security for the external database. Do not enter a default value or change any of the other Parameter settings.
Lab. The user submitting the Load Set for execution can specify the lab for which to load data.
Note: Because this is a predefined, required Parameter you should not change anything except the default value in the Load Set definition, or the Load Set may not function properly. |
The only Planned Output for any Oracle Clinical Load Set is a log file. See "About Load Set Planned Outputs" for further information.
Do not set the Remote Location Parameter in the Execution Setup. See "OC Labs Load Set Parameters".
For general information on defining Execution Setups, see "Creating, Modifying, and Submitting Execution Setups"
This section contains the following topics:
Oracle Clinical Randomization Load Sets load real or dummy treatment pattern information for Oracle Clinical studies. Randomization Load Sets load data from a single Oracle Clinical table: TREAT_ASSIGN_ALL_VIEW.
This table contains two separate sets of data. One set contains the actual treatment codes that reveal which patient is receiving which treatments. This information is normally blinded in Oracle Clinical, and cannot be loaded into Oracle LSH until it has been unblinded in Oracle Clinical. The second set of data is dummy data that Oracle Clinical generates randomly.
Oracle LSH automatically sets the Blinding Flag of the target Table instance to Yes.
The same Load Set instance can load either the real treatment codes or the dummy data. To load dummy data, no special security privileges are required. To load the real codes, the user running the Load Set must satisfy security requirements in both Oracle LSH and Oracle Clinical:
Oracle Clinical Blinding Security Security access to the real treatment codes within Oracle Clinical is controlled by the Randomization Access Status Code (RAND_ACC_STAT_TYPE_CODE). The Oracle Clinical Randomization Adapter checks the value of this code in Oracle Clinical. The value in Oracle Clinical determines whether the real data can be loaded or only the dummy data, and determines the value set for the Blinding Status of the target Table instance:
If the value is either Open or Release, the Load Set can load the real codes. The system sets the Blinding Status of the target Table instance to Unblinded.
If the value is Access, and the RXA_ACCESS.TREAT_ACCESS_STUDY view exists in the user account being used to connect to Oracle Clinical, then the Load Set can load the real codes. The system sets the Blinding Status of the target Table instance to Blinded.
If the user is trying to download the real codes and the RAND_ACC_STAT_TYPE_CODE value is anything other than Open, Release, or Access, the system raises an error and the load fails.
Blinding Security To run a Randomization Load Set on dummy data, an LSH user must have normal security access to the Load Set instance and to the target Table instance.
In addition, to load the real treatment codes, the user must also have special blinding-related privileges on the target Table instance within the same User Group through which he or she has security access to the Table instance:
If the data is currently blinded, the user must have Blind Break privileges.
If the data has been unblinded, the user must have Read Unblind or Blind Break privileges.
If the user has Unblind privileges, he or she can permanently unblind the data within LSH; see "Unblinding Table Instances". Even after data has been permanently unblinded, users must have Read Unblind privileges to view the data.
Oracle Clinical Randomization Load Sets have no attributes.
The system automatically creates a Table Descriptor (and its underlying Table definition) for the Oracle Clinical table TREAT_ASSIGN_ALL_VIEW.
Note: Do not change this Table Descriptor. It belongs to the OC Randomization adapter and any changes to it will make the adapter invalid. |
You must map the Table Descriptors to Table instances.See "Defining Table Descriptors" for instructions.
Oracle Clinical Labs Load Sets have the following runtime Parameters:
Remote Location. The user submitting the Load Set for execution must select his or her own remote location/connection combination (or a remote location with a shared connection) at runtime to ensure the proper security for the external database. Do not enter a default value or change any of the other Parameter settings.
Study. The user submitting the Load Set for execution must specify the study for which to load real or dummy treatment codes.
Treatment Data Type. If set to Dummy (the default value) the system loads the dummy data. If set to Real, the system loads the real treatment codes, if all security requirements are met.
The only Planned Output for any Oracle Clinical Load Set is a log file. See "About Load Set Planned Outputs" for further information.
Do not set the Remote Location Parameter in the Execution Setup. See "OC Randomization Load Set Parameters".
For general information on defining Execution Setups, see "Creating, Modifying, and Submitting Execution Setups"
This section contains the following topics:
Oracle Clinical Study Data Load Sets load study-specific non-patient data into LSH, including:
Discrepancies
Data Clarification Forms (DCFs)
Page tracking information
Patient status information
See "OC Study Data Load Set Table Descriptors" for a complete list of tables that this Load Set type can load.
Each execution of this type of Load Set retrieves the most recent data from the selected tables in Oracle Clinical.
Click Update and enter a value for the following attribute:
Remote Location. Click the Search icon and choose a source remote location/connection combination from the list of values.
LSH automatically creates Table Descriptors and their underlying Table definitions from the Oracle Clinical tables you specify. LSH stores the Table definitions in the current Application Area.
To specify the Oracle Clinical tables to load, do the following:
Click Upload Table Descriptors to specify the Oracle Clinical tables you want to load. The system generates a list of tables in the study in the location that you specified in the Attributes section.
Select one or more Oracle Clinical tables to load. The choices are:
Refer to the Oracle Clinical documentation for information on these tables. Functional information about Data Clarification Forms (DCFs), discrepancies, page tracking, and validated reported values is in Conducting a Study. Functional information on patient positions and statuses is in Creating a Study. Table structure information on all tables is in the Oracle Clinical Stable Interface Guide.
Click Apply. The system returns you to the Load Set screen and displays the Table Descriptors in the Table Descriptors subtab.
Map the Table Descriptors to Table instances. See "Mapping Table Descriptors to Table Instances" for further information.
Oracle Clinical Study Data Load Sets have the following runtime Parameters:
Remote Location. The user submitting the Load Set for execution must select his or her own remote location/connection combination (or a remote location with a shared connection) at runtime to ensure the proper security for the external database. Do not enter a default value or change any of the other Parameter settings.
Study. The user submitting the Load Set for execution must specify the study from which to load data.
Note: Because this is a predefined, required Parameter you should not change anything except the default value in the Load Set definition, or the Load Set may not function properly. |
The only Planned Output for any Oracle Clinical Load Set is a log file. See "About Load Set Planned Outputs" for further information.
Do not set the Remote Location Parameter in the Execution Setup. See "OC Study Data Load Set Parameters".
For general information on defining Execution Setups, see "Creating, Modifying, and Submitting Execution Setups"
You can install a Load Set instance directly from its Properties screen, using the Install button, or in its Work Area (see "Installing a Work Area and Its Objects").
When you install a Load Set instance using the Install button on its Properties screen:
The system checks in the Load Set instance and definition, and also the Table instances in the current Work Area to which the instance is mapped.
The system checks if the Load Set is installable. If not, the system performs Automatic Mapping by Name on any unmapped Table Descriptors. If the Load Set is still not installable and there are still unmapped Table Descriptors, the system creates Table instances in the current Work Area from the Table Descriptors and maps them.
In the case of Oracle Clinical DX Load Sets and Oracle Clinical SAS DX Load Sets, if the Load Set has no Table Descriptors, the system creates a target Table Descriptor for each active Data Extract View at the specified Remote Location for the specified Oracle Clinical study or study set. It also creates a matching Table instance in the current Work Area for each Table Descriptor and maps the matching Table Descriptor and Table instance.
The system attempts to install the Load Set instance and the Table instances to which it is mapped. The system displays a success or error message. If the installation fails, the error message displays the name of any objects that were not installable.
Note: If any of the mapped Table instances or the Load Set definition is not installable, the system cannot install the Load Set instance. See Appendix A, "Installation Requirements for Each Object Type" for the reasons these objects may not be installable. |
Log File To see the log file for the installation, you must go to the Work Area Installation screen, as follows:
Click the Applications tab. The main Application Development screen opens.
Click the name of the Work Area you are working in. The Work Area screen opens.
From the Actions drop-down list, select Installation History.
Click Go. The system displays the Installation History screen with the log files in chronological order.
Click the View Log link for the most recent installation attempt or for the date and time that you ran the install process. The system displays the log file.
For information on installation and on reading the log file, see "Installing a Work Area and Its Objects".
This section contains the following topics:
If you have the necessary privileges, you can modify a Load Set either through an instance of it in a Work Area or directly in the definition in its Domain or Application Area. In most cases it makes sense to work through an instance in a Work Area for the following reasons:
In order to use or test changes to the definition you must create and install an instance of it.
If you work through an instance, the system automatically repoints the instance to the new version of the definition.
However, if you need to change properties of the definition, you must work directly in the definition in its Domain or Work Area.
Whether you work in an instance or directly in the definition, when you check in the new version of the definition you have the opportunity to upgrade instances of the original definition to the new version; see "Upgrading Object Instances to a New Definition Version".
On the Load Set instance's Properties screen, click Update to enter changes. Oracle LSH creates a new version of the instance you are working on and applies your changes to it when you click Apply. Click Cancel to discard your changes and the new version.
You can modify some properties through the Actions drop-down list; see "Using the Actions Drop-Down List" for further information.
Note: You must reinstall the Load Set for the changes to take effect. |
You can modify the following:
Name See "Naming Objects" for further information.
Description See "Creating and Using Object Descriptions" for further information.
Definition Source This field applies to the instance only. It specifies the Load Set definition to which this Load Set instance points. It generally does not make sense to change the source definition for the following reasons:
Changing the definition may result in a new set of Table Descriptors, Parameters, and Planned Outputs.
Any new Table Descriptors are not mapped.
The Load Set's status changes to Non Installable.
If you want to change to a new version of the same definition, use the Upgrade Instance option from the Actions drop-down list.
You can go to a Load Set definition's Properties screen in one of the following ways:
From the Load Set's Properties screen: Click the hyperlink of the Load Set definition that appears in the Definition field. See "Definition".
From the Domain or Application Area where you created the definition: Click Manage Definitions to view all the definitions in that Domain or Application Area. Click the definition name.
Once on the Load Set definition screen, click Update to enter changes. Oracle LSH creates a new version of the definition. You can change the following properties:
Name See "Naming Objects" for further information.
Description See "Creating and Using Object Descriptions" for further information.
You can modify some properties through the Actions drop-down list; see "Using the Actions Drop-Down List" for further information.
You cannot modify Load Set Table Descriptors because they must be identical to the external table or data set on which they are based. However, for most Load Set types you can add or remove Table Descriptors.
You can change the Table Descriptor mappings, which are part of the Load Set instance, not the definition. You do not need to check out the definition to modify the mappings. This may be useful if you want to load data into a standard Oracle LSH Table instance whose name or Column names differ from the source Table or data set.
Table Descriptors belong to the Load Set definition.
You cannot add or remove Attributes or Parameters because they are predefined for each Load Set type.
You can change some Parameter values in one or more Execution Setups. Select Execution Setups from the Actions drop-down list in the Load Set instance in the Work Area. See "Creating, Modifying, and Submitting Execution Setups".
You cannot add or remove Planned Outputs because they are predefined for each Load Set type.
You can change the Planned Outputs' classifications, which affect the classifications of the actual outputs. See "Classifying Outputs" for further information.
Planned Outputs belong to the Load Set definition.