Lessons Learned from Getting Data into STORET: A State

1
Lessons Learned from Getting Data into STORETA
States Perspective
Mike Beauchene, State of Connecticut Department
of Environmental Protection Mellony Hoskinson,
Dwane Young, RTI Dave Wilcox, Gold Systems
2
STORET Success
3
Overview

• Connecticuts Data Management System
• Re-formatting data to STORET specifications
• Column Mapping
• Crosswalk Tables
• Queries
• Challenges/Benefits

4
The Process
CHAOS
Database
Step 1. Create CT DEP Database
Step 2. Populate the database
Step 3. Data Analysis
Update database and create crosswalks

Queries
Step 4. Build Queries
Step 5. Load to STORET
Now that the process is developed, data can be
loaded to the CT DEP database, and easily
migrated to STORET.
5
(No Transcript)
6
Connecticut - 2001
Step 1 CT DEP creates a database to store
monitoring data that meets DEPs needs

• Move data system from hard copy to electronic
  system
• Use STORET data structure as guideline for
  setting up Connecticuts database
• Projects
• Trips
• Stations
• Key to this process is implementing required
  STORET data elements
• Analytical methods
• Gear Type
• Sample Collection Procedures, etc.

7
Database Elements
Samples
Stations
Projects
Trips
8
Connecticuts Data Management System
Step 2 Populate CT DEP Database

• Assigned Project IDs and appropriately tagged all
  related data under project
• Project managers selected from STORET lists which
  methods, gear, collection procedures, etc. were
  used on project
• Projects are maintained separately, but all
  contain same data elements and can be imported
  into STORET

9
From State to STORET
10
Analysis
Step 3 STORET Comparison

• Identify and map CT DEP data elements to the
  corresponding STORET data elements
• Projects
• Stations
• Trips
• Activities
• Identify missing data elements that would be
  required by STORET

11
Crosswalk Tables
Step 3 cont. Build Crosswalk Tables

• Match CT DEP values with STORET values
• Accounts for capitalization, abbreviations, etc.
• Provides a means for accounting for data elements
  that are required by STORET, but is not in DEPs
  database
• Can be easily modified to reflect changes made in
  current system
• Crosswalk Tables are the keys to creating new
  STORET formatted queries

12
(No Transcript)
13
Data Select Queries
Step 4 Build Queries that use DEP database and
crosswalk tables to create SIM ready files for
loading into STORET

• Query design
• State data tables
• Crosswalk tables
• Queries take care of conditional situations (i.e.
  adding Non-Detect when necessary)
• Since the queries run directly on CT DEP
  database, when DEP data changes, so do the
  queries
• Provide a means for flagging data in CT DEP
  database that has been loaded into STORET

14
(No Transcript)
15
(No Transcript)
16
Load the Data to STORET
Step 5 Load the data into STORET

• Using SIM and the data queries, DEP can now load
  the data into STORET
• Using these same queries, DEP then can flag all
  of the data (Stations, Activities) that have been
  loaded to STORET so that they would be excluded
  from future data loads

17
Challenges to Overcome

• Some data elements discovered during the data
  analysis that are required by STORET, and
  therefore required changes to CT DEPs database.
• Crosswalks have to be kept up to date. If CT DEP
  adds a new characteristic, then that
  characteristic needs to be added to the crosswalk.

18
Benefits to State

• Maintain original data management system
• If there is a need to change the format for the
  SIM-ready files, then its relatively easy to
  change the queries.
• DEP doesnt need to adopt all of the STORET
  codes, but rather just needs to keep the
  crosswalks up to date.
• Now that the data analysis is done, and the
  process is established, future data loads to
  STORET are relatively painless.