Attachment_1_-_PWS.pdf

Attachment 1

Performance Work Statement Hydrologic Database Consulting Support

1.0 INTRODUCTION

The Yuma Area Office (YAO), Bureau of Reclamation (Reclamation), is responsible for operations and maintenance of the Colorado River flows, releases, and salinity levels between Parker Dam and the Mexican border. This includes scheduling, monitoring, and recording the river’s flow and salinity levels, to meet the water needs of irrigation districts and the Republic of Mexico. To better accomplish this mission, we must continually refine our database methodology, how we capture and use data for predictions, and how we display data for customers. The primary repository for all Colorado River water information is the Hydrological Database (HDB).

2.0 BACKGROUND

• Water samples are collected by BOR personnel and analyzed using the Laboratory Information Management System (LIMS), an Oracle database. Salinity and other chemical data are exported from LIMS to HDB (also Oracle).

• Flows data are captured by a combination of manual and Supervisory Control and Data Acquisition (SCADA) methods and transferred to HDB.

• Well data are captured via SCADA and stored in the Yuma Area Well Management Systems (YAWMS); these are then exported to HDB.

• Data are processed and analyzed, and reports generated, using Oracle PL/SQL procedures, the HDB computation processor, PowerBuilder routines, RiverWare™, and MS Office applications.

3.0 SCOPE

The contractor shall use Oracle, SQL Server, internal HDB functions and procedures, ODBC, MS Office applications, and Unix to perform the required deliverables. The period of performance for this requirement is one (1) base year and three (3) option years; each of which has a one (1) year performance period. The contractor shall be on-site at YAO, for three (3) days (subject to COR approval) every six months. The contractor will not work federal holidays. The work consists of the following:

3.1 HDB maintenance, development, training, and documentation.

3.2 Support for Minute 323 and Future Minutes.

3.3 Migrate PowerBuilder Functionality to HDB API.

3.4 Waterfall implementations for SCADA and Ihistorian and Groundwater Data Management.

3.5 Provide general support to existing programs/application currently in use by the

Government.

4.0 APPLICABLE DIRECTIVES

4.1 Fully comply with Reclamation and DOI security standards.

4.2 All applicable Bureau of Reclamation clauses that pertain to the acquisition of software support. On-site contractor must complete a 2-hour IT Security training module

4.3 Background Investigations.

Contractor employees who will have access to Department of the Interior (DOI) information or will develop custom applications are subject to background investigations. The level/complexity of background investigations must be the same as for a federal employee holding a similar position; DM441, Chapter 3, provides guidance for the appropriate background investigations based on types of access. Ordinarily, the vendor should be responsible for paying the cost the background investigations. Existing clearances at the same or higher levels may be accepted. Work cannot begin on the DOI system until the background investigation has at least been initiated and the National Agency Check (NAC) is completed.

4.4 User account for both Lower Colorado Hydrologic Database (LCHDB2) and Yuma Area office Hydrologic Database (YAOHDB)

The contractor requires a user account for both LCHDB2 and YAOHDB due to the nature of YAO’s water operations and the sharing of data between YAO and Boulder Canyon Operations Office. YAO relies on data stored in LCHDB2 to make effective water management decisions. As a result, there is extensive connectivity between YAOHDB and LCHDB2. The contractor requires write access to all meta data in LCHDB2 to efficiently define additional data connections between YAOHDB and LCHDB2. Some of these connections must be configured on the remote database, rather than locally. Additional privileges associated with the database role but not limited to META_DATA_ROLE, SAVOIR_FAIRE, and CONNECT are needed. This will require an account with the database.

5.0 DELIVERABLES

All documents, programs, and models produced during this project will become the property of the U.S. Government.

The Contractor will notify Reclamation in writing if there is intent to publish any articles based on work performed under this project. Draft publications will be reviewed by Reclamation staff, and ultimate permission to publish shall be authorized by Reclamation in writing.

BASE YEAR

Period of Performance: 09/20/2023 to 09/19/2024

5.1 Data Management, Quality Analysis, and Quality Control

5.1.1 Ongoing management of YAOHDB data, including creating meta data to support new data acquisition, creating data-loading protocols, and designing and building needed computations.

5.1.2 Periodic inspection of incoming Supervisory Control and Data Acquisition and XConnect data to ensure that it is in expected range and of required frequency and determine if exception handling protocols need to be implemented or if instrument calibration is warranted. Implement range checking on telemetry values. Update rating tables as needed.

5.1.3 Minute 323 – Work with International Water Boundary Commission (IBWC), YAO, and BCOO Water Operations and BCOO Accounting to continue to quality check and optimize management of data related to Minute

323. This includes analysis/validation of spreadsheets and reports provided by IBWC, data loader modification and development, maintenance, and creation of computations in YAOHDB, meta data to support enhanced routing options, and reporting requirements and design. Specifically add data flag functionality to the IBWC data loader for Reserve Water and BWSCP created and delivered, provide data entry capabilities for DPOC water for NIB credit, and integrate BWSCP delivered into computations once it is being reported by IBWC.

5.1.4 Maintain, Optimize, and Create Computation using OpenDCS - Continue design, development, and testing of computation, utilizing computation groups where possible and Python as needed to handle complex situation.

Focus to be given to salinity computations for groundwater data.

Continue to work with PWRI and HDB API developers to identify potential feature enhancements and opportunities for optimization.

5.2 Database and End User Support

5.2.1 Water Operations Support – Provide technical transfer, feasibility assessments and ad-hoc data summaries to Water Operations personnel as needed.

5.2.2 HDB Application Programming Interface (API) – Work with IT and Reclamation software developers to further enhance custom data entry and reporting tools migrate remaining PowerBuilder data loading and reporting tools using the HDB API. High priority to be given to functional requirements for and design/implementation of a new Table 4 Report.

5.2.3 Database Administration - Assist/support YAO and BCOO IT in YAOHDB database management tasks, such as data storage space management and performance.

5.2.4 Ongoing database schema maintenance - Analyze database schema objects (tables, views, stored procedures, functions) and drop as permitted by updated loading applications and computations.

5.2.5 Waterfall implementation for SCADA and Ihistorian communication across firewall to HDB. Provide technical support for implementation of SqlServer database mirroring from behind firewall to administration side of firewall, and for uploading of administration side SqlServer data into YAOHDB.

Determine new/modified functional requirements for YAOHDB to communicate with administration side SqlServer database; design and implement changes.

5.2.6 Create a tool for analysis of Mexico Excess Flows component data. This tool will retrieve all component data from LCHDB and YAOHDB and highlight any differences. It will automate using the HDB API.

5.3 RiverWare Support – Provide analyses of data sources, gather requested data, work with modeler(s) to design effective methods for managing and computing data required by

RiverWare models, and create and maintain indicated meta data and RiverWare/HDB mappings. Specifically assist with analysis of data sources for the Lower Colorado River Efficiency Study.

5.4 Groundwater Data Management

5.4.1 242 Pipeline – Fine tune data management, computations and reporting related to the 242 pipeline. Automate storage of measured flows for 242 Pipeline and Yuma Mesa Conduit once they are available, and integrate them as needed into computations, reports, and data analysis tools.

5.4.2 IHistorian data Loading and Analysis

5.4.2.1 Update SqlServer database stored procedures and

YAOHDB mappings to support new well status tags for the 242 wells as their instruments are upgraded. Support YAO IT in reconfiguring calculated flow tags.

5.4.2.2 Modify SqlServer database stored procedures to capture more than one hour of IHistorian data in case of extended down-time in communication between the two.

5.4.2.3 Determine how to access more accurate power data

(daily energy consumed) for production wells from Ihistorian and load this into YAOHDB.

5.4.2.4 Work with YAO Water Operations to determine how to

further integrate drainage well-based data into operational decisions.

OPTION PERIOD ONE

Period of Performance: 09/20/2024 to 09/19/2025

5.5 Data Management, Quality Analysis, and Quality Control

5.5.1 Ongoing management of YAOHDB data, including creating meta data to support new data acquisition, creating data-loading protocols, and designing and building needed computations.

5.5.2 Periodic inspection of incoming SCADA and XConnect data to ensure that it is in expected range and of required frequency and determine if exception handling protocols needs to be implemented or if instrument calibration is warranted. Implement range checking on telemetry values. Update rating tables as needed.

5.5.3 Import valid r_base data for which there is no corresponding r_interval data into the appropriate interval tables.

5.5.4 Minute 323 - Work with IBWC, YAO, and BCOO Water Operations and BCOO Accounting to continue to quality check and optimize management of data related to Minute 323. This includes analysis/validation of spreadsheets and reports provided by IBWC, data loader modification and development, maintenance, and creation of computations in YAOHDB, meta data to support enhanced routing options, and reporting requirements and design.

Specifically add data flag functionality to the IBWC data loader for Reserve Water and BWSCP created and delivered, provide data entry capabilities for DPOC water for NIB credit, and integrate BWSCP delivered into computations once it is being reported by IBWC.

5.5.5 Maintain, Optimize, and Create Computation using OpenDCS - Continue design, development, and testing of computation, utilizing computation groups where possible and Python as needed to handle complex situation.

Focus to be given to salinity computations for groundwater data.

Continue to work with PWRI and HDB API developers to identify potential feature enhancements and opportunities for optimization.

5.5.6 Data Alarms - Work with YAO Water Operations personnel to define data alarm conditions and implement them as possible using the alarm functionality of OpenDCS. Train users to implement their own data alarms using OpenDCS.

5.6 Database and End User Support

5.6.1 Water Operations Support – Provide technical transfer, feasibility assessments and ad-hoc data queries and data summaries to Water Operations personnel as needed.

5.6.2 HDB API Data Entry and Report Development – Continue work with YAO IT and Reclamation software developers to further enhance custom data entry and reporting tools using the HDB/API. High priority to be given to functional requirements for and design/implementation of a new Table 4 Report.

5.6.3 Database Administration – Assist/support YAO and BCOO IT in YAOHDB database management tasks, such as data storage space management and performance.

5.6.4 Ongoing database schema maintenance – Analyze database schema objects (tables, views, and stored procedures functions) and drop as permitted by updated data loading applications and computations.

5.6.5 Support for possible LIMS upgrade, including analysis, design, and implementation of any YAOHDB schema changes or code modifications.

5.7 RiverWare Support – Provide analyses of data sources, gather requested data, work with modeler(s) to design effective methods for managing and computing data required by RiverWare models, and create and maintain indicated meta data and RiverWare/HDB mappings. Specifically assist with analysis of data sources for the Lower Colorado River Efficiency Study.

5.8 Groundwater Data Management – Continue work with YAO Water Operations to determine how to further integrate drainage well-based data into operational decisions.

This includes identifying new sources of groundwater data storing the data into YAOHDB and synthesizing it into further data points for decision-making support.

OPTION PERIOD TWO

Period of Performance: 09/20/2025 to 09/19/2026

5.9 Data Management, Quality Analysis, and Quality Control

5.9.1 Ongoing management of YAOHDB data, including creating meta data to support new data acquisition, creating data loading protocols, and designing and building needed computations.

5.9.2 Periodic inspection of incoming SCADA and XConnect data to ensure that it is in expected range and of required frequency and determine if exception handling protocols need to be implemented or if instrument calibration is warranted.

5.9.3 Implement range checking on telemetry values. Update rating tables as needed.

5.9.4 Minute 323 – Work with IBWC, YAO, and BCOO Water

Operations BCOO Accounting to continue to quality check and optimize management of data related to Minute 323. This includes analysis/validation of spreadsheets and reports provided by IBWC, data loader modification and development, maintenance, and creation of computations in YAOHDB, meta data to support enhanced routing options, and reporting requirements and design.

5.9.5 Maintain, Optimize and Create Computations using OPENDCS - Continue design, development, and testing of computation, utilizing computation groups where possible and Python as needed to handle complex situation.

Focus to be given to salinity computations for groundwater data.

Continue to work with PWRI and HDB API developers to identify potential feature enhancements and opportunities for optimization.

5.10 Database and End User Support

5.10.1 Water Operations Support – Provide technical transfer, feasibility assessments and ad-hoc data queries and data summaries to Water Operations personnel as needed.

5.10.2 HDB API Data Entry and Report Development – Continue work with YAO IT and Reclamation software developers to further enhance custom data entry and reporting tools using the HDB API. High priority to be given to functional requirements for and design/implementation of a new Table 4 Report.

5.10.3 Database Administration – Assist/support YAO and BCOO IT in YAOHDB database management tasks, such as data storage space management and performance.

5.10.4 Ongoing database schema maintenance – Analyze database schema objects (tables, views, stored procedures, functions) and drop as permitted by updated data loading applications and computations.

5.10.5 Support for future LIMS upgrade, including analysis, design, and implementation of any YAOHDB schema changes or code modifications.

5.11 RiverWare Support – Provide analyses of data sources, gather requested data, work with modeler(s) to design effective methods for managing and computing data required by RiverWare models, create, and maintain indicated meta data and RiverWare/HDB mappings. Specifically assist with analysis of data sources for the Lower Colorado River Efficiency Study.

5.12 Groundwater Data Management – Continue working with YAO Water Operations to determine how to further integrate drainage well-based data into operational decisions. This includes identifying new sources of groundwater data, storing the data in YAOHDB, and synthesizing it into further data points for decision-making support.

5.13 Documentation and Training

5.12.1 Update / maintain existing documentation of the time series data in YAOHDB, including source of data, its usage, and how the data compares with data for the same physical location but from a different source.

5.12.2 Document all data maintenance, query and analysis tools regularly used by Water Operations and database programmers and administrators.

5.12.3 Document Python Computations

OPTION PERIOD THREE

Period of Performance: 09/20/2026 to 09/19/2027

5.14 Data Management, Quality Analysis, and Quality Control

5.14.1 Ongoing management of YAOHDB, including creating meta data to support new data acquisition, creating data loading protocols, and designing and building needed computations.

5.14.2 Periodic inspection of incoming SCADA and XConnect data to ensure that it is in expected range and of required frequency and determine if exception handling protocols need to be implemented or if instrument calibration is warranted.

5.14.3 Minute 323 – Work with IBWC, YAO, and BCOO Water Operations and BCOO Accounting to continue to quality check and optimize management of data related to Minute 323. This includes analysis/validation of spreadsheets and reports provided by IBWC, data loader modification and development, maintenance and creation of computations in YAOHDB, meta data to support enhanced routing options, and reporting requirements and design.

5.14.4 Maintain, Optimize, and Create Computations using OpenDCS – Continue design, development, and testing of computation, utilizing computation groups where possible and Python as needed to handle complex situation.

Focus to be given to salinity computations for groundwater data. Continue to work with PWRI and HDB API developers to identify potential feature enhancements and opportunities for optimization.

5.15 Database and End User Support

5.15.1 Water Operations Support – Provide technical transfer, feasibility assessments and ad-hoc data queries and data summaries to Water Operations personnel as needed.

5.15.2 HDB API Data Entry and Report Development – Continue work with YAO IT and Reclamation software developers to further enhance custom data entry and reporting tools using the HDB API. High priority to be given to functional requirements for and design/implementation of a new Table 4 Report.

5.15.3 Database Administration – Assist/support YAO and BCOO IT in YAOHDB database management tasks, such as data storage management and performance.

5.15.4 Ongoing database schema maintenance – Analyze database schema objects (tables, views, stored procedures, functions) and drop as permitted by updated data loading applications and computations.

5.16 RiverWare Support – Provide analyses of data sources, gather requested data, work with modeler(s) to design effective methods for managing and computing data required by RiverWare models, create, and maintain indicated meta data and RiverWare/HDB mappings. Specifically assist with analysis of data sources for the

Lower Colorado River Efficiency Study.

5.17 Groundwater Data Management – Continue work with YAO Water Operations to determine how to further integrate drainage well-based data into operational decisions. This includes identifying new sources of groundwater data, storing the data in YAOHDB and synthesizing it into further data points for decision-making support.

5.18 Documentation and Training

5.18.1 Document YAOHDB Data Sources – Create comprehensive documentation of the time series data in YAOHDB, including source of data, its usage, and how the data compares with data for the same physical location but from a different source.

5.18.2 Document YAOHDB – Comprehensive update of YAOHDB database documentation to reflect new uses of Computation Processor, enhanced data loading, and important meta data.

5.18.3 YAOHDB Training – Gather functional requirements for YAOHDB training from both Water Operations and IT, integrate into previously delivered training, and deliver training.

5.19 Holidays

The Federal Government and contractors will observe the following holidays. Normal work functions are closed for the holidays listed below. Schedules may or may not observe these holidays.

New Year’s Day

Martin Luther King Jr. Birthday

President's Day

Memorial Day

Juneteenth Day

Independence Day

Labor Day

Columbus Day

Veterans Day

Thanksgiving

Christmas

Holidays also include any other holiday designated by Federal law, Executive Order or Presidential Proclamation.

When holidays fall on a Saturday, the preceding Friday is observed; when any such day falls on a Sunday, the following Monday is observed. Observance of such days by Government personnel shall not be cause for additional period of performance or entitlement to compensation except as set forth in the contract.

6. PERFORMANCE REQUIREMENTS SUMMARY

Task Performance

Standard Acceptable

Quality Level Surveillance

Method Incentive/

Disincentive

Data Management, Quality Analysis, and Quality Control Deliverable 5.1, 5.5, 5.9, and 5.14

Develop functionality to manage and quality check data in

YAOHDB

All data and computations are executed as before the conversion.

Government will confirm that no data was lost.

Government will run inspect data before and after export to HDB.

Incentive: Positive Past Performance Disincentive:

Negative Past Performance

Database and End User Support Deliverable 5.2, 5.6, 5.10, and 5.15

Develop functionality to manage the changes in HDB as needed

All data and computations migrated without loss. Government will confirm that no data was lost.

Government will inspect data before and after edits to HDB.

Incentive: Positive Past Performance Disincentive:

Negative Past Performance

RiverWare Support for Deliverable 5.3, 5.7, 5.11, and 5.16

Create new interfaces between Riverware and

HDB

Data integrity will be maintained.

Government will confirm that no data was lost.

Government will inspect data before and after export to HDB.

Incentive: Positive Past Performance Disincentive:

Negative Past Performance

Groundwater Data Management in YAOHDB Deliverable 5.4, 5.8, 5.12 and 5.17

Begin development of Wellfield spreadsheet in HDB using computation processor

All data and computations are executed as before the conversion.

Government will confirm that both sets of reports yield identical results.

Government will run reports in parallel in Excel spreadsheet and

HDB.

Incentive: Positive Past Performance Disincentive:

Negative Past Performance

Ongoing Update HDB system Documentation Deliverable

5.13 and 5.18

A User’s Manual will be written to detail how to administer the HDB, and how programmers should save, edit, and retrieve data from it.

Government will insure that the expected outcomes result from instructions provided in the manual.

Government will perform all tasks documented in the manual.

Incentive: Positive Past Performance Disincentive:

Negative Past Performance