The database was developed in coordination with the client using Microsoft Office Excel 2007. This format was chosen in order to create an asset management tool that is commonly understood by the engineering staff that would be working with the system. The Excel spreadsheets provide ease in the creation and manipu-lation of the large amount of data through simple func-tions such as sorting and filtering, and provide the ability to produce reports summarizing filtered and sorted data through pivot tables.
Different groups of worksheets make up the Brooklyn Battery Tunnel’s database:
• Support/Reference worksheets;
• Master-Administrator Only worksheet;
• End-User Database worksheets;
• Database Expansion worksheets; and
• Summary worksheet(s).
Support/Reference worksheets
These worksheets are the source of defined and known information contained in the Master-Administrator Only worksheets. These include: a summary of the repair/
rehabilitation projects and the coding used to define the element level that is inventoried and rated, along with the deficiency types applicable to each element.
The Support/Reference worksheets standardize the terminology throughout the databases and minimize manual work during database updates by simplifying the steps for modifying or expanding the current range of data in the databases. The vast majority of the current
DECEMBER 2014 http://www.pbworld.com/news/publications.aspx
Figure 2 - Sample listing of element types
Fire and Life Safety Asset Management and Program Support
information included in the Master-Administrator Only worksheet comes from the support worksheets through equations and links.
The identified elements for the Tunnel Ventilation Sys-tem within the tunnel worksheets are shown in Figure 3.
Master-Administrator Only worksheet
The Master-Administrator worksheet contains all the original data from the inventory and condition inspec-tion. This worksheet is locked and can only be manipu-lated by users with necessary rights and passwords.
End-User Database worksheets
The End-User Database worksheets include a Filter worksheet and a Pivot Table worksheet and are in-tended to be used for queries, data access, sorting,
filtering, and reporting without working directly in the database spreadsheet. The data is therefore protected from inadvertent changes while sorting and filtering are being performed.
The Filter worksheet is set-up with Microsoft Excel tools to filter specific data by area, regions, stations, element(s), and element ratings and/or deficiencies. This worksheet includes a sum of the quantity column and average of the rating column. This is also a dynamic calculation and provides the values for the elements that are visible during the filtering process. As an example, to manage the elements associated with the tunnel fireline, such as the fire hose valves and gate valves, the worksheet can be filtered as shown in Figure 4.
The Pivot Table worksheet allows users to extract spe-cific data from the databases by turning on spespe-cific col-umns from the master database and then filtering them to specific values. In contrast to the Filter worksheet, the Pivot Table allows for more specific extractions of data and allows users to display and print only the col-umns and rows that are needed (see Figure 5). On the right hand side, the “Pivot Table Field List” lists all the columns that are referenced from the Master-Adminis-trator Only worksheet.
As part of the tunnel drainage system, pumps are lo-cated in the Brooklyn Portal Pump Station, the Manhat-tan Blower Building, and the Governors Island Ventilation Building. A query of the tunnel drainage system housed on the Brooklyn side would result as shown in Figure 5.
Database Expansion worksheets
The ‘New Element’ tabs within the tunnel and buildings da-tabase workbooks are intended for use only when adding
DECEMBER 2014 http://www.pbworld.com/news/publications.aspx
Discipline System Element
Structural Tunnel Ventilation Acoustic Baffl e Structural Tunnel Ventilation Damper Door Motor Support Structural Tunnel Ventilation Duct - Horiz. & Vert.
Structural Tunnel Ventilation Fan Motor Pad Structural Tunnel Ventilation Fan Housing/Shaft Pad Structural Tunnel Ventilation Damper Door Mechanical Tunnel Ventilation Damper Door Motor
Mechanical Tunnel Ventilation Fan
Mechanical Tunnel Ventilation Fan Housing Mechanical Tunnel Ventilation Fan Shaft Mechanical Tunnel Ventilation Fan Motor Mechanical Tunnel Ventilation Fan Motor Belt Guard
Electrical Tunnel Ventilation Fan Motor Control PMP Electrical Tunnel Ventilation PLC - I/O Rack Panel Electrical Tunnel Ventilation Power Capacitor
Figure 3 - Identified elements for the tunnel ventilation system
Figure 4 - Filter worksheet with elements associated with the tunnel fireline
Fire and Life Safety Asset Management and Program Support
new elements to the Master-Administrator Only worksheet.
This allows the database to be expanded to include new items that are added as part of the facility’s updates.
Summary worksheets
These worksheets are provided for the tunnel systems and sum each element’s total quantity and the element’s quantity per rating value, and quantity per deficiency type.
The east and west tubes of the Brooklyn Battery Tunnel are tallied separately. Therefore the condition rating of various tunnel elements can be reviewed per query.
Conclusion
The asset management database is an essential tool that can be used on a day-to-day basis or when scheduling and budgeting projects that will maintain the facility in a “state of good repair”. Within the database there are several work-sheets that can be used to manage the various assets.
Some are better suited for reports while other worksheets are better suited for obtaining data for monitoring the asset condition. Each worksheet aids in obtaining the necessary data, in the desired format, to make an informed decision.
During the aftermath of Superstorm Sandy, the data-base was used to determine replacement quantities for items damaged in submerged sections of the tubes (water filled approximately 6,000 feet of the 9,000 foot-long tunnel) and in the flooded sublevels of the buildings, and to obtain pertinent sump pump informa-tion. Its use during the operational response to that storm in 2012 contributed to the east tube reopening to limited bus service after 13 days and the west tube soon afterwards.
Ferdinand Portuguez is a Supervising Engineer with 22 years of experience in structural design, construction management, con-dition inspections, and cost estimating and assessment. He has a structural background and is a registered PE in New York State.
Debra Moolin is a Structural Engineer (PE) with over 30 years of experience and a focus on bridge and tunnel repair and rehabilitation. She has worked on all project phases, from in-spection, evaluation, and testing through design and construc-tion support services.
DECEMBER 2014 http://www.pbworld.com/news/publications.aspx
Figure 5 - Pivot Table worksheet showing query of the tunnel drainage system