There are a number of factors that make the design of a sound data conversion and migration strategy a Herculean task. Some of the factors are:
- A need for a well-defined target data structure model.
- An in-depth understanding of the functionality of the source data structure.
- Constant changes on the target model design have a knock-on effect on conversion process design.
- Source data quality, if poor, needs to be cleansed in order to be successfully migrated.
- Degree of complexity of the target model in relation to the source data model.
- The differences in task definitions between the source and target data structures.
This article defines a standard generalized data conversion process methodology that can be applied for any legacy data conversion project.
Design Approach Varies
Data conversion and migration process are generally designed for two systems in the broadest sense, which are online transactional processing (OLTP) systems and online analytical processing (OLAP) systems. The approach for data conversion and migration for these two systems can vary considerably as the outcome of the conversion process serves different needs.
Online Transactional Processing Systems
As their name suggests OLTP systems are involved in transactional processing of data. Data conversion and migration of an OLTP system is a one-time affair. The conversion and migration process is executed only once to accomplish this. The following aspects are generally given low priority while designing the conversion methodology for such systems:
- Overall time taken for the conversion and migration process.
- Script performance of conversion and migration process.
- Code documentation
The reason for low priority in such areas is attributed to the fact that this is a throwaway sort of design. Once the conversion is accomplished the migration scripts are rendered useless.
Online Analytical Processing Systems
OLAP systems are data warehouses that are primarily used for analysing the transactional data. These are decision support systems (DSS) providing the management with useful and critical information about the business: for instance, systems that provide demand-supply statistics, fund flow analysis and trade volumes. Data conversion and migration to such a system would be a critical process that would be run periodically unlike conversion process for OLTP systems. The frequency is generally dictated by the nature of the business. For instance, if it were stock market information that is being analyzed, the conversion and migration process would run every day or, perhaps, every hour.
Conversion process design should address the following issues with extreme care when dealing such systems:
- Conversion process complexity.
- Time taken for the conversion and migration process.
- Script performance of conversion and migration process.
- Frequency of execution of conversion process.
- Code documentation.
A conversion process design for such a system should be well documented as it is to be reused. Performance issues can pose severe problems depending on the frequency at which the conversion process is required to be run. If the conversion takes an eternity in an industry where it is expected to run at the end of the day, then it would severely affect the decision support system.
The source system for an OLAP system would be a corresponding OLTP system. An in-depth understanding of functional data structure model of the OLTP system is a must to arrive at a sound, acceptable conversion process design for OLAP.
Conversion Process Methodology for OLTP Systems
Generally, there would be many source systems that would need to be integrated in a conversion process and migrated to a single target model. Conversion process methodology does not vary due to the presence of more than one legacy system but the level of difficulty increases with more number of systems. Complications arise if a thorough planning is not in place to tackle these systems effectively. The conversion and migration process consequently tend to fail. To stop this from happening there should be a sound methodology that would show us the successful way, if not eliminate all the unpleasant surprises during this journey.
Figure 1: Holistic View of Legacy Data Conversion Process
As shown in Figure 1, the legacy data conversion process is fairly straightforward but is not free of complications. The following phases can be distinctly identified as the building blocks of such a data conversion process.
- Phase 1: Data Structure Model Analysis
- Phase 2: Data Mapping
- Phase 3: Conversion Process Design and Execution
Phase 1: Data Structure Model Analysis
This is the first phase of any data conversion project. The data structure model analysis involves an in-depth study (qualitative and quantitative in the case of a legacy system) of both the legacy and target systems. The following questions are required to be answered in this phase:
- What is to be migrated?
- Are there more than one legacy systems that are required to be migrated?
- Can the data in legacy system be migrated into the target system without data cleansing?
- What is the level of data cleansing required to maximize migration?
- What are the major complications involved in the conversion process?
The most significant problem associated with this phase is the dearth of information about the target system and the frequent changes to the target model. Any change in the target system model should be taken into account to analyze the consequences of the change in the eventual conversion process. This renders the whole process to be iterative until a point wherein there is a freeze on the target system data structure model or a complete understanding of the legacy system has been reached.
