HomeMy WebLinkAbout14- Task Order 6-13-14 Tetra Tech, Inc. Bozeman Landfill Work Plan for Database Management System Task Order
Work Plan for Database Management System
Bozeman Landfill
Bozeman, Montana
June 13, 2014
CLIENT: CONSULTANT:
The City of Bozeman Tetra Tech. Inc.
P.O. Box 1230 851 Bridger Drive, Ste 6
Bozeman, Montana 59711-1230 Bozeman, MT 59715
Attention: Mr. Craig Woolard, PE Attention: Mr. Kirk Miller
INTRODUCTION
This work plan is targeted at improving accessibility to the large amount of data collected over
the last 20 years or more at the Bozeman Landfill site. The plan includes the integration of
tabular data from ongoing groundwater, landfill gas system monitoring, and indoor air programs
into a single relational database and compilation of relevant documents into a single easily
accessible, yet secure, online document management system. Creating a single database
allows for a wider array of queries to be developed to improve the ability for City staff and their
consultants and counsel to retrieve, download and view data. The approach and outputs that
will be programmed into the database management system can be modified at any time,
however, we have identified what we anticipate will address the bulk of your needs.
Data access gained from this database management system will improve the speed at which
project staff can find and present data for analysis, reporting or communication with the public.
The database management system will also make it possible for lay people within City
government to access the data with some basic training. In general, we believe the system will
lower costs of the overall landfill gas program by allowing City staff direct access to the data
instead of having to request data from Tetra Tech. The data management system should also
help expedite the landfill Corrective Measures Assessment (CMA) and remedial design process
by making it easier and faster for Tetra Tech staff to find, export and analyze data.
During our May 21st meeting the City selected the Access based data management system out
of the three options we presented. As we delved into our approach to developing an Access
based system, we determined that we could provide the basic level of a web based user
interface for less money than the Access based system, while providing better functionality. In
addition, web based systems are easier to upgrade and modify since a single program needs to
be modified instead of having to send revisions to each user. Hence, going forward this web-
based approach will also be less expensive to keep populated with new date as well as to
upgrade and modify.
SCOPE OF WORK
Most numerical data (lab results, water levels, vacuum readings etc.) are already in a Microsoft
Access database format. Additionally, some groundwater and landfill gas system data are in
Microsoft Excel files. Use of the current Access database requires moderate to high levels of
knowledge in the use of database management systems and Microsoft Access. We propose to
migrate these data to a Microsoft SQL Server database hosted on a Tetra Tech cloud server
and develop a web application to support query, reporting, and export of the data. The web
Work Pian —Data Base ivianagernent System
Bozeman Landfill, Bozeman, Montana
June 13, 2014
application will allow non-technical users to access and query the database. The application will
have pull down menus, buttons, and text boxes wherein data can be queried and outputs
produced in tables or as reports. It will also allow the export of data to formats compatible with
Microsoft Excel for charting, graphing, and analysis. The interface will be set up to match the
outputs anticipated to be needed by the City, their consultants and counsel. In addition to
making the data easier to access, the web application will ensure there is a single authoritative
database being accessed by all project stakeholders, allow control of access to data, and allow
new data to be available to all users immediately upon database update.
Table 1 presents the data queries that will be possible by a person with a relatively basic
understanding of the data through an easy-to-use interface. We have tried to anticipate the
likely queries. However, additional queries can be easily programmed into the system at a later
date as specific data needs arise.
This approach requires a minimal understanding of databases to make queries. We will design
the system to be intuitive to use as possible and provide a "Quick Start" guide for its use. We
can also provide this training to whomever the City identifies as needing it.
Any data in the system will be exportable to Excel files in a wide variety of data subsets and
formats for more advanced manipulation (e.g., risk assessment) or graphing. Additionally, Tetra
Tech can add graphing, mapping, and analysis functionality directly into the web application at
the City's request.
TABLE 1 QUERIES TO BE 3ROGRAMMED INTO USER INTERFACE
DATA SET PROGRAMMED QUERIES
Indoor Air VOCs lab data Query by house number, parameter, date range, pre and
post mitigation. Preset outputs for homeowner summary
tables to list all houses above a screening level.
Indoor Air radon lab data Query by house number, parameter, date range, pre or
post mitigation.
Groundwater quality VOC, metals and Query by well number, parameter, date range, well
other lab data completion type. Pre-set outputs for monitoring reports
and statistical analysis.
Groundwater elevation Query by well number, date range. Preset outputs for
monitoring reports and import into GIS for contouring.
Methane monitoring wells and Soil Gas Query by well number, parameter, date range. Preset
Probes field measurements outputs for monitoring reports.
Flare field measurements Query by parameter and date range. Preset outputs for
monitoring reports.
Methane monitoring wells, Soil Gas Query by well number, parameter, date range. Preset
Probes and Flare laboratory data outputs for monitoring reports.
Mitigation System vacuum data Query by house number, probe number, date range and
value range. Preset outputs for import into CAD.
Page 2 of 4
Work Plan —Data Base Management System
Bozeman Landfill, Bozeman, Montana
June 13, 2014
Management of Field Forms, Field Notes, Reports and other information in PDF format
A large portion of document data collected for the long term groundwater monitoring project and
the indoor air project have been scanned to PDFs as part of the ongoing project. The remaining
documents are either stored as paper copies in our project files or, in the case of reports, are in
electronic formats (Word, CAD or PDFs for more recent reports). Relevant documents will be
scanned to PDFs, so all documents are in the same format. This information will be uploaded to
the SharePoint site that has been set up for the project. This site will be accessible to all parties
that are identified by the City as needing access. The information will be easily accessible
through an internet connection and authorization provided by Tetra Tech.
The PDF files will be organized by home or well number in directories similar to a network
directory. This way it is simple to search for all documents related to a specific house or well
(field forms, results letters, data tables etc.). The type of documents to be included and how
they will be organized are outlined in Table 2.
TABLE 2 FIELD FORMS, NOTES AND REPORTS ON SHAREPOINT SITE
DATA SET PROGRAMMED QUERIES
Indoor Air Field Forms and field PDFs of forms or notes on SharePoint site organized by
notes house number.
Indoor Air homeowner letters PDFs of complete letters on SharePoint site organized by
(with Floor Plans, Schematics house number and pre and post mitigation.
and data tables)
Indoor Air radon field forms and PDFs of forms or notes on SharePoint site organized by
notes house number.
Groundwater field forms and PDFs of forms or notes on SharePoint site organized by well
notes and well completion logs number.
Methane monitoring, Soil Gas PDFs of forms or notes on SharePoint site organized by well
Probes and flare monitoring field number.
notes
Mitigation System Floor Plans PDFs of CAD drawings on SharePoint site organized by
house number.
Data Validation Reports PDFs of all reports will be grouped together on SharePoint
site since each report may cover data from multiple houses).
Schedule
Tetra Tech's anticipated schedule to complete this task is to have the data application
completed in three to six weeks for the City to review then full functionality in six weeks from
authorization to proceed.
Page 3 of 4
Work Plan—Data Base Management System
Bozeman Landfill, Bozeman, Montana
June 13, 2014
Compensation
Estimated costs to complete the foregoing scope of services are not to exceed $23,550. A
breakdown of these proposed expenditures is contained in the attached cost estimate. The
services will be conducted on a time and materials basis.
Special Conditions
This Task Order will be considered "Additional Services" under Article 5 of the terms and
conditions of the existing Professional Services Agreement between Tetra Tech and the City of
Bozeman, dated April 15, 2014.
CLIENT: CONSULTANT:
City of Bozeman - . Tetra T 6,1
By: \Jfti4. , By: �t
Its: vi C\1 fl ein Its: Senior Project Manager
Date: OT 221 2b1/4 Date: June 13. 2014
NOTICE OF CONFIDENTIALITY This Task Order(Including attachments) contains information confidential to Tetra
Tech, Inc., its subsidiaries, and subcontractors. It is intended only for City of Bozeman to consider Tetra Tech
providing professional services at the Bozeman Landfill Any other use is prohibited. without the prior written consent
of Tetra Tech.
Page 4 of 4
ESTIMATED BUDGET
MODIFICATION TO CONTRACT FOR DATABASE MANAGEMENT SYSTEM
BOZEMAN LANDFILL
BOZEMAN,MONTANA
Date Submitted:6/13/2014
Database Management System
Labor Units Rate Cost
Sr.Project Manager,per hour 4 $170.00 $680.00
Sr Programmer 14 $175 00 $2,450 00
Project Programmer(2 staff) 176 $105,00 $18,480.00
Hydrogeologist 12 $95.00 $1,140.00
GIS 12 $55 00 $660 00
Labor Subtotal $23,410 00
DIRECT COSTS
Tetra Tech Vehicle Mileage,per mile 250 $0.56 $140.00
Per Diem and Lodging,per person per day $140.00 $0.00
Subtotal $140.00
TOTAL $23,650.00
Tetra Tech
lit TETRA TECH MEM O
To: Clerk of the Bozeman City Commission
Cc w/o Rick Hixson, P.E., Craig Woolard, P.E., City of Bozeman
attachment:
From: Larry Cawifield, P.E. f'
Date: September 3, 2014
Subject: Transmittal of Bozeman Landfill Task Order for Database Management System
Enclosed are two copies of the subject Task Order with original signatures for consideration by the Bozeman City
Commission. Do not hesitate to contact me at (406) 461-0234 if there are any questions.
Tetra Tech
851 Bridger Drive. Bozeman, MT. 59718
Tel 406.582 8780 tetratech.com