Safe Drinking Water Information System Compliance Monitoring Data & NextGen

1. Safe Drinking Water Information System Compliance Monitoring Data & NextGen Greg Fabian, PMP to Exchange Network National Meeting Philadelphia, PA May 30, 2012

2. Topics • SDWIS State – Usage and Architecture • Current Data Flows • The National Perspective – SDWIS Fed • Filling in the Gaps - the Drinking Water Strategy • Compliance Monitoring Data – Building a Data Flow • Data Mapping and the ECOS IPT • Moving to SDWIS NextGen Office of Water | Office of Ground Water and Drinking Water | USEPA

3. SDWIS State Background • EPA-developed system used by Primacy Agencies to manage their PWSS programs • Used to determine water system compliance with the National Primary Drinking Water rules • Includes many other features for managing… • Engineering information/inspection results • Drinking water buyer/seller/ownership • Administrator/Operator/Emergency contacts • Assistance and enforcement actions • Laboratories and samples/sample results • Monitoring and sampling schedules Office of Water | Office of Ground Water and Drinking Water | USEPA

4. Primacy Agencies Using SDWIS State American Samoa Michigan NCWS Navajo Nation R9 & R1 Tribal Guam Michigan CWS N. Mariana I. Puerto Rico Virgin Islands Wyoming (R8) EPA R2-R8, R10 Considering SDWIS/State Use SDWIS/State Not Considering SDWIS/State Partially Use or Implementing SDWIS State Office of Water | Office of Ground Water and Drinking Water | USEPA

5. SDWIS Components and Data Flows XML File State Data System(s) EPA Maintained SDWIS Component Field Notebook Quarterly Report eDV State Maintained System/Application Installed at EPA Installed at States SDWIS State SDWIS Fed ODS Extract E-Data Verification SDWIS FedRep SDWIS State XML File Sanitary Survey Extract Desktop Sanitary Survey Drinking Water Data Warehouse SDWIS Operational Data Store (ODS) Central Data Exchange (CDX) Extract Transform Load XML File Data Bridge Migrate To State Exchange Network Node SDWIS FedRep XML File Drinking Water Watch SDWIS ODS/Web Standard Reports Services Lab-to-State XML Sampling ODBC (Ad Hoc) CSV XML Paper Data Entry Apps State Web Site EPA Regions and HQ Users EPA Enforcement Staff Public Access to DW Data State Data Entry Operators Samples and Sample Results Submitted by Laboratories Office of Water | Office of Ground Water and Drinking Water | USEPA

6. SDWIS Data Flows – State to EPA using Current SDWA 3.0 XML Schema • All states use FedRep to validate SDWA XML document • FedRep also extracts data from SDWIS State • States can submit other samples, but it’s voluntary and not loaded into the SDWIS Fed Data Warehouse SDWIS State CDX Exchange Network Inventory FedRep Actions Other System Pb/Cu Office of Water | Office of Ground Water and Drinking Water | USEPA

7. SDWIS Data Flows – State to EPA using Current SDWA XML Schema ECHO • ODS Transformation Services checks the submissions and adds referential integrity to the resulting database objects • SDWIS Fed Data Warehouse (SFDW) creates new objects based on reported and derived data optimized for querying • SFDW extracts go to OECA’s ECHO system, EnviroFacts, and various reports CDX SDWIS ODS Transformation Inventory ODS SFDW EFacts Actions Pb/Cu Reports Office of Water | Office of Ground Water and Drinking Water | USEPA

8. SDWIS Data Flows – State to EPA using Current SDWA XML Schema • Lab-to-State is a CROMERR-compliant portal where labs can submit sample data as eDWR XML or.csv files. • Lab-to-State produces an XML file with lab results document • SDWIS XML Sampling is used to parse the eDWR document and load samples data into the SDWIS State DB eDWR (xml) SDWIS State XML XML Sampling Lab-to-State .csv Office of Water | Office of Ground Water and Drinking Water | USEPA

9. Determining Compliance • SDWIS compares the schedules (when to take certain samples) with the sample results • If no samples are loaded within the date range, then SDWIS will generate a candidate M&R violation • If samples exceed certain values, then SDWIS will generate the appropriate MCL, MRDL, TT, or Other candidate violation • Primacy Agencies accept or reject candidate violations • Accepted violations are sent into EPA using the process described earlier Office of Water | Office of Ground Water and Drinking Water | USEPA

10. The National Perspective – Data Transformations • State reported data are stored as-is in SFDW • Derivations are used to improve query performance and make assumptions about data • Inactive water system – active facility • Ground water or surface water sourced system • Correcting coordinates for water system facilities located in Manchuria • “Total replace” of data each quarter • Previously reported data not reported in current quarter Office of Water | Office of Ground Water and Drinking Water | USEPA

11. Quarterly Reporting Schedule CYQ3 CYQ4 CYQ1 CYQ2 CYQ3 • Primacy agencies • Report data each quarter • Have 90 days to report the data • Result = data are 90 days old when received by EPA • Primacy Agencies can optionally report each month FYQ4 FYQ1 FYQ2 FYQ3 FYQ4 CmpYQ4 CmpYQ1 CmpYQ3 CmpYQ2 CmpYQ1 OCT Jan Apr Oct Jul Jul CY – Calendar yr FY – Gov’t Fiscal yr Cmp – Compliance yr Office of Water | Office of Ground Water and Drinking Water | USEPA

12. The National Perspective – an Incomplete Picture • Current data flows to EPA are exception-based and focused on compliance • Insufficient data for • Measuring effectiveness of current regulations and treatment technologies • Giving the public a consistent view of the quality of their drinking water • Leaves the drinking water regulatory community with letting others tell the drinking water story at the national level Office of Water | Office of Ground Water and Drinking Water | USEPA

13. Administrator’s Drinking Water Strategy Goals – March 2010 • Address contaminants as groups rather than one at a time so that enhancement of drinking water protection can be achieved cost-effectively • Foster development of new drinking water technologies to address health risks posed by a broad array of contaminants • Use the authority of multiple statutes to help protect drinking water • Partner with states to develop shared access to all public water systems (PWS) monitoring data Office of Water | Office of Ground Water and Drinking Water | USEPA

14. Data Sharing Goals • Promote the use of advanced technologies for facilitating information and data exchange between states and EPA • Enhance compilation and analyses of PWS information to strengthen the analysis of potential drinking water public health concerns without additional on the states • Share powerful data analysis tools with states to target program oversight, compliance assistance, and enforcement to needed areas based upon risk to public health • Implement interactive communication tools enabling states, the utilities, and consumers to obtain timely information for learning more about their drinking water and performance of drinking water systems for public health protection Office of Water | Office of Ground Water and Drinking Water | USEPA

15. EPA/ECOS/ASTHO/ASDWA Data Sharing MOU • Negotiated and signed Nov 2010 • Data Sharing Implementation Work Group (IWG) launched Dec 2010 • Primary goal: Recommend to the MOU Steering Committee a set of compliance monitoring data (CMD) elements which would eventually would be housed in SDWIS NextGen and shared with EPA • Using SDWIS State as a basis, the IWG identified the set of CMD data elements • Sep 2011, IWG issued its report to the MOU Steering Committee recommending the CMD elements Office of Water | Office of Ground Water and Drinking Water | USEPA

16. MOU Steering Committee Approved CMD Data Elements Based on SDWA 3.0 XML Schema Original ModifiedNew • Inventory • Water System • Water System Facilities • Treatment Plant • Analyte Removal • Sampling Point • Facility Analyte Level • Legal Entity Affiliations • Service Areas • Geographic Areas • Locations • Treatments • Flow • Samples • Samples/Sample Summaries • Sample Results • MDP Summaries • Monitoring Period Averages • Actions • Sampling Plan • Sampling Points • Collection Dates • Sampling Schedule • Monitoring Period • MCLs • Violations • Enforcements • Associated Violations • Compliance Schedules • Milestone Events • Compliance Schedule • Compliance Schedule Activity • Site visits • Compliance Schedule • Deficiencies Office of Water | Office of Ground Water and Drinking Water | USEPA

17. ECOS IPT – Implementing the CMD Data Flow • IPT launched in Jan 2012 • Laurie Cullerot, NH and Greg Fabian – co-chairs • Work on defining the XML schema began in advance • Scope is to go beyond the EPA-Primacy Agency exchange by considering other stakeholders • Identified additional data elements requested by • CDC – Public Health Tracking Network • APHL – Lab QA data • Six year review data submission (“ICR”) • Developed SDWA 4.0 schema Office of Water | Office of Ground Water and Drinking Water | USEPA

18. Possible CMD Flow • Data mapping tool – maps Primacy Agency data sources to the SDWA 4.0 schema and validates/checks errors • Intention is for EPA to provide mapping tool to the Primacy Agencies Reporting Agency Exchange Network CDX SDWIS Fed SDWIS FedRep SDWIS State Extract/Validate EN Node/ Node Client SDWA 4.0 Extract-Transform -Load CDX Web/ ENSC Other Database Data Mapping SDWIS ODS Data Warehouse CMD XML Document Excel Workbook Mapping Tool Office of Water | Office of Ground Water and Drinking Water | USEPA

19. Data Mapping Pilot • Started late April, scheduled completion June 11 • AR, LA, NH, NY and OR • OH used by contractor as a basis for mapping • Goal is to • Test the SDWA 4.0 schema • Evaluate efficacy of using off the shelf data mapping tools • Altova Map Force selected for the pilot • Transmit data using SDWA 4.0 through to CDX • If the mapping tools prove useful, EPA will • Develop scripts for mapping SDWIS State data • Provide mapping tools to the primacy agencies • Offer some training in tool use Office of Water | Office of Ground Water and Drinking Water | USEPA

20. Additional Flows to Test • Focus right now is on CMD flow from Primacy Agencies to EPA • Other flows need to be tested, such as: • Labs to Primacy Agencies • Tribes and Territories to EPA Regions • Primacy Agencies to CDC • Flows between agencies within a state and regions • Flows between Utilities and Primacy Agencies Office of Water | Office of Ground Water and Drinking Water | USEPA

21. Beyond SDWIS to NextGen – Architectural Highlights • Cloud-based hosting • One app, one database with secure areas within the database for each Primacy Agency • “Componentized” application using Service Oriented Architecture (SOA) • Leverages Business Process Management (BPM) for workflow management and compliance determination • Supports process improvements geared to reducing the amount of time users spend in the system • Delivered to the end user through their web browser Office of Water | Office of Ground Water and Drinking Water | USEPA

22. NextGen and its Stakeholders Primacy Agency Users Primacy Agency Systems EPA HQ Compliance, enforcement, inventory/engineering, sampling, schedules Billing, permitting, plan review, other apps/data Inventory, actions, compliance monitoring data NextGen Public EPA Regions Real time data uses? Drinking water quality Occurrence data Inventory, Reports Sample results Water Systems Other Federal Agencies Laboratories Office of Water | Office of Ground Water and Drinking Water | USEPA

23. Regulatory Users • Primacy agency users are the end users and will use NextGen as part of their day-to-day activities • Primacy agency systems interface with NextGen and provide additional features specific to each agency • EPA Regional users have oversight responsibilities for their states and may have different access needs than EPA HQ users • EPA HQ users have program oversight responsibilities at the national level • Includes OGWDW and OECA uses of data reported to EPA Office of Water | Office of Ground Water and Drinking Water | USEPA

24. Non-Regulatory Users • Other federal agencies, such as CDC and DHS, are consumers of SDWIS data (occurrence, locations, etc.) • The public can view data pertinent to the quality of their drinking water or for other studies and purposes • Laboratories may submit sampling data and results to SDWIS NextGen*, which may route the data to the cognizant state • Utilities may…* • Approve samples submitted to SDWIS NextGen • View/request modification of certain data elements • There are probably other use cases yet to be defined *Requires further analysis Office of Water | Office of Ground Water and Drinking Water | USEPA

25. NextGen Value Proposition • Primacy Agencies… • Will spend less time “feeding the system” because of process improvements built into NextGen • Beginning effort to identify business process improvements • Do not have to maintain their own IT infrastructure and the staff needed to support NextGen • Reduced cost for upgrading/easier access to upgrades • EPA… • Reduced software maintenance cost – one app, one version • Reduced operating cost – cloud hosting Office of Water | Office of Ground Water and Drinking Water | USEPA

26. Some NextGen “Gotchas” • ODBC connectivity to the NextGen database • Many Primacy Agencies have “add-on” applications that connect to SDWIS State using ODBC • Web services can be used instead in many cases • Perceived loss of control of data • Data are no longer physically located in a state data center • Ensure Primacy Agencies can control access to their data • Cost model still being worked out • Primacy Agency cost sharing with EPA using grants? Office of Water | Office of Ground Water and Drinking Water | USEPA

27. What’s Next? • Complete the data mapping pilot and other flows • Distribute data mapping tools to Primacy Agencies • Begin flowing CMD and 6-year data • Complete NextGen Business Process and Requirements Analysis by Oct 2012 • Establishing parts of the solution architecture – BPM/BRE and SOA/ESB • Full Next operational capability in Sep 2014 • Decommission SDWIS State Sep 2015 Office of Water | Office of Ground Water and Drinking Water | USEPA

28. Questions? Greg Fabian fabian.gregory@epa.gov (202) 564-6649 Office of Water | Office of Ground Water and Drinking Water | USEPA