Attachment_A-Performance_Work_Statement.pdf

140R8119Q0250 LC Hydrologic Database Consulting Support

Attachment A

Performance Work Statement Hydrologic Database

Consulting Support 08/14/2019

1.0 INTRODUCTION

The Yuma Area Office (YAO), Bureau of Reclamation (BOR), 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, in order 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 four (4) days (subject to COR approval) every two months at a minimum. 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 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.

Attachment A

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 in order 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 in order 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 the course of 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/01/2019 to 08/31/2020

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.

Attachment A

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 ifexception 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.

5.1.4 Data Alarms – Work with Water Operations and IT, PWRE, and HDB community to ensure that YAO’s requirements and interests are addressed in the development of an HDB-wide system for data alarms.

5.1.5 Optimize Python and Salinity Group Computations – Continue development and testing of computation groups and complex Python computations, which modularize and greatly increase performance and ease of maintenance of YAOHDB computed data. Implement in production.

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 Migrate PowerBuilder Functionality to HDB Application Programming Interface (API) – Work with IT and contractor software developers to migrate remaining PowerBuilder data loading and reporting tools to a modular, flexible tool using the HDB API. This will include extensive analysis and documentation of functional requirements, as well as testing and training.

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 Oracle 12G upgrade – Participate in functional requirements for upgrade to Oracle 12 and database server.

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.

5.4 Wellfield Spreadsheet Migration and Groundwater Data Management

5.4.1 242 Pipeline – Gather functional requirements for data management and reporting related to the 242 pipeline, design and create meta data and computations to fulfill operational and reporting needs, design and work with IT to develop reports.

5.4.2 IHistorian Raw Data Loading and Analysis – Load raw data from the Ihistorian into YAOHDB for more thorough analysis and effective data management and aggregation. This will support more accurate flow computations and groundwater data management.

OPTION PERIOD ONE

Period of Performance: 09/01/2020 to 08/31/2021

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 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.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 YAO IT and Reclamation software developers to further enhance custom data entry and reporting tools using the HDB/API.

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.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.

5.8 Wellfield Spreadsheet Migration and Groundwater Data Management

5.8.1 242 Pipeline – Fine tuning data management, computations and reporting related to the 242 pipeline, as dictated by real-world experience with the pipeline functioning.

5.8.2 Groundwater Computations – As wellfield data analysis improves with storage of raw SCADA data in YAOHDB, quality check and/or modify and /or develop groundwater computations as needed to meet evolving requirements of the Water Operations Group.

OPTION PERIOD TWO

Period of Performance: 09/01/2021 to 08/31/2022

5.9 Data Management and Telemetry Data Analysis

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.

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

5.9.3 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.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 Database Administration – Assist/support YAO and BCOO IT in YAOHDB database management tasks, such as data storage space management and performance.

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.

5.12 Wellfied Spreadsheet Migration and Groundwater Data Management – Continued fine-tuning of groundwater data management, analysis computations as usage of telemetered wellfield data in YAOHDB increases.

5.13 Documentation and Training

5.12.1 Write a basic developer/maintenance guide for Python computations in

YAOHDB.

5.12.2 Continue to develop/maintain documentation on usage and maintenance of computation groups in YAOHDB.

OPTION PERIOD THREE

Period of Performance: 09/01/2022 to 08/31/2023

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 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.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 Database Administration – Assist/support YAO and BCOO IT in YAOHDB database management tasks, such as data storage management and performance.

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.

5.17 Documentation and Training

5.17.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.17.2 Document YAOHDB – Comprehensive update of YAOHDB database documentation to reflect new uses of Computation Processor, enhanced data loading, and important meta data.

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

5.18 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

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

Wellfield Spreadsheet Migration and Groundwater Data Management in YAOHDB Deliverable 5.4, 5.8 and 5.12

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

A User’s Manual will be written to detail how to administer the

Government will insure that the expected outcomes result from

Government will perform all tasks documented in the manual.

Incentive: Positive Past Performance

Attachment A

Task Performance Standard

Acceptable Quality Level

Surveillance Method

Incentive/ Disincentive

Deliverable

5.13 and 5.17

HDB, and how programmers should save, edit, and retrieve data from it.

instructions provided in the manual.

Disincentive:

Negative Past Performance