EPA Exchange Network SDWIS Data Flow

1
EPA Exchange Network SDWIS Data Flow

• Maine SDWIS/State system using FedRep XML output
  files

2
(No Transcript)
3
New Data Flow
FedRep
Monthly
Quarterly
DIRECT PULL
Annual
ODS
Certification
4
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.

5
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.

6
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.

7
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.

8
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. (?)

9
(No Transcript)
10
SDWIS Data Flow using FedRep Output

• EPAs 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!

11
SDWIS Data Flow using FedRep Output

• Maines 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.

12
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

13
(No Transcript)
14
EPA SDWIS/FedRep Summary Report Inventory
United States Environmental Protection Agency Originating Agency Name MEOriginating Agency Type S Extraction Begin Date 2004-12-31Extraction End Date
Information
Source User ID Requested Date Origin Name Job Number Contact Information (Email Address)
Extracted Information FedRep 2005-02-17 091354 bob.peterson_at_maine.gov
Validation Information From State FedRep 2005-02-17 091354 ME 19 bob.peterson_at_maine.gov

Certification Information From State FedRep 2005-02-17 093626 ME 20
Contact Information Name Bob Peterson Organization Maine Telephone [phone] Comment for test onlyEmail bob.peterson_at_maine.govEmail david.h.ellis_at_maine.gov Name Bob Peterson Organization Maine Telephone [phone] Comment for test onlyEmail bob.peterson_at_maine.govEmail david.h.ellis_at_maine.gov Name Bob Peterson Organization Maine Telephone [phone] Comment for test onlyEmail bob.peterson_at_maine.govEmail david.h.ellis_at_maine.gov Name Bob Peterson Organization Maine Telephone [phone] Comment for test onlyEmail bob.peterson_at_maine.govEmail david.h.ellis_at_maine.gov Name Bob Peterson Organization Maine Telephone [phone] Comment for test onlyEmail bob.peterson_at_maine.govEmail david.h.ellis_at_maine.gov
15
State Inventory Counts
Objects 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
16
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
17
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 thats 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.

18
SDWIS Data Flow using FedRep Output

• A UNIX CRON job executes a script every morning
  at 0015.
• The executed UNIX script executes a Java .jar
  file.
• The main method in the .jar file belongs to the
  DocSubmitterManager.  It does the following
• Query a database table to retrieve a result set
  of all the "active" ("A") jobs.

19
SDWIS Data Flow using FedRep Output

• 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
• Create a stub to the Node to which the document
  (file) is to be sent.
• Ping the stub to verify the Node is available.

20
SDWIS Data Flow using FedRep Output

1. Create an FTP client to retrieve the file for
   this job.
2. 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.
3. Place the created NodeDocument in an NodeDocument
   array.
4. Authenticate against the node that will be
   receiving the document (file).
5. Invoke the Submit method on the receiving node
   (pushing the file to the node).
6. Return the transactionId (resulting from the
   Submit call) to the calling method in
   DocSubmitterManager.java.

21
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.
• Log success or failure of running jobs.
• E-mail results to developer.

22
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 kdunne_at_sso.org.

23
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 e-Reporting 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_con
  verter.htm.