Transcript EPA Exchange Network SDWIS Data Flow

EPA Exchange Network
SDWIS Data Flow
Maine SDWIS/State system using
FedRep XML output files
New Data Flow
FedRep
SDWIS/STATE
Federal
Enf Data
Flow
Other State XML
Doc
Data Systems
Data
Warehouse
CDX
XML Doc
Repository
Monthly
Standard
Reports
Quarterly
Ad Hoc
Reports
DIRECT
PULL
EPA
Regional
Office
Certification
ODS
Staging
Tables
Annual
OLAP
Tools
SDWIS Data Flow using FedRep Output
• SDWIS/FedRep v1.1, dated 8/9/04, is an
integrated set of tools, including a message
notification tool, that extracts data from
SDWIS/State applications using SQL Server,
DB2 DBMS's, and ORACLE.
• FedRep assists DW Public Water System
Supervision Primacy Agencies, EPA regions and
EPA Headquarters with the extraction
(SDWIS/State only), formatting (SDWIS/State
only), validation, and certification of federally
reportable drinking water data.
SDWIS Data Flow using FedRep Output
• Primacy agencies not using the SDWIS/State
application, or using it for only part of their
federally reportable data flow, have the
responsibility of mapping and extracting data
from their own database(s). The data must be
formatted into XML documents compatible with
the published XML schema used by FedRep.
• SDWIS/FedRep is based upon the J2EE
architecture so it can be deployed in diverse
operating environments.
SDWIS Data Flow using FedRep Output
• The majority of states use SDWIS/STATE.
• EPA has used SDWIS/FED database.
• An Oracle database, known as
Operational Data System or ODS/SDWIS,
is replacing the SDWIS/FED database.
• The cutoff date for EPA to turn off
SDWIS/FED is fast approaching. It is
important that States keep informed of the
status of SDWIS modernization.
SDWIS Data Flow using FedRep Output
• The Drinking Water Protection Division at
EPA headquarters issued a 2/3/05 memo
requesting “assistance in encouraging
primacy agencies to provide quarterly data
using the modernized SDWIS data flow.”
• Call 202-564-4624 or 202-564-3942 to
subscribe to the “SDWIS Modernization
Update” e-mail newsletter to stay up to
date on the progress.
SDWIS Data Flow using FedRep Output
ODS/SDWIS Timeline
• Test ODS/SDWIS is now accepting zipped
XML files up to 10 MB in size via CDX.
• Production ODS/SDWIS is expected to be
ready to accept XML files pushed to CDX
by state nodes by 07/05.
• Phase III of SDWIS modernization will
implement pull of data from state node. A
pilot was to begin 9/04. (?)
SDWIS Data Flow using FedRep Output
• EPA’s FedRep software extracts quarterly
report information from the SDWIS/STATE
database into an XML file that is compliant
with the EN drinking water schema.
• FedRep runs a majority of the EPA data
edits on your local system so you can
correct most data errors before sending
your files to CDX. Win-Win!
SDWIS Data Flow using FedRep Output
• Maine’s Drinking Water program, part of
DHHS/Division of Health Engineering/
Bureau of Health, beta tested FedRep for
EPA.
• BOH decided to join ME DEP in using the
EN node to transmit its reports to EPA and
to leverage its FedRep system at the
same time.
SDWIS Data Flow using FedRep Output
FedRep Features
• Extract EPA-format XML output files for 3 EPA
quarterly reports (Inventory, Actions, Samples)
from SDWIS/STATE or custom DW database.
• Validates data in files extracted from
SDWIS/STATE, creating a separate “rejected”
file for your attention.
• E-mails you that the job is done. The e-mail
contains links to validated and rejected XML files
and to a reports list. Sample report follows…
SDWIS/FedRep Summary
Report
Inventory
EPA
United States
Environmental Protection
Agency
Originating Agency Name : ME
Originating Agency Type : S
Extraction Begin Date : 2004-12-31
Extraction End Date :
Information
User ID
Requested Date
Extracted Information
FedRep
2005-02-17
09:13:54
Validation Information
From State
FedRep
2005-02-17
09:13:54
ME
19
Certification
Information From State
FedRep
2005-02-17
09:36:26
ME
20
Source
Contact Information
Origin Name
Job Number
Contact
Information
(Email Address)
bob.peterson@m
aine.gov
bob.peterson@m
aine.gov
Name : Bob Peterson Organization : Maine Telephone : 207-287-1979 Comment : for test only
Email : [email protected]
Email : [email protected]
State Inventory Counts
Extracted
Accepted
Rejected
Both
WaterSystem
1993
1991
2
0
WaterSystemFacility
9465
9464
1
0
LegalEntity
3791
3791
0
0
LegalEntityAffiliation
5279
5279
0
0
ServiceArea
1954
1954
0
0
GeographicArea
1976
1976
0
0
Location
2227
2227
0
0
Treatment
1230
1230
0
0
FacilityFlow
1786
1786
0
0
Objects
Error Counts
WaterSystem
Error Code
Error Count
R50100 - If the water system is
a non-wholesaler of water and
the Retail Service Connections
is less than 15, the Retail
Population Served should be
greater than or equal to 25.
2
R50120 - If the water system is
a non-wholesaler of water and
the Retail Population Served is
less than 25, the Retail Service
Connections should be equal
to or greater than 15.
2
Total
4
WaterSystemFacility
Error Code
Error
Count
R50530 - The submission of the Seller Source Treatment Code is required for
purchased sources (Facility Type Code = CC or NP).
1
R50470 - The submission of the Seller PWS ID is required for a purchased source
(Facility Type Code = CC or NP).
1
Total
2
SDWIS Data Flow using FedRep Output
• To create a drinking water flow, a.k.a.
“exchange”, to CDX via the node using
FedRep output files:
– Zip the FedRep output files.
– Place files on a server that’s accessible to the
node.
– Build a scheduler job that checks the server
for the 3 file names each calendar quarter.
– Pick up files, place in a SOAP message, send
to CDX.
SDWIS Data Flow using FedRep Output
1. A UNIX CRON job executes a script
every morning at 0015.
2. The executed UNIX script executes a
Java .jar file.
3. The main method in the .jar file belongs
to the DocSubmitterManager. It does the
following:
a) Query a database table to retrieve a result
set of all the "active" ("A") jobs.
SDWIS Data Flow using FedRep Output
b) Loop through the result set of jobs,
performing these steps per job:
•
•
Verify that the job needs to be sent.
If job needs to be sent, call a method on the
DocSubmitter object which does the following:
i.
Create a stub to the Node to which the document (file)
is to be sent.
ii. Ping the stub to verify the Node is available.
SDWIS Data Flow using FedRep Output
iii. Create an FTP client to retrieve the file for this job.
iv. Create a NodeDocument object, populate it with the
information from the method call and change the File into a
byte[] to use in the NodeDocument.
v. Place the created NodeDocument in an NodeDocument
array.
vi. Authenticate against the node that will be receiving the
document (file).
vii. Invoke the Submit method on the receiving node (pushing
the file to the node).
viii. Return the transactionId (resulting from the Submit call) to
the calling method in DocSubmitterManager.java.
SDWIS Data Flow using FedRep Output
•
•
If job needs to be sent, log the results of the call
to the node's Submit method for this job.
Loop to the next job.
4. Log success or failure of running jobs.
5. E-mail results to developer.
SDWIS Data Flow using FedRep Output
Helpful Resources
• State IT profiles now available on ECOS website
ECOS has added detailed profiles of each state to its
website containing information about integration and
information technology projects, Exchange Network
grants, Network node status, Exchange Network data
flows, and contact information for staff working on
specific projects and initiatives. This tool will help to
facilitate the exchange of information between states and
it also highlights the interesting and innovative ways that
states are managing environmental data. If you have any
questions about the profiles please contact Kristen
Dunne at [email protected].
SDWIS Data Flow using FedRep Output
Helpful Resources
• eDWR XML Schema-to-SDWIS/State Sampling via EDI
Converter is available thanks to the efforts of the
Network Exchange Drinking Water Challenge Grant
Project Team and support from the EPA Office of
Environmental Information. This converter is a valuable
tool for any State that wishes to implement an eReporting solution of laboratory analysis results and who
also uses SDWIS/State to manage their drinking water
data. Read more about the converter at
http://www.exchangenetwork.net/flow/water/edwr_conver
ter.htm.