System and method for automatic data mapping

• US 7,596,573 B2
• Filed: 06/11/2003
• Issued: 09/29/2009
• Est. Priority Date: 06/11/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Bardini, Agnes, Loo, Kevin, O'Neil, Owen
• Primary Examiner(s): Pham; Khanh B
• Application Number:
  US10/458,972
• Publication Number:
  US 20070179939A1
• Time in Patent Office: 2,302 Days
• Field of Search: 707 1- 10, 707100-1041
• US Class Current: 1/1
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

A system and method for automatic data mapping of a source object to a target object. Field maps can be automatically determined from the source and target objects, database platform, ETL maps, user-defined and data conversion rules, default values, or other pre-defined parameters. Where a change occurs in the source or target data structure, the present invention ensures proper mapping between the source and target data structures occurs, thus reducing the burden of exception handling on the user and preventing oversight in correcting improper mapping. The present invention can also be used by a compiler to generate SQL steps during compile-time processes to design field maps that can be stored as an application class.

Claims

1. A computer-implemented method for automatically generating data maps used by applications, the method comprising:

receiving information at a computer system identifying a section of data in a data repository having a source object and a target object;

receiving information at the computer system specifying a set of map rules, each map rule in the set of map rules configured to match source objects in the data repository to target objects in the data repository in response to a set of criteria;

receiving information at the computer system defining literal data as a set of default values to be assigned to fields of the target objects in the data repository where no match is found by the computer system based on the set of map rules between the source objects in the data repository and the target objects in the data repository, each default value in the set of default values comprising predetermined literal data;

applying the set of rules to the source object and the target object using the computer system to determine a match between the source target and target object based at least one map rule in the set of map rules,

if the computer system determines a match based on the set of rules between the source object and the target object, automatically constructing a first map definition for the section of data in the data repository using the computer system based on the at least one rule thereby assigning the source object to the target object, wherein a field map created from the first map definition assigns data in the data repository associated with the source object to the target object;

if the computer system fails to yield a match based on the set of rules between the source object and the target object, automatically constructing a second map definition for the section of data in the data repository using the computer system thereby assigning a default value in the set of default values to the target object, wherein a field map created from the second map definition assigns literal data associated with the default value in the set of default values to the target object; and

generating the field map using the computer system based on the first or second map definition.

2. The method of claim 1 wherein the literal data associated with the default value comprises a record-level default value.

3. The method of claim 1 wherein the literal data associated with the default value comprises a system-level default value.

4. The method of claim 1 wherein the at least one rule in the set of rules specifies information that defines a transformation of the source object to the target object for the first map definition constructed by the computer system.

5. The method of claim 1 wherein the at least one rule in the set of rules specifies information that defines a translation of the source object to the target object for the first map definition constructed by the computer system.

6. The method of claim 1 wherein the at least one rule in the set of rules specifies information that assigns the source object to the target object for the first map definition constructed by the computer system.

7. The method of claim 1 further comprises assigning literal data associated with a system level or a record level default value to the target object using the computer system based on the field map.

8. The method of claim 1 further comprising processing background information using the computer system to further define the target object based on one or more of record structure or data type conversion.

9. The method of claim 8 wherein processing the background information further comprises processing information using the computer system based on a platform type and a data type.

10. The method of claim 1 further comprising:

detecting a modification using the computer system in the section of data in the data repository; and

adjusting the target object using the computer system based on the modification.

11. An automatic mapping system comprising:

a data repository;

application server hardware configured to exchange data with the data repository; and

a mapping engine hosted by the application server hardware and configured to automatically generate a field map, absent direct user intervention, using a source object and a target object from the data repository based on information specifying a set of map rules, each map rule in the set of map rules configured to match source objects in the data repository to target objects in the data repository in response to a set of criteria, and information defining literal data as a set of default values to be assigned to fields of the target objects in the data repository where no match is found based on the set of map rules between the source objects in the data repository and the target objects in the data repository, each default value in the set of default values comprising predetermined literal data,

wherein the mapping engine automatically defines the field map to assign literal data associated with a default value in the set of default values to the target object when the set of map rules fails to yield a match between the target object and the source object, and

wherein the mapping engine automatically defines the field map to assign data in the data repository associated with the source object to the target object when at least one of the map rule in the set of map rules yields a match between the target object and the source object.

12. The automatic mapping system of claim 11 wherein the data repository further comprises:

a management system; and

an interface for exchanging data and information with the application server and the mapping engine.

13. The automatic mapping system of claim 11 wherein the data repository further comprises:

a presentation layer;

a data layer; and

an engine layer.

14. The automatic mapping system of claim 13 wherein the presentation layer further comprises:

a data source for providing a source field;

a map designer for designing the field map; and

a scheduler for integrating the field map.

15. The automatic mapping system of claim 13 wherein the data layer further comprises:

an ETL engine for extracting, transforming, or loading data;

a data transformation module exchanging data with the mapping engine; and

a query module for structured calling of data from the data repository based on the field map.

16. The automatic mapping system of claim 11 wherein the mapping engine further comprises:

an initialization module for initializing field map generation;

a rules module for administering the set of map rules for the mapping engine;

default module for specifying literal data associated with the set of default values;

a map definition module for integrating the set of map rules and the set of default values to define a target field;

a field conversion module for converting the target field using the map definition module; and

a field map generator for generating the field map.

17. An apparatus that includes a processor for automatically generating data maps used by applications, the apparatus comprising:

means for receiving information identifying a section of data in a data repository having a source object and a target object;

means for receiving information specifying a set of rules configured to match source objects in the data repository to target objects in the data repository;

means for receiving information defining literal data as a set of default values to be assigned to target objects in the data repository where no match is found between source objects in the data repository and the target objects in the data repository based on the set of rules, each default value in the set of default values comprising predetermined literal data;

means for determining whether the source target matches the target object based on the set of rules,

means for, if the means for determining yields a match based on the set of rules between the source object and the target object, automatically defining a field map for the section of data in the data repository, wherein the field map assigns data in the data repository associated with the source object to the target object;

means for, if the means for determining fails to yield a match based on the set of rules between the source object and the target object, automatically defining a field map for the section of data in the data repository, wherein the field map assigns literal data associated with a default value in the set of default values to the target object; and

means for generating the field map.

18. A computer program product embodied on a computer readable medium storing instructions operational when executed by a computer system for automatically generating field maps used by applications, the computer program product comprising:

code for receiving information identifying a section of data in a data repository having a source object and a target object;

code for receiving information specifying a set of rules configured to match source objects in the data repository to target objects in the data repository;

code for receiving information defining literal data as a set of default values to be assigned to target objects in the data repository where no match is found between source objects in the data repository and the target objects in the data repository based on the set of rules, each default value in the set of default values comprising predetermined literal data;

code for determining whether the source target matches the target object based on the set of rules,

code for, if a match is found based on the set of rules between the source object and the target object, automatically defining a field map for the section of data in the data repository, wherein the field map assigns data in the data repository associated with the source object to the target object;

code for, if a match is not found based on the set of rules between the source object and the target object, automatically defining a field map for the section of data in the data repository using, wherein the field map assigns literal data associated with a default value in the set of default values to the target object; and

code for generating the field map.

19. A method performed by a computer system for servicing requests for data, the method comprising:

receiving a request at the computer system from a user interacting with an application to access data stored in a data repository;

identifying a first set of fields in a plurality of database fields at the computer system based on the request;

determining a second set of fields in the plurality of database fields at the computer system;

obtaining a set of required rules, a set of required default values, a set of optional map rules, and a set of optional default values at the computer system;

each rule in the set of required rules specifying criteria that enable the computer system to match source fields in a database to target fields in the database, each required default value in the set of required default values specifying literal data used by the computer system to assign to target fields in the database, each optional rule in the set of optional rules specifying criteria that enable the computer system to match source fields in the database to target fields in the database without overriding a matching based on the set of required rules and literal data specified in the set of required default values, each optional default value in the set of optional default values specifying literal data used by the computer system to assign to target fields in the database without overriding a matching based on the set of required rules and literal data specified in the set of required default values;

determining at the computer system whether a field in the first set of fields matches a field in the second set of fields based on the set of required rules;

if a match is determined at the computer system based on the set of required rules, defining a field map at the computer system between the field in the first set of fields and the field in the second set of fields based on an action specified in one or more satisfied required rules in the set of required rules;

if a match is not determined at the computer system based on the set of required rules, determining at the computer system whether to assign a required default value to any fields in the first set of fields;

based on a determination at the computer system to assign a required default value to a field in the first set of fields, defining at the computer system a field map for the field in the first set of fields where literal data associated with a required default value in the set of required default values is specified to populate the field in the first set of fields;

determining at the computer system whether a field in the first set of fields matches a field in the second set of fields based on the set of optional rules;

if a match is determined at the computer system based on the set of optional rules, defining a field map at the computer system between the field in the first set of fields and the field in the second set of fields based on an action specified in one or more satisfied optional rules in the set of optional rules without overriding a matching based on the set of required rules and literal data specified in the set of required default values;

if a match is not determined at the computer system based on the set of optional rules, determining at the computer system whether to assign an optional default value to any fields in the first set of fields;

based on a determination at the computer system to assign an optional default value to a field in the first set of fields, defining a field map at the computer system for the field in the first set of fields where literal data associated with an optional default value in the set of default values is assigned to the first field without overriding a matching based on the set of required rules and literal data specified in the set of required default values;

based on a determination at the computer system that any fields in the first set of fields remain unassigned, assigning a null value at the computer system as the data for unassigned fields in the first set of fields; and

automatically generating a data map at the computer system that maps data to the first set of fields based on field maps defined by the computer system;

wherein the data map is used by the computer system to service the request from the user interacting with the application to access data stored in a data repository.