In 2005, we were tasked by the District of Columbia Department of Transportation to create an enterprise software solution that would help manage performance-based contracts to monitor the maintenance of street lights and parking meters.
For years, the city had been outsourcing services such as the regular repairs and maintenance of street lights and parking meters to low-bid vendors, but experienced substandard results. Timely, pro-active maintenance that would ensure safety (via working street lights) and steady revenue (via working parking meters) was important to serving the city’s constituents. The city decided there must be a better way to motivate vendors to become attentive to what mattered most: delivering quality services on time, addressing the need of monitoring vendor performance by building performance metrics into the software.
The city wanted to be able to monitor contract details, many of which were found in 1000+ page contracts for city services, yet have the flexibility to alter nuances when the contract renewed. Depending on the problems diagnosed by the field technicians, the solution could have different timelines and criteria for penalties, which then had different fees depending on the degree of lateness.
Next, the city wanted report cards, pre-formatted templates that would display performance metrics at the click of a button. Report cards would also be used to assess penalties on vendors who did not meet their work commitments. The report cards must produce any or all of the performance metrics as articulated from each vendor’s contract.
The city wanted the solution to be transparent. The detailed backup of any summary or report card result needed to be available at the click of a button. In effect, all supporting evidence of successes or failures needed to be available at all times to both vendors and the district contract managers. If the vendor exceeded their mandates for a specified period of time, they would be rewarded by the city.
The city also wanted the solution to be web-based and hosted outside of the DC intranet environment. DC did not want to be in the “hosting business” and wanted the contractor to manage a hosted solution, providing all security and operation support.
Furthermore, the city had a long list of feature “must haves,” including the ability to automatically batch and/or transfer daily data to and from other district-owned systems (call center and work order systems) and vendor field work systems. It also needed to display field work activity on a map using Google Maps’ API. The data entry needed to be minimal and easy for a diverse range of user computer literacy. “Easy button” style features to minimize keystrokes, mouse-over help and Q&A style input was needed to maintain a high degree of accuracy. The city needed to be able to extract data for reports – in nearly unlimited ways without requiring a programmer. The city wanted to easily accommodate new reports, and have a standard fare of pre-formatted templates and to have easy access to commonly referenced links – that would link to other websites.
The Approach & Solution…
After a careful review of the contract-based performance metrics for both street lights and parking meters, we discovered several common denominators. These common denominator issues were quick turnaround of work, timelines for completion, penalties for each day late, and overall operability as a percentage of the total inventory. We found that these variables could be constructed into a table, allowing the algorithms to be dynamic. This meant that the city would be able to adjust the performance measures, remove or add new ones without requiring programmatic support. This solved the Report Card challenges, since all algorithms could be constructed based on the values stored in the data.
Next, we evaluated the need for flexible reporting. We concluded that the best route for the city was to be able to search, filter, and sort and group nearly every field and metric from the data set, but in an intuitive way. More data choices could easily translate to more difficulty for the end users, so to alleviate this problem we opted to use what the users were most familiar with. Since users were comfortable using desktop spreadsheets, we provided them with output that simulated a spreadsheet, with columns that could easily be moved, added, or (temporarily) removed, filters could contain, exclude, start with or equal, and complex “if/then” and “and/or” filters, which could be created by advanced power users. Similar to using Excel, we modeled the solution based on what the end users would find comfortable, and any “spreadsheet style” preference could be re-used by storing it as a template. This made the reporting options for users nearly limitless and solved the problem of ease of use.
To allow the application to extend its value, we incorporated links that the city wanted to reference via other websites into the data. With a table driven approach, the city could create unlimited links and make these links available to selected end users.
By the use of web services and XML, we constructed batch services that allowed for the seamless migration of data among city systems and the systems of their prime vendors. Depending on how the contracts were translated, some of the data moved in batches on a daily basis, while other data might be transferred in real time. We created alerts to allow the users to be informed of potential data transfer issues as well as routine successes, then built backup safeguards to allow repeated attempts to migrate data in the event that one of the transfer servers was unavailable.
As each vendor maintained their own “master” inventory of assets (such as an inventory of streetlight poles, or an inventory of parking meters), we provided hooks that allowed these inventories to be maintained “offline” (i.e. by the vendor’s systems) and then batch updated on a regular frequency.
We needed to find a hosting environment that could ensure the city experienced zero downtime, and therefore we knew we needed an ISP that was sufficiently large enough as to offer 24/7 support and be entirely US based. We also needed the servers to be configured exactly as our software specifications required, with mirrored servers, and top-notch security protocols. We found exactly what we needed in a solution that would fit the city’s budget.
In 2005, the first version of iSLIMS (Internet Service Line Inventory Maintenance System) was released using .asp / Crystal Reports and was delivered for use by street lights, on time, on schedule and exceeding the expectations of the city. In 2006, iSLIMS for Parking Meters was released and used to track and monitor parking meter repair performance. The iSLIMS solution for both street lights and parking meters shares data with various vendors including Xerox, MCDean and other city repair contractors.
In 2012, we released an upgrade that replaced Crystal Reports with a more device independent version that could be used on tablets, using the state-of-the-art DevExpress data grid functionality and incorporating Google Maps API features, which allow city assets to be viewed geospatially.
Summary of Solution …
- Work order or project driven
- Web based GUI (ASPX / .NET / SQL / DevExpress)
- Managed hosting on US-based servers with 99.9+% up-time
- Crystal Reports / DevExpress spreadsheet style data grids
- Data driven links to other websites
- Map connectivity via Google Maps API
- Accommodates batch / offline data transfer to/from the city’s work order system, and vendors include Xerox, MCDean
- Supports unlimited users, currently at 200+ users
- Combines pre-formatted report card performance measure reports and ad hoc reports
- Ad hoc reports are fully customizable to user and can be exported to common formats (XLSX, PDF, RTF, CSV, TXT)
- Multiple installations: parking meters & street lights
- Exports contacts (people) and organization data to XLSX, CSV, TXT
- ODBC connection to MS Access
- Manages multi-million dollar efforts
- Allows for inventory, performance measures and non-project activities to be tracked
- Initial web release in 2005 for street lights, followed by installation in 2006 for parking meters. Release in 2012 to replace Crystal Reporting with DevExpress data grids
- Four-tier user permission hierarchy with different menus for different levels of access
- User manuals and quick user guide
- Continue to provides consistent and dedicated customer support (since 2005)