This article was written regarding work conducted under legacy company names: Continental Mapping, GISinc, or TSG Solutions. These three companies merged in January 2021 to form a new geospatial leader Axim Geospatial.
The City of Carmel had decades’ worth of permitting data in an outdated and no longer supported system. As a result, searching for data became a day-to-day struggle that cost the team a significant amount of time and stress.
Photo credit: carmel.in.gov
They knew they needed to transfer that data into a modern, more sustainable system. But with so much critical data to transfer, they worried about losing invaluable information during the transition. The last thing they wanted to deal with was losing the valuable information that had been collected over the years.
So how did the city preserve its permitting data and ensure a smooth migration? In this article, we review the process the City of Carmel and our team at Axim Geospatial followed to successfully transfer decades of essential data into the Cityworks platform.
More Data = More Risk That Things Will Go Wrong
Several years prior, the City had implemented Cityworks for work orders and asset management. The team was familiar with the software and saw its potential. So when they began to look for a new permitting system, Cityworks PLL was a natural fit.
The City chose to partner with Axim because of our extensive Cityworks experience. They entrusted us with implementing the new system and migrating data to Cityworks PLL.
The City of Carmel faced unique challenges in the quantity and type of data stored. So we started with a one-week, onsite Discovery Workshop. During this workshop, our team identified and outlined the core configuration approach through collaborative workflow diagramming, case data detailing, and report reviews. This allowed us to provide the city with an accurate proposal that truly reflected their identified scope.
During the workshop, we discovered that the city had multiple databases containing permitting information and that some of the data had been previously migrated over from an even earlier system. It was imperative that our team transfer all of this information over properly, or the city could lose years of hard work and their previous migration investments.
Carmel’s 4-Step, Stress-Free Cityworks Migration Process
After analyzing the databases, Axim rolled up our sleeves and got to work. We developed and followed a custom 4-step process to migrate the data into Carmel’s new PLL system. Our overall focus: minimize headaches while maximizing efficiency.
- Conflate the Existing Databases
Three databases containing permitting data: two Microsoft Access and a SQL Server. We conflated these databases by comparing the contents of the Access databases to what was in the SQL Database. Finally, we imported any records that did not exist in the SQL Database using FME tools.
This process provided clarity for both parties and ensured that nothing essential was lost during the transfer to Cityworks PLL.
- Develop a Crosswalk Document
Once all the records were in a single SQL database, Axim mapped the data elements from the original database schema to the Cityworks PLL schema. This required extensive study of the data to determine if and where it should go in Cityworks PLL.
Obviously not every data item had a corresponding data element in PLL, and not all required PLL records were found in the legacy system. The City deemed some of the legacy system data elements that did not map directly to PLL to be of low value to the new system. These were not migrated. Whenever data was evaluated, the City was an active part of the process.
For all data that the City deemed essential, Axim created special Case Data Groups in PLL to act as the target schema within the new system. Dates, users, statuses, tasks, payments, comments, people, contractors, and roles were all mapped to the new Cityworks PLL configuration.
If data items required by PLL did not exist in the previous system, Axim mapped in placeholders to satisfy the system. In some cases, the team even created legacy case types to hold permits that were no longer used. With this process, the City had peace of mind knowing that nothing fell through the cracks.
- Script FME Workspace and Migrate Data
Once the Crosswalk was completed and approved, Axim developed multiple custom workspaces in FME to move the data into the correct locations in Cityworks.
Our team used multiple transformers in each workspace, which allowed the data to be cleaned and transformed so it would seamlessly fit into the Cityworks database schema. This included running the permits through a geocoder to provide the permits with an X/Y coordinate so they could be spatially aware in Cityworks.
Another transformation included adding special characters to each permit number to easily designate which legacy system they had migrated from. Utilizing the FME workspaces to migrate the data allowed Axim to easily re-run the migration in an iterative fashion, so the data could be loaded and reviewed in a test site.
To provide the City with the confidence of knowing the migration was done the right way, our team verified the data’s accuracy and consistency at each step of the process by utilizing record counts, test filters, and manual reviews. Once the data was migrated into Carmel's Cityworks site, we retired legacy template configurations used to hold information and ensure that users would utilize the most current permit configurations.
- Document the Migration
Finally, to set the City of Carmel up for success, Axim documented:
-
- Where it can now be found in the legacy databases
- Where it can now be found in the PLL schema
- How the data is represented in the PLL user interface
Because the migrated permits did not have the “workflow” associated with permits created in PLL, and the Case Data Groups were different, we followed special procedures to handle cases that were still in the process of completion. These procedures were discussed with City staff to ensure they knew what to do with these permits until they were all closed.
The Final Results: All Essential Data in One Secure Place
Thanks to the legacy data migration into Cityworks, the City of Carmel was able to upgrade to a new, more robust system without losing years’ worth of valuable data. City staff can easily access any data they need without digging through multiple legacy applications, saving time and energy on daily tasks.
Data migration is never easy. Often, it feels like fitting a square peg into the round hole. Fortunately, with Axim's vast knowledge of the Cityworks system and database schema, combined with the power of the FME, the legacy data is now accessible in PLL and City staff can continue to work through those permits that were still open at the time of migration.
Could Cityworks be the ideal solution for your organization’s unique needs? Reach out to the Axim team and we will work together to:
- Identify and prioritize your organizational goals
- Determine the best way to solve your business problems
- Create a stress-free plan to migrate to your new solution
- Educate your team so your organization is set up for success for the foreseeable future
Types of Software Used:
- Cityworks PLL version 15.4.2
- FME Desktop
