TRI State Data Exchange The Best Thing Since

TRI State Data Exchange …The Best Thing Since …. ! Feb 28 2007 Michigan SARA Title III

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 12/17/2021 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 Sharing Agreement with EPA 12/17/2021 Michigan SARA Title III 3

Business Process & Technical Questions Explain your TRI business process and needs ¡ TRI data l l ¡ What is your back end data system (Oracle, SQL, other)? Data structure (file format, types of data) What data do you need? l l l All? Form R and Form A? Additional state reporting requirements, i. e. , data elements or chemicals Data access, output and reporting (front end application) 12/17/2021 Michigan SARA Title III 4

Technical & Functional Requirements Questions ¡ Node l l ¡ Do you have an operational node? Can it accept incoming TRI XML data flow? Converter to process the XML file l l Can this be developed in-house or will you need a contractor? Can other states share their converter? 12/17/2021 Michigan SARA Title III 5

Technical & Functional Requirements Questions ¡ Any data system changes needed to accept the TRI data flow? l l l ¡ 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? l Do we need to be more aggressive in promoting source code sharing? 12/17/2021 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? 12/17/2021 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? l l “Viewer” to read only? Front end to read, review and manipulate data? 12/17/2021 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 12/17/2021 Michigan SARA Title III 9

Michigan Data Exchange Process Consultant 12/17/2021 Michigan SARA Title III 10

CDX TRI Data Flow 1) TRI reports submitted to CDX converted to 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 enfo. Tech 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 12/17/2021 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: l l l ¡ ¡ XML schema Converter Data tables and mapping Who will responsible for needed system changes? Should you have a maintenance contract? 12/17/2021 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 l More accurate data l Less time spent on data verification and quality assurance l 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 l Data analysis l Pollution prevention efforts 12/17/2021 Michigan SARA Title III 13

Is it Really Worth It? ¡ Efficiencies in data collection and management l l 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 ¡ 12/17/2021 Michigan SARA Title III 14

Return on Investment Michigan TRI Results Total Annual Operational Cost Pre-EN ($/year) $50, 07 7 Total Annual Operational Cost Post-EN ($/year)[1] $10, 50 0 Initial Capital Investment ($) $18, 85 7 Post-EN Annual Maintenance Cost ($/yr) $1, 483 Post-EN Annual State Savings ($/year) [2] $38, 09 4 ROI Summary Average ROI (5 year basis)[3] 202% [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 Payback period (years) 0. 5 [3] This value uses the average annual savings above 12/17/2021 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)? 12/17/2021 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: l l Document showing Internet submission process EPA-DEQ Memorandum of Agreement ¡ Article published in department bulletin ¡ Currently: Expanding outreach to increase Internet submissions 12/17/2021 Michigan SARA Title III 17

Thanks for Attending! Ruth Borgelt State TRI Coordinator Michigan SARA Title III Program 517 -373 -9804 borgeltr@michigan. gov Michael Beaulac State Assistant Administrator Executive Division 517 -241 -7808 beaulacm@michigan. gov 12/17/2021 Michigan SARA Title III 18