Diagnosing the problem
After a few conversations with senior members on the project, the issues became apparent - the project requirements between both parties were misaligned. Many requirements had been discussed and agreed in abstract terms, but were not specified in sufficient detail. Other requirements were simply not scoped, either being very generalised or missing completely.
Furthermore, the existing method for extracting and migrating client data was not viable. It was incorrect and extremely slow, taking five hours to export data for 12 clients. This would not scale to 1000+ clients as it would surely need to for a successful migration.
Confident in my view of the situation, I discussed this with the CEO and Lead Business Development Manager - and assured them that these issues were not insurmountable.
Aligning requirements
In an afternoon, I drafted and pitched an initial project plan - to scope out our missing requirements and work to get the project back on track.
The core questions that needed addressing, and that guided my steps forward, were:
- What do the developers need for their system to operate successfully?
- What does PayCheck need to provide their service to all clients? Which of these requirements does the new system lack?
Over the next two weeks, I arranged daily meetings with the developers and key members of PayCheck to explore these requirements fully - assuring those involved that addressing these issues at a high level of detail would be vital to getting the migration back on track.
With requirements in hand, we had a shared understanding of what gaps remained and could plan their development. It also allowed us to group our clients based on requirements that were already met - and those due to be developed in the future. Using this to prioritise development time, we settled on a client migration schedule.
Rolling up sleeves
All that remained was to develop a scalable data migration solution.
The first step was producing a mapping document - specifiying what data points were needed, where to locate them, what processing was required and where they needed to go.
This was an extremely arduous process. The existing MSSQL database systems were poorly maintained and lacked normalisation. Related data was held in different tables with unrecognisable names and often in different databases altogether. Composing the mapping document meant locating and piecing together the required data points from across the company's ecosystem.
After doing so, developing the data extraction and mapping solution was more straightforward. Built in Python, an object oriented design formed basic models of the core components of a payroll system, allowing for related objects to be more easily accessed (i.e. an employee's pension information.)
Establishing connections to databases with Pyodbc, Pandas was used to query the data, manipulate it into the required format and output to CSVs - to be sent to the developers for import.
This solution allowed us to export data for our entire portfolio of 1000+ clients in 12 minutes. In contrast to previous methods that took five hours to export 12 clients, this was a substantial improvement that allowed us to iterate much faster.
Implementation
With these core problems resolved, we could begin migrating data to the new system. Each month data would be exported, cleaned and imported into the new system. Payroll administrators would test parallel test clients against live payroll of the current month, and if satisfied the client would go live on the new system.
This required coordination throughout the entire company - myself in charge of the data, to the payroll team running the parallel tests, to our communications teams with clients themselves.
This flexible process continued for 15 months throughout the migration - adjusting as new developments were released. In particular, this was carried out while navigating the fallout of COVID-19, which was particuarly impactful for the payroll industry due to furlough - adding increased strain on our existing operational capacity.
Outcome
The delivery of the migration was a continuous working process, following the framework I had laid out. In March 2021, the last batch of clients were migrated to the new system.
The changes led to an immediate 10% decrease in time spent per payroll. More importantly, the new system gave robust data insight into our operations - alongside increased stability, usability and adaptability of systems - allowing the company to use this as a foundation for continuous improvement for years to come.