Safe Drinking Water Information System Compliance Monitoring Data

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

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 Next. Gen 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 2

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 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 3

Primacy Agencies Using SDWIS State American Samoa Michigan NCWS Navajo Nation R 9 & R 1 Tribal Guam Michigan CWS N. Mariana I. Puerto Rico Virgin Islands Wyoming (R 8) EPA R 2 -R 8, R 10 Considering SDWIS/State Use SDWIS/State Partially Use or Implementing SDWIS State 5/30/12 Not Considering SDWIS/State Office of Water | Office of Ground Water and Drinking Water | USEPA 4

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

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

SDWIS Data Flows – State to EPA using Current SDWA XML Schema Inventory CDX Actions Pb/Cu SDWIS ODS Transformation ECHO ODS SFDW EFacts Reports 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, Enviro. Facts, and various reports 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 7

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

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 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 9

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 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 10

Quarterly Reporting Schedule CYQ 3 FYQ 4 Cmp. YQ 1 Jul CYQ 4 FYQ 1 Cmp. YQ 2 OCT CYQ 1 FYQ 2 Cmp. YQ 3 Jan CYQ 2 FYQ 3 Cmp. YQ 4 Apr Primacy agencies • Report data each quarter CYQ 3 FYQ 4 Cmp. YQ 1 Jul Oct CY – Calendar yr FY – Gov’t Fiscal yr Cmp – Compliance yr • Have 90 days to report the data Result = data are 90 days old when received by EPA Primacy Agencies can optionally report each month 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 11

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 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 12

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

Data Sharing Goals 1. Promote the use of advanced technologies for facilitating information and data exchange between states and EPA 2. Enhance compilation and analyses of PWS information to strengthen the analysis of potential drinking water public health concerns without additional on the states 3. Share powerful data analysis tools with states to target program oversight, compliance assistance, and enforcement to needed areas based upon risk to public health 4. 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 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 14

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 Next. Gen 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 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 15

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

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 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 17

Possible CMD Flow Reporting Agency Exchange Network CDX SDWIS Fed EN Node/ Node Client CDX Web/ ENSC Extract. Transform -Load SDWIS Fed. Rep SDWIS State Extract/Validate SDWA 4. 0 Other Database Data Mapping Excel Workbook CMD XML Document SDWIS ODS Data Warehouse Mapping Tool 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 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 18

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 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 19

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 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 20

Beyond SDWIS to Next. Gen – 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 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 21

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

Regulatory Users Primacy agency users are the end users and will use Next. Gen as part of their day-to-day activities Primacy agency systems interface with Next. Gen 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 have program oversight responsibilities at the national level • Includes OGWDW and OECA uses of data reported to EPA 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 23

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 Next. Gen*, which may route the data to the cognizant state Utilities may…* • Approve samples submitted to SDWIS Next. Gen • View/request modification of certain data elements • There are probably other use cases yet to be defined *Requires further analysis 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 24

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

Some Next. Gen “Gotchas” ODBC connectivity to the Next. Gen 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? 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 26

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 Next. Gen 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 5/30/12 Office of Water | Office of Ground Water and Drinking Water | USEPA 27

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