Data Migration Challenge
A successful Data Migration effort is often critical to a system implementation. These implementations can be a new or upgraded ERP package, integration due to merger and acquisition activity or the development of a new operational system. The effort and criticality of the Data Migration as part of the larger system implementation project is often overlooked, underestimated or given a lower priority in the scope of the full implementation project. As an end result, implementations are often delayed at a great cost to the organization while Data Migration issues are addressed.
Basic principles for data migration to lower the project time, to lower staff time to develop, lower risk and lower the total cost of ownership of the project.
Some of principles include:
1. Leverage staging strategies
2. Utilize table driven approaches
3. Develop via Modular Design
4. Focus On Re-Use
5. Common Exception Handling Processes
6. Multiple Simple Processes versus Few Complex Processes
7. Take advantage of metadata
Leverage Staging Strategies: in data migration it is recommended to employ both a legacy staging and pre-load staging area. The reason for this is simple, it provides the ability to pull data from the production system and use it for data cleaning and harmonization activities without interfering with the production systems. By leveraging this type of strategy you are able to see real production data sooner and follow the guiding principle of ‘Convert Early, Convert Often, and with Real Production Data’.
Utilize Table Driven Approaches:
Developers frequently find themselves in positions where they need to perform a large amount of cross-referencing, hard-coding of values, or other repeatable transformations during a Data Migration. These transformations often have a probability to change over time. Without a table driven approach this will cause code changes, bug fixes, re-testing, and re-deployments during the development effort. This work is unnecessary on many occasions and could be avoided with the use of configuration or reference data tables. It is recommend to use table driven approaches such as these whenever possible. Some common table driven approaches include:
l. Default Values – hard-coded values for a given column, stored in a table where the values could be changed whenever a requirement changes. For example, if you have a hard coded value of NA for any value not populated and then want to change that value to NV you could simply change the value
in a default value table rather then change numerous hard-coded values.
2. Cross-Reference Values – frequently in data migration projects there is a need to take values from the source system and convert them to the value of the target system. These values are usually identified up-front, but as the source system changes additional values are also needed. In a typical
mapping development situation this would require adding additional values to a series of IIF or Decode statements. With a table driven situation, new data could be added to a cross-reference table and no coding, testing, or deployment would be required.
3. Parameter Values – by using a table driven parameter file you can reduce the need for scripting and accelerate the development process.
4. Code-Driven Table – in some instances a set of understood rules are known. By taking those rules and building code against them, a table-driven/code solution can be very productive. For example, if you had a rules table that was keyed by table/column/rule id, then whenever that combination was found a
pre-set piece of code would be executed. If at a later date the rules change to a different set of pre-determined rules, the rule table could change for the column and no additional coding would be required.
Develop Via Modular Design:
As part of the migration methodology, modular design is encouraged. Modular design is the act of developing a standard way of how similar mappings should function. These are then published as templates and developers are required to build similar mappings in that same manner. This provides rapid development, increases efficiency for testing, and increases ease of maintenance. The result of this change is it causes dramatically lower total cost of ownership and reduced cost.
Focus On Re-Use:
Re-use should always be considered during Informatica development. However, due to such a high degree of repeatability, on data migration projects re-use is paramount to success. There is often tremendous opportunity for re-use of mappings/strategies/processes/scripts/testing documents. This reduces the staff time for migration projects and lowers project costs.
Common Exception Handling Processes:
Employing the Velocity Data Migration Methodology through its iterative intent will add new data quality rules as problems are found with the data. Because of this it is critical to find data exceptions and write appropriate rules to correct these situations throughout the data migration effort. It is highly recommended to build a common method for capturing and recording these exceptions. This common method should then be deployed for all data migration processes.
Multiple Simple Processes versus Few Complex Processes:
For data migration projects it is possible to build one process to pull all data for a given entity from all systems to the target system. While this may seem ideal, these type of complex processes take much longer to design and develop, are challenging to test,and are very difficult to maintain over time. Due to these drawbacks, it is recommend to develop many simple processes as needed to complete the effort rather then a few complex processes.
Take Advantage of Metadata:
The Informatica data integration platform is highly metadata driven. Take advantage of those capabilities on data migration projects. This can be done via a host of reports against the data integration repository such as:
1. Illustrate how the data is being transformed (i.e., lineage reports)
2. Illustrate who has access to what data (i.e., security group reports)
3. Illustrate what source or target objects exist in the repository
4. Identify how many mappings each developer has created
5. Identify how many sessions each developer has run during a given time period
6. Identify how many successful/failed sessions have been executed
These design principles provide significant benefits to data migration projects and add to the large set of typical best practice items that are available in Velocity. The key to Data Migration projects is architect well, design better, and execute best
A successful Data Migration effort is often critical to a system implementation. These implementations can be a new or upgraded ERP package, integration due to merger and acquisition activity, or the
development of a new operational system. The effort and criticality of the Data Migration as part of the larger system implementation project is often overlooked, underestimated or given a lower priority in
the scope of the full implementation project. As an end result, implementations are often delayed at a great cost to the organization while Data Migration issues are addressed.
Microsoft Certified Solutions Associate (MCSA)