Project Initiation. Planning and Implementation

Request for Certification and Release of Funds Form

All Certified Projects Must Follow NM State Policies and Procurement Code

Project Governance

Project Name

/ Surface Water Quality Information Database (SQUID) Enhancements
Date / 9/23/15
Lead Agency / Environment Department
Other Agencies

Executive Sponsor

/ Mary Montoya
Agency Head / Secretary Ryan Flynn
Agency CIO/IT Lead / Mary Montoya
Project Manager / Tom McMichael
Project Abstract(Provide a brief description and purpose for this project)
The New Mexico Environment Department (NMED) recently completed the merger of our in-house water quality database with our Assessment Database to create the Surface water QUality Information Database (SQUID) in an Oracle platform under Exchange Grant #OS-83480201-1 (previous EPA grant award).
Via SQUID, NMED is able to submit Clean Water Act (CWA) 303(d)/305(b) Integrated List attainment conclusions to EPA’s Assessment TMDL (Total Maximum Daily Load) Tracking and ImplementatioN System (ATTAINS) via the EPA Office of Water Integrated Reporting (OWIR) Exchange Network data flow. As part of EPA’s Water Quality Framework effort, it has been announced that the OWIR schema will be retired. Therefore, NMED needs to re-design SQUID to be compliant with evolving EPA reporting requirements related to CWA 303(d), 305(b), and additional state provided TMDL-related information.
EPA is re-designing the ATTAINs based on input from state partners as part of their Water Quality Framework initiative. NMED Surface Water Quality Bureau (SWQB) and Office of Information Technology (IT) personnel actively participated in two Water Quality Framework/ATTAINs re-design workgroups. Specific to this project proposal, NMED participated in monthly workgroup discussion for the Data Elements Workgroup 1, which focused on the data elements for 303(d), 305(b), and TMDL reporting. The ATTAINs re-design will also compliment EPA’s “New 303(d) Vision” initiative; NMED SWQB personnel were also active state participants in this effort.
In addition, NMED’s previous efforts as a state in the forefront with respect to electronic CWA 303d/305b Integrated List through NM’s version of EPA’s Assessment Database (ADB) and now ATTAINS has led to solid, communicative working relationships with EPA’s Office of Water lead personnel on this effort, as well as their primary contractor (RTI). NMED would leverage these working relationships and intimate knowledge of ATTAINs previous design and schema to build on and complement this previous work in order to continue electronic timely, efficient submissions to the re-designed ATTAINs. As discussed with EPA’s lead Water Quality Framework personnel, NMED believes SQUID could be a great model for other states needing to set up or enhance current electronic submissions. NMED provides both surface water quality technical expertise and IT expertise towards this effort, and has a proven track record of proposing and implementing successful Exchange Network Grants.
NMED’s project goals, outputs, and associated outcomes are detailed below. Successful completion of these goals and associated outputs will be instrumental in helping NMED achieve environmental results through improved electronic access and reporting of CWA 303(d), 305(b), and TMDL-related data. The ultimate outcome is that improved access to quality information on the state of New Mexico’s surface waters will assist in the development and implementation of appropriate strategies to protect, enhance, and restore our surface waters.
Planned Start Date / 10/1/15 / Planned End Date / 9/30/18 (end date of assistance award)
Requested amount this Certification / $166,028.00 / Remaining Appropriation not Certified / 0

Appropriation History(Include all Funding sources, e.g. Federal, State, County, Municipal laws or grants)

Fiscal Year
/ Amount / Funding Source
FY16- FY17 / $166,028.00 / Federal Assistance Award # 83583501
Proposed Major Deliverable Schedule and Performance Measures

Major Project Deliverable and Performance Measure

/ Budget /

Due Date

/

Project Phase

Develop Project Narrative, Scope, Deliverables, Budget and submit grant proposal to EPA / Internal NMED resources only to prepare grant proposal / Completed, submitted to EPA 11/14/14, awarded July 2015 / InitiationPlanning

1.1Participate in conference calls with EPA Office of Water, their contractors, and any other demo states to provide additional vetting of proposed schema.
1.2 Provide QA testing of proposed schema as requested for EPA OW.
PerformanceMeasure:
/ $30,000.00 / 5/1/16 / Implementation

2.1 Modify the NM SQUID ATTAINs application for 303(d)/305(b) electronic reporting capability to support the new Water Quality Framework/ ATTAINs re-design reporting requirements/schema.
2.2 Testing and finalization of 303(d)/305(b) flow to Water Quality Framework.
2.3 Submit NM’s EPA-approved 2016-2018 Integrated List to new Water Quality Framework.
2.4 Enhance NM SQUID to include necessary TMDL-related reporting elements.
2.5 Testing and finalization of 303(d)/305(b) flow to Water Quality Framework.
2.6 Submit NM’s information on recently completed TMDLs information to new Water Quality Framework.
Performance Measure:

/ $127,028.00 / 7/1/17 / Implementation

3.1 Present project results and lessons learned to 1) NM’s local region (EPA Region 6), 2) at national water quality conference
3.2 Provide simple way/process for interested states to access SQUID Oracle-based template for customization.
Performance Measure:
/ $9,000.00 / 10/31/17 / Implementation
Budget
Comments:
Description
/

FY16 & Prior

/ FY17 / FY18
Staff - Internal
/ Systems Analyst and java deployment services / $7,127.00 / $4,500.00
Consulting Services / Java development services / $22,873.00 / $$127,028.00 / $4,500.00
Hardware
Software
Total / $30,000.00 / $127,028.00 / $9,000.00

IT System Analysis(On this document, or as an attachment, provide a summarydescription of the technical architecture)

The technical architecture will not change as a result of this project. The Exchange Network Node architecture has been in place and operational since 2006. The hardware platforms hosting the databases were moved to DoIT in FY2015. The hardware platforms hosting the node software and the application software were virtualized and moved to the DoIT UCS platform n 2015. This project will result in enhancements to application and node software only.

Interoperability (Describe If/how this project interfaces with existing systems/Applications)

This project will result in no new system interfaces.

Independent Verification & Validation (Include summary of the latest IV V report)

This function is provided through EPA controls. The department submits a Quality Assurance plan to EPA 90 days after the start of the award and the department must submit Quality Assurance reports in accordance to the assistance award (see attached).

Significant Risks and Mitigation Strategy

Risk 1: if the system is not upgraded to meet the new EPA requirements NMED could be found to be out of compliance with the Clean Water Act

Mitigation 1: NMED Cabinet Secretary requests exemption for electronic submission of data until the system can be upgraded to meet the requirements.

Risk 2:

Mitigation 2:

Transition to Operations: (Describe agency plan to migrate project solution to production. include agency plans to fund and maintain this project after deployment. )
The support for the application will remain the same as it is today. The NMED helpdesk will assess the issue and assign tickets to the support staff currently assigned to the SQUID application.

Revision: 6/18/13

Page 1