Continuing from the earlier post on Data Migration, here is the concluding piece on various Data Migration Strategies (by Soumendra Mohanty inDM Review Special Report, May 2004):
In Part 1, we discussed what could be the phases in a typical data migration project. The same questions will keep on coming back to the project managers again and again-
- How am I going to determine the performance criteria?
- What are the risks and how am I going to manage those?
- Do I have a failure mode effect analysis document which captures the failure modes and does a cause and effect analysis?
There are many other questions that arise. Let us look at the different strategies and checkpoints which are an integral part of a data migration project.
While implementing data migration architecture, the data migration team has to take a number of considerations. Following are a few of such considerations:
- Data volume analysis
- Source system and target system processing power
- Complexity of data mapping rules and business rules
Point-to-Point Data Migration Architecture
If during transformation, several records are normalized into separate database records that will result in a significant increase in the overall data volume, extract data from the source system(s) as is and move it to a staging area in the target system, then apply cleansing and transformations locally.
Highlights:
- Reduced network round trip
- Local transformations means the actual data migration process is over, data has actually reached the targeted server.
- Leverage processing power of target server
Hub-Spoke Data Migration Architecture
Figure 2 shows partitioning source data and preconverting the historic data in the source environment and supports any number of source and target systems (spokes) while managing the overall ETL processes through a hub.
Highlights:
- Can accommodate any number of sources and/or targets
- Data rules are kept at a separate layer
- Load balanced on target server
1. Making Sense of Data Migration
The biggest challenge with a data migration project is: Making the target system understand what the source system is telling it.
Here are a few key practices and issues with data migration projects.
Comprehensive Mapping. Every data field that is going to be migrated from the source system to the target system must be defined and examined to ensure compliance with field lengths, data types, domain values permitted, system rules, integrity checks and any other possible issues.
A detailed data map is critical to understanding where information is going as well as whether there are any known or avoidable obstacles in the way of successfully arriving there.
A good data map will detail an in-depth cross-referencing of all mutual fields across the source system and the target system. Ideally it should include:
- Names of applicable to and from fields
- Lengths and data types of these fields
- Any logic involved in mapping such as string truncations or validations against any business rules
Extract Validation. Data in source system is known to contain problems or can be unknowingly incorrect due to many possible factors including human keying-in errors and/or a lack of checks and accountability particularly in less sophisticated systems. Any validation rules that can be utilized to locate and fix these problems should be performed on the first-pass data extract extending the process to multiple iterations if required.
It is common that some errors will not surface until others have been identified and fixed. Whereas the source system may ignore the discrepancies with, for example, items like same persons billing address recorded different in different files or database tables, the target system, potentially having better business rules, may opt for a Type I or Type II slowly changing dimension implementation for the same address change. Data validation and clean up is an essential and key component of a good migration plan.
Quality Transformation. Data extracted from the source system needs to be transformed or translated into a format that the target system can import and understand. This transformation will not only the defined data mappings, but will also execute any underlying business logic functions that may be essential to populating more complex data structures.
Fortunately, these stages can be efficiently performed by technically advanced ETL tools such as Informatica, Ab Initio, DataStage, etc.
So far we have discussed the technical aspect of data migration. However, as with many IT projects, the what, where and when is just as important as the how. When dealing with the management aspect of a data migration project, following issues should be considered.
2. Phased or Big Bang Approach?
When choosing to migrate data from one system to another, does it make sense to try to accomplish this all at once or move data over through a controlled phase of multiple releases?
Naturally there will be pros and cons to both options, considering which approach will best fit your organization needs to be evaluated on a variety of factors.
Some examples of these factors can be as straightforward as how much data there is to migrate or as seemingly abstract as the amount of training effort it will take to make a "big bang" worthwhile to your organization in terms of the ROI.
3. Stakeholder Expectation
How long will the migration take place? How many internal resources must the client IS team commit to the migration and for what period of time? What is the impact on the other business-critical processes? What is the cost?
The answers to these questions should all be addressed before a single object is extracted or a single transformation is designed. A data migration project cannot succeed if it is poorly scoped therefore project schedules, estimated level of effort, costs and resource requirements should be all provided and adhered to.
4. Rollback
When importing data into a target system, what happens if the data migration fails? Are we prepared to either utilize existing transaction rollback functionality or do we have capacity to design and build our own if none exists? How do we manage the client expectation in such cases? Do we have a mitigation plan in place? Have we discussed these with the client IS team and business users?
Answers to these questions gives us an additional layer of security and contributes a lot in terms of executing the project in time, within the budget as well as managing customer expectation all along.
5. Scalability
Naturally, when one starts to talk about data migrations with information owing back and forth, and how this will improve business, the issue of scalability is bound to surface.
As a manager, just as you should ensure that you have the infrastructure in place to support foreseeable growth, you should also ensure that your data migration can be leveraged for this growth as well.
6. Replication
The issue being: what happens in case of disaster or irrecoverable system failure? Commonly this issue will rear its head during a data migration project - typically born from the pressure placed on the manager to get a migration right so that 100 percent production is never at a stake.
Migrating data to a backup system at the same time as a new target system should be seriously considered to add one more layer of security and ensure that the disaster recovery plan is in place.
Data migration is an important aspect of most software development efforts yet that importance is often overlooked or inadvisably minimized. It is the rare software development effort indeed where the eventual measure of success is not in some way dependent upon the accurate migration of data. The reason for this is quite simple - opting for a new system is a business decision having its own priority. However, migrating historical data to make the new system work may not be a top priority factor, rather making the new system work and sustain the business critical processes are the top most factors.
The success of the data migration project lies in a seamless data movement and always remains on the shadow of implementing the new system.
Following are few common risks that will summarize the data migration project discussion we had so far.
Failure to treat data migration as a project unto itself. Data migration is complex undertaking that should not be regarded as merely a peripheral effort to the main development project. The data migration effort should be treated as a complete sub-project with a defined process, a thoughtfully derived time and cost estimate, and a series of phases that can be tracked or managed.
Underestimating the time and cost of data migration. It is important to perform a reasonably diligent survey of source systems in order to determine the quality of those source system's documentation and source data. If the source system does not have up-to-date data documentation in the form of data model and data dictionary, the task of determining the structure and data types of the desired source data and it's mapping to the target data will be increased in time and cost. If the source system has less stringent data quality requirements than the target system or if the data quality of the source system has been allowed to lapse over time, the actual act of performing the migration will take longer time due to the need to perform post-migration data clean up.
Lack of end-state data quality. If the migration effort does not formally specify the level of end-state data quality and the set of quality control tests that will be used to verify that data quality, the target domain may wind up with poor data quality. This will negatively impact the perceived outcome of the development effort.
Failure to support the organizational support. When the complexity and importance of data migration is not adequately appreciated it may be difficult to gain organizational support for that data migration, especially in terms of funding and resources. It may be even more difficult to garner a positive level support in separate organizations that have primary responsibility for the source data. This can happen when the organization supporting the source data feels threatened by the new system or it can happen simply because the migration effort is not a top priority for that organization.
Lack of appreciation for the complexities of data mapping. The central effort of data migration is understanding the source data and developing the mapping that allows the data in the source domain to be accurately transformed and moved to the target domain. There are many factors affecting mapping that can be ignored:
- Ensuring that the semantics sense of a given attribute is correctly mapped: the same datum may carry a different name in the source domain than in the target domain; the source domain and the target domain may carry the same name for what is conceptually a different datum.
- Understanding that the number of entities and their respective relationships may be vastly different between the source domain and the target domain.
- Strategies and extensions may have to be developed to handle certain intractable mappings if they are discovered. An attribute may exit in the source domain that does not exist in the target domain and vice versa.
These issues and subsequent impacts may manifest themselves in both quantitative and qualitative ways:
In quantitative sense this can result in:
- Costs associated with error detection
- Costs associated with error rework
- Costs associated with error prevention
- Time delays in operations
- Costs associated with delays in processing
In a qualitative sense this can result in:
- Difficult and/or erroneous decisions
- Organization wide data inconsistency
- Low acceptance level by users of the new system
The most important factors in mitigating the risks of data migration are to treat the data migration as a project and to use a sound methodical process having the following KPIs:
Data Profiling - Gain a complete understanding of the content, structure, quality, and integrity of the data of the source system.
Data Mapping - Develop an accurate set of data mapping specifications from the source system to the target system.
Migration Approach and Architectural Considerations - Whether point-to-point migration or hub-and-spoke migration, this needs to be evaluated and carefully articulated.
Development - Selecting an ETL tool to automate the migration process and make it more scalable should be a high-priority item.
Quality Assurance - Conduct mock migrations, pilot migrations before the final migration run; this will ensure that the migration process is robust and trusted.
