TRI State Data Exchange

1
TRI State Data ExchangeThe Best Thing Since .!
2
What did We Agree To?

• Receive TRI reports via CDX
• Hold facilities harmless for failed transmission
• Guaranteed an active functional Network Node is
  in place

3
What did we do to make it happen?

• You need to be convinced
• Management support
• Information Technology (IT) support within your
  Agency
• A Network Node (Node)
• XML File Converter
• Stored Procedures to Handle Data
• Data Sharing Agreement with EPA

4
Business Process Technical Questions

• Explain your TRI business process and needs
• TRI data
• What is your back end data system (Oracle, SQL,
  other)?
• Data structure (file format, types of data)
• What data do you need?
• All? Form R and Form A?
• Additional state reporting requirements, i.e.,
  data elements or chemicals
• Data access, output and reporting (front end
  application)

5
Technical Functional Requirements Questions

• Node
• Do you have an operational node?
• Can it accept incoming TRI XML data flow?
• Converter to process the XML file
• Can this be developed in-house or will you need a
  contractor?
• Can other states share their converter?

6
Technical Functional Requirements Questions

• Any data system changes needed to accept the TRI
  data flow?
• Change back end (SQL, Oracle, other)
• Existing database structure
• Will changes be done in-house or contract?
• Can other states share coding for data systems or
  processes?
• Do we need to be more aggressive in promoting
  source code sharing?

7
Node Administration Data Management

• Who will monitor CDX node traffic?
• How will CDX submissions be processed?
• How will CDX revisions or duplicates be handled?

8
Node Administration Data Management

• How will you receive the data to your actual
  computer?
• How will you view the CDX submittals?
• Viewer to read only?
• Front end to read, review and manipulate data?

9
Michigan Node TRI Data

• Node developed in .NET with SQL Server database
• Michigan node modified for inbound TRI data from
  CDX
• TRI Access XP database converted to SQL Server
• Exchange tables created to hold XML data
• Converter developed to populate XML data to SQL
  Server exchange tables
• SQL procedures developed to process data in
  exchange tables to TRI production database

10
Michigan Data Exchange Process
11
CDX TRI Data Flow
1) TRI reports submitted to CDX converted to XML XML Converter developed by EPA
2) CDX sends XML files to Michigan node CDX Node
3) Michigan node returns transaction ID to CDX XML files stored in node server Michigan Node .NET, C Windows Server 2000 OS IIS 5 Web Server SQL Server
4) XML data converted and TRI data populates exchange tables Converter developed by enfoTech SQL Server 2000
5) Stored procedure processes exchange table data into TRI database SQL Server Procedures SQL Server 2000
12
How will future TRI reporting changes affect you
(and how will you handle it)?

• Changes to TRI reporting means changing
• XML schema
• Converter
• Data tables and mapping
• Who will responsible for needed system changes?
• Should you have a maintenance contract?

13
Is it Really Worth It?

• Facilities submit data electronically to EPA - no
  longer submit to the state
• Largest cost savings - elimination of paper
  process facility disk submissions, then manual
  data entry to state database
• More accurate data
• Less time spent on data verification and quality
  assurance
• Note Cost reduction slightly offset for
  increased IT and maintenance
• MDEQ saved 0.5 FTE by eliminating mail
  processing data entry steps
• More time for other program activities
• Data analysis
• Pollution prevention efforts

14
Is it Really Worth It?

• Efficiencies in data collection and management
• Significant reduction in staff hours to review,
  process, and file submissions
• Significant improvement in data processing time
• For 2004, 60 CDX submission 56 of forms
  processed by July 1
• For 2005, 78 CDX submission 70 of forms
  processed by July 1

15
Return on Investment
Michigan TRI Results Michigan TRI Results
Total Annual Operational Cost Pre-EN (/year) 50,077
Total Annual Operational Cost Post-EN (/year)1 10,500
Initial Capital Investment () 18,857
Post-EN Annual Maintenance Cost (/yr) 1,483
Post-EN Annual State Savings (/year) 2 38,094
ROI Summary ROI Summary
Average ROI (5 year basis)3 202
Payback period (years) 0.5

1 Total annual operational costs, based on a
five year average of the post Exchange Network
operational costs. 2 Average annual savings
based on 5-year post Exchange Network
scenario 3 This value uses the average annual
savings above
16
Things to Consider

• How will you promote CDX to your reporters?
• How will you manage low tech filers (disk and
  paper)?

17
Facility Outreach

• Strategy to facilitate electronic submission
  through the Internet
• Held workshops in computer lab to provide
  hands-on use of TRI-ME
• Presentation at EPA TRI workshop
• Information posted on website
• Document showing Internet submission process
• EPA-DEQ Memorandum of Agreement
• Article published in department bulletin
• Currently Expanding outreach to increase
  Internet submissions

18
Thanks for Attending!

• Ruth Borgelt
• State TRI Coordinator
• Michigan SARA Title III Program
• 517-373-9804
• borgeltr_at_michigan.gov
• Michael Beaulac
• State Assistant Administrator
• Executive Division
• 517-241-7808
• beaulacm_at_michigan.gov