140R3026Q0095_-_BCOO_YAO_PWS.docx

140R3026Q0095 Bureau of Reclamation

PERFORMANCE WORK STATEMENT

Bureau of Reclamation (Reclamation) Hydrologic Database (HDB) Technical Support Services HDB Instances in Interior Region 8: Lower Colorado Basin (BCOO and YAO)

1. 1. Objective.

The objective of this performance-based order is to maintain and further develop the Bureau of Reclamation's Hydrologic Database (HDB) through the Contractor's provision of services for on-call technical support, troubleshooting of HDB malfunctions, development of modifications to HDB, support of HDB enhancements, and the development, documentation, and distribution of software applications that interface with HDB.

1.2. Background.

HDB is currently used in production throughout Upper Colorado Region, Boulder Canyon Operations Office, Upper Columbia Area Office, Eastern Colorado Office, Yuma Area Office, and Lahontan Basin Area Office. The Upper Colorado Region Water Resources Group (WRG) staff administers and provides daily support to Area and Field offices within Reclamation by utilizing HDB.

The HDB system is critical to the day-to-day operation of the Colorado River Reservoirs as well as many other reservoirs operated by Area offices within the Upper Colorado Region and within Reclamation. HDB is also critical to enabling support of ongoing modeling activities, National Environmental Policy Act (NEPA) processes, and Endangered Species Act (ESA) compliance within the Upper Colorado Region and other Reclamation offices. HDB also supports the Upper Colorado Region website and other Reclamation websites by being a central source of updated operational information to the public and stakeholders of Reclamation facilities.

The HDB system is the foundation for Reclamation's Database of Record. As conceptualized by the Database of Record defining document, the driving vision for the Hydrologic Database is to:

• Consolidate data existing in various systems and personal spreadsheets.

• Ensure complete and consistent data collection and storage, documentation of data sources.

• Provide historical information on other existing HDB data versions including information on when the data was either used or available for use.

• Perform as a means of quality control.

• Allow the user access, at any time interval, to the original source data for calculated or summarized data.

• Provide for an automated generation of time-aggregated summary data.

Reclamation managers are often interrogated formally or informally regarding the basis for operational decisions—decisions that are usually based on data such as forecasts, flows, available water supply and downstream demands. Reasonable explanation of decisions requires maintaining the exact data considered in the decision and being able to reproduce the overall state of the system and retrieve forecast information available at the time the decision was made. Enhanced capability to document the basis for the past decisions is urgently needed to assist Reclamation's defense in litigation and other processes.

Development of the database of record will facilitate Reclamation's ability to rely on and interpret data to make sound operational decisions, provide an indisputable record of data used to make past decisions, and provide a complete record of best available historical and forecast data for research proposed and policy development.

The HDB system is comprised of the Oracle based HDB database combined with a collection of software applications (computer programs) that are set up to automatically collect and store a variety of operational and hydrologic data from various data sources. Software applications within the system also allow operational staff to access the data for day-to-day operations and modeling of dams, reservoirs, and related features. These software applications collect and store data into HDB, while others allow it to be retrieved from HDB and displayed in ways that assist operation staff and the public to interpret the state of the hydrologic system.

2.0 Definitions and References.

2.1 Definitions.

Qualified Contractor Personnel means those Contractor employees directly performing on this task order possess or obtain within 6 months of award all the following skills, capabilities, and knowledge to provide a high-quality level of technical support services:

• High level of knowledge with regard to the HDB database structure, including database relationships in HDB, including DECODES and the Computational Processor.

• Full knowledge of the HDB objectives with respect to time-series data management including, but not limited to computation management, interval data consistency, model data management, validation, quality assurance, etc.

• Expert-level knowledge of Oracle database programming, as well as in other computer programming languages such as python, JAVA, C, Perl, and VB.net.

2.1.1 Business Hours.

Normal business hours are 8:00 a.m.–4:00 p.m., Monday through Friday, Mountain Time. However, the Government will pay only for time directly spent on phone calls involving HDB technical support and for any other HDB technical support offline such as travel expenses, software programming support, etc., directly related to HDB technical support. In other words, the cost of standby time for contractor personnel waiting to accept calls is not going to be chargeable to this order.

2.1.2 References.

• Hydrologic Database - Bureau of Reclamation http://www.hydrodb.net

• National Environmental Policy Act http://www.epa.gov/Compliance/nepa

• Endangered Species Act https://www.law.comell.edu/uscode/test/16/chapter-35

3. Tasks.

The Contractor shall perform the following tasks:

3.1 Provide On-Demand Technical Support.

3.1.1 Provide technical support to Reclamation LC offices in the installation and configuration tasks of the HDB and all supporting applications.

3.1.2 Consult with Reclamation on identification of problems and solutions, including development of new functional requirements.

3.1.3 Continue to provide information to Reclamation by way of informational meetings, and/or develop additional technical documents as requested.

3.1.4 Respond to e-mails, phone calls, and new or outstanding tickets for requests of information or assistance in any matters dealing with the HDB.

3.2 Database System Maintenance and System Malfunction Resolution Services.

3.2.1 Troubleshoot, diagnose, and solve software and database management problems involving HDB system malfunctions, including database upgrade strategic planning and assistance to manufacturers or Reclamation current supported product and version.

3.2.2 Create, install, and test modifications and regular updates to the operational systems and applications at Reclamation's Lower Colorado Boulder Canyon Operations Office to keep functioning and operational both the production and test HDB databases and the installed applications.

3.2.3 Maintain SCADA and USGS data loaders.

3.3 HDB Development Coordination and Documentation.

3.3.1 Attend and participate in conference calls as requested, user groups, or team meetings and other impromptu meetings, including travel to meetings that will be scheduled to discuss HDB issues. Anticipated meeting: HDB Users Group Meeting located in Salt Lake City, UT; or other agreed-upon Reclamation site.

3.3.2 Produce minor enhancements to the database and the installed systems in order to meet new specifications and guidelines requested by and in collaboration with the HDB technical team. (Minor enhancements would include new or updated scripts, stored procedures, and functions. Major enhancements that fall outside the scope of minor development will be requested in a separate task order and would be decided upon in consultation between the Contractor and Reclamation.)

3.3.3 Integrate the HDB enhancements and code modifications into the HDB source code and database scripts baseline. Any data modifications that are applied to the LC HDB primary tables will be collected and inserted into the baseline data and documented.

3.3.4 Maintain and support the HDB source code and the computation processor algorithms and appropriate documentation.

3.3.5 Design and develop new algorithms and database capabilities to support minor enhancements requested per task item 3.3.2.

3.3.6 Coordinate OPENDCS development with the other participating entities.

3.3.7 Produce and maintain HDB and OPENDCS user guide and documentation for DBA and users of HDB including tables, views, packages, procedures, functions, algorithms, and functionality.

3.3.8 On-going production and maintenance documentation for USGS data loader.

3.3.9 Maintain functionality of HDB website:

• Documentation of HDB and user guides

• Installation scripts

• HDB related tools and application software

• Bug reporting

• Administrative tools

3.3.10 Maintain 24-Month Study Report Writer application and functionality.

3.3.11 On-going work to build onto Open DCS Toolkit a component or algorithm that allows users to create alarms that send e-mails and or text messages when conditions warrant. Specific alarms will include the following: missing data, constant value (stuck sensor), duration and magnitude of accumulated data, rate of change, threshold triggering, and absolute value.

3.3.12 Assist with development and implementing water quality data collections and reporting from past records (electronic or written) into HDB.

3.3.13 Assist implementing decodes filed data polling module for collection tools on development instance as potential replacement process of collecting field data for insertion into HDB.

3.3.14 Assist with the creation of additional HDB features such as water quality and water testing results tables and query tools.

4.0 Reports.

The Contractor shall prepare and submit monthly status reports to all participating Reclamation Offices, Water Resources Groups. This report shall contain the following information:

• Dates and length of times for each incident for which on-demand HDB technical support services are rendered by the Contractor to Reclamation engineers calling in for HDB technical support.

• Description of the issue involved for each call, as well as results of the call (i.e., actions assigned; resolution of a specific system malfunction, etc.).

• Details for all authorized Contractor travel associated with this task order such as dates, times, locations, specific costs, purposes of travel, and what contributions the Contractor provided as a result of travel.

• The task order number, a brief description of the overall task, and the status of performance and completion of the task, including a determination by the Contractor as to whether it will complete the task by the deadline specified in the associated task order. If the task order is time-and-materials, the Contractor shall also provide, for each task order, the total number of hours and dollars expended to date, as well as projection on the hours and dollars estimated to be required for completing the specified task(s).