So, you’ve successfully implemented your new permitting system. The software is live, organizational workflows and calculations have been added, and office and field staff are fully on board. But what do you do with historical case data still sitting in your legacy system?
Migrating legacy cases from an outdated, unsupported system can seem like an overwhelming project. In reality, it’s worth the time and cost to migrate legacy data. Properly executed data migration eliminates the need to continue hosting, training, and using the old system for retrieval of information. It can also provide a tool for creating “in-process” cases within Cityworks in a way that reduces data entry time. For organizations migrating from a platform that’s not spatially aware, this process can also take advantage of the Cityworks GIS backbone for old cases.
A longtime user of Cityworks AMS, the City of O’Fallon, Illinois, decided to expand to Cityworks PLL in 2017. The community development department needed a way for the public to electronically submit applications, schedule inspections, and make integrated online payments. They also needed a good PLL system that could support geospatial data and maps.
Early in the process, the City of O’Fallon reached out to Burns and McDonnell, the city’s professional services provider. Their expectations and feedback helped guide the city’s development goals for data conversion.
From PLL implementation to data migration, the entire project took 13 months and involved 10 internal staff and three Burns and McDonnell consultants. By the end of the project, the team had migrated more than 46,000 legacy cases with searchable tags. Remarkably, the team also moved 5,366 active or “inflight data” cases, giving field inspectors immediate access to update cases in Cityworks while on site.
Here’s the secret to O’Fallon’s success.
At the onset, O’Fallon separated project components into must-haves, want-to-haves, and would-be-nice-to-haves. The motto for managing success was, “Build for the must-haves, budget for the want-to-haves.” Although the city wanted to deploy Public Access, for example, they decided to save it for a separate project so the team could focus first on the PLL implementation and data migration.
Test Early and Frequently
Before jumping into the full implementation, O’Fallon initiated a pilot project in a separate test environment to explore the workflow process. The team identified a few representative case types and designed workflow process steps for them.
This pilot stage allowed key managers and technical staff to get familiar with the complexities of the new system. It also allowed us to vet the proposed workflow and case system without committing the entirety of the budget to a new process. Once the full implementation was underway, technical implementers sought and applied regular feedback from managers, a sampling of end users, and other stakeholders.
Document and Streamline Workflows
Before creating a single case, O’Fallon created a fully annotated list for each case type that outlines all tasks, task results, and branching task results. A spreadsheet in a SharePoint shared work environment worked well for this purpose. The city found that result sets and tasks grew quickly during case template build-outs, so the team also used this opportunity to streamline workflows as much as possible.
While project teams worked independently, they also met collectively for weekly or bi-weekly conference calls. These collaborative meetings became more frequent as task responsibilities shifted from whiteboard plans to fully-articulated processes. The teams used a mix of SharePoint, a Microsoft Teams site, and shared Office documents to collaborate with and inform key stakeholders.
Expect the Unexpected
Early in the build-out phase, the team discovered that the legacy permitting system didn’t have clear distinctions in its table structure to determine case status. The fields and tables varied depending on the record type. This posed a significant obstacle to data conversion, since the team had intended to separate these record types into “In Process” and “Legacy Historical” groups.
IT Manager Dan Gentry brought his expertise to the fore. Working with key community development staff, Gentry used Microsoft Power BI to create a schematic to help Burns and McDonnell identify and recode each record’s status. He was also able to provide real-time reports on legacy records with additional data fields that needed migration.
To import legacy data, the team used Cityworks’ custom tables in PLL for Case Types. By now, the project teams had unscrambled the old system’s data into four main groups: General Permits, Licenses, Code Enforcement, and Crime Free Housing permits. Each of these groups was further separated into historical archive case types and “in process” cases. The team then created eight custom PLL tables, each with approximately twenty fields, to hold imported data from the legacy system.
Another hurdle involved data search for the end user. Although PLL custom tables were the best solution for capturing key data from the legacy system, the table elements were not searchable from the user interface.
After the legacy data was migrated to PLL, the team realized an updated SQL query could be used to populate the Tag field in PLL case tables with formatted values such as permit ID, permit type, date created, expiration, and more. With tag parameters set, a user can easily search among 46,000 legacy records for all business licenses that expired on April 1, 2015, for example. Using a combination of Tag elements, a user can also return all legacy case types that were home occupancy permits created in the past 12 months.
Close the Deal
Regardless of the innovation inspired and milestones achieved, a project needs to be completed before it can be judged a success. Projects that influence, drive, and enable (or disable) an employee’s ability to complete her work at some point bring anxiety and fear of failure.
To help ensure complete buy-in and establish Cityworks as the definitive source of record for converted “in process” cases, O’Fallon took inspiration from Cortez and decided to “burn the ships!” Team leadership announced that, at go-live, the old platform would be converted to a read-only platform.
While the short-term effect was to increase anxiety, the subsequent Cityworks training was well-received. Staff readily applied their knowledge to help anticipate and plan for potential workflow issues that might be experienced at go-live. On the day of go-live, city employees were committed to making the new system work with the migrated data, and the results have been beyond expectation.
Chad Quinn is a GIS coordinator at the City of O’Fallon.