Evolving From Siloed Databases to Enterprise Workflows

The southern Piedmont region of the United States, nestled between the Appalachian Mountains and the eastern coastline, is one of the fastest growing regions in the country. The ongoing development boom and steady population influx over the last several decades, while generally a sign of a robust economy, can also lead to environmental indicators such as increased stormwater runoff, flooding, and surface water quality degradation if left unchecked.

Charlotte-Mecklenburg Storm Water Services was established in the early 1990s to address these issues. The mission of the joint municipal and county stormwater utility is to manage the runoff from rainfall, reduce flooding, restore flood plains, and protect the water quality of surface waters countywide.

With a strong GIS infrastructure already in place at the organization, Cityworks was a natural fit to help manage workloads related to addressing these concerns. City of Charlotte Storm Water Services began using Cityworks Desktop in late 2006 in coordination with Mecklenburg County Land Use and Environmental Services Agency. Over the course of several years, the city developed its own internal instances of Cityworks. This resulted in three separate Storm Water Services sites with each configured to meet the different business requirements for individual subgroups: water quality permitting, stormwater project tracking, and stormwater inventory. Ultimately, overlap in some lines of business, upgrade requirements, and other potential efficiencies led to the need to consolidate these into a single site.

Water Quality Permitting

The first site developed inhouse for Storm Water Services was a water quality permitting application. Built with Cityworks PLL 2012, this application was set up to track permitting activities, project impacts, and mitigation bank credits and debits. It didn’t utilize GIS data and was basically a repository of permit types and issuance dates, tabular data, and attached documentation. Customizations in the 2012 site included custom tables to track mitigation credits and debits, and stream and wetland impacts. The oldest of the city’s internal Cityworks applications, the permitting site was in severe need of an upgrade. As part of the process, Cityworks PLL was modified to capture basic project summary data, mitigation credits and debits, monitoring activities, and permits and permit issuance dates. Future enhancements may include a more robust GIS component, but all the permitting sites haven’t been mapped for use as assets in GIS, yet.

Stormwater Project Tracking

The stormwater project tracking site, another application merged during the upgrade project, is used to track general stormwater issues, engineering and design projects, capital improvement projects, and stormwater-related CDOT work. Additionally, onsite investigations of stormwater issues are performed via Cityworks custom inspection forms.

While it didn’t require many significant functional changes, an upgrade was necessary to move away from the Silverlight map. Originally a Cityworks 2015 application, it had the most customizations and reports across the three individual instances and was used as the core site during this project. Following an upgrade to Cityworks 15.4.1, data and map services from the other two were migrated into it. This resulted in one application and data repository that users could log into, preventing them from having to manage project-related work in multiple sites with multiple logins. Customizations integral to this site include a two-way BizTalk-integration with the City’s 311 application (Emerald) and service request generation in the previously separate stormwater inventory application.

Stormwater Inventory 

The third site developed by Storm Water Services that was upgraded and combined during the project was the stormwater inventory application (Cityworks 2015). Primarily used for updating and maintaining inventory-related GIS features, it’s also used for tracking dam and culvert inspections. Along with some standard XML customization, automated status changes, and the stormwater project tracking inventory request functionality, it contains a customized inventory area tool that allows users to create a polygon for field or desktop inventory data capture and work order generation. Key metrics such as the number of existing features, estimated features, and estimated and actual hours for data capture and review are calculated and tracked within the work order.

Challenges 

While upgrading and merging the three sites resulted in efficiencies for users and the ability to maintain all the data in a single database, the process wasn’t without challenges. The same Cityworks custom fields were being utilized for different items in the project tracking and inventory sites. These needed to be reconfigured, resulting in all available custom fields being utilized.

The impact on reporting was minimal since only one site has reporting generated from custom field data, but it could have been problematic if that wasn’t the case. Global settings for fields such as Priority and Status were also different between sites and had to be combined. XML layouts for project tracking and inventory service requests and work orders are significantly different. The workaround implemented to address this involves assigning project tracking XML to the Cityworks Office layout, and inventory XML to the Cityworks Tablet layout. Users switch between layouts within the same site depending on the data they need to view. Some of these more minor challenges, while creating some day-to-day workflow changes, were addressed through staff training. As an early adopter of Cityworks Desktop and Cityworks AMS, implementations were designed for specific subgroups within departments based on specific business needs and the technology available at the time.

While this was still an advance over paper or spreadsheets, it still yielded some siloed workflows. The Cityworks platform has evolved significantly since those early days. An organization starting from scratch today with a new Cityworks application build will have the benefit of developing an all-encompassing work order management system that incorporates entire departments or even the entire municipality.

Future Opportunities

The functionality of the Cityworks GIS-centric asset management and permitting platform looks much different than it did in 2006, or even 2012. Cityworks AMS and PLL include many more customization options, and they should be taken advantage of—with the knowledge that those customizations should be revisited at each software upgrade.

By Ashley Conine, Sr. Business Analyst, City of Charlotte Innovation & Technology