Transcript TRI State Data Exchange …The Best Thing Since ….!

TRI State Data Exchange
…The Best Thing Since ….!
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
5/27/2016
Michigan SARA Title III
2
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
5/27/2016
Michigan SARA Title III
3
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/27/2016
Michigan SARA Title III
4
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?
5/27/2016
Michigan SARA Title III
5
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?
5/27/2016
Michigan SARA Title III
6
Node Administration &
Data Management



Who will monitor CDX node
traffic?
How will CDX submissions
be processed?
How will CDX revisions or
duplicates be handled?
5/27/2016
Michigan SARA Title III
7
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?
5/27/2016
Michigan SARA Title III
8
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
5/27/2016
Michigan SARA Title III
9
Michigan Data Exchange Process
TRI Process Overview – Michigan Perspective
MI/EPA TRI Node Pilot - Responsibilties
Machine
Access
to Web
Services
Secure Access
EPA Program Offices
web
EPA - TRI
Node
Data
Storage
TRI Converter
Populates
Exchange Tables
Stored
Procedure
(based on
Mapping)
Program Area Data
Systems
XML
parsing
Michigan
Node
XML
Exchange
Tables
Michigan - TRI
CDX
EPA-NAAS
EPA
5/27/2016
enfoTech
Consultant
Michigan SARA Title III
Michigan
10
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
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
XML files stored in node
server
5/27/2016
Michigan SARA Title III
11
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?
5/27/2016
Michigan SARA Title III
12
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
5/27/2016
Michigan SARA Title III
13
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


5/27/2016
For 2004, 60% CDX submission = 56% of
forms processed by July 1
For 2005, 78% CDX submission = 70% of
forms processed by July 1
Michigan SARA Title III
14
Return on Investment
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)
Post-EN Annual State Savings ($/year) [2]
$1,483
$38,094
ROI Summary
Average ROI (5 year basis)[3]
Payback period (years)
202%
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
5/27/2016
Michigan SARA Title III
15
Things to Consider


How will you promote CDX to your
reporters?
How will you manage low tech filers
(disk and paper)?
5/27/2016
Michigan SARA Title III
16
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
5/27/2016
Michigan SARA Title III
17
Thanks for Attending!
Ruth Borgelt
State TRI Coordinator
Michigan SARA Title III Program
517-373-9804
[email protected]
Michael Beaulac
State Assistant Administrator
Executive Division
517-241-7808
[email protected]
5/27/2016
Michigan SARA Title III
18