Michigan SARA Title III Data Exchange Strategies

1. TRI State Data Exchange…The 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 Michigan SARA Title III

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 Michigan SARA Title III

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) Michigan SARA Title III

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? Michigan SARA Title III

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? Michigan SARA Title III

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? Michigan SARA Title III

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? Michigan SARA Title III

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 Michigan SARA Title III

10. Consultant Michigan Data Exchange Process Michigan SARA Title III

11. CDX TRI Data Flow Michigan SARA Title III

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? Michigan SARA Title III

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 Michigan SARA Title III

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 Michigan SARA Title III

15. Return on Investment [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 Michigan SARA Title III

16. Things to Consider • How will you promote CDX to your reporters? • How will you manage low tech filers (disk and paper)? Michigan SARA Title III

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 Michigan SARA Title III

18. 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 Michigan SARA Title III