Integrated Compliance Information System (ICIS) and Beyond: Current and Future Plans and Technology

1. Integrated Compliance Information System (ICIS) and Beyond:Current and Future Plans and Technology Presented by: Alison Kittle, U.S. EPA Bill Rensmith, Windsor Solutions, Inc. Mike Beaulac, Michigan DNRE Tony Jeng, enfoTech & Consulting Exchange Network National Meeting May 30, 2012

2. Agenda • ICIS Today • Future of ICIS • ICIS Electronic Submission Tools • OpenNode2 Starter Kit for ICIS • EN-Node2 Plug-In for ICIS • Resources Available to States

3. Agenda • ICIS Today • Future of ICIS • ICIS Electronic Submission Tools • OpenNode2 Starter Kit for ICIS • EN-Node2 Plug-In for ICIS • Resources Available to States

4. ICIS Today DMR Batch • In production since May 2008 • 6 state agencies using it (AK, AR, IL, MI, MN, TN) • 12 NetDMR states using it (AR, CO, CT, HI, IN, KY, LA, MD, SD, TN, UT, TX) Full Batch • Phase 1 • Batching of permit related data • 7 state agencies submitting this data (AL, DE, FL, KY, MO, OH, PA) • In production since February 2011 • Phase 2 • Batching of inspection related data • 6 state agencies submitting this data (AL, DE, FL, KY, OH, PA) • In production since November 2011

5. ICIS Today Full Batch • Phase 3 • Batching of enforcement action, violation and program report related data • 15 State agencies to be migrated out of PCS (AZ, IA, KS, ME, MS, NJ, NC, ND, OR, SC, VA, VT, WA, WV, WY) • Scheduled to be in production December 2012

6. Agenda • ICIS Today • Future of ICIS • ICIS Electronic Submission Tools • OpenNode2 Starter Kit for ICIS • EN-Node2 Plug-In for ICIS • Resources Available to States

7. Future of ICIS • Full Batch Data Flow Enables ICIS For: • Electronic exchange of information between EPA and states (e.g., NPDES Permitting, Enforcement and Compliance information). • Electronic reporting from the NPDES regulated universe to EPA • Electronic reporting of compliance monitoring data from inspectors in the field • Electronic submission of data from other EPA systems

8. Future of ICIS • Electronic reporting of compliance data from inspectors in the field: • Region 4 inspectors to send their compliance monitoring data and RCRA inspection reports directly to ICIS from the field via CDX • EPA is piloting tablet software for the PCB andNPDES Construction Storm Water compliance monitoring programs

9. Agenda • ICIS Today • Future of ICIS • ICIS Electronic Submission Tools • OpenNode2 Starter Kit for ICIS • EN-Node2 Plug-In for ICIS • Making the Future of ICIS and Electronic Reporting a Reality

10. Free .NET and Java software posted on the Exchange Network used for submitting all data families to ICIS Stores data extracted from state NPDES system into staging tables structured like ICIS Generates XML submission files and submits them to ICIS through CDX Returns results of ICIS processing States must extract their NPDES data into the staging tables with ICIS reference codes ICIS Electronic Submission Tools 10

11. OpenNode2 ICIS-NPDES Full Batch Plugin Submitting Data to ICIS the Easy Way Bill Rensmith, Windsor Solutions, Inc.

12. OpenNode2 • Project Background • About OpenNode2, flows, and plugins • About this Project • Project Timeline • How it Works • Staging Tables • Submission Lifecycle • Live Demonstration • Implementer Responsibilities • Options for non-OpenNode2 States • Q & A

13. About OpenNode2 • Open source Exchange Network node software • OpenNode2 can: • send data to other Network partners • make data available for others to query • Most widely used node software on the Network • Available at http://code.google.com/p/opennode2

14. About Flows and Plugins • Each type of data on the network is a “flow” • There is a flow for each regulatory area (air, waste, etc…) • ICIS-NPDES is one of many available flows • EPA defined the rules for sending data via the ICIS-NPDES flow • Each flow is implemented as a Plugin in OpenNode2 • Plugins are the software that contain the functionality needed to support a specific flow • The OpenNode2 Google code site has plugins for most major regulatory flows to EPA

15. About this Project • The ICIS-NPDES Full Batch flow is complex • 46 different data families • 149 tables (complex data types) • 1195 fields (simple elements) • 963 business rules • To reduce implementation challenge, EPA and ECOS wanted to make available tools to simplify flow implementation for states.

16. About this Project (cont’d) • In September 2011, EPA and ECOS engaged Windsor to develop full batch data flow plugin for OpenNode2 • Ubiquity of OpenNode2 made it a good choice to reach the widest potential audience of states • To reduce cost, only developing for the .NET version of OpenNode2 (most commonly used version) • Pilot with Washington Dept. of Ecology • Summer 2012

17. Project Timeline • 1/5/2012 – Plugin design completed • 2/8/2012 – Staging tables released to Google Code • 3/13/2012 – Beta Plugin released to Google Code • 4-8/2012 – Test/Implement in WA • 8/23/2012 – Final Plugin release to Google Code • 12/2012 – Release of ICIS v4 Plugin • Adds support for compliance and enforcement modules

18. Overall Submission Workflow

19. Workflow Lifecycle • Data Preparation Stage • State-specific Extract, Transform and Load (ETL) • Change Detection Process • Submission Stage • Result Processing Stage • Retrieve Accept/Reject Report and Parse/Store Results • Store Accepted Records • Full lifecycle must complete before repeating • Checks in place to prevent out-of-sequence execution

20. ICIS-NPDES Staging Tables • Two sets of staging tables: • ICS_FLOW_LOCAL – Agency’s NPDES data to send to ICIS • ICS_FLOW_ICIS – Copy of all data successfully sent to ICIS

21. Stage 1: Data Preparation • ETL Step • Refresh “local” tables with latest data from agency NPDES database

22. Stage 1: Data Preparation (cont’d) • Detect Changes Step • Database routine compares “Local” data with “ICIS” data to determine what needs to get sent. • Sets Transaction Codes (N, C, R). Leave Transaction Code blank if data is already in sync with ICIS. • Inserts records into “Local” for Deletes (D, X).

23. Stage 2: Submission • Plugin builds payload for all modules/records that have a Transaction Code set.

24. Stage 3: Result Processing • Download, Parse and Store Results Step: • When processing is complete, Node downloads and parses Accepted and Rejected Transactions into a Result Tracking Table.

25. Stage 3: Result Processing (cont’d) • Store Accepted Transactions Step: • Accepted Records are copied from “Local” to “ICIS”. • Performed by a stored procedure executed by the plugin.

26. Stage 3: Result Processing (cont’d) • How do I audit successes/failures? • All feedback from ICIS is stored in result tracking table (ICS_SUBM_RESULTS) • Only stores the accepted transactions from the most recent submission • Stores all errors received for a given business key’

27. Live Demonstration

28. What Can I Download? • http://code.google.com/p/opennode2/

29. What Can I Download? (cont’d) • Plugin • Plugin.zip – this is what you upload to OpenNode2 • Database scripts – for SQL Server and Oracle • Creates needed tables, procedures, views… • Documentation • Plugin Implementation Guide PDF • Just the facts. Describes how to install and configure the plugin and database components. • Plugin Design Specification PDF • All the nitty-gritty details of the plugin design. Very useful to understand the details of how the plugin components work.

30. Implementer Responsibilities • Set up staging tables in state environment • Map source systems to staging tables Mapping to ICIS-NPDES lookup values • Develop data extraction and transformation routines to copy data from state database to staging tables • Set up node and plugin • Flow data to CDX Test environment • Monitor for errors. Fix by: • Fixing data in source system • refining ETL logic • Migrate to production

31. Questions and Answers http://code.google.com/p/opennode2 Webinar Video: http://youtu.be/7qP2d6CUypsb

32. Michigan eDMR Demonstration by Mike Beaulac, Michigan DEQ Electronic Submission Plug-Ins 32

33. NMS: NPDES Management System E2: Online DMR Reporting System EN Node: ICIS-DMR batch loading to CDX/ICIS Electronic Submission Plug-Ins 33

34. Permittee requests an account Authorized representative submits a TPA to DEQ to request an account (preparer, viewer, certifier) DEQ approves the request, establishes accounts, sends security credentials to the requestor DMR Reporting Forms NMS pushes monitoring/reporting requirements to E2 (supports monthly, quarterly, semi-annual, annual DMRs) DMR Reporting & Management Permittee: Online data entry, Excel copy-and-paste, XML upload, compliance checks, comment results, data validations, make revisions (745 participating facilities, 76% out of 976) DEQ: late/no submission report, email reporting alerts, etc. Procedures in meeting CROMERR requirements Electronic Submission Plug-Ins 34

35. Electronic Submission Plug-Ins • Parameter requirement by Monitoring Point and Stage • Seasonal requirements • Effective dates could be staged

36. Electronic Submission Plug-Ins Commenting Result. Violation Alert! Incomplete!

37. Electronic Submission Plug-Ins Commenting Result. Report results for parameters not listed on the DMR form…

38. EN-Node2 ICIS Plug-In Demonstration by Tony Jeng, enfoTech & Consulting, Inc. ICIS Electronic Submission Tools 38

39. ICIS-Batch Data Family Structure

40. EN-Node Plugins for ICIS-NPDES Unpermitted Facility Plug-In Basic Permit Plug-in General Permit Plug-in Master General Permit Plug-in DMR Plug-in Inspection Plug-in Report Plug-in Compliance Determination Plug-in Enforcement Plug-in Optional Payloads Single event Schedule Violation Optional Payloads Enforcement Actions Optional Payloads for Basic PMT Biosolids CAFO CSO POTW Pretreatment Storm Water MS4 … Optional Payloads for General PMT Biosolids CAFO CSO POTW Pretreatment Storm Water MS4 … Optional Payloads for Master General PMT Biosolids CAFO CSO POTW Pretreatment Storm Water MS4 … Optional Payloads: CAFO Inspection SSO Inspection CSO Inspection Storm water Inspection … Optional Payloads: SSO Annual Report SSO Monthly Report Storm Water Event Storm Water Program Report …

41. EN-Node ICIS-Batch Implementation • Submissions: • Types: X, N, R, C • Delete Permit: To delete a Permit, all child records must be deleted first (limits, DMR, violations, inspection, etc.) • Reload DMR: For period of over-lapping permits, DMRs need to be reloaded • Generic data validation: • State-to-ICIS code translation • Data type & length • Schema validation • State-specific validation… • Reports: • Search/View State Data • Search/View Submit data • Search/View Transactions & status • View ICIS processing reports • Data Push Options: • Triggers, • Stored Procedures, • Schedule Jobs, • …

42. EN-Node ICIS Submission Options

43. EN-Node Flow Wizard - Process Configuration

44. EN-Node ICIS Plugins • Availability Status • Download EN-Node & Plug-ins: https://code.google.com/p/en-node2/downloads/list

45. Agenda • ICIS Today • Future of ICIS • ICIS Electronic Submission Tools • OpenNode2 Starter Kit for ICIS • EN-Node2 Plug-In for ICIS • Resources Available to States

46. Resources Available to States OEI Exchange Network Grants • Funding to states, territories and federally recognized Indian tribes to support development of Environmental Information Exchange Network related activities • Announcement of the availability of funds and soliciting of proposals and partial applications from applicants occurs through the Exchange Network Grant web page at www.epa.gov/networkg

47. For More Information Contacts • ICIS-NPDES Electronic Submissions • Alison Kittle kittle.alison@epa.gov • Michigan eDMR • Mike BeaulacBealacM@Michigan.gov • EN-Node • Tony JengTony_Jeng@enfotech.com • OpenNode2 • Bill Rensmithbill_rensmith@windsorsolutions.com

48. Questions ???