data migration in computers, Lecture notes of Information Systems

Download data migration in computers and more Lecture notes Information Systems in PDF only on Docsity!

Legacy System

Data Conversion

And Migration

Proprietary Notice

All trademarks, services marks, trade names, logos, icons and other intellectual property rights (the ―Intellectual Property‖) are proprietary to Key Management Group, Inc (KMG) or used by KMG as permitted through contractual arrangements with respective proprietors. The content of this testing process shall at all times continue to be the property of KMG/ proprietors and will be strictly protected by copyright and other applicable laws. Any usage, reproduction, retransmission, distribution, dissemination, sale, publication or circulation of the said content without the express prior written consent of KMG is strictly prohibited. Nothing in this document shall be construed as granting by implication or otherwise a license or a right to use such Intellectual Property, or indulge in reproduction or decompiling or sublicensing such Intellectual Property.

The users/clients expressly recognizes, admits and confirms that all the IPR rights in the development of this testing process is the sole property of KMG and the user /client shall not claim or in any way assist, aid, permit or abet others in claiming any right title or interest there in or thereto. If required by KMG the user/client shall cooperate in having KMG register the IPR in this documentation, if deemed necessary.

© Unless otherwise acknowledged, the entire IPR (Intellectual Property Rights) in this testing process and contents thereof absolutely vests in KMG. All rights reserved.

1 Introduction

From time to time various Business organizations implement new Software Application System to replace the functionality currently delivered by one or more legacy systems. Complications arise when there is an attempt to take the information currently maintained by the legacy system and transform it to fit into the new system. More often, the data structure of the legacy systems is different from the new application being implemented, and that difference is not just limited to the table names, field names or attributes or sizes. The types of databases are different and diverse, or the entity relationships definitions in the new system are not compatible with the older legacy application. To the business organizations all the data being held in the legacy system remains critical for their business functions and decision making.

To bring the legacy system data to the new application some Data Conversion must take place, where an initiative, separate or concurrent with the implementation of the new application, is undertaken to convert data from one structural form, used by the legacy application to the structural from required by the newer application.

Often in a Data Conversion process, one would tend to think that any two similar systems that maintain the same sort of data, as they are doing very similar functions should map from one to another without much trouble. But that is not really the case as -

• In Legacy systems, historically, data integrity checks were not strictly enforced, leaving orphan data
• Theoretical design differences exist between hierarchical and relational systems.
• Legacy data may require some data cleansing.

Other Factors contribute to complexity of such projects are

• Need for a well-defined target data structure model.
• 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.

Therefore it is important to have a sound, methodological approach by which organizations can undertake Data Conversion projects, which will help to confront unpleasant surprises on later stages and resolve those issues fast and effectively.

Key Management Group. Inc. (KMG) often helps various business organizations to implement the new Software Application, especially in the area of Property and Casualty (P&C) Insurance and Enterprise Resource Planning (ERP) package software. To help our business clients, KMG designed and developed a methodology and adopted that for such Data Conversion projects.

This document attempts to document that methodology, which it does by stating various processes to be performed at various stages of Conversion and Migration of Legacy System Data, and the Roles and Responsibilities of various stakeholders and participants in the projects.

Though over the years, this document was reviewed and refined and republished by KMG, based on the experience of many such projects, it is important to note that the methods and processes described within this document is generic in nature. Each project presents its own challenges and opportunities and KMG personnel engaged in the respective project design and develop, add or modify steps within the broad framework of this methodology, for the benefit of that specific project.

New Development

BPO

Business Analysis

Testing

Application Support

Legacy Web-enabling

Services

2 About KMG

KMG was established in US in 1990 and is among Top 100 outsourcing companies in US. It is listed in the top 100 outsourcing companies in the world, top 10 fastest growing Indian-owned companies in the US & among top 50 software companies in India. It has a Dun & Bradstreet rating of ―Good- 2A1‖.

KMG provides software development and maintenance solutions mainly for Insurance domain. The main emphasis is on the P&C Insurance sector in the US. Almost 75% of its revenue comes from maintenance of legacy applications. It is also quite capable for developing applications from scratch using the latest state-of-art technologies & architectures (Including SOA). Once again, most of the development work is centered around the Insurance vertical.

KMG has a large pool of Business Analysts with exposure to all lines & facets of the Insurance industry. This group helps us retain the knowledge & bridge the gaps between the end-users & the development teams.

KMG’s onsite-offshore model and industry expertise enables the company to enter into long-term, mutually beneficial strategic partnerships with many Fortune 500 companies. Unlike most other Indian-based software services firms, KMG maintains a large development team in the US. This team is used to interact with the client & provide a longer overlap to the users.

KMG has its headquarters in NY with 4 Offshore Development Centers in India (Bangalore, Delhi, Chandigarh and Kolkata) and 3 sales-cum-development centers in US (New York, San Diego, & Chicago).

The offshore entity was established in 2000 and has grown at a very high rate over the last eight years. At this time, KMG has around 100 professionals in the US who are supported by another 400 in India. KMG has resource expertise that covers Microsoft.NET technologies, J2EE, Mainframe, IBM iSeries (AS/400) and Software Testing.

KMG is building a 600-seat state-of-art development center near Chandigarh (Mohali). This center will also house a large training center for providing training to in-house / external resources on legacy systems. The center will be fully operational by end of 2010.

3.2 Guiding Principles/Assumptions

This methodology is covering the automated process of Conversion and Migration of Legacy System Data, not the Manual Data conversion. Manual data loads will occur where data volumes are low and there is no time or cost benefit in automating the data loads.

The process of Automated Data Conversion could be through a pre-packaged Tool procured from one of the software vendors in the market or could be developed by KMG, based on the specific requirement of the Project. Benefits and costs of the both approach will be reviewed.

The Extraction and Conversion Process will be tested prior to the production conversion as per KMG Methodology, described later in this document. This is to forecast precise conversion timeframes and to validate/test programs/tools configuration.

This methodology is designed for Single tier and Single time Conversion and migration. Single-tier migration is a migration in which all source data is migrated onto one or more devices within a single tier—that is, primary storage.

This methodology does not define process for Continuous and policy-driven migration which, unlike all other data migrations, is a continuous process (requiring the installation of an archival application) that uses a set of user-defined policies to determine, in real time, when and where data needs to be moved.

4 Planning

To achieve the goal of a successful Legacy data migration through conversion, it is imperative that a lot of upfront planning happens prior to that move of data, irrespective of the complexity of the task.

In this stage KMG, in conjunction with its client, prepares a plan, which is also intended to shorten the duration of the Conversion/migration process and also reduces business impact and risk.

The Migration Plan , which is the end result of the planning process by KMG, defines

• Requirements i.e. what data is moved, where it is moved, how it is moved, when it is moved, and approximately how long the move will take
• Team i.e. the Users, Business Analysts, System/Data Analysts, Testers, Target Application Users
• Environment under which the Legacy (Source) and the Target Application system operates, What will be the Data Conversion Stage Area
• Schedules - and task distribution, staffing levels
• Configuration Management Plan – to maintain control over changes needed due to various reasons

The other objectives of this planning process by KMG is to design and document resolutions for events like

• Application downtime,
• Performance degradation,
• Technical incompatibilities, and
• Data corruption/loss.

KMG would initiate such project by deploying a Project Manager and an experienced Analyst to gather information and develop the Migration Plan

Location Client – On-Site

KMG CLIENT

Participants Project Manager CIO / CTO Business Analyst / SME Project Manager Business Analysts Technical Lead (Legacy System) Technical Lead (Target System)

Output The Migration Plan

4.2 Identify Team and Stakeholders

KMG expects that the Top Management / CIO from Client’s side would designate the following

a. Client Project Owner of the proposed Conversion/Migration project, who would have the absolute power to resolve all issues and take necessary decisions

b. Client Project Coordinator/ Manager – to assist KMG in all phases of the project and expected to be well versed with either or both Application Systems - Legacy and the Target Application system

Then the KMG Project Manager, in consultation with Client Project Owner and Client Project Coordinator / Manager, would identify various key personnel, who will be required to be part of the team for the project of Conversion and Migration of the Legacy System Data.

The table below defines the roles and responsibilities of various individuals – participating in Legacy System Data Conversion project from the Client side.

Roles Responsibilities Business Analysts -^ Explains the Business Processes/Functions of the Legacy System and Target System

• Identify Data elements / Groups related to various Business Process and Functions
• Help to prepare / validate the Data Profile created by KMG’s Business / Data Analysts
• Validate Data Mapping document – developed by KMG’s Business Analysts and Technical Leads for the Conversion and related reporting.
• Analyze impacts of the Data Conversion processes and related actions (including Freezing of operation) on the Business

Technical Lead (Legacy System)

• Assist in KMG’s team in learning/exploration of the Legacy System Environment
• Help in developing the Statistics (File/Table Counts, Record/Rows count) for the Legacy System
• Assist in Identifying the Legacy System Entities (in terms of File / Table name, Record Layouts)
• Resolve queries during Analysis phases
• Identify and extract relevant Legacy System Data for KMG’s Technical Team for Testing of Conversion Programs/ Migration Tool Technical Lead (Target System)
• Assist in KMG’s team learn/explore the Target System Environment
• Assist in Identifying the Target System Entities (in terms of File / Table name, Record Layouts)
• Resolve queries during Analysis and Mapping phases
• Makes technical decisions relating to the configuration of data exchange and transform features – after Data Mapping exercise is completed
• Assist in determination Test results based on extracted relevant Legacy System Data for KMG’s Technical Team for Testing of Conversion Programs/ Migration Tool

Roles Responsibilities Database Administrator

• If RDBMS being used in either or both of the Legacy and Target system, DDL could be generated to identify Table and filed properties
• Create and Maintain Test Database for the Target System and Staging Environment for the actual conversion

System / Network Administrator

• Provide help in understanding the Legacy System Environment and Target System Environment
• Help to setup up Staging Area (Servers/ network)
• Connectivity between Legacy System and Staging Area
• Execute the process of DATA LOAD in target environment Project Coordinator/ Manager
• Manages the Data Conversion Processes and repo/rting
• Review and if required modify, develop the Migration Plan
• Coordinate and Provide necessary resources – as per the Migration Plan
• Assign the right Technical resources

KMG will assign Technical Resources to the Project team –

• Business Analyst, having relevant experience of the business processes
• Data Analyst / Subject Matter Expert (SME)
• Technical Lead – with experience in design and develop application system
• Developers, when required

In KMG - all projects are overseen by a Project Owner (Normally a AVP/VP of the company). Often in KMG project the PM and the Technical Lead have interchangeable skills and act as backups for each other.

The backup developers continuously shadow the main team members. They are as good as any other developer in the team and can take over from anyone at a day’s notice. These people are used in cases of attrition as well handling spikes in the load (if any). These people also make sure that the work is not disrupted if any member goes on vacation. A fresh person is added to the team as backup the day any backup is absorbed into the team

4.4 Create Project Schedule

KMG understands that at this stage it would not be possible to prepare a detailed realistic schedule as there would be too many unknown parameters. Since this methodology uses a targeted search strategy, the amount of time required to cleanse data is directly proportional to the number and complexity of the mappings and transforms which must be applied to the source data.

An especially tricky part of doing any planning is that the target database is usually a ―moving target‖ – it is being developed at the same time as the cleansing and conversion software, and is always subject to change. If the new software is not a custom application, but a commercially available package, then it makes the schedule creation process little easier even though that possibility still remains as most of the package system often heavily customized.

KMG will create a schedule – using a project plan and pert chart, showing tasks involved, dependencies between the tasks, and the staff required to perform each task. The schedule will include time to be spent on task of familiarizing with the legacy systems’ operations and its data elements and MIS staff with the data cleansing effort, and the tasks required from them.

Key milestones in KMG methodology are

a. The Migration Plan in place and agreed. b. Project team is formed. c. Completion of Source and Target System Data Profiling d. Completion of Data Mapping Operation and creation of Data Map Specification e. Completion of task to identification of Data Cleansing requirement f. Decision on usage of Automated Conversion tool or Development of Customized Solution g. Development of Acceptance criteria h. Identification and procurement of Conversion Tool – if a Tool to be used i. Design, Development and System Testing of Customized Solution – if that is decided j. Test plan is written. k. Creation of the Staging area l. Test run of Conversion Process and validation m. Execution of the Conversion Process n. Reconciliation / data checking reports are run. o. Acceptance criteria and performance metrics are evaluated. p. A go / no-go decision takes place. q. Data Load in the Target System r. Validation by the Target System s. Data issues may be resolved post-migration.

KMG Project manager develops the schedules in consultation with the Client and the Project team member, taking their experience and issues into consideration.

Project schedules would be revised based on the actual time taken to resolve complex issues and procurement of necessary hardware, software resources and team resources.

4.5 Configuration Management Plan

The Configuration Management (CM) Plan typically specifies how versions of the software will be managed.

KMG would include a detailed procedure for controlling changes to the

• Mapping requirement and document
• Software changes, in case customized solution to be deployed

A Change Control Board (CCB) could be constituted, consisting of KMG Project manager and Client Project Owner, to review any proposed database changes and its impact. This would be critical to ensure communication between the members of the project teams – whether software development team, Data Analysts/ SMEs.

KMG will review and recommend, if required, Business freeze in multiple areas – which could be a critical and required component of such Legacy Data conversion effort. Such requirement would be explained and reviewed with business data owners, management and auditors.

Business freeze requirements should be addressed in detail through a separate document and circulated well before the timelines - established for the cutover plan.

5.1 Source and Target Data Profiling

In this stage, Data Structures 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 analysis is undertaken by the KMG Data Analysts/SMEs with the help of Client Business Analysts / Technical Leads, and documented in Data Profile repository. This repository, which integrates source and target data in mutual format, provides a profile of the various entities - visibility into that data’s usage, capacity, and growth patterns. Various interfaces and reports utilizing the to-be-migrated data are also considered.

Also considered areas are

a. Must data integrity be checked during (not just after) migration? b. How much data will be moving from point to point (server to server)? c. What is the estimation of the amount of transformation and cleansing needed? d. What are the data profiling and data validation rules/phases applicable to the data?

KMG’s Data profiling process consist of three sequential steps with each step building on the information produced in the previous steps. Data sources are profiled in three dimensions: down columns (column profiling) ; across rows (dependency profiling) ; and across tables (redundancy profiling).

Column Profiling. Column profiling analyzes the values in each column or field of source data, inferring detailed characteristics for each column, including data type and size, range of values, frequency and distribution of values, cardinality and null and uniqueness characteristics. This step allows analysts to detect and analyze data content quality problems and evaluate discrepancies between the inferred, true meta data and the documented meta data.

Dependency Profiling. Dependency profiling analyzes data across rows comparing values in every column with values in every other column and infers all dependency relationships that exist between attributes within each table. Dependency profiling identifies primary keys and whether or not expected dependencies (e.g., those imposed by a new application) are supported by the data. It also identifies "gray-area dependencies" those that are true most of the time, but not all of the time, and are usually an indication of a data quality problem.

Redundancy Profiling. Redundancy profiling compares data between tables of the same or different data sources, determining which columns contain overlapping or identical sets of values. It looks for repeating patterns among an organization's "islands of information". Redundancy profiling identifies attributes containing the same information but with different names (synonyms) and attributes that have the same name but different business meaning (homonyms). It also helps determine which columns are redundant and can be eliminated and which are necessary to connect information between tables. Redundancy profiling eliminates processing overhead and reduces the probability of error in the target database.

KMG believes that developing an accurate profile of existing data sources is the essential first step in any successful data migration project. The most significant problem associated with this phase could be if there are frequent changes to the Target Application System Data model. Any change in the target system model would have to 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.

5.2 Data Mapping

Data Mapping is the process in which each source data elements are assigned to one or more target data element. After having done the analysis, KMG’s team of Business Analysts, Data Analysts / SMEs and Technical Leads undertakes data mapping process of identifying and documenting the target field for each of the fields in legacy system.

KMG’s objective of this process is to produce a comprehensive mapping between Legacy and Target System. 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.

Data mapping process would yield the following results:

• Gaps in the design of the target system:

a. Un-mapped Target System Data Entity – this are the cases where data requirement is not satisfied by Legacy/Source system. Specification could be developed for Deriving/defaulting data values for such cases – specifically when that data is mandatory field in Target System. b. Un Mapped Legacy System Data Entity - for which no Target Data element exists in the target system. This will lead to data loss, which might NOT be desirable in all cases.

Data mapping process aims at identification and resolution of such gaps and such issues can be addressed in different ways.

1. Ignore the data in legacy system if it is not used.
2. If it is business critical data then request for modification of the target system.
3. In cases wherein target system cannot be modified and it is business critical data that is absolutely essential to retain, consider some workaround to incorporate it as a part of some other field that can be easily understood and extracted with business consent.

• Data Map (Transformation specification): At the end of the data mapping process, a detailed document would be in place that would show the target field identified for each legacy field. Apart from identifying the target fields to which the legacy fields are mapped, mapping specifications define the rules to be applied in the conversion process. These rules are commonly known as Transformation rules.
• Referential Rules for Integrity check and domain values permitted,
• Validation rules – Application specific Business rules

Data mapping is an iterative process. For any change in the design of the target system or change in rule for setting a value of a particular field there is a need to amend the mapping specification reflecting the changes in Transformation rules.

The resulting Data Map (Transformation specification) document would be used later

a. In conjunction with third-party data migration tools to extract, scrub, transform and load the data from the old system to the new system or b. Develop a customized Application system to convert the Legacy / Source system data into Target System data model. This will provide essential information to the programmers creating conversion routines to move data from the source to the target database.

5.4 Design Migration Architecture

Figure 3

The generic framework for a Data Conversion process consists of following steps

a. Data Extraction – Read and Gather data from source data store(s) into another storage, and if required converted to the data format of the Target System (e.g. EBCIDIC to ASCII) and loaded in Staging Area

b. Validation and Cleansing – to confirm content and structure of extracted data in light of business rules and fulfills integration rules based on the referential rules of Target System. Data Cleansing is performed at this time based on requirements identified during Analysis phase.

c. Transformation - convert the extracted data from its previous form into the target form. Transformation occurs by using Transformation Rules defined in Data Map (Transformation specification) and lookup tables.

d. Validation -Target System - confirm content and structure of transformed data is valid for target.

e. DATA LOAD - Write the data into the target database, either through script or copying data using system utilities.

Steps (a), (b), (c) and (d), shown in figure 3.0 above, are part of the ―Convert‖ phase of the Data Conversion and Migration methodology. Step (d) i.e. DATA LOAD is part of the ―Migrate‖ phase of the project.

KMG makes necessary changes, depending on the specific requirement of each project, to the tasks as defined in the various steps of the framework and create the executable process for Conversion of the Legacy Data

At this stage, KMG, in consultation with Client IT team, analyze and determines whether

• To use a data transformation tool to manage their data Conversion/migration effort? or
• Design and Develop an application system – specifically designed to fulfill that specific conversion requirement based on the Data Map (Transformation Specification) by a group of technical experts?

KMG also determines, based on some of the information and Client requirement – whether to execute the Conversion process in all at once or move data over through a controlled phase of multiple releases? KMG analyze pros and cons to both options, considering which approach will best fit for the project based on organization needs - to be evaluated on a variety of factors, like how much data involved and requirement of the Target System.

5.5 Build Converted Data Test Plan

After determining the Framework, execution model – i.e. tool route or development of the tailored solution, KMG develops Test Process and Plan based on the Data Map (Transformation Rules).

Testing process in Data Conversion and Migration project could be categorized into two :

• Physical errors are the result of syntactical errors of the scripts / programs, which can be easily identified and resolved.
• Logical errors – these are identified and resolved during Test phase. Such errors are result of the quality of the mapping effort. During Implementation/Testing scripts/programs developed based on the Data Map i.e. Transformation specification, are executed.

Based on the on the Data Map (Transformation Rules) KMG, creates Test Plans – in which KMG identifies Legacy System data element and determines the Target system element and the expected results based on set of extract to be used for testing. This test plan is to be prepared for each of the data element being converted.

Response to the following queries would be gathered and verified by Testing team

a. How many records were expected to be created by the scripts being tested? b. Did the correct number of records get created? If not, why? c. Has the data been loaded into the correct fields? d. Is the data load complete – or are certain fields missing? e. Has the data been formatted correctly? f. Are any post-migration clean-up tasks in order?

The goal of a successful data migration is to keep the length of the deploy phase(s) to a minimum.

During Pilot /Testing, KMG would determine the quality of data mapping, by providing the populated target data structures to the users that assisted in the analysis and design of the conversion scripts/application system.

That would help Client Integration/Functional team to understand the data and would allow the user to physically interact with the new, populated data structures of the Target System.