TRI National Conference

February 13, 2008

Implementing the State Data Exchange in Kentucky

or…

“If It Was That Easy, What Did We Do Wrong?…”

Navigating through Implementation…

MOA

ZEBRA TRIFIDs

Test Files

Schema

Ping th

e

Node

Kentucky’s System

• Intel-based Dual Processor Server

• .NET 1.1 framework

• Oracle 9 database

• Hardware purchased in 2005, tested awaiting use

• Data storage varies, some directly on server (TRI); some query-based in other database (TEMPO)

Kentucky’s Data Management Processes

• Tools for Environmental Management and Protection Organizations (TEMPO) and COMPASS developed by CGI-AMS

• Replaced 65 individual databases

• Potential flows identified for the Node: TRI, NEI, FRS, AQS, SDWIS, RCRAinfo, eDMR, Kentucky Watershed Modeling Information Portal… many that directly involve TEMPO\COMPASS

Timeline to Joining the Exchange Network

• Network Node purchase and testing• Meeting in Atlanta • Selection of TRI State Data Exchange• 8/06 - Model MOA edited for Kentucky • 8/15-24/06 - Routed Through Legal and Management• 9/12/06 - Submit MOA to EPA (1st All Flows MOA) (received

by EPA 9/20/06, signed 9/22/06)• 10/26/06 - Receive ZEBRA TRIFIDs• 10/27/06 - Begin 3-step testing and completed• 11/17/06 - Receive RY2005 flow• 35 Days from signature to completion

Communication

• Booth at 2006 Governor’s Conference on the Environment

• Email to all technical contacts from RY2004 and RY2005

• Kentucky Association of Manufacturers and Greater Louisville Inc.

Results RY 2006

• RY2005: 100% Paper (over 5 linear feet)

• RY2006: 451 Facilities– 76% CDX– 10% Diskette– 9% TRI-ME web– 6% Paper (7 ½ linear inches )

Benefits

• Ease of submitting to EPA and state simultaneously

• Fewer errors in submissions• Ease of reconciling state and federal submissions• Streamlined submissions in future years• Less state staff time processing submissions• Reduced storage space of archived submissions• State has enhanced data analytical capabilities

We’ve received the data, NOW WHAT!!

Needs\Desires:

• Ability to view data that has been submitted, sort and query

• Evaluate trends: top facilities, top chemicals, geographic trends

• Simple to maintain, limit IT contracting

Options:

• Develop front end

• Use someone else’s front end

• XML Add-In for Excel

XML Toolbox

http://www.microsoft.com/downloads/details.aspx?familyid=e315c516-2c2c-4870-a189-d47a5d7ffeb3&displaylang=en

Steps for Spreadsheet Development

• Install XML Toolbox (for Excel 2003)

• Open XML Submission

• Select “Use XML the Source task pane”

• Build spreadsheet: drag desired fields to spreadsheet.

• Import XML files (select all files in folder)

Future Directions

• Continue to evaluate other front ends

• Contact paper submitters from RY2006

• Evaluate key metrics:– Top releases– Top facilities for target chemicals

• Evaluate temporal trends