Brewing SEDD In-House

1
Brewing SEDD In-House

• Automated generation and validation of Staged
  Electronic Data Deliverable in a commercial
  laboratory

Jakub Rehacek, Ph.D., PEL Laboratories, Inc.
2
Who are we?

• PEL Laboratories, Inc. is a small laboratory (50
  people)
• Specializing in high end analytical and data
  management services
• Providing full service capabilities in Inorganic,
  Organic, and Wet Chemistry Analyses
• Highly automated

3
Overview

• Laboratory Background Info
• SEDD Creation Prerequisites
• SEDD Generation
• SEDD Review and Validation

4
Why is SEDD good for a Lab?

• PEL provides EDDs in over 100 different EDD
  flavors to its clients
• Parser maintenance issues
• Data exchange difficult multiple contractors on
  the same project
• SEDD
• single common data format
• pass-thru review and validation by contractor

5
Laboratory Background

• Data Acquisition
• LIMS
• QC
• Reports EDDs
• Web Portal
• Web Services

6
Data Acquisition

• Advanced stages of the SEDD deliverable call for
  high levels of detail in the reporting of
  analytical results.
• Success in automated SEDD generation starts at
  the bench.
• The data acquisition software must have the
  capability of providing complete access to the
  raw data underlying the acquisition of analytical
  results.
• PEL uses a suite of highly customized data
  acquisition software with automated transfer to
  LIMS.

7
LIMS

• The success of automated SEDD generation actually
  starts long before the samples arrive at the
  laboratory
• Project Profile built during the bid phase
• QC and analytical requirements as well as HC and
  EDD format are locked in
• Real-time tracking and interaction with the Web
  Portal
• Automated QC review at each stage of lab
  processing
• McDonalds screen real-time project status
• All parameters required for SEDD are collected
  along the way and stored in LIMS

8
Quality Control

• Automated batteries of Quality Control Checks at
  various stages of sample package generation
• Assembly-line, step-by-step, computer driven
  sample prep
• Barcode verifications of samples and spike
  solutions
• Reasons for manual integrations must be explained
• Sample Narratives are generated automatically
• Capture raw instrument data down to the
  chromatograms/spectrograms.
• All the data is kept in the system
• Cross-linked with clean-up/prep info and with
  calibration runs
• Essential ingredients for generation of the SEDD
  files.

9
Reports EDD Hard Copy

• Project Managers generate SEDD at the push of a
  button
• The SEDD is generated automatically in
  conjunction with hard copy reports directly from
  our LIMS.
• SEDD is validated as a part of the final package
  QC/data validation check sequence.
• We routinely generate both SEDD and
  customer-specified EDD at the same time in order
  to ease the transition into the SEDD-based
  deliverables.
• All information comes from the same source (LIMS)
  so that we can guarantee that the SEDD, custom
  EDD, and hardcopy will have identical values.
• We can re-generate SEDD deliverables for
  historical projects that have already been
  processed through the Lab, but reported in a
  different format.

10
Web Portal

• Complete on-line data management system directly
  interfaced with LIMS
• Real-time Project Status/Package Tracking
• Preliminary results available online immediately
  after release
• Data Review and Validation
• Screening Criteria/Action Levels
• Chain-of-Custody Documents online
• Immediate Access to Test Results
• Executive Report"
• QC and calibration data is also available
• Hardcopy Reports available online
• Billing Information
• Historical billing information is maintained
• Automated project financial summaries to assist
  with the Sarbanes-Oxley Act disclosure
• Final invoices may be downloaded in PDF format
• Auto-Email Notification/Reports
• EDD's are available online at the same time the
  work order is finalized

11
Web Services

• The next step in Client-Laboratory interaction
• A web service is a web site to be used by
  computer programs instead of by humans.
• Each web service is a small application
  accessible through the Internet.
• Web services use XML for transferring
  information.
• The SEDD deliverable is uniquely qualified for
  use as a data transfer medium between web
  services.
• Simple Excel spreadsheet transparently links to a
  laboratory web service to retrieve relevant
  analytical results for further processing.

12
Automated Generation of SEDD

• Available Tools
• SEDD Tool EPAs data converter
• Commercial LIMS
• Off-the-shelf tools (MS SQL Server, .NET)

13
EPA SEDD Tool

• The SEDD Tool's primary task is to convert local
  database data into an Extensible Markup Language
  (XML)-compliant file for delivery to other remote
  data systems.
• The SEDD Tool addresses the U.S. Environmental
  Protection Agency's need to receive analytical
  data electronically from its contracting
  laboratories.

14
Commercial LIMS

• Several commercial LIMS software vendors have
  incorporated the SEDD generation into their
  products.
• Laboratories have to work with the vendor to
  ensure that their SEDD generators are kept up to
  date with the latest changes in the SEDD format.

15
Off-the-shelf tools

• The SEDD is an XML document. It can be created
  fairly easily with the use of generic programming
  tools.
• PEL chose the Microsoft product line because it
  links up with the business productivity software
  used in the lab.
• The MS SQL Server database platform provides a
  robust back-end foundation for data storage and
  manipulation.
• The Visual Studio.NET suite provides tools for
  development of data retrieval engines and user
  interfaces.
• Microsoft Office applications are the ultimate
  consumers of the final data products as they
  provide a familiar interface for users.
• All modern database and programming platforms
  have embraced the XML, and provide a multitude of
  tools to generate, manipulate, and consume the
  XML.

16
Prerequisites for Automated SEDD generation

• All the fields must be available in LIMS
• Correct and streamlined back-end database
  structure.
• All the data elements required by SEDD must be
  present in the database.
• The correct linkages must be set.
• Required data are collected along the way as the
  samples are processed through the laboratory.
• The data acquisition is an integral part of the
  process, and can be streamlined. For example
• the sample bottle ids are stored in the system
• the pre-weight information is also readily
  available for relevant calculations.
• pH and sample temperatures recorded
• Relationships established
• The SEDD file format stores the analytical
  results information as well as information about
  the relationship of the result to associated
  quality control samples and instrument
  calibration. These relationships must be
  established in the underlying data structures for
  error-free automated EDD generation.

17
SEDD Generation Process

• Query LIMS SQL
• The SEDD creation is a matter of querying the
  data store to retrieve the desired Work Order
  results and associated QC information.
• The amount of information and complexity of
  relationships between the SEDD nodes increases
  with each level of SEDD (1, 2a, 2b, 3).
• We have built a modular query and retrieval
  engine that brings in all relevant information
  based on the SEDD level being produced.
• XML export routine in .NET
• Visual Studio.NET provides a feature-rich
  environment for the creation of XML documents.
• Entire XML document is generated and fully formed
  according to the Document Type Definition (DTD)
  template.
• The System.XML namespace provides all classes
  necessary for creating, processing, and
  validation of XML files.

18
Validation/Review Tool

• PELs Data Validation/Review tool is an on-line
  service that assists a project chemist or
  independent validator with review of our
  chemistry.
• Data review/Validation
• QC Reports

19
Validate Review Chemistry

• The SEDD contains all necessary relationships
  between the results and QC samples
• validated against the DTD, and then parsed
• analytical runs are cross-referenced to prep
  batches and to calibrations
• reported results can be reviewed from many angles
• The SEDD Stage 3 allows clients to recalculate
  the results from the raw on-column values
  independently from our data processing software
  and LIMS.

20
QC Review Reports

• Our Review tool makes it possible to generate
  reports quickly via our website. The reports
  include an Executive Summary report showing all
  values detected above the Method Detection Limit,
  and a comprehensive Laboratory Review Checklist
  (LRC) that quickly summarizes any exceptions. A
  detailed QC flagging report can also be created
  to assist with validation flag applications in
  our clients data systems.
• Our system can also export EDDs in multiple
  formats to facilitate transition from legacy data
  systems. Clients can upload the SEDD data file
  and export in ERPMS, ERIS, or any other format
  that PEL currently supports. PEL Laboratories,
  Inc., is currently working with other companies
  to facilitate automated communication in order to
  provide push-button validation of laboratory
  results.

21
Questions

• Contact info
• Jakub Rehacek, Ph.D.
• VP Information Systems
• PEL Laboratories, Inc.
• jrehacek_at_pelab.com
• 813-247-2805
• www.pelab.com