Method and system for migrating data

• US 6,151,608 A
• Filed: 04/07/1998
• Issued: 11/21/2000
• Est. Priority Date: 04/07/1998
• Status: Expired due to Fees

• Current Assignee: Crystallize, Inc.
• Original Assignee: Crystallize, Inc.
• Inventors: Abrams, Helene G.
• Primary Examiner(s): Breene, John
• Assistant Examiner(s): ROBINSON, GRETA LEE
• Application Number:
  US09/056,360
• Time in Patent Office: 959 Days
• Field of Search: 707/204, 707/200, 707/100, 707/202, 707/4, 707/10, 707/205, 707/101
• US Class Current: 707/679
• CPC Class Codes: G06F 16/214 Database migration support
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/99955 Archiving or backup

Abstract

Method and system of the invention migrate data from one or more ASCII files and/or from one or more relational databases to one or more relational database tables without the need to write code. In doing so, the invention allows the user to define mapping templates and conditionals to assist in translating and transforming data values. The invention also enforces referential integrity, data dependencies, order of operations, and uniqueness constraints using a predefined set of migration rules templates that are based on the principles of relational design. The invention uses these mapping and migration rules templates to intelligently generate instructions for updating or populating relational database destination tables. The instructions control the data transfer, data translation, data transformation, data validation, foreign key insertion, and the addition of required codes and flags in the destination tables. A migration engine of the system includes a data map architect and an update processor which spawns the templates and migrates the data dynamically, utilizing the data definitions for the destination tables. In addition to supporting conversion efforts, the invention provides support for performing consolidation, restoration from an archive, migration to new instances, upgrading to a new release, adding bolt-ons and enhancements, and changing business requirements. The audit trail in the invention is complete enough that the entire migration process can be reversed without compromising the integrity of either the source or the destination application.

Claims

1. An automated computer-implemented method for migrating source data from at least one source to at least one destination table of a database having a schema without a user having to write computer code, the method comprising the steps of:

defining patterns which describe format and content of the source data;

applying the patterns to the source data to create transformed data;

associating migration rules based on the schema with the patterns to generate a set of instructions that define migration paths; and

loading the transformed data in a sequence into the at least one destination table based on the set of instructions, the at least one destination table having a defined format and destination fields, wherein the step of loading is automatically sequenced based on the migration rules so that referential integrity is maintained.

2. The method as claimed in claim 1 further comprising the step of loading the data from the at least one source into at least one temporary table having source fields.

3. The method as claimed in claim 2 wherein the at least one temporary table is a relational database table.

4. The method as claimed in claim 2 wherein the at least one source is an ASCII file and wherein the method further comprises the steps of creating an extract table with the data from the at least one source and loading the extract table into the at least one temporary table.

5. The method as claimed in claim 2 wherein the at least one source is a relational database table and wherein the step of loading the data from the at least one source includes the step of automatically loading the relational database table into the at least one temporary table.

6. The method as claimed in claim 2 further comprising the step of associating the source fields with the destination fields.

7. The method as claimed in claim 6 further comprising the step of creating templates for transforming or translating the data from the at least one source to obtain the transformed data.

8. The method as claimed in claim 7 further comprising the step of utilizing a computer to generate user information and wherein the step of creating is based on the computer-generated user information.

9. The method as claimed in claim 8 further comprising the step of automatically inserting logical and arithmetic operators in universal or conditional statements.

10. The method as claimed in claim 6 further comprising the steps of creating at least one intermediate table having a format and fields and linking the at least one temporary table and its fields with the at least one intermediate table and its fields wherein the format and the fields of the at least one intermediate table are substantially identical to the format and fields of the at least one destination table and wherein the transformed or translated data is utilized to populate the at least one intermediate table.

11. The method as claimed in claim 7 wherein the step of creating the templates is performed interactively with the user.

12. The method as claimed in claim 7 wherein the templates include data migration patterns having conditionals with logical operators to provide instructions for translating and transforming the data from the at least one source.

13. The method as claimed in claim 12 wherein the patterns include a transfer mapping pattern.

14. The method as claimed in claim 12 wherein the patterns include a monovariate translation pattern.

15. The method as claimed in claim 12 wherein the patterns include a multivariate concatenation translation pattern.

16. The method as claimed in claim 12 wherein the patterns include a multivariate reverse concatenation translation pattern.

17. The method as claimed in claim 12 wherein the patterns include a multivariate transformation pattern.

18. The method as claimed in claim 7 wherein the set of instructions are generated based on the templates and the schema.

19. The method as claimed in claim 7 further comprising the step of utilizing the templates to validate the data in the at least one temporary table for uniqueness constraints.

20. The method as claimed in claim 7 further comprising the step of utilizing the templates to validate the data in the at least one temporary table for verifying data dependencies.

21. The method as claimed in claim 7 further comprising the step of utilizing the templates to validate the data in the at least one temporary table for enforcing referential integrity.

22. The method as claimed in claim 7 further comprising the step of utilizing the templates to validate the data in the at least one temporary table for moving the data into the at least one destination table.

23. The method as claimed in claim 7 further comprising the step of utilizing the templates to validate the data in the at least one temporary table for checking that mandatory destination fields are filled.

24. The method as claimed in claim 7 further comprising the step of grouping the templates into batches.

25. The method as claimed in claim 1 wherein the database is a relational database and the at least one destination table is a relational database table.

26. The method as claimed in claim 1 wherein the data is migrated from a plurality of sources.

27. The method as claimed in claim 1 wherein the transformed data loaded into the at least one destination table updates or adds to records already in the at least one destination table.

28. The method as claimed in claim 27 wherein the data is migrated from a plurality of sources.

29. The method as claimed in claim 1 wherein the transformed data loaded into the at least one destination table changes the data already in the at least one destination table.

30. The method as claimed in claim 29 further comprising the step of changing base configurations including flex fields.

31. The method as claimed in claim 18 further comprising the step of adding bolt-ons or other modifications to a relational application.

32. The method as claimed in claim 1 wherein archival data is restored.

33. The method as claimed in claim 1 wherein the source data is migrated for one installation or instance of the database to another.

34. The method as claimed in claim 1 wherein the source data is migrated from one version of the database to another.

35. The method as claimed in claim 1 wherein the source data is migrated across environments such as from a development environment to a test environment, a training environment or a production environment without changing setup parameters.

36. The method as claimed in claim 1 wherein the source data is converted from a legacy system to a new system.

37. The method as claimed in claim 1 wherein multiple instances of a subledger are consolidated in a multiorg environment.

38. The method as claimed in claim 1 wherein systems in support of mergers or acquisitions are consolidated.

39. The method as claimed in claim 1 wherein the transformed data is loaded into multiple destination tables substantially simultaneously and wherein the multiple tables includes at least one table which might later be loaded onto a separate system.

40. A computer-implemented system including a migration engine for migrating data from at least one source to at least one destination table of a database having a schema without a user having to write computer code, the migration engine comprising:

a data map architect for creating a set of migration rules based on the schema; and

an update processor for;

generating a set of instructions that define migration paths based on the migration rules;

transforming the data based on the set of instructions to obtain transformed data; and

loading the transformed data in the at least one destination table based on the set of instructions, the at least one destination table having a format and fields, wherein the update processor loads the transformed data in an automatic sequence so that referential integrity is maintained.

41. The system as claimed in claim 40 wherein the data map architect loads the data from the at least one source into at least one temporary table having source and destination fields.

42. The system as claimed in claim 41 wherein the at least one temporary table is a relational database table.

43. The system as claimed in claim 41 wherein the at least one source is an ASCII file and wherein the data map architect also creates an extract table with the data from the at least one source and loads the extract table into the at least one temporary table.

44. The system as claimed in claim 41 wherein the at least one source is a relational database table and wherein the data map architect automatically loads the relational database table into the at least one temporary table.

45. The system as claimed in claim 41 wherein the data map architect also associates the source fields with the destination fields based on the schema.

46. The system as claimed in claim 45 wherein the data map architect loads the data from the at least one source into the at least one temporary table and verifies data dependencies without the user having to write computer code.

47. The system as claimed in claim 45 wherein the data map architect creates templates for transforming the data from the at least one source based on the schema to obtain the transformed data.

48. The system as claimed in claim 45 further comprising at least one intermediate table having a format and fields and wherein the update processor links the at least one temporary table and its fields with the at least one intermediate table and its fields wherein the format and the field of the at least one intermediate table are substantially identical to the format and fields of the at least one destination table and wherein the update processor populates the at least one intermediate table with the transformed data.

49. The system as claimed in claim 47 wherein the data map architect creates the templates interactively with the user.

50. The system as claimed in claim 47 wherein the templates include data migration patterns and logical operators to provide instructions for translating and transferring the data from the at least one source.

51. The system as claimed in claim 50 wherein the patterns include a transfer mapping pattern.

52. The system as claimed in claim 50 wherein the patterns include a monovariate translation pattern.

53. The system as claimed in claim 50 wherein the patterns include a multivariate concatenation translation pattern.

54. The system as claimed in claim 50 wherein the patterns include a multivariate reverse concatenation translation pattern.

55. The system as claimed in claim 50 wherein the pattern includes a multivariate transformation pattern.

56. The system as claimed in claim 47 wherein the update processor generates the set of instructions based on the templates and the schema.

57. The system as claimed in claim 41 wherein the update processor moves the data from the at least one source to the at least one temporary table.

58. The system as claimed in claim 57 wherein the update processor enforces uniqueness constraints without the user having to write computer code.

59. The system as claimed in claim 57 wherein the update processor enforces referential integrity without the user having to write computer code.

60. The system as claimed in claim 57 wherein the update process specifies order of operations for moving data into the at least one destination table without the user having to write computer code.

61. The system as claimed in claim 57 wherein the update processor checks that mandatory fields are populated without the user having to write computer code.

62. The system as claimed in claim 48 wherein the update processor moves the data from the at least one source first to the at least one temporary table where it is transformed and then to the at least one intermediate table for correction to obtain corrected data.

63. The system as claimed in claim 47 wherein the update processor groups the templates into batches.

64. The system as claimed in claim 40 wherein the data base is a relational data base and the at least one destination table is a relational database table.

65. The system as claimed in claim 40 wherein the migration engine migrates the data from a plurality of sources.

66. The system as claimed in claim 40 wherein the transformed data loaded into the at least one destination table by the update processor updates or adds to records already in the at least one destination table.