Client Onboarding - A Data Migration Case Study

The business context and objective.

A leading share incentive plan administrator was in communication with several potential new clients who were investigating the possibility of moving their plan administration. Pier Solutions Ltd was already engaged by the plan administrator to migrate data between its internally managed systems and was approached to enable the onboarding of the necessary client customer, plan and transaction data.

The challenges

When Pier Solutions Ltd was first involved, the potential new clients had not made their current administrators aware of their investigation of other service providers and so were unable to provide details of the data that needed to be onboarded.

• The business development team were keen to assure the potential clients that onboarding would be swift and straight-forward, but plan administration managers and treasury staff required assurance that the onboarded data would reconcile to assets received and would reliably support the functionality of the administrative system.

• Because the plan administrator had recently adopted a new internal administration system, there was no experience of onboarding into it. Previous onboarding had always targeted the old administration system which was now close to being decommissioned.

• No standard existed for the transfer of data between share plan administrators

The options considered

1. Onboard into the old administrative system then use internal systems migration tools to migrate to the new system.

2. Onboard directly into the new administrative system using the tools and team that had been working on internal migrations.

The approach taken

Although the first option appeared superficially attractive, given that there was previous experience of onboarding into the old system and recent migrations out of it to the new system, the uncertainty in the quality and structure of the data to be taken-on introduced a significant risk that these would require rework. Also, given the intention to decommission the old system, this approach clearly did not have a long-term future.

So, the decision taken was to utilise the tools, technology, process and human resources that had delivered the recent internal systems migration, and progressively evolve the capability to onboard directly into the new system. This approach would be geared to delivering the necessary data quality as a quickly as possible for those clients already in the pipeline, without specific goals for reuse of code, but to establish a process and architecture that would allow evolution of the transformation logic and intermediate data structures.

The tools

The MDL Tool, developed by Pier Solutions Ltd was used with an Oracle database for the intermediate datastores. Analysts used their preferred database tools to analyse the structure and integrity of source data and to explore strategies for transforming it.

• The previous administrator

  • The plan manager - with responsibility for delivering closing administration and delivering the plan data.

  • Plan Data expert - with a detailed knowledge of the plan data and the ability to action extracts.

• The new plan administrator

  • The plan manager - with responsibility for receiving the plan data and enabling service delivery via the administrative system.

  • Treasury manager - with responsibility for verifying the receipt and recording of the share and cash assets.

  • The business development manager - with responsibility for securing the client contract and the service delivery definition contained therein.

  • The Data Protection Officer

• The migration team

  • Data analysts - with responsibility for analysing data integrity, specifying data transform rules and creating migration reports.

  • Migration lead developer - with responsibility for orchestrating the implementation in MDL including decisions concerning performance optimisation.

  • Migration developers.

The Process

The process started immediately the client gave notice to the current administrator of its intention to cease their engagement. The first step being to obtain a test data extract that the current administrator asserted contained all of the necessary information defining the plan holders, their investments, preferences and transactional history. This data would be loaded via MDL as soon as column constraints had been established and analysts would begin their investigation of it. Typically, initial investigations would raise queries concerning the scope of holder data to be taken-on, the absence of transactional information and sometimes the self-consistency of the information. Such matters would be raised directly with the stakeholders who provided the source data and any significant issues that appeared to exist would then be escalated to project management and hence to appropriate business decision makers.

Data transformation began by identifying source tables needed for each target table, and the estimation of a complexity rating for each target table, to facilitate work estimation and planning. The analysts then worked on each target table to define the rules to identify each individual record to be loaded to the target table, recording these in a manner that business stakeholders could understand whilst also recording candidate SQL to be used in test reports.  Crucially, the specification for each target table included a distinct key column that was constructed from a concatenation of pertinent source data information. This migration source key column was present only in the staging area table for the target table and would be implemented with a unique index so that any data that failed this constraint would be immediately flagged to the team. Developers then implemented the rules, where necessary creating intermediate tables to facilitate reuse or improve performance, and analysts would build integrity reports using their independently generated SQL.

As developers implemented the rules these would be embedded into the MDL control framework enabling a continuous integration and build, initially only populating the staging tables, but once a testable group of target tables is ready to be populated, the MDL tool would be connected to an appropriate target test system to complete the load process. The loaded data would then be evaluated using a suitable functional test programme to evaluate its integration with the system logic, and to produce standard business reports and so build business confidence in the data quality.

In parallel with the development build, the analysts refined and embedded their test SQL into the MDL framework so that optionally the migration process could be instructed to create verification and summary report output.

How this approach addressed the challenges

·        We resisted the temptation to provide an up-front estimate for the time and effort required to complete the work, highlighting that we could only sensibly estimate once the structure and integrity of the source data was clearer. The clients then engaged with their current administrators to manage the uncertainty in the final transfer date, enabling us to begin to secure and analyse the source data.

·        As the structure and integrity of the source data, and the complexity of the transform emerged, we incrementally and continuously calculated and refined our work estimate. As risks and issues were identified that potentially threatened the timeline, these were immediately discussed with the business stakeholders.

·        As we addressed additional clients and data from differing third party administrators, the structure and integrity of our intermediate data stores evolved and stabilised so that our transform rules formed two layers, to populate a set of intermediate tables independent of the data source and from there to populate the target system data structure. As these standardised intermediate tables were reused, the lead-time and cost of subsequent migrations reduced.

·        As we have highlighted in another blog, this sector would benefit from the adoption of a data exchange standard. Clearly this would require collaboration, discussion and formal agreement, but the above experience highlights the benefits of doing so.

Conclusion

Our iterative and agile approach to the task allowed us to identify issues early, refine our intermediate data stores to easily support new scenarios and data idiosyncrasies. The Piersolutions framework allowed coding and testing of the migration rules to be started early in the process, while the analysis was only partially complete, so limited resources could be deployed efficiently and effectively. Consequently, we were able to quickly establish and gradually improve estimates for the delivery date, resolve integrity issues and deliver the necessary data into the new service provider system.

The client was left with a performant, robust and reliable onboarding mechanism and a proven process for extending this should new source datasets from third parties need to be processed (along with the obvious benefits of that).

Contact Piersolutions Ltd for more details or to discuss how we can assist you.