초록 ▼

Embodiments of the invention comprise a method and apparatus for transforming data. Systems implementing the invention allow for loading the data from one or more source tables into one or more destination tables. The system allows for parsing source data fields, defining new source data fields, com

Embodiments of the invention comprise a method and apparatus for transforming data. Systems implementing the invention allow for loading the data from one or more source tables into one or more destination tables. The system allows for parsing source data fields, defining new source data fields, combining source fields to create source field combinations, and combining destination fields to create destination field combinations. The system allows for mapping source fields and values to destination fields and values, where either the source fields and values or destination fields and values may be field and value combinations, and allows for transforming field values based on destination field type. The tools provided in a system implementing the invention provide a user the ability to intervene at each step during the data transform task. A user may manually input a hierarchy definition, a specific mapping, define rules for combining data fields, or define rules for converting data values.

대표청구항 ▼

What is claimed is: 1. A computer program product comprising: a computer-usable medium having a computer program for transforming a source data structure to a destination data structure embodied therein, said computer program configured to: obtain a source data structure comprising a set of source

What is claimed is: 1. A computer program product comprising: a computer-usable medium having a computer program for transforming a source data structure to a destination data structure embodied therein, said computer program configured to: obtain a source data structure comprising a set of source tables, a set of source fields, a set of source records, a set of source table relationships, and a set of source values; join a plurality of source tables selected from said set of source tables; parse a source field selected from said set of source fields and split said source field into a plurality of destination fields; partition a plurality of source fields selected from said set of source fields into a combined destination field; transform a source value into a destination value having a different data type or different unit of measure; obtain transformation data comprising information associated with at least one set selected from said set of source tables, said set of source fields, said set of source records, said set of source table relationships and said set of source values and at least one destination data structure; access a first source field of said source data structure and display all existing values for said first source field and obtain transformation data associated with all existing values to allow for field-at-a-time handling of said set of source fields to eliminate run-time exceptions; transform said at least one set selected from said set of source tables, said set of source fields, said set of source records, said set of source table relationships and said set of source values into said at least one target destination. 2. The computer program product of claim 1 wherein said destination data structure comprises a database. 3. The computer program product of claim 2 wherein said database comprises catalog data. 4. The computer program product of claim 2 wherein said database comprises financial data. 5. The computer program product of claim 1 wherein said transformation data comprises mapping information and said computer program is configured to use said mapping information to execute a means for mapping said source data structure to said destination data structure. 6. The computer program product of claim 5 wherein said mapping information is displayed to a user. 7. The computer program product of claim 5 wherein said mapping information comprises field-level mapping information that identifies a correlation between at least one of said set of source fields and at least one destination field. 8. The computer program product of claim 5 wherein said field-level mapping information is displayed to a user. 9. The computer program product of claim 7 wherein said at least one of said set of source fields in said field-level mapping comprises a source field combination having a plurality of source fields. 10. The computer program product of claim 9 wherein said source field combinations are displayed to a user. 11. The computer program product of claim 7 wherein said at least one destination field in said field-level mapping information comprises a destination field combination having a plurality of destination fields. 12. The computer program product of claim 11 wherein said destination field combinations are displayed to a user. 13. The computer program product of claim 7 wherein said at least one of said set of source fields in said field-level mapping information comprises a source field combination having a plurality of source fields and said at least one destination field in said field-level mapping comprises a destination field combination having a plurality of destination fields. 14. The computer program product of claim 13 wherein said source field combinations and said destination field combinations are displayed to a user. 15. The computer program product of claim 7 wherein said mapping information comprises value-level mapping information that identifies a correlation between at least one of said set of source values of said at least one of said set of source fields and at least one of said set of destination values of said at least one destination field. 16. The computer program product of claim 15 wherein said value-level mapping information is displayed to a user. 17. The computer program product of claim 15 wherein said at least one of said set of source values in said value-level mapping comprises a source value combination having said set of source values of a plurality of said at least one of said set of source fields associated with a set of destination values of said at least one of said destination fields. 18. The computer program product of claim 17 wherein said source value combinations are displayed to a user. 19. The computer program product of claim 15 wherein said value-level mapping comprises a destination value combination having a plurality of said set of destination values of said at least one of said destination fields associated with a plurality of said set of source values of said at least one of said set of source fields. 20. The computer program product of claim 19 wherein said destination value combinations are displayed to a user. 21. The computer program product of claim 15 wherein said set of source values of said at least one of said set of source fields in said value-level mapping comprises a source value combination having values of a plurality of said at least one of said set of source fields and said destination values of said at least one of said destination fields in said value-level mapping comprises a destination value combination having a plurality of said at least one of said destination fields. 22. The computer program product of claim 21 wherein said source value combinations and said destination value combinations are displayed to a user. 23. The computer program product of claim 1 wherein said transformation data comprises type information and said computer program comprises a means for converting said set of source fields from a source type to a destination type based on said type information. 24. The computer program product of claim 23 wherein said type information is displayed to a user. 25. The computer program product of claim 1 wherein said transformation data comprises type information for converting said set of source fields from a source type to a destination type based on said type information. 26. The computer program product of claim 1 wherein said computer program is configured to merge said set of source values of said set of source fields into source value combinations comprising a plurality of source values and convert said source value combinations into destination fields of said destination data structure. 27. The computer program product of claim 26 wherein said source value combinations are displayed to a user. 28. The computer program product of claim 1 wherein said computer program is configured to generate hierarchy among said set of source values of said set of source fields into source value hierarchies comprising a plurality of source values and convert said source value hierarchies into destination fields of said destination data structure. 29. The computer program product of claim 28 wherein said source value hierarchies are displayed to a user. 30. The computer program product of claim 1 wherein said transformation data is generated automatically. 31. The computer program product of claim 1 wherein said transformation data comprises parsing information and said computer program is configured to execute a means for parsing data values from descriptive fields. 32. The computer program product of claim 1 wherein said source data structure comprises descriptive fields having data values and said computer program is configured to use said transformation data to extract said data values from said descriptive fields. 33. The computer program product of claim 1 wherein said computer program is configured to generate at least one added source field in accordance with said transformation data. 34. The computer program product of claim 33 wherein said added source field is displayed to a user. 35. The computer program product of claim 1 wherein said computer program is configured to generate at least one cloned source field containing a copy of said set of source values in one of said set of source fields in accordance with said transformation data. 36. The computer program product of claim 35 wherein said at least one cloned source field is displayed to a user. 37. The computer program product of claim 35 wherein said values of said at least one cloned source field is displayed to a user. 38. The computer program product of claim 1 wherein said computer program is configured to generate at least one split-into-hierarchy source field containing a hierarchy based on said set of source values in one of said set of source fields in accordance with said transformation data. 39. The computer program product of claim 38 wherein said at least one split-into-hierarchy source field is displayed to a user. 40. The computer program product of claim 38 wherein said set of source values of said split-into-hierarchy source field are displayed to a user. 41. The computer program product of claim 1 wherein said computer program is configured to generate at least one plurality of split-into-multiple source fields each containing components of said set of source values in one of said set of source fields in accordance with said transformation data. 42. The computer program product of claim 41 wherein said plurality of split-into-multiple source fields are displayed to a user. 43. The computer program product of claim 41 wherein said set of source values of said plurality of split-into-multiple source fields are displayed to a user. 44. The computer program product of claim 1 wherein said computer program is configured to extract data values from descriptive fields by identifying said data values within said descriptive fields, parsing said data values from said descriptive fields, and populating said at least one new source field wit said data values. 45. The computer program product of claim 1 wherein said transformation data comprises measurement information and said computer program uses said measurement information to execute a means for normalizing units of measure within said set of source fields. 46. The computer program product of claim 45 wherein said measurement information is displayed to a user. 47. The computer program product of claim 45 wherein said means for normalizing further comprises combining numeric value and said unit of measure from a plurality of source fields. 48. The computer program product of claim 1 wherein at least one of said set of source values within said set of source fields comprises an improperly formed measurement value comprising a numeric value and a unit of measure. 49. The computer program product of claim 48 wherein said unit of measure comprises an implicit unit of measure associated with said set of source values. 50. The computer program product of claim 48 wherein said unit of measure comprises inconsistent string values. 51. The computer program product of claim 48 wherein said units of measure differ within a plurality of said values within each of said set of source fields. 52. The computer program product of claim 48 wherein computer program is further configured to append said unit of measure to set of source values missing said unit of measure. 53. The computer program product of claim 1 wherein said source data structure comprises a plurality of joined data sources. 54. The computer program product of claim 1 wherein said transformation data comprises matching information and said computer program is configured to use said matching information to execute a means for matching said set of source records to said destination records. 55. The computer program product of claim 54 wherein said matching information comprises record-level information identifying said correlation between at least one of said set of source records and at least one of said destination records. 56. The computer program product of claim 54 wherein said matching information is displayed to a user. 57. The computer program product of claim 54 wherein said matching information indicates a new destination record is to be created with at least one of said set of source values from one of said set of source fields from one of said set of source records. 58. The computer program product of claim 54 wherein said matching information indicates at least one of said destination fields in at least one of said destination records is to be updated with at least one of said set of source values from one of said set of source fields. 59. The computer program product of claim 54 wherein said matching information indicates at least one destination record is to be replaced with at least one of said set of source records. 60. The computer program product of claim 1 wherein said computer program is further configured to transform said set of source values in accordance with said transformation data. 61. The computer program product of claim 60 wherein said transformed set of source values are displayed to a user. 62. The computer program product of claim 1 wherein said computer program comprises an integrated interface for obtaining said transformation data. 63. The computer program product of claim 62 wherein said source data structure is represented within said integrated interface as a hierarchy. 64. The computer program product of claim 63 wherein said hierarchy comprises a visual representation of said set of source tables. 65. The computer program product of claim 63 wherein said hierarchy comprises a visual representation of said set of source fields. 66. The computer program product of claim 63 wherein said hierarchy comprises a visual representation of said source table relationships. 67. The computer program product of claim 66 wherein a user can define additional relationships between said set of source tables. 68. The computer program product of claim 63 wherein said hierarchy comprises a visual representation of said source data values. 69. The computer program product of claim 62 wherein said destination data structure is represented within said integrated interface as a hierarchy. 70. The computer program product of claim 69 wherein said hierarchy comprises a visual representation of said destination tables. 71. The computer program product of claim 69 wherein said hierarchy comprises a visual representation of said destination fields. 72. The computer program product of claim 69 wherein said hierarchy comprises a visual representation of said destination table relationships. 73. The computer program product of claim 69 wherein said hierarchy comprises a visual representation of said destination data values. 74. The computer program product of claim 1 wherein said computer program comprises a means for handling exceptions a source field at a time. 75. The computer program product of claim 1 wherein said computer program is configured to collapse said set of source values within said set of source records down to a set of distinct values within said set of source records. 76. The computer program product of claim 75 wherein said set of distinct values is configured to act as a proxy for said set of source values. 77. The computer program product of claim 75 wherein each distinct value within said set of distinct values is configured to act as a proxy for all instances of said distinct value across said set of source records. 78. The computer program product of claim 75 wherein said transformation data is applied to said set of distinct values. 79. The computer program product of claim 75 wherein said transformation data is applied once to each distinct value rather than once for each instance of said distinct value, and is automatically propagated to each instance of said distinct value.