LabLynx KB:How data archiving works

From LIMSWiki
Revision as of 20:43, 12 October 2011 by Shawndouglas (talk | contribs) (Created article.)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigationJump to search

Data archiving in ELab

Here is a semi-technical overview of how data archiving works in ELab.

Currently in the Oracle ELab database we have several archive tables. They are:

  • PIM_ResultArchive
  • ELB_LimitArchive
  • ELB_AttributeArchive
  • LIM_BatchArchive
  • GEN_ResultArchive

(Note: Some of these tables only exist in version LLX-O-6.10)

These tables are used to store historical data usually mapped in from previously used systems. These tables also get populated on a regular basis with current LIMS data This set of tables provides one place to go to retrieve data for reporting purposes.

When sample results are completed they are flagged to be migrated to the result archive tables. (This action takes place in the LIM_TSTRP_STATUS_AFT_UPD trigger on the LIM_SAMPLETESTRESULTPROCESSES table.) The flagged field is the BinaryArchiveStatus field in the LIM_STRRDetail table. This flag gets a number that comes from the following query:

Select MODULEBINARY From ELB_PKL_ModuleTypes Where ModuleID = 0

This number represents all modules (ModuleID = 0). Each module in the system has its own unique modulebinary number. If BinaryArchiveStatus matches the result from the query above, the result is ready to be migrated to the archive tables. As results are moved to the archive tables, the respective modulebinary number is then subtracted from the BinaryArchiveStatus field in the LIM_STRRDetail table for that result record. Follow the example below.

Let's say the value from the above query equals 524287. We have results in the LIM_STRRDetail table that have that same number in the BinaryArchiveStatus field. When it is time to archive PIMS data, we look through the LIM_STRRDetail table for records that have a BinaryArchiveStatus containing the binary number 512. This is the binary number that represents the PIMS module. The value 524287 contains 512 so those records will be archived. If the record has never been archived before, all relevant data will be inserted in to the archive tables. If the record was previously archived, the procedure will update all the data for that record in the archive tables.

Once the record in LIM_STRRDetial has been archived the BinaryArchiveStatus for that record is then modified. In this case we will subtract 512 from 524287 to get 523775. The BinaryArchiveStatus for the record that was just archived will be updated to 523775. This way when the archiving procedure for PIMS is fired again it will exclude that record.

Archiving procedures

As of version LLX_O_6.10, LabLynx has two archiving procedures: one for GEN and one for PIMS. These procedures can be copied and modified to meet each client's needs. As the procedures are fired, they preform the tasks mentioned above in the example. They may also populate the LIM_BatchArchive, ELB_LimitArchive ,and ELB_AttributeArchive tables.

When does the archiving take place? As of version LLX_O_6.7, the firing of the different modules' archiving procedures was handled via an Oracle JOB set up under LabLynx supervision. As of version LLX_O_6.10, the option exists for real-time archiving of results. For this to work a specific client configuration must exist for configuration type "Triggered Event - On Result Complete (Archive)" (CONFIGTYPEID= -77). This configuration will need to be a SQL script that fires the client's specific archive procedure or the LabLynx generic archive procedure.

How does the system flag a record to be archived? Records will be flagged for archiving when they are approved and moved into the "Final Results" edit process. Results that have previously been approved and now reside in the "Final Results" edit process will be reflagged for archiving if they are reapproved. So users can rebatch samples that are in the "Final Results" edit process, modify those results, then approve them; the records will then be reflagged for archiving.

Related questions