H APPLICATION INVENTORY

(1)

Application/Software Name

Begin and End Date Brief Technical Description Brief Data Description Brief Application Description 331 131 26 40 43 0 29 50 Resources Attachment H APPLICATION INVENTORY Implementation of the Milwaukee Metropolitan Sewerage District's Geographic Information System Vision

CONTRACT M03003E04 Real Estate GIS--M03003P02

7/30/02-12/28/02

Real Estate GIS data tables are stored on an Oracle server; a Microsoft Access 2000 front exists. Access to these tables is required to view, edit, and add attribute data to the Real Estate GIS system. Asp.net is used to serve up information on a District intranet site. MicroStation SE connects to this Oracle database to create an interactive GIS.

457 Historic Scanned Documents (pdf); 2074 scanned-linked Real Estate Documents (pdf); 152 not linked; 332 tiled dgn's containing MMSD Real Estate Interests; 1 Microsoft Real Estate Data Editor Database front; 2116 polygons contained within 1 merged shapefile (331 duplicates need to be eliminated)

The Real Estate GIS was created under contract M03003P02 under the direction of Dave Misun (MMSD PM) in conjunction with Kapur and delivered in 2004. The Real Estate GIS aimed to achieve the following: (1) Move Existing Real Estate information to a GIS, (2) Provide the ability to create new Real Estate information in a GIS, (3) Make a useful, usable, and extendable GIS. Prior to the GIS, District Real Estate interests were hand drawn in a plat book; records were kept hard copy. This project digitized District Real Estate interests (polygons), scanned hard copy documentation, and created a linked orcale database with an Access front containing pertinent attribute data. The application was built with a GIS front as well as a database entry. Upon delivery, the system was not updated on a regular basis. Please see Backlog summary for specific maintenance backlog calculations. To ease use, the tiled dgn's were merged and converted to a shapefile, which is now used in ArcMap to create simple maps illustrating parcel locations in respect to other existing Conveyance, Watercourse, and base data.

Estimated Data Backlog Calculations

Number of duplicate polygons that must be eliminated Number of historic documents that require replacements

Documents that have been numbered, scanned, data in access database and drawn in plat book

Documents numbered, scanned and data in access database Documents numbered and scanned

Documents numbered

Documents that need to be numbered Number of errata

ArcGIS Connection Procedure.doc; MicroStation Link Procedurenew.doc; MicroStation MMSDID TAG Procedures.doc; Real Estate GIS Database Connection - System DSN Configuration.doc; ErrataListFinal.doc; Training Presentation.ppt

(2)

Begin and End Date Brief Technical Description Brief Data Description Brief Application Description 30+ 12 Resources Watercourse GIS--M03003E01 6/15/03-3/31/04

Watercourse GIS data resides in an ESRI ArcSDE 9.x Personal Geodatabase. This application uses ArcMap 9.x and has a supplemental ArcIMS 8.x intranet site (currently off-line).

There are currently 1,456 scanned project files (tifs). The Personal Geodatabase contains 23 datasets and a total of 69 Feature Classes.

The Watercourse GIS was created under contract M03003E01 under the direction of Dave Misun (MMSD PM) in conjunction with Dakota Intertek and delivered in 2004. The Watercourse GIS aimed to achieve the following: (1) Conversion and integration of District Watercourse information into a GIS compatible with District standards, develop and implement a browser based front-end application, and provide tools for future maintenance. A customized ArcMap interface was delivered; however, data maintenance has not been performed and tools have not been used. ArcIMS was upgraded to 9.1, and the intranet site was never upgraded. The system has not been widely used. The Personal Geodatabase served as a basis for the development of the Watercourse Inspection and Maintenance Program Project which feeds directly into the Asset Information Management System (AIMS).

Estimated Data Backlog Calculations

QAQC of MMSD owned data currently within PGDB

Number of Contracts Requiring Update into Watercourse GIS

MMSD WC_Use Case Design and Need Analysis.doc; Feature Attribute Table spreadsheets; MMSD WC Feature Conversion.doc; MMSD WC Browser

Functionality.doc; MMSD WC Client Side Tools and Functionality.doc; MMSD WC Introduction to ArcGIS.doc; MMSD WC Training Cover Page and TOC; MMSD WC Project Specifications; MMSD WC Symbology; Scheme; System Architecture

(3)

Begin and End Date Brief Technical Description Brief Data Description Brief Application Description Estimated Data Resources unknown

The purpose of the CMMS Watercourse Database is to store, catalog, organize and report watercourse inspection information. The CMMS database uses a hierarchical approach to watercourse classification. Every inspectable object belongs to a Watershed, SubWatershed, and Reach. Inspectable objects are any structures or natural elements that can possibly impact the flow and stability of the watercourse. They are divided into the following categories: flow devices, channel and flood plains, channel linings/armaments, habitat devices, and electrical control structures. The CMMS database is used to respond to significant events such as storms and constituent call-ins. It is also used to proactively monitor and maintain natural and engineered watercourse elements. Data is stored in Microsoft SQL Server and a personal geodatabase while the user interacts with the data through ArcView and a Microsoft Access “front end”.

Responsibility for Inspection backlog does not effect this project. CMMS Database User Manual

Watercourse CMMS

2005

The Watercourse CMMS Database resides in SQL complimented by an Access 2003 front which is connected to a simple ArMap 9.x interface. The database interacts with the GIS to allow for dual viewing of graphic and attribute data. A ToughBook application utilizing replication allows for entry of data in the field. NOTE: This application used the Watercourse GIS personal geodatabase as a foundation from which Inspection Points were created. Watercourse CMMS Inspection Points are then "dumped" into AIMS on a regular basis.

(4)

Begin and End Date Brief Technical Description Brief Data Description 4252 3664 4639 12702 Brief Application Description 238 4 44 42 Resources Conveyance GIS--M03003 12/1997- 6/2000

The Conveyance GIS has three major components: CAD software, Oracle database, and customized application. The Oracle 8 database records (Client PC's use Oracle 9i to connent) relate to CAD (MicroStation V7) key fields. For each element, one table contains facility information and a second table contains hydraulic analysis and CAD information. The customized application consists of basic structure /annotation mapping and editing, structure data query/edit/raster file display, database/CAD file verification and reconciliation, system-wide and geographic area querying/reporting/mapping, Winref (an application that provides safe and quick access to archival maps and data), Buildmap (an application that can build customized map products safely and quickly), and MMSD Sewer (an application that can be used to maintain CAD and database).

Design files are stored in quarter-section tiled format and named and accessed by utilizing the quarter-section number. A file extension indicates which map series is represented by that file. For example, the design file representing the Near Surface Collector system located within quarter-section 540 will be named 540.nsc. The Conveyance GIS currently utilizes the following general data categories and file numbers respectively:

MMSD Data (5 data types)

City of Milwaukee Data (2 data types) Base Data (7 data types)

Scans (MMSD Contracts, TIFs)

The Conveyance GIS was created under contract M03003 under the direction of Nancy Schultz (MMSD TS) in conjunction with the City of Milwaukee over a 3.5 year period and delivered in 2000. The Conveyance GIS constructed a digital information system for MMSD facilities within Milwaukee County. It consists of the following components: • CAD files display facilities including manholes (4,555), pipes (7,205), connections (2,278), grates, vents, lids, building slabs, outfalls, and other information

• Database records contain sewer facility, hydraulic analysis, contract, raster file, and retired facility information.

• Raster images of drawings such as construction plans

• Software to input, maintain, display, query and report facility information.

• MMSD staff training for the software and data, which will result in new ways of thinking Estimated Data

Backlog Calculations

Individual Fixes (i.e. place new Connection) Dataset Changes (i.e. add a new attribute) Contracts

Committed Projects (Planned)

MMSD AM_FM Conveyance GIS Intro.doc; Conveyance GIS Binder including: MMSD Sewer Application/WinRef Set-up on Microstation, MMSD Window/Reference/Report Operations Guide, Element Symbology Appendices, MMSD AM/FM Project Database Data Directory, MMSD AM/FM/GIS Project Database Table Definitions with Option List Items and Values, GIS Conversion Procedures

(5)

Begin and End Date Brief Technical Description Brief Data Description Brief Application Description 294 466 Resources Estimated Data Backlog Calculations

Marquette Interchange WisDOT Bores MMSD Project Bores*

*This is a summary of known Geotechnical GIS Database Backlog. Please note that this list contains approximate number of bore log reports for each known contract that has been completed since 2003 (at which time the Geotech Phase I project was completed and new geotech data ceased being updated). Profiles are not included in this summary. Listed number of bores may be inaccurate for several reasons, including that fact that when tallied, this number does not reflect redundant bores that may have been historic references and are already contained within the Geotech GIS database.

Geotechnical Cores/Boring Log Data Project Administration and Maintenance Manual (Phase II); MMSD_ContrInput_GeotechData.pdf; MMSD_Final_DataDict_Oracle.xls (Phase I); MMSD_UserManual_Landscape.doc (Phase I);

MMSD_Upload-Download_Tools.ppt (Phase II); GeotechDB_MaintenanceManual.pdf (Phase II); GeotechDB_UserManual.pdf (Phase II); MMSD_MaintenanceManual_fin.ppt (Phase II); MMSD_UserManual_fin.ppt (Phase II); MMSD_UserONLYManual_fin.ppt (Phase II); MMSD UserONLYManual fin for handouts SJA.ppt (Phase II)

The Geotechnical GIS consists of the following Feature Classes: Boring Locations (8166 points; 5049 of which are associated with scanned Bore Log Report images), Profiles (572 records), and Project Areas (175 records). There are currently 6573 scanned bore log reports contained within 797 project folders.

The Geotechnical Cores/Boring Log GIS was created under contract M03004P01 under the direction of Urbain Boudjou (MMSD PM) in conjunction with STS in two phases. The first phase, completed in December of 2005, converted historical MMSD data up to 2003 and created a database and GIS. As the application was used, MMSD determined that data extraction and data input was inefficient. MMSD contracted with STS for Phase II in August of 2005 to improve this system and to integrate WisDOT data. The Geotechnical GIS assists MMSD project managers in the design phase of future geotechnical projects. Knowledge of existing geotechnical conditions serves to eliminate redundant geotechnical data collection. This application allows for the following: (1) efficient upload of scanned bore logs, GIS points representing bore log locations, and associated attribute; (2) efficient export of this information on a case basis.

Geotechnical Cores/Boring Log Data Project--M03004P01

12/16/02-12/30/05 (Phase I); 8/19/05 - present (Phase II)

Tables reside in an Oracle 9i database connected to a GIS ArcMap 9.x interface utilizing ArcSDE 9. Two Access 2003 fronts have been created for data upload and data export.

(6)

Begin and End Date Brief Technical Description Brief Data Description Brief Application Description Estimated Data Backlog Calculations Resources Application/Software Name

Begin and End Date Brief Technical Description Brief Data Description Brief Application Description Estimated Data Backlog Calculations Resources 2020 MetroMap November 2003- February 2006

MetroMap is packaged as a custom DLL extension that can be loaded into an ArGIS 9.x interface pulling data from a 9.x personal geodatabase. The application was developed using Microsoft Visual Basic 6 and ESRI's MapObjects software running in a client-server network environment.

Data is contained within 19 Arc 9.x geodatabases organized into logical divisions (i.e. Basemap, Census) containing a total of 47 Feature Datasets and 443 Feature Classes, of which 199 are annotation. Additionally, there are 7 mapping layout templates.

MetroMap allows the user (who is assumed to have basic ArcGIS 9.x proficiency) to efficiently recreate figures delivered in the 2020 Facility Plan and perform minor customization of these maps. It is oriented for users to view, query, edit, report, and customize map specific data from the GIS database.

Responsibility for backlog does not effect this project.

MMSD_UserGuide.pdf

M and R Connection Database

2006

The Connections database currently utilize Microsoft Access 2003 with SQL Server

Database contains pertinent information regarding MIS M and R Connections and associated scanned plans (pdf format)

Database allows for efficient access to connections data and related information via simple forms and pre-existing queries.

unknown at this time.

(7)

Begin and End Date Brief Technical Description Brief Data Description Brief Application Description Estimated Data Backlog Calculations Resources

AIMS (Deliverable of CMOM contract M03008P01)

7/1/02-12/31/05

AIMS is a tool composed of a variety of technical pieces, including: • Oracle Database

• Internet Information Server (Web Site Host) • BI-Cycle Data Warehouse

• File Transfer Protocol (FTP) Service • Direct Connection to MMSD Databases • Replacement Planning Tool (Scenario Planner)

• Asset Reconciliation and Class Maintenance Tool (Scenario Planner) • Dedicated Server Hardware

• Web-based asset, work management and project management reports

• Client-based OLAP tools to access and analyze asset, work management and project Data is currently imported into AIMS from the following: MAXIMO, Conveyance GIS, Watercourse CMMS, Primavera P3E, Great Plains

AIMS was created as a deliverable of the CMOM Contract M03008P01 under the direction of Debra Jensen (MMSD PM) in conjunction with Brown and Caldwell. AIMS, or the Asset Information Management System, is the result of an organization-wide team effort to implement asset management at the District. AIMS is the information system component and facilitates storing, tracking, and reporting asset information for capital assets owned or under the jurisdiction of MMSD. AIMS imports asset information (on a weekly basis) from other District databases, including the District-owned data

maintained by United Water in MAXIMO. The asset data is warehoused and optimized for efficient analysis and reporting.

AIMS is designed to be perpetual and easily modified. Asset data, once loaded, is stored in AIMS permanently. During subsequent loads, source asset records are compared with existing “known” assets to find new, missing or modified information. Assets are compared and analyzed using class-based parameters.

Responsibility for Inspection backlog does not effect this project.

(8)

Milwaukee County Stream Inventory GIS Information

9/28/2004

An ArcGIS 9.0 and ArcReader standard interface with shapefiles and hyperlinked photographs

Greater than 12 layers including photo points, potential project locations, location of infrastructure, and more.

The Milwaukee County Stream Inventory GIS allows users to view data relating to subject matter. This project was created by Milwaukee County (who retains all rights and responsibilities for this data).

none

Milwaukee County Stream Inventory GIS "Read Me" file

2020 Digital Library

2004-2006

This application resides in an Oracle database served up on intranet site using .net and contains a hyperlinked directory of files.

unknown

The 2020 Digital Library was built as part of the 2020 Facility Plan to allow stakeholders to easily store and access reports and materials related to this planning effort. There is a thought that the use of the digital library may be expanded from a 2020 planning effort tool to a District-wide light document management tool.

unknown

(9)

Begin and End Date Brief Technical Description Brief Data Description Brief Application Description

Estimated Data none Resources

Begin and End Date Brief Technical Description Brief Data Description Brief Application Description Estimated Data Resources

Root River Sediment Transport Planning Study--W30003P01

March 2005 - present

Currently no deliverables in-house. Anticipated SDE Personal Geodatabase accompanied by ArcMap customized interface and ArcIMS Intranet site.

unknown; anticipate cross sections, control points, hydrology, site conditions files for Root River and vicinity

Anticipated GIS-integrated sediment transport model and geomorphic assessment tools that will provide users a tool to determine the potential impacts on the vertical and horizontal stability of the stream channels within the District's jurisdiction that may result from future work within the watercourses for flood management, stream crossings, and restoration.

Root River Sediment Transport Planning Study RFP Response (March 2005)

Menomonee River Watershed GIS

Completed in February 2001

ArcView 3.x apr with ArcView 3 shapefiles

Approximately 30 shapefiles in the form of points, lines, and polygons. One point file is hyperlinked to multiple scanned site condition photographs.

The Menomonee River Watershed GIS was created ) in conjunction with DTM Consulting LLC. This GIS-based tool aimed to assist the MMSD in efforts to guide the planning process and achieve a balanced watershed and stream corridor management plan in the District planning area.

unknown

(10)

Begin and End Date Brief Technical Description Brief Data Description Brief Application Description Estimated Data Resources Application/Software Name

Begin and End Date Brief Technical Description Brief Data Description Brief Application Description Estimated Data Backlog Resources

Storm Sewershed Analysis Report M03023P01

2004- present

Spatial data compatible with ArcGIS format

unknown

The primary goal of this study is to incorporate new stormwater sampling data collected from the latter half of 2004 through 2006 with existing information and perform a statistical analysis of all the stormwater data necessary to identify trends and seasonal variations, to evaluate first flush vs. composite sample concentrations, to calculate pollutant loads, and to compare stormwater data against local combined sewer overflow (CSO) data. The intent of this analysis is to improve the MMSD's understanding of stormwater impact on receiving water quality. GIS will be used to easily access this information via a spatial front.

none none

Pathogens Source Identification Study

2006-present

Data will be delivered in a Database and ArcGIS compatible format

Spatial data will be collected using GPS. Primary key will link location to associated attribute data within database.

The Pathogens Source Identification Study is being managed by Chris Magruder in conjunction with UWM in no less than two phases. This project was designed to measure and evaluate water quality improvements and establish benchmarks of performance necessary to meet the publicly inspired goals of the 2020 Facilities Plan Elements of this study will utilize physical, chemical and microbiological methods as targeting tools to identify areas within the watersheds and/or within the sewer system which are in need of infrastructure improvements to curb polluted stormwater runoff. none

(11)

USGS Corridor Study--Phase III

2003-2005

ArcGIS customized interface and geodatabase providing query tool for multi-agency water quality and ecological data for use by MMSD and general public

unknown

The USGS Phase III Corridor Study Contract is a collaborative effort involving the USGS, Wisconsin DNR, SEWRPC, Marquette University, UWM, and MMSD. The goal of this three-phased project was to develop and populate a Corridor studies database with stakeholder watercourse-related data, conduct a Baseline Inventory, and continue development/maintenance of this database. Topics of interest include: biological sampling and toxicity, emerging contaminants, stream flow, urban stream toxicity, microbiological investigations, and source area study of cryptosporidium and giardia. Phase III will develop an internet mapping interface for MMSD Corridor Study database allowing users to easily view and perform analysis on spatial data via canned queries.

none

unknown

Roadway Plans DOT Database

2006

The Roadway Plans DOT database currently utilize Microsoft Access 2003

Contains information on Roadway plans received by Planning Department.

Textual data with plan for hyperlinked scanned plans

unknown at this time. none

(12)

Begin and End Date Brief Technical Description Brief Data Description Brief Application Description Estimated Data Backlog Resources 2006- present

The Real Estate database currently utilize Microsoft Access 2003 front with SQL Server

database is currently not populated with data

This database consolidates numerous spreadsheet used internally to track land acquisitions. Customized reports and forms are included to meet the needs of Real Estate and Capital Business Management personnel.

unknown

none

(13)

Primavera 5.0-Project Management Module (P3E)

not applicable

Primavera’s Project Management module is comprehensive, multi-project planning and control software, built on Oracle 9.i server database. The database storing the information is relational in nature and consists of roughly 120 tables, 10 views, and 50 stored procedures. External custom programming interfaces and procedures for direct ODBC connections and Primavera SDK connections have been created using a code generator for the Java language called “Vignette.” Custom reports have been

developed in Crystal 8.5 and upgrades of these reports Crystal XI enterprise is currently being evaluated. The standard interface which most users see was created by Primavera.

Almost all capital projects/programs have information stored in Primavera. The information contains financial, time, and resources for each project/program. Inputs to the system are made mostly through the standard interface. Accounting information is put in through a custom application linked to Lawson accounting system.

Stores and manages projects in a central location. It supports work breakdown structures (WBS), organizational breakdown structures (OBS), user-defined fields and codes, critical-path-method (CPM) scheduling, and resource leveling providing an enterprise project structure (EPS). This gives the tools to project managers needed to manage multiple projects, from the highest levels of the organization to the individuals that perform specific project tasks. Multiple users can access the same projects concurrently.

• Centralized resource management, including resource timesheet approval and the ability to communicate with project resources who use Primavera Timesheets (timesheets is extra and we do not currently have)

• Integrated risk management • Issue tracking

• Management by threshold

• A tracking feature that enables you to perform dynamic cross-project rollups of cost, schedule, and earned value

• Work products and documents that can be assigned to activities and managed centrally

• A Report wizard that helps you create customized reports to extract any data from the Project Management database

N/A

(14)

Primavera--Expedition Contract Management Module

Not applicable

Expedition 8.0 Users Guide – ExpedUserGde.pdf (Note: Currently using version 10.1) Expedition Database Server:

The database server stores the project data (Contains contracts administration, bidding information, documents, etc related to projects). Web clients access project data through the Expedition Web server. The database server is installed with Expedition. Expedition Web Server and Application Server Host Components:

• Internet access

• A firewall configured to allow only HTTP requests to the port and TCP/IP address of the Web server.

Components included with Expedition: • Jetty Web Server

• JBoss Application Server

• Expedition database running on Sybase Adaptive Server Anywhere • Sybase JConnect JDBC driver

Projects in the expedition database may have the following information: Records and Purchase Orders, Cost Worksheet, Invoices/Requisitions, Materials Delivery,

Companies, Trends, Submittals, Contract drawings, Sample materials, Permits, Who received what and when

• Reviews all submittals and can approve when necessary.

• Keeps up to date on estimates, contracts, job cost, and subcontractor communication on a web-based repository.

• Tracks contracts from original estimates to buyouts. Responsibility for backlog does not affect this project.