Transcript Example Slide Title - The Exchange Network

TRI Pilot Data Flow
via the Exchange Network
Michigan’s Experience
Michael Beaulac – Michigan DEQ
Bill Rensmith – Windsor Solutions, Inc.
Whad’ya Node? Conference
New Orleans, LA – 2/28/05-3/1/05
TRI Integrated Project Team
EPA
Virginia DEQ
South Carolina DEQ
Windsor Solutions, Inc.
ECOS
Indiana DEM
Michigan DEQ
EnfoTech & Consulting, Inc.
Computer Science Corp.
Lockheed Martin
TRI XML Schema Inputs
Standards &
Guidelines
Existing TRI Formats
CRM1
TRI-ME
Flat File Format
CRM2/SSC’s
Form R and A
EDSC 2.0
EN DRCs &
W3C Standards
Business
Requirements
IPT Meetings
TRI XML
Schema
Reporting
Instructions
EnviroFacts
Data Model
TRI Pilot Data Flow
TRI Next Generation Data Flow
Project Tasks
High-Level Tasks
1. Convert TRI production database to SQL Server
and remap front end
2. Configure Node for TRI data flow
3. Testing with CDX
Michigan
TRI Overview
The Michigan TRI Technical
Architecture
Michigan Node
EPA Node
5
web
XML file
stored on
Node
1
(CDX)
EPA CDX Node
submits Michigan
Node TRI XML file
deqwebdmz
7
TRI
Database
(exchange
tables)
deqsqlprod
Michigan Node
returns transaction
ID to EPA
2
Node logs
incoming request
as Processing
3 Node grabs XSLT
from Node (contains
XML parsing logic)
Node
Database
4
Node parses
XML file, stores
in TRI exchange
tables
6
deqsqldmz
Node logs
incoming request
as Complete
DMZ
Inner Network
TRI Node Administration Console
Some Preliminary Lessons Learned …
Make the exchange server accessible to the
Node because firewall “rules” may prevent
data passing through the Node Application
Server to Exchange Tables
Verify cycle-time per transaction for single and
multiple chemical reports per submission
XML Instance files archived in Node
Application Server and file server to verify
accuracy and completeness of data
MI Infrastructure to Support TRI Pilot
EPA Program
Offices
Machine
Access
to Web
Services
web
EPA-TRI
Internet
CDX
EPA-NAAS
443
1
2
XML
Format
Deqw2dmz
(TRI Converter)
Deqw1dmz
(TRI Converter)
Deqsqldmz
(Production Node_db)
3
DB Format
Deqsqlprd
(TRI database)
Deqinet
(Node.Administration)
Internal Network
1433
Load Balanced
DMZ Zone
Node.WebServices
XML Instance File – Facility Identification
XML Instance File – Chemical and Activity
Cycle Time Transactions
TRIFID
# Chem.
Cycle-Time
48192BSFCR1609B
12
4 Min 12 sec
49001THPJH7171P
34
2 Min 5 sec
48174MCHGN36345
47
5 Min 45 sec
48667THDWCMICHI
86
3 Min 16 sec
To Be Completed …
CDX translator (TRI ME => XML) issues:
creates one submission block for all chemicals generated w/in a facility.
Does not handle multiple SIC codes per chemical. This has been/is
being corrected.
Translator doesn’t handle Dioxins due to reporting unit differences
(grams vs. lbs.)
State notification to facilities: MI cannot echo back to reporting
facilities that we received a submitted instance file from CDX
Who best to do this? CDX or states?
Possible joint development of solution to “submit” from State
Node to back-end db (multiple platforms and environments …
Oracle and SQL, J2EE and .NET)
Preliminary Costs*
Task
Document review, testing, QA, etc.
Develop and implement a TRI data
flow strategy
Install TRI Converter w/site visit (dB
and App.)
Linkage of converter with existing
Node
Cost
(100-200hrs)
$1,000-2,000
$2,000-3,400
$1,500-3,000
*Does not include Node development and db conversions, etc.
(i.e., MS Access => SQL/Oracle)
Preliminary Costs*
Task
Map TRI Schema to data backend
data source
Develop procedures to convert
from exchange tables to backend
system
Test of data flow (with EPA)
Document results
Total
Cost
$1,500-3,000
$2,000-4,000
$2,000-4,000
$1,500-3,000
$11,500-22,400
*Does not include Node development and db conversions, etc.
(i.e., MS Access => SQL/Oracle)
Conclusion & Incentives
TRI CDX transactions reduces data processing
time, save resources and improves data accuracy,
Enables continuous QA/QC,
Eliminates need for the UTIL upload program,
Could potentially eliminate redundancy in
reporting certain environmental and business
information to the state and local governments
Reporting facilities could realize a reduction in
reporting time resulting in cost saving
Process
~ Time
• Open mail and separate current from
previous year (if applicable),
• data stamp (paper and disk)
submission & log receipt
3 weeks
• Authenticate certification letter and
disk
• Upload data on disk to TRI db via
UTIL (Electronic submittals equate to
3229 reports; 90% of data received)
2 weeks
• Manually key paper submittals using 3 weeks
TRI-ME and upload data into TRI dB
via UTIL (Paper submittals equate to
343 reports; 10%)
• QA accuracy of uploaded data
1 week
• File certification letter with paper
reports & disks,
• archive prior year data at record
center.
1 week
Cost Benefits
State:
10-12 weeks savings in internal staff
resources = ~0.25 FTE => $17,500/yr
ROI => 6 months - 2 years
Facility:
1000 facilities @ 1-2 hours X $25.00/hr =>
$25,000 - $50,000 per year
EPA:
Unknown at this time … anybody???
More Information:
http://www.exchangenetwork.net/flow/cross/tri.htm
Robert Jackson (MI DEQ) 517-373-2731
Ruth Borgelt (MI DEQ) 517-373-9804
Michael Beaulac (MI DEQ) 517-241-7808
Sara Pushpala (MI DIT) 517-373-8565
Phyllis Donahue (EPA–TRI) 202-566-0338
Natalia Vainshtein (CSC) 301-306-6465
Bill Rensmith (Windsor Solutions) 503-675 7833, x217
Kevin Jeffery (Windsor Solutions) 503-675 7833, x202
Doug Timms (enfoTech) 609-896-9777, x129
Ryan Teising (enfoTech) 609-896-9777, x148