In-Depth
The Hidden Costs of Enterprise Data Conversion
In today's fast-paced business environment, the competitive edge goes to the company with the best information and the most-efficient processes for delivering that information. Many re-engineering efforts involving business processes are designed to ensure that vital information is delivered in the most adept manner. This is particularly important for implementing enterprise applications and data warehouses.
Determining the right mix of clients and servers is a complex process and involves many segments of an organization. You have to select hardware, evaluate database software, decide whether to buy or build applications, customize off-the-shelf applications and train personnel. Often underestimated - or worse, overlooked - in this process is the task of actually moving legacy data from older "big iron" computers to newer enterprise systems.
Because data conversion does not require a significant up-front expenditure, companies are sometimes surprised by the time requirements and expense of the actual implementation. If it is performed as an afterthought, data conversion can add considerable expense to the project and can delay the implementation of new systems.
The Right Tools, The Right Plan
Whether you're converting to an enterprise environment or planning to implement new thin clients, protecting your organization from the hidden costs of data conversion requires two critical elements: the right tools and the right plan. The challenge is to integrate the tools into a comprehensive data conversion plan.
Following a simple set of four steps will reduce both the time and the cost of any implementation:
Step 1. Factor data conversion into your efforts at the beginning, giving it the same priority as selecting hardware and software.
Step 2. Map the steps for a successful data conversion process at the outset. Be sure to include creating a conversion team with adequate resources and data conversion skills.
Step 3. Arm your conversion team with the tools it needs in order to get the job done, including using integrated tool sets that can save both time and expense.
Step 4. Make data conversion part of the overall enterprise infrastructure. Data conversion will need the same quality assurance, continuing care, and resources as any other part of the infrastructure, particularly if it is an ongoing process.
A Top-Down Approach
Avoiding the hidden costs of migrating data calls for a top-down approach. Data conversion must be treated with no less regard than long-term budget planning, in which all facets of the organization are broken out, analyzed and planned according to their relationship to the bottom line.
A successful strategy challenges a company to build an infrastructure to implement and support the data-conversion effort on an ongoing basis. An integral part of that infrastructure is a diverse team of users and specialists with varying levels and types of expertise with both the existing legacy system and the destination enterprise application, coordinated by a project manager:
DBAs. Database administrators (DBAs) for the client/server databases: The DBAs will have the necessary knowledge of relational theory, although they may not have detailed knowledge about the specific application's constraints.
Users. Often the "owners" of legacy systems, the users know and understand the actual data. Their understanding does not necessarily extend to the physical structure or format of the legacy data.
Programmers. The programmers will write the code to transfer data from the legacy system. The users and DBAs give them guidance on how to convert and send the right data to the right place.
Clearing the Obstacles
To take full advantage of enterprise systems, an organization needs the continuity of historical information that exists in the legacy data. Inconsistent or inaccurate data will undermine the usefulness of the enterprise system and cause considerable problems in the transition to new applications, requiring more work to be done to make the new system operable.
Several obstacles can complicate the conversion process:
*Underestimating the time required: Complex data-conversion projects can take from six months to more than two years to complete, depending on the volume of data.
*Potential for human error: If you use SQL programming to accomplish data conversion and transformation, the process of writing, debugging and rerunning the code creates another source of data errors that can be difficult to detect.
*Inadequate documentation: If either the source or destination application is poorly documented, the project team must spend a great deal of time analyzing data to reverse-engineer the database.
*Complex relational data models for destination data: You have to understand the model of the new application to decide where data belongs in the new relational model.
*Inconsistent or poor data in legacy systems: If you have application changes over time or poor input controls, you may have poor-quality or inconsistent data in the legacy systems. You should clean this data before moving it to the new application.
*If you don't clear these obstacles, the conversion process will be slower and more costly, resulting in a significant delay in implementing the new application.
The good news: Your organization can overcome these obstacles. You can contain the time and expense of data conversion by including it in the overall implementation strategy, planning the process carefully and researching the most effective and efficient ways to convert data.
In addition, there are now tools on the market that can speed up the data conversion process and deliver a higher level of quality than can be achieved with reliance on programming.
A New Breed of Tools
The database tools market has matured in the last few years, and the growth of the enterprise market and data warehousing has introduced a new breed of tools aimed at supporting conversion. Organizations using these conversion tools as part of an overall conversion plan can reduce conversion time by 50 to 75 percent. These tools can be used to:
- Analyze source and destination data structures for formatting, constraints and dependencies (This is particularly valuable when documentation is inadequate or nonexistent.)
- Transform data by using business-rule concepts
- Convert and load extracted flat files to the relational database management system
- Verify loads through use of query tools
- Document the actual conversion process
The best tools have an intuitive graphical environment that requires little training - the conversion team's tasks are too significant to accommodate an extensive learning curve.
The advantages of using these conversion tools can be substantial. A typical example is that of a large electrical equipment manufacturer involved in converting a mainframe database to a enterprise Oracle database used outside consultants to write conversion scripts to migrate the data to an Oracle Financials and Manufacturing application. The consultants needed three to four days to write each script.
Using an automated conversion tool-set, the manufacturing company's staff was able to create similar scripts in a single day, without having to write or debug programs. The tool-set enabled all team members with expertise in the business rules and processes of an organization to participate in the conversion process, resulting in an implementation that more closely met the needs of end users.
Sizing Up Conversion Tasks
The conversion team faces several tasks, which can be divided into two categories: planning (analytical) and implementation (programming).
Planning Tasks. These require that the team have a conversion strategy before doing actual implementation work. The following action items enable the right planning decisions to be made:
- Defining the relationship between legacy data and the RDBMS tables
- Identifying database table columns assigned by hierarchical dependencies
- Identifying columns requiring constant default values
- Identifying columns requiring data manipulation, such as concatenation or multiplication by other values
- Identifying columns to be derived from lookup tables or sequences
- Validating columns against data in other tables, to maintain referential integrity during the conversion
Data conversion must be part of an overall implementation plan. A key component of this plan is the evaluation and selection of a Data Conversion product. Look for the following capabilities in your tool selection process:
- A complete set of data transformation functions
- Good integration with target database
- Support for all phases of data conversion 3/4 graphical viewing and modification of target database, transformation, mapping, loading and querying 3/4 all well-integrated
- Easy to use and modify through iterative cycles
- Ability to test transformations and mapping before committing data to the target database
- Ability to interactively test the transformation and validation rules before they are committed to the database
- Mapping the right data fields to the right columns
Implementation Tasks. Due to the complexity of transforming and mapping large amounts of legacy data from a variety of sources, implementation can be complicated and exceptionally time-consuming. Implementation tasks are identified based on the decisions made in the planning phase and focus on the following aspects:
- Creating data extraction/download programs for the legacy system
- Creating flat files for legacy data
- Defining/creating interface tables
- Populating interface tables
- Defining entity conversion and default values
- Implementing data transformations in interface tables
- Copying interface tables to production tables
- Confirming application table load
- Validating entity conversion
- Repeating the process to refine the validations and transformations
By explicitly taking each of these steps into account before the actual conversion work begins, the team can anticipate obstacles and apply the appropriate resources and tools in order to maintain the project schedule. In a traditional data conversion project, these resources include a range of programming activities, including C programs or SQL scripts. Outside consultants often assist during this phase.
NYSE Takes Stock in Data Conversion
A good example of an effective data conversion effort was conducted by the New York Stock Exchange (NYSE). The NYSE was faced with converting operations from its legacy systems (IBM mainframes running MVS) to relational database technology in a enterprise environment (Hewlett-Packard servers running UNIX). The ongoing conversions and database maintenance had to be performed by an internal staff that had little prior knowledge of SQL or UNIX.
The NYSE began with a systematic plan that focused on understanding the source and target data relationships before any data conversion was attempted. The team studied the legacy data carefully and used outside consultants - who understood the new Oracle Financials applications well - to document the requirements of the target Oracle database. Finally, the NYSE chose a conversion tool that let it focus on defining the data relationships rather than on C programming.
The NYSE began migrating financial records to the Oracle Financials module of Oracle Applications in the middle of 1995. Since then, the finance department has completely converted three years' worth of ledger records (including budget, journal entries, and cost allocations), current accounts-payable vendor records, and non-retired fixed-asset records. Well over 200,000 general ledger records, 8,000 asset records and 5,000 vendor records have been converted, representing the majority of the NYSE's legacy data. The NYSE went live with three new enterprise financial applications on January 1, 1996, just six months after beginning the conversion process.
Using Your Blueprints
Underestimating the task of moving data to enterprise systems is akin to constructing an office building without blueprints: You may eventually get the job done, but you'll most likely add inexcusable labor and material costs that could have been avoided. If you plan ahead and use the right tools, your data conversion effort will realize measurable time and monetary savings, ultimately helping your organization achieve the competitive edge it is seeking from its right-sizing efforts.
ABOUT THE AUTHOR:
Bob Bessin is Director of Marketing for SmartDB Corporation (Palo Alto, Calif.). He joined SmartDB Corporation from Network Intelligence Inc., a start-up developer of enterprise network management systems, where he also served as director of marketing, as well as working with Hewlett-Packard and Network General Corporation.