Batch Loading Lab Ranges

Oracle Clinical accepts lab range data in batch mode. Batch loading data is more efficient and less prone to introducing new errors for large quantities of data than manually entering data. Loading of lab ranges is for new lab ranges only; you cannot perform updates of this data using the batch loader.

Put the input file into the directory specified in the LAB_ROOT setting of the OCL_STATE local reference codelist. If LAB_USERDIRS in the same codelist is set to Y, copy the file into the user-specific subdirectory for the user who will run the job. See the Oracle Clinical Administrator's Guide for more information.

This section explains the processes:

• Loading Data
  
• Validating Data
  
• Transferring Data
  
• Executing the Process
  
• Running the Lab Procedures Re-execution Pre-Process Batch Job
  

Loading Data

The goal of the data load stage is to verify that a value is present for each mandatory key column. There are no checks for the validity of the data at this stage; only that it exists.

The system loads the data into a temporary table, TEMP_RANGES. If there are existing rows for the lab being loaded, then it aborts and displays a message, "Another Batch Data Load in Progress for this lab <Lab Name>." The input to the process is the lab name, the full pathname to the data file, the domain name, and the delimiters used for the data, which are entered using a form described in detail in Executing the Process.

After all validations are complete, the entire data file is loaded into TEMP_RANGES. If there are errors, the status code in the LAB_DATA_FILES table is updated to BAD LOAD, but rows without errors are inserted in TEMP_RANGES.

For more information, see:

• File Format
  
• Example
  

File Format

The data file you load must be in a standard format, including the following columns in this order: Lab Name, Lab Range Subset Code, lab test Question, Sex, Minimum Age, Maximum Age, Minimum Value, Maximum Value, Lab Unit Name, Effective Start Date, Effective End Date, Minimum Age Unit, and Maximum Age Unit.

You can use whatever delimiter you want in the file and specify it in the execution window.

Example

The delimiter here is a comma (,):

CENTRAL,,SODIUM,F,10,20,-5,10,u/ml,,,DAYS,DAYS

The two commas after the Lab Name and two commas after the Lab Unit Name mean that the Lab Range Subset, Effective Start Date and Effective End Date are not provided. Leading and trailing blanks must be stripped before insertion into the TEMP_RANGES table.

Validating Data

During preparation stage the system performs the following validations on the data loaded into the temporary table:

• Checks input data against existing database definitions for duplicate rows for:

• • Lab Name

  • Question Name

  • Lab Unit

• Performs lab range validations to check:

  • Relative values of the age fields—maximum_age should be greater than the minimum_age

  • Overlap in gender fields

  • Date range; with the last row having an open Effective End Date

  • minimum_value and maximum_value

    • These parameters can be zero, a positive value, or a negative value.

    • maximum_value should be greater than minimum_value, if both the values are not null.

After the error checking, the system produces a printable report of the results of the load and prepare stages, indicating the number of erroneous records and providing the PSUB job ID, which can be queried in the Batch Jobs window.

Transferring Data

Following checks of the TEMP_RANGES table for validation errors and duplicate rows, the permanent lab ranges database tables are populated from the rows in the TEMP_RANGES table. If no duplicate rows exist then all the rows from the TEMP_RANGES are inserted into the RANGES table. Duplicate rows are handled as follows:

• If you have the same lab range (combinations of key values, i.e. Lab, Question, Gender, minimum_age, maximum_age) and different minimum value or maximum value, update the record with the new minimum or maximum value, or both.
• If the audit has to be recorded in the range_comment column, then the values of previous values and the updated values can be shown in the comment.
• If the audit is not being recorded in the range_comment column, then the range_comment can also have a different value and can be updated.
• If you have the same key combinations (Lab, Question, Gender, minimum_age, maximum_age) and different Effective Start and End Dates, rearrange data according to the rules to accommodate the new effective dates.
• If your date ranges overlap in a record, rearrange all rows for that key set.

For more information, see:

• Example
  

Example

If the existing rows are as follows:

Question, Gender, Min.Age, Max.Age, Effective Start ,Effective End

• QUESTION ,B ,10 ,20 ,19980110 ,19980209
• QUESTION ,B, 10, 20 ,19980310 ,19980409

adding the following new row, which comes in with the same combination of keys except for the minimum age and maximum age,

Question ,Gender, Min.Age, Max.Age, Effective Start, Effective End

QUESTION, B ,10 ,20, 19980210 ,19980309

results in the following final set of rows:

Question ,Gender ,Min.Age ,Max.Age ,Effective Start ,Effective End

• QUESTION, B, 10 ,20, 19980110, 19980209
• QUESTION ,B ,10, 19980210 ,19980309
• QUESTION ,B ,10, 20, 19980310, 19980409

Executing the Process

You set parameters and execute batch data loads of lab range data from within Oracle Clinical. To execute a batch data load:

1. From the Labs menu, select Lab Batch Data Load, and Lab Ranges Batch Data Load.
2. Complete the fields as follows:

   Run Error Check Only (Y/N)? specifies whether to run a preliminary error check on the file (Y) or execute the full batch load (N).

   Lab Data File Name provides the full pathname to the data file. The file must be in the directory specified in the LAB_ROOT setting of the OCL_STATE local reference codelist. If LAB_USERDIRS in the same codelist is set to Y, copy the file into the user-specific subdirectory for the user is submitting the job. See the Oracle Clinical Administrator's Guide for more information.

   Column Delimiter used in the Data File identifies the delimiter used in the file to separate data elements. There are no restrictions in choosing this delimiter.

   Domain Name identifies the domain of the lab test Questions.

   Default Effective Dates (Y/N)? specifies whether the program should set the effective dates (Y) or if you plan to specify them manually in Oracle Clinical (N).

3. Click Submit Job.

The program produces a detailed output file, providing a complete listing of the events that occurred during execution. If the process completes successfully, it places a new row in the RANGES table.

Running the Lab Procedures Re-execution Pre-Process Batch Job

If you change any part of a lab range definition that has been used against production data, you must immediately execute the PSUB job by selecting Lab Procedures Re-Execution Pre-Process from the Lab menu. If you do not run this job, your changes will not be applied to existing data.