The Challenge
Dover District Council (DDC) faced the formidable task of migrating complex data from their 15-year-old on-premise legacy database to a more flexible and scalable cloud-based solution. The existing database infrastructure was not only outdated but also inefficient in handling modern data processing demands.
The legacy system was used across a large number of council departments where it served as a comprehensive case management system for environmental crimes such as fly tipping and dog fouling, as well as food safety inspections, licensing, contaminated land, and port health issues. As each department employed its own processes and data schemas, this presented a complex challenge to unify this information into a central holding.
The Solution
DDC needed a flexible and straightforward way to untangle 15 years’ worth of disparate data stored in different formats and schema. Leveraging the advanced capabilities of FME, the team extracted, manipulated, filtered and merge data to create a new singular dataset.
The DDC migration project had three key stages:
Data Assessment and Planning: The Council conducted a thorough analysis of the existing database to map all data assets and their interdependencies. This gave a clear understanding of the migration scope and complexities involved.
Stakeholder Engagement: Using FME, they were able to build and test the data migration processes, collaborating with each department to review the output and make any changes they needed. If any issues arose, the team could also easily return to the specific part of the FME workspace to make corrections and adjustments. This approach allowed for continuous validation and cleansing, ensuring each department’s data was accurately and securely transferred.
Data Integration and Migration: FME proved to be an indispensable tool during the migration process. To minimise any disruptions in daily operations, DDC conducted the migration process incrementally, moving one department’s data at a time. With FME, DDC filtered out outdated cases, updated codes, and merged various case types with precision. They also utilised FME to transform date formats and join fields, ensuring all data conformed to their new standardised schema.
After filtering out data that wasn’t needed, the Council successfully migrated 3GB of textual records and 600GB of attachments to the new system. The entire project spanned over a year, with the FME phase lasting eight months.
The Benefits
Using FME for DDC’s data migration offered significant monetary and time-saving benefits.
Over 4 months saved: FME dramatically reduced manual labor and eliminated the need for software development, which saved over four months. This significantly reduced associated costs.
Enhanced Stakeholder Engagement: The use of FME secured crucial stakeholder buy-in and streamlined the user onboarding process at DDC. This fostered greater confidence among all parties involved, improving the transition to cloud-based infrastructure.
Data Accuracy and Compliance: By leveraging FME, DDC enhanced the accuracy of its data to meet regulatory requirements. This is especially crucial in the public sector, where data integrity is paramount.
"I can think of several other options to extract and manipulate data, but these would have required knowledge of coding and some of our transformations ended up being very complex. In FME this was seamless and could be tested instantly to confirm it had worked as required."