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 8-1 Process of Creating a Data Mart Definition and Instance
A Data Mart exports all the data in a set of Oracle Life Sciences Data Hub (Oracle LSH) Table instances to one or more files for the purpose of recreating Oracle LSH data in an external system in a verifiable and reproducible manner. A Data Mart is an Oracle LSH primary executable object whose data file output is also called a Data Mart.
You can use Data Marts for many purposes, including:
submitting data to a regulatory agency
exporting a set of Oracle database tables to another system for data mining
sharing data with a partner organization
long-term data storage in text (or other) format
To include data from a Table instance in a Data Mart output, you create a Table Descriptor and map it to the Table instance. You can include data from any number of Table instances in a single Data Mart. As with other executables you can run a Data Mart on current data or on a snapshot, depending on settings in its Execution Setup.
Data Mart Types You can create Data Marts in the following formats:
Text Export
Text Fixed Format
Text Delimited Format
SAS Export
SAS Data Sets
SAS Transport (CPORT or XPORT)
Oracle Export
Execution You define Execution Setups for Data Marts the same way you do for other Oracle LSH executables. See "Creating, Modifying, and Submitting Execution Setups" for further information.
Reports on Data Mart Definitions and Instances From the Actions drop-down list, you can generate reports that provide information on a Data Mart definition or instance; see Chapter 14, "System Reports" for information.
When you create a Data Mart in a Work Area, you are actually creating an instance of a Data Mart definition.
To create a new Data Mart instance:
In a Work Area, select Data Mart from the Add drop-down list.
Click Go.
The system displays the Create Data Mart screen.
Choose one of the following options:
Create a new Data Mart definition and instance. Choose this option if no Data Mart definition exists that can meet your needs, either as it is or with some modification.
Create an instance from an existing Data Mart definition. Choose this option if a Data Mart definition already exists that meets your needs.
If you can adapt an existing Data Mart 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 Data Mart definition and instance in the Create Data Mart screen, additional fields appear.
Enter values in the following fields:
Name. See "Naming Objects".
Description. See "Creating and Using Object Descriptions".
Data Mart Type. The options are Oracle Export, SAS Export, and Text Export. See "Defining Different Types of Data Marts" for further 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 Data Mart.
The system opens the Properties screen for the new Data Mart instance.
See the following instructions:
If you create an instance of an existing Data Mart, its Table Descriptors and Attribute and Parameter settings are already defined. See "Creating an Instance of an Existing Definition" for instructions.
After you have created the Data Mart 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 8-1, "Process of Creating a Data Mart Definition and Instance".
See "Modifying Data Marts" for information on modifying Data Marts.
If you are working in a Work Area, you see the properties of both the Data Mart instance and the Data Mart 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 Source This field specifies the Data Mart definition to which this Data Mart instance points. See "Definition Source".
You can upgrade to a new version of the same definition. See "Upgrading to a Different Definition Version from an Instance".
Validation Status This field displays the current validation status of the Data Mart 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 Data Mart: Installable or Non Installable. See Appendix A, "Installation Requirements for Each Object Type".
Version This field displays the current version number of the Data Mart instance.
Version Label This field displays the version label, if any, for the current Data Mart instance version.
For further information on object versions, see "Understanding Object Versions and Checkin/Checkout".
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 Data Mart instance is pointing to the latest version of the Data Mart definition. If set to No, this Data Mart instance is pointing to an older version of the Data Mart definition.
Checked Out By This field displays the name of the person who has the Data Mart 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.
Data Mart Type This field displays this Data Mart definition's type: Text Export, SAS Export, or Oracle Export. See Defining Different Types of Data Marts.
Validation Status This field displays the current validation status of the Data Mart 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 Data Mart 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 Data Mart: Installable or Non Installable. See Appendix A, "Installation Requirements for Each Object Type".
Export File Name This attribute applies to all Data Mart types. It is the name of the file where the output of the Data Mart gets stored. This is the only attribute for SAS and Oracle Export types of Data Marts. See "SAS Data Mart Attribute", and "Oracle Export Data Mart Attribute". Also see "Text Data Mart Attributes".
Mode This attribute applies only to Text Data Marts. It is the mode used to create the text export file: Fixed or Delimited. See "About Text Data Marts" for an explanation of the two modes.
File Name Extension This attribute applies only to Text Data Marts. A Text Data Mart output may be of .txt (Fixed mode) or .csv (Delimited mode) type. See "Text Data Mart Attributes".
You cannot add or remove attributes or Parameters. They are predefined for each type of Data Mart. You can change their values in the Data Mart definition, but not in the Execution Setup.
To change attribute values:
Click the Update button in the Data Mart Attributes section of the screen.
Modify attribute values as necessary. Most attributes have an allowed list of values. Use the Search icon to select one.
Click Apply.
For information on the attributes and Parameters specific to each type of Data Mart, see:
From a Data Mart instance in a Work Area, you can use the following buttons:
Install Click Install to install the Data Mart instance, including any mapped source Table instances in the same Work Area; see "Installing Data Mart Instances". For a list of reasons a Data Mart instance may not be installable, see Appendix A, "Installation Requirements for Each Object Type".
Submit Click Submit to run the Data Mart instance. Before you can run the Data Mart, 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 Data Mart instance properties. See "Modifying Data Mart Instance Properties". You can also edit Data Mart Attributes by clicking the Update button in the Data Mart Attributes section of the screen. See "Setting Data Mart Attribute Values".
Check In/Out and Uncheck Click these buttons to check out, check in, or uncheck the Data Mart definition. Different buttons are displayed in the Data Mart 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".
For all Data Mart types, you specify the data to include in the output file by adding one or more source Table Descriptors to the Data Mart and mapping each one to a Table instance whose data you want to export to the file. Depending on the type of Data Mart, the system may create one file per Table Descriptor or one file for the Data Mart as a whole; see "Defining Different Types of Data Marts".
Data Marts can have only source Table Descriptors. They write to files, not tables.
Oracle LSH automatically creates the Planned Outputs for a Data Mart based on the Data Mart type and on the source Table Descriptors you specify. If you do not want to include all the Columns in a source Table instance in the Data Mart output, delete the unnecessary Columns from the Table Descriptor mapped to that Table instance.
For instructions, see "About Table Descriptors" and "Mapping Table Descriptors to Table Instances".
You cannot add or remove Parameters. They are predefined for each type of Data Mart. You can change their value and other settings either in the Data Mart definition, in the Execution Setup definition, or allow the user to set their value in the Execution Setup at runtime.
To change Parameter values and other settings in the Data Mart definition:
Check out the Data Mart definition if it is not already checked out.
In the Parameters subtab, click the hyperlink in the Name column of the Parameter you want to modify.
Click Update.
Modify the following attribute values if necessary:
Note: Because these are predefined and required Parameters you should not modify any attributes other than the following or the Data Mart may not function properly. |
Read Only. To prevent users from changing the value of this Parameter at runtime, check Read Only. To allow users to change the value at runtime, leave unchecked.
Prompt. The prompt is the label displayed for the Parameter in the Execution Setup. You can change it if you want to.
Default Value. You can change the default value in the Parameters List of Values. If you also make the Parameter Read Only, the default value becomes the value used at runtime.
Click Apply.
For information on the attributes and Parameters specific to each type of Data Mart, see:
When you first create a Data Mart definition, the system creates one Planned Output for the .log file and one or more others, depending on the Data Mart type. The Filename attribute value determines the output file names. The system adds the extension appropriate for the Data Mart type.
In addition, for SAS Dataset mode and Text Data Marts, when you add a Table Descriptor to the Data Mart, the system creates a Planned Output whose file name is the Table Descriptor name (in lowercase) plus the extension appropriate for the mode:.sas7bdat or, for Text Data Marts, .txt or .csv.
You can modify the classification of a Planned Output, which affects the classification of the corresponding actual Data Mart output; see "Classifying Outputs".
At runtime the system creates only the outputs appropriate for the mode and other settings. See:
Oracle LSH automatically creates Attributes, Parameters, and Planned Outputs for Data Marts based on the Data Mart type.
This section includes information on the Parameters and Planned Outputs for each Data Mart type:
This section includes the following topics:
There are two modes of Text-type Data Marts: Fixed and Delimited. Both modes produce one file for each Table Descriptor, named Data_Mart_name.txt or .csv. You can combine all the files in a single .zip file; see "Zip Result Flag".
Fixed-format Data Marts use more space than delimited-format Data Marts but result in faster performance.
Fixed Format Text Data Marts For a fixed-format Data Mart, Oracle LSH automatically uses the length defined for each Column of the Table Descriptor to determine the number of characters to dedicate to each column value in the output file.
For Columns of data type Varchar2, the number of characters dedicated to a column in the output file equals the defined Column length.
For Columns of data type Number, the system uses the Column length plus one character for the plus (+) or minus (-) sign and, if the Column's number is defined as having precision, an additional character for the decimal point (or other decimal marker).
For Columns of data type Date, the system uses the date format string that is part of the Column definition or, if no format string is defined, uses 20 characters to accommodate the default format, DD-MON-YYYY/HH24:MI:SS.
When the system generates the file, it inserts the actual value for each row and then adds the number of spaces necessary (if any) to reach the number of characters dedicated to that Column. At the end of each record, the system inserts the carriage return appropriate for the operating system.
Delimited Format Text Data Marts For a delimited-format Data Mart, Oracle LSH inserts a character as a delimiter after every column value for every record (except the last) as well as a carriage return at the end of each record. You must specify the character to be used as the delimiter. In addition, you can specify a different character to be used as an enclosing character around each character and date column value, and specify whether or not the system should actually use the enclosing character; see "Text Data Mart Parameters" for further information.
Text Data Marts have the following predefined attributes. To change their values, click Update in the Attribute section of the screen.
Export File Name Enter the name you want to give the zipped file that holds the individual text files. The default value is text_dm.zip
. You must enter lowercase text. If you remove the .zip as part of the value, the system adds it.
Note: The system produces this zipped file only if at runtime the value of the Zip Result Flag Parameter is either Zip or Both. |
Mode Click the Search icon and then select either Fixed or Delimited. See "About Text Data Marts" for an explanation of the two modes. The default value is Delimited.
File Name Extension Click the Search icon then select an extension for the file that will contain the actual data. Select .txt for fixed mode Data Marts and .csv for delimited mode.
Oracle LSH automatically creates Parameters for Text-type Data Marts. For information on how you can modify them, see "Setting Data Mart Parameter Values".
Note: Because these are predefined, required Parameters you should change only the default value, Read Only setting, or Prompt for each Parameter or the Data Mart may not function properly; see "Setting Data Mart Parameter Values". |
Zip Result Flag Select one of the following values as the default:
No. If set to No, the system does not generate a zipped file. The Data Mart output consists of one text file for each Table Descriptor and the log file. The system disregards the defined Export Filename.
Zip. If set to Zip, the system generates a single zipped file that includes the text files generated for each Table Descriptor and the log file.
Both. If set to Both, the system generates a text file for each Table Descriptor, creates a zipped file that contains all the text files and the log file, and also leaves a copy of each text file and the log file outside the zipped file.
The default value is Zip.
FirstRow Desc If you are creating a fixed-format Text Data Mart, you must enter a value for this Parameter, which determines the way the Data Mart handles the first row for each Table Descriptor.
Yes. If set to Yes, the system inserts the Column names as the first row in the text file for each Table Descriptor. If the Column name is too long for its length, the system truncates it. If it is a different format (for example, a Column name in text for a data type Number Column), the system inserts a warning in the Data Mart log file.
No. If set to No, the system starts the file with the data of the first record, not the Column names.
The system ignores this value for delimited text Data Marts.
Operating System Select your operating system: UNIX or MS Windows. The system inserts the appropriate carriage return for the operating system at the end of every record. If you are using Linux, select UNIX. The default value is UNIX.
Delimited-Format Text Data Mart Parameters
If you are creating a Delimited-format Text Data Mart, the following Parameters must each have a value. The system ignores these values for fixed-format text Data Marts. See Example 8-1, "Delimited Text Export Data Mart with Separator Character Only" and Example 8-2, "Delimited Text Export Data Mart with Separator and Enclosed Characters" for further information.
Separator Character The system appends the separator character (delimiter) to every column value of every row (except the last value, where there is a carriage return instead) in order to clarify where one value ends and the next one begins.
The separator character should not appear as part of any column value of any record. The default value is a comma (,). You can enter any two characters. For example, to specify tab as the separator character, enter \t
.
Enclosing Character The system prepends and appends the enclosing character to every character and date column value of every record. The character should be one that does not appear as part of any column value of any record. The default value is a double quotation mark (").
Note: The delimiter character and the enclosing character must be different. If they are the same, the Data Mart execution will fail. |
Use Enclosing Character? Select Yes or No.
Yes. If set to Yes, the system encloses every column value with the character you specify in the Enclosing Character Parameter.
No. If set to No, the system does not use the enclosing character.
Example 8-1 Delimited Text Export Data Mart with Separator Character Only
If your Table Descriptor has Columns Patient ID, Patient Initials, and Date of Birth, 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
Oracle LSH creates one Planned Output for each Table Descriptor in the Data Mart definition. At runtime, the system generates one .txt or .csv file for each Planned Output. The file type is determined by the value of the File Name Extension attribute.
Depending on the value of the Zip Result Flag Parameter, the system may also create a Planned Output for a zipped file to contain all the files. The file name for this output is filename_attribute_value.zip.
The system also creates a Planned Output for the log file, named text_dm.log.
You can classify a Planned Output in order to classify the actual output when it is generated; see "Classifying Outputs" for further information.
This section includes the following topics:
Oracle LSH supports the following types, or modes, of SAS Data Marts, when you have purchased and installed SAS with Oracle LSH:
SAS CPORT A SAS CPORT Data Mart consists of a single .cport file containing data from all the Table instances mapped to the Data Mart's Table Descriptors.
SAS XPORT A SAS XPORT Data Mart also consists of a single .xpt file containing data from all the Table instances mapped to the Data Mart's Table Descriptors.
Note: For SAS XPORT format's compatibility with SAS v6, Oracle LSH truncates long Table Column names (variables in a SAS data set) to 8 characters. Also, because of a limitation in SAS v6, you must ensure that Table instances mapped to a SAS XPORT Data Mart do not contain character data longer than 200 characters, to prevent the Data Mart's execution from failing. |
SAS Data Sets A SAS Dataset Data Mart includes one .sas7bdat file for each Table Descriptor. If you set the Zip Results Parameter to Zip, all the .sas7bdat files and the .log file are included in a single .zip file.
SAS Data Marts have one attribute: Filename. Enter a descriptive name. The system gives this name to the Planned Output and to the actual Data Mart output file. Do not add a file extension.
Note: This attribute must have a value. The Data Mart is not installable without a file name. |
Oracle LSH automatically creates Parameters for SAS Data Marts. You can change the values of the following required Parameters only in the Data Mart definition:
Mode Select the mode that corresponds to the type of output you need: CPORT, XPORT, or Dataset. The default value is CPORT.
Zip Results The Zip Results Parameter setting has an effect only for SAS Data Marts of the Dataset mode. CPORT and XPORT Data Marts cannot be zipped.
For SAS Dataset Data Marts, select one of the following values:
No. If set to No, the system does not generate a zipped file. The Data Mart output consists of one sas7bdat file for each Table Descriptor, plus the log file.
Zip. If set to Zip, the system generates a zipped file that includes the sas7bdat file generated for each Table Descriptor and the log file. The system also generates and uploads the individual SAS data sets for each Table Descriptor as separate outputs.
The default value is Zip.
Oracle LSH includes a predefined Planned Output for the .log file and for each SAS Data Mart mode. At runtime the system creates only the outputs appropriate for the mode and, in the case of Dataset Data Marts, the Zip Results Parameter. The names and file names for these Planned Outputs are:
CPORT: SAS DM.cport (name), filename_attribute_value.cport (file name)
XPORT: SAS DM.xpt (name), filename_attribute_value.xpt (file name)
Zipped Dataset. SAS DM.zip (name), filename_attribute_value.zip (file name)
Dataset (zipped or not): Both the name and filename are table_descriptor_name.sas7bdat.
All file names are in lowercase.
You can classify a Planned Output in order to classify the actual output when it is generated; see "Classifying Outputs" for further information.
This section includes the following topics:
Oracle LSH generates a command file for the Oracle Export utility to produce (in Table mode) a single Oracle Export file containing the data and metadata of all the Table instances mapped to the Table Descriptors of the Data Mart. The names of the export tables are the same as their corresponding Table Descriptors.
You can send Oracle Export Data Marts only to another Oracle database. To import its data, the external Oracle database must use the Oracle Import utility.
The Oracle Export file created by the Data Mart holds the tables in alphabetical order.
See Oracle® Database Utilities 11g Release 2 (11.2), at http://download.oracle.com/docs/cd/E11882_01/server.112/e16536.pdf
.
There is only one attribute: the file name of the actual Data Mart output. The default name is: oracle_export_dm.dmp. You can change this to a more meaningful name by clicking Update and entering the name you prefer. If you remove the .dmp file extension, the system adds it.
The Oracle Export Utility requires the following parameters. More information is available in the Oracle Export Utility documentation.
Note: Because these are predefined, required Parameters you should change only the default value, Read Only setting, or Prompt for each Parameter or the Data Mart may not function properly; see "Setting Data Mart Parameter Values". |
Compress Should Oracle Export export data into a single extent? The default value is Y.
Statistics Specifies the type of database optimizer statistics to generate when the exported data is imported. Options are ESTIMATE, COMPUTE, and NONE. The default value is ESTIMATE.
For Oracle Export Data Marts, the system creates one Planned Output for the whole Data Mart to contain data from all Table instances mapped to the Data Mart instance's Table Descriptors. The Planned Output name is always Oracle Export DM Export File. The file name is filename_attribute_value.dmp.
The system also creates a Planned Output for the log file. All Oracle Export log files have the same name: oracle_export.log.
You can classify a Planned Output in order to classify the actual output when it is generated; see "Classifying Outputs" for further information.
You can install a Data Mart 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 Data Mart instance using the Install button on its Properties screen:
The system checks in the Data Mart instance and definition and also the Table instances in the current Work Area to which the instance is mapped.
The system attempts to install the Data Mart 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 the Data Mart definition or any of the Table instances to which the Data Mart is mapped is not installable, the system cannot install the Data Mart 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 Data Mart definition 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 as a whole, 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 Data Mart 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 install the new version for the changes to take effect. |
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 Data Mart definition to which this Data Mart 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 Data Mart'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.
Note: It is possible to select a new source definition of a different Data Mart type. Exercise caution when selecting a different definition. |
You can go to a Data Mart definition's Properties screen in one of the following ways:
From the Data Mart's Properties screen: Click the hyperlink of the Data Mart definition that appears in the Definition field. See "Definition Source".
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 Data Mart 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.
In the Data Mart definition you can remove existing Table Descriptors and add different ones.You must check out the definition to add, remove, or update Table Descriptors, but not to map, unmap, or remap Table Descriptors.
In the Data Mart instance, you can change the Table Descriptor mappings, which are part of the Data Mart instance, not the definition; see "Mapping Table Descriptors to Table Instances". You do not need to check out the definition to modify the mappings.
You cannot add or remove Attributes or Parameters because they are predefined for each Data Mart type. Attributes and Parameters belong to the Data Mart definition. You must check out the definition to update Parameters. In the Data Mart definition you can change Attribute values and, for Parameters, the default value and Read Only and Prompt settings. You should not change anything else or the Data Mart may not function properly.
To change Parameter values at the instance level you can use the Execution Setup. To make your settings available to other instances of the same Data Mart, allow the Execution Setup to be used as a Template. Select Execution Setups from the Actions drop-down list in the Data Mart instance in the Work Area. See "Creating, Modifying, and Submitting Execution Setups".
Planned Outputs are predefined for each Data Mart type and belong to the Data Mart definition. You cannot modify Data Mart Planned Outputs except to change their classifications, which affect the classifications of the actual Data Mart output. See "Classifying Outputs" for further information.