How to overcome data migration chaos when migrating between 2 or more fund service providers

1. Introduction

Data migration chaos is only a step away once you start moving data between systems and fund service and system providers.

There are many pitfalls to avoid when the outcome of a migration can have a big economic effect on the fortunes of an Investment firm and their respective clients. Consider how many ways things can go badly wrong:

• Different data structures between the existing and new provider systems
• Holes or mistakes in the source data, especially when you need to migrate many years of historic data
• Data volumes making the migration process unreliable during the migration process
• The difficulty reconciling the economics and risk factors between the 3 system or service provider sources, especially when there are different pricing models and market data
• The complexity of involving multiple parties in the process such as multiple fund service providers systems and multiple systems
• Ensuring data security for participants in the process – you can’t share data via spreadsheets and email and meet data privacy policies easily
• Aligning multiple parties to agreed standard instrument and valuation tolerance check thresholds and reporting

Read on for more on how to achieve a successful fund and system data migration, focussing on issues caused by data, and avoid data migration chaos.

References

• https://www.protecto.ai/blog/how-to-plan-a-data-migration-project
• https://etlsolutions.com/how-to-plan-a-data-migration-project/
• https://prodktr.com/segue/

2. Planning a data migration

With any data migration you’d better have a plan. This sounds obvious but your plan needs to coordinate the work of many people both at the level of a business analyst, systems programme, right up to the fund manager and owner. Your plan needs to include:

1. The people involved and their roles
2. The steps in the plan both at a day-to-day level, but at the communication and governance level
3. The way in which progress will be tracked and reported
4. The way issues and risks to the project will be identified and recorded, assessed, managed and reported upwards
5. The way decisions will be made, especially once results from trial migrations begin appearing
6. The timing and method of communications
7. Business acceptance rules and criteria and stakeholder responsibility 
8. Clear and simple task definition and timing allocations.
9. The commitments from key governance participants for their time and involvement

Engage a firm who knows how to run such projects and underpin the project with Segue simple technology to load, manage, map and insert data from one system to another and perform multi way reconciliation.

3. Data review and cleaning

One of the assumptions in avoiding data migration chaos is that the source systems actually contain all the data you need to meet the requirements of the target system. Given this risk, investing time in analysing the source data reaps rewards later in the migration process. Your analysis should be looking for:

• Completeness: Does each record for a trade, or related data item, actually have all the required fields for all items in the database? Any gaps with missing fields, (which may be implied or calculated) needs recording and addressing.
• Time span: Whilst the primary objective is to migrate all currently active transactions to the target system, it is a requirement to have data going back at least 7 years. For a transaction and position which was executed more than 7 years ago – is there a continuous record of that trade, and it’s value, covering that 7 year time span?
• Structure: Does the source system represent data in a way you can retrieve successfully? Is any trade data stored in a secondary structure or another system which means performing multiple data queries to then recreate the target data?
• Repairs: Given all the above – are there problems with the data which will make the migration unreliable or unachievable which means the source provider must invest in data repairs or improvements before going forward?

A structured and sequenced approach to reviewing the source data is a milestone on a data migration project and should be linked to the project plan. At the governance level, this can make a difference between moving forwards, or having to spend more time preparing before the next steps.

References

• https://www.tableau.com/learn/articles/what-is-data-cleaning
• https://blog.trginternational.com/data-cleansing-best-practices

4. Data mapping to your target system

Having gathered together good quality structure data (and avoided data migration chaos so far) the next challenge is inserting your precious data into the target system. This is where the “square peg into a round hole” metaphor becomes useful. If we assume you’ve retrieved and stored transaction data into an intermediate location, such as your own database or flat files, you now need to adjust the structure of the data to fit the target system.

At this point you will experience challenges such as

• Missing data attributes from the source which are required by the target
• Structure mismatches from the source to the target
• Data dependencies between data structures
• Validation rules in the target causing rejections
• Data duplication in the source, which needs to be resolved first
• Data ownership and security rules in the target system, such as needing permissions to load data, and then make transactions active.
• Data volume constraints. Can the target system accept new transactions quickly enough, and does it have the capacity to store all this new data?

All these points mean you need a repeatable process as you won’t get it right the first time or even quickly. During each run of the migration a meaningful set of metrics should be gathered to enable upward reporting on progress. That leads to the next topic on testing your migration.

References

• https://www.sciencedirect.com/topics/computer-science/target-mapping
• https://segment.com/blog/data-mapping/

5. Data Migration testing

If you’ve adopted the best practises above you might be avoiding data migration chaos, but the next steps may uncover hidden data migration chaos in the migrated portfolio. 

Once you have a set of data, and a mapping to the target system, it is time to test your migration process and measure the results. Only when you try to add transaction, position, and security set up information to the target system will you discover any hidden constraints, dependencies, or inaccuracy in your data.

For fund data it is likely you will need to prepare the security master file first, covering the required historic period. Alongside the security master data may be related reference date to support those records. Without this supporting data the primary transactions won’t “connect” into the target system.

With any system load, you will need to decide on some thoughtful ways to validate the results. A simple check is the number of inserted data records compared from source to target. That assumes there is a one-to-one mapping from source to target, and in the case of some complex structured transactions this may not be the case.

Our Segue platform has a wide range of pre-configured capital markets system and fund service providers source libraries supporting over 45 asset classes. The user selects the source destination provider and Segue will automatically generate the required system outputs for transaction, positions and fund information including the security master. Segue inbuilt 3-way position and transaction reconciliation capability performs real-time data validation control and reconciles to user defined parameters comparing existing provider to new provider including fund administrator Key benefits include pre-configured source system libraries, immediate data validation and 3-way transaction and position reconciliation for increased performance and oversight and control including valuations and P/L.

A deeper method is to retrieve valuations for each trade or strategy and compare. You will need to gather those valuations at the same point in the business cycle, such as close-of-business, to eliminate differences in market data values.

Once you compared valuations, you should also compare the daily P&L, greeks and risk sensitivities, this will give you a forensic view into the underlying economics of a trade and portfolio.

Given all these metrics available within Segue, you then have clarity on the quality of the data migration and enables you to feed exceptions into your tracking process for resolution. This also feeds into your project tracking and upwards reporting for an overall view on the state of the project.

6. Data security

When moving data between systems you are exposing data to risks whilst in transit. Once data is extracted from the source system it is then in the hands of the team carrying out the migration, and may be held in intermediate systems during the process. As the data is moved into the target system, it is likely that the source, intermediate and target systems are all within different organisations, different locations and different technology stacks. The data must also be transported between organisations, and the method of transport needs to be considered carefully.

Key points to consider for data security during a migration are:

1. Ensuring data integrity, not losing data during transit
2. Securing access to the data before and after transit – stopping anyone who might intercept the data from having access (during transit) due to mistakes or malicious intent
3. Complying with data privacy regulations such as GDPR in the EU and UK, but possibly CCPA in California for consumer data
4. Controlling access to data to specific individuals via specific methods – in other words use whitelists, 2FA and other methods to exclude anyone not directly required to view the data
5. Make a data security plan and require approval at the governance level
6. Include a clear definition of liability for any data security risks or issues within the project framework

An issue which will occur during most migrations, is how to move data between multiple organisations securely without using email or Excel, and allowing multiple parties to participate in the migration. The answer from Prodktr is to use our secure cloud-API based SaaS technology solution Segue with a strong pre-configured instrument library and connections with leading service and system providers. 

7. Fund specific data coverage

Features of funds which cause unexpected issues include:

• A mix of traditional investment products with new digital assets. Firms are now carrying tokens and chain based assets which don’t fit into the usual database structures. Digital assets are represented in data structures which may lie outside the usual capital markets database formats and need their own parallel migration project.
• Manual adjustments to trades to accommodate non-standard circumstances could involve the addition of fee events which need to be captured and applied to the target system.
• Customised changes to cashflow schedules to implement non-standard strategies can cause valuation changes which aren’t represented by the top-down trade parameters. If these aren’t detected using the reconciliation methods suggested above, the migration may fail
• Manual lifecycle events in the life of a trade, driven by corporate actions or volatility events must be detected and carried over
• Data volumes can themselves blow up a migration. In the best circumstances the set of transactions to be migrated can be treated as one bundle – but if the performance or capacity of either the source or target systems don’t allow this, the migration will need to be constructed from multiple batches of data and recreated at the destination
• Multiple participants in a migration raise the complexity level. Imagine a fund moving to a single service provider from more than three underlying source providers. The recreation of the portfolio at the target will involve a multi-way reconciliation

8. Our recommendations

Fund and system migrations are complex and the risks many and varied. If your firm is considering such a project, here are reasons why our Segue technology should be part of your considerations:

• We have already completed multiple fund migrations and have the experience (and bruises) within our technology
• We have invested in our Segue platform to address the manual risks above of data structure, security, reconciliation and formats
• We have specific expertise in the Capital Markets industry, and use that to help support your data migration successfully
• Don’t assume your in-house team has the expertise, capacity and tools to complete such a complex project
• Segue is pre-configured to support over 45 instrument types and has proven integration library with the leading system and service providers
• In-built 3 way trade and position reconciliation capability

9. More reading on data migration chaos

Here are some external sources which provide further background on data migrations:

• Prodktr: https://prodktr.com/risks-for-data-migrations/
• PwC: https://www.pwc.com/jg/en/services/advisory/blogs/five-ways-to-take-headache-out-of-fund-migration.html
• Ankura: https://angle.ankura.com/post/102hjcr/how-to-mitigate-the-risks-and-challenges-in-data-migration

---

Photo by Lanju Fotografie on Unsplash