Method and process to optimize correlation of replicated with extracted data from disparated data sources

• US 20030225742A1
• Filed: 05/31/2002
• Published: 12/04/2003
• Est. Priority Date: 05/31/2002
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Will, Eric W., Tenner, Jeffrey Wayne
• Granted Patent: US 7,200,619 B2
• Time in Patent Office: Days
• US Class Current: 707/2
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems
• CPC Class Codes: G06Q 30/02 Marketing; Price estimation or determination; Fundraising
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A method, a data structure, a computer program product and a computer-readable medium for correlating at least a first plurality of data records and a second plurality of data records, each data record of the first plurality of data records being uniquely identified within a corresponding data source by an associated internal identifier and each data record of the first and second plurality of data records comprising at least one external identifier. According to one embodiment, the method comprises determining a data record of the first plurality of data records and at least one data record of the second plurality of data records having an identical external identifier; and mapping the at least one determined data record of the second plurality of data records to the internal identifier associated with the determined data record of the first plurality of data records.

Claims

1. A computer-readable medium comprising computer-executable instructions for performing, when run on a computer system, an operation of correlating at least a first plurality of data records and a second plurality of data records, each data record of the first plurality of data records being uniquely identified within a corresponding data source by an associated internal identifier and each data record of the first and second plurality of data records comprising at least one external identifier, the operation comprising:

determining a data record of the first plurality of data records and at least one data record of the second plurality of data records having an identical external identifier; and

mapping the at least one determined data record of the second plurality of data records to the internal identifier associated with the determined data record of the first plurality of data records.

2. The computer-readable medium of claim 1, wherein the determining comprises:

for each data record of the first plurality of data records;

retrieving the associated internal identifier from the data record;

retrieving the at least one external identifier from the data record; and

creating a mapping data record in a mapping data structure, the mapping data record comprising the retrieved associated internal identifier and the at least one retrieved external identifier.

3. The computer-readable medium of claim 2, wherein the determining further comprises,

for each data record of the second plurality of data records:

retrieving an internal identifier associated with the data record, the retrieved internal identifier uniquely identifying the data record of the second plurality of data records within a corresponding data source;

retrieving the at least one external identifier from the data record; and

comparing the at least one retrieved external identifier with the at least one external identifier of each mapping data record in the mapping data structure; and

wherein the mapping further comprises, if the at least one retrieved external identifier matches an external identifier of a specific mapping data record in the mapping data structure;

copying the retrieved internal identifier and an indication of the corresponding data source to the mapping data record.

4. The computer-readable medium of claim 3, wherein the mapping further comprises, if the at least one retrieved external identifier does not match an external identifier of any mapping data record in the mapping data structure:

associating an available internal identifier with the data record of the second plurality of data records, the available internal identifier uniquely identifying the data record within the mapping data structure; and

copying the associated available internal identifier, the retrieved external identifier and an indication of the corresponding data source to the mapping data structure; and

creating a new mapping data record in the mapping data structure, the new mapping data record comprising the associated available internal identifier, the retrieved external identifier and the indication of the corresponding data source.

5. A computer-readable medium comprising computer-executable instructions for performing, when run on a computer system, an operation of creating a data warehouse mapping data structure to correlate at least two different data sources, the operation comprising:

creating a plurality of mapping data records in the warehouse mapping data structure, each mapping data record comprising;

a first value representing an internal identifier uniquely identifying the mapping data record in the warehouse mapping data structure;

a second value representing an external identifier of one of a data record of a first data source and a data record of a second data source; and

a third value representing an internal identifier uniquely identifying a data record of the second data source in the second data source, the data record of the second data source having the second value as external identifier;

whereby a correlation between the first and the second data sources is established.

6. The computer-readable medium of claim 5, wherein the first value comprises an internal identifier uniquely identifying the data record of the first data source in the first data source.

7. The computer-readable medium of claim 5, wherein the operation further comprises:

loading the data from the first data source from a real-time data source into the data warehouse.

8. The computer-readable medium of claim 7, wherein loading data from the real-time data source comprises replicating the data into the data warehouse.

9. The computer-readable medium of claim 8, wherein replicating the data into the data warehouse comprises:

monitoring the data in the real-time data source; and

if a portion of the data has changed, launching a replication program suitable for copying the portion of the data into the data warehouse.

10. The computer-readable medium of claim 5, wherein the operation further comprises:

loading the data from the second data source from an extracted data source into the data warehouse.

11. The computer-readable medium of claim 10, wherein loading data from the extracted data source comprises extracting the data to the data warehouse.

12. The computer-readable medium of claim 11, wherein extracting the data to the data warehouse comprises:

launching an extraction program suitable for copying the data into the data warehouse.

13. The computer-readable medium of claim 5, wherein the operation further comprises:

loading the data from the first data source from a real-time data source into the data warehouse; and

loading the data from the second data source from an extracted data source into the data warehouse.

14. The computer-readable medium of claim 13, wherein loading data from the real-time data source comprises replicating the data into the data warehouse;

and loading data from the extracted data source comprises extracting the data to the data warehouse.

15. The computer-readable medium of claim 14, wherein replicating the data into the data warehouse comprises:

monitoring the data in the real-time data source; and

if a portion of the data has changed, launching a replication program suitable for copying the portion of the data into the data warehouse; and

wherein extracting the data to the data warehouse comprises;

launching an extraction program suitable for copying the data into the data warehouse.

16. The computer-readable medium of claim 5, wherein the operation further comprises:

replicating the data from the first data source from a real-time data source into the data warehouse;

for each data record in the replicated data;

copying an internal identifier uniquely identifying the data record in the first data source from the data record to the first value in a mapping data record;

copying the external identifier from the data record to the second value in the mapping data record;

extracting the data from the second data source from an extracted data source to the data warehouse;

for each data record in the extracted data;

comparing the external identifier of the data record with the second value in each created mapping data record; and

if the external identifier of the data record matches the second value in a specific mapping data record;

copying the internal identifier from the data record in the extracted data to the third value in the specific mapping data record.

17. The computer-readable medium of claim 5, wherein the operation further comprises:

extracting the data from the second data source from an extracted data source to the data warehouse;

for each data record in the extracted data;

comparing the external identifier of the data record with the second value in each created mapping data record; and

if the external identifier of the data record matches the second value in a specific mapping data record;

copying the internal identifier from the data record in the extracted data to the third value in the specific mapping data record; and

if the external identifier of the data record does not match the second value in a mapping data record;

creating a new mapping data record;

attributing a first value to the new mapping value; and

copying the external identifier from the data record to the second value in the new mapping data record.

18. A computer-readable medium comprising computer-executable instructions for performing, when run on a computer system, an operation of correlating data from at least two different data sources in a data warehouse, the operation comprising:

loading data from a first data source into the data warehouse, the data from the first data source comprising a plurality of first internal identifiers and a plurality of first external identifiers;

creating a warehouse mapping data structure on the basis of the plurality of internal identifiers each mapped to an associated first external identifier;

loading data from a second data source into the data warehouse, the data from the second data source comprising a plurality of second internal identifiers each associated with a second external identifier, wherein at least one of the associated second external identifiers is identical to one of the first external identifiers; and

mapping each second internal identifier associated with a second external identifier that is identical to one of the first external identifiers in the warehouse mapping data structure to the first internal identifier of the identical matching first external identifier, whereby a correlation between data of the first and the second data sources is established.

19. The computer-readable medium of claim 18, wherein loading data from the first data source into the data warehouse comprises loading the data from a real-time data source.

20. The computer-readable medium of claim 19, wherein loading data from the real-time data source comprises replicating the data into the data warehouse.

21. The computer-readable medium of claim 20, wherein replicating the data into the data warehouse comprises:

monitoring the data in the real-time data source; and

if a portion of the data has changed, launching a replication program suitable for copying the portion of the data into the data warehouse.

22. The computer-readable medium of claim 18, wherein loading data from the second data source into the data warehouse comprises loading the data from an extracted data source.

23. The computer-readable medium of claim 22, wherein loading data from the extracted data source comprises extracting the data to the data warehouse.

24. The computer-readable medium of claim 23, wherein extracting the data to the data warehouse comprises:

launching an extraction program suitable for copying the data into the data warehouse.

25. The computer-readable medium of claim 18, wherein loading data from the first data source into the data warehouse comprises loading the data from a real-time data source and wherein loading data from the second data source into the data warehouse comprises loading the data from an extracted data source.

26. The computer-readable medium of claim 25, wherein loading data from the real-time data source comprises replicating the data into the data warehouse and wherein loading data from the extracted data source comprises extracting the data to the data warehouse.

27. The computer-readable medium of claim 26, wherein replicating the data into the data warehouse comprises:

monitoring the data in the real-time data source; and

if a portion of the data has changed, launching a replication program suitable for copying the portion of the data into the data warehouse; and

wherein extracting the data to the data warehouse comprises;

launching an extraction program suitable for copying the data into the data warehouse.

28. A method of correlating at least a first plurality of data records and a second plurality of data records, each data record of the first plurality of data records being uniquely identified within a corresponding data source by an associated internal identifier and each data record of the first and second plurality of data records comprising at least one external identifier, the method comprising:

determining a data record of the first plurality of data records and at least one data record of the second plurality of data records having an identical external identifier; and

mapping the at least one determined data record of the second plurality of data records to the internal identifier associated with the determined data record of the first plurality of data records.

29. A method of creating a data warehouse mapping data structure to correlate at least two different data sources, the method comprising:

creating a plurality of mapping data records in the warehouse mapping data structure, each mapping data record comprising;

a first value representing an internal identifier uniquely identifying the mapping data record in the warehouse mapping data structure;

a second value representing an external identifier of one of a data record of a first data source and a data record of a second data source; and

a third value representing an internal identifier uniquely identifying a data record of the second data source in the second data source, the data record of the second data source having the second value as external identifier;

whereby a correlation between the first and the second data sources is established.

30. A method of correlating data from at least two different data sources in a data warehouse, the method comprising:

loading data from a first data source into the data warehouse, the data from the first data source comprising a plurality of first internal identifiers and a plurality of first external identifiers;

creating a warehouse mapping data structure on the basis of the plurality of internal identifiers each mapped to an associated first external identifier;

loading data from a second data source into the data warehouse, the data from the second data source comprising a plurality of second internal identifiers each associated with a second external identifier, wherein at least one of the associated second external identifiers is identical to one of the first external identifiers; and

mapping each second internal identifier associated with a second external identifier that is identical to one of the first external identifiers in the warehouse mapping data structure to the first internal identifier of the identical matching first external identifier, whereby a correlation between data of the first and the second data sources is established.

31. A mapping data structure residing in storage, the mapping data structure comprising a plurality of data records, each data record comprising:

a first portion comprising a warehouse internal identifier;

a second portion comprising an external identifier common to a first data source and a second data source; and

a third portion comprising an internal identifier of the second data source;

wherein at least one data record of the plurality of data records comprises a warehouse internal identifier representing an internal identifier of the first data source, a common external identifier associated with the internal identifier of the first data source and an internal identifier of the second data source associated with the common external identifier, whereby a correlation between data of the first and the second data sources is established.

32. The mapping data structure of claim 31, wherein at least one data record of the plurality of data records comprises a warehouse internal identifier that does not match any internal identifier of the first data source.

33. A warehouse mapping table residing in storage, comprising:

a plurality of external identifiers common to a first data source and a second data source, a warehouse internal identifier for each of the plurality of external identifiers, and an internal identifier of the second data source for a least a portion of each of the warehouse internal identifiers, whereby data from the first and second data sources is correlated.

34. The warehouse mapping table of claim 33, wherein a warehouse internal identifier exists for each internal identifier of the first data source.

35. The warehouse mapping table of claim 33, wherein at least a portion of the warehouse internal identifiers are determined according to internal identifiers of the first data source.

36. A computer, comprising:

a memory containing at least;

a data warehouse for storing data of a first and a second data source; and

a mapping data structure for correlating the data of the first and second data sources in the data warehouse, the mapping data structure comprising a plurality of data records, each data record in the mapping data structure comprising;

a first portion comprising a warehouse internal identifier;

a second portion comprising an external identifier common to a first data source and a second data source; and

a third portion comprising an internal identifier of the second data source;

wherein at least one data record of the plurality of data records comprises a warehouse internal identifier representing an internal identifier of the first data source, a common external identifier associated with the internal identifier of the first data source and an internal identifier of the second data source associated with the common external identifier; and

a processor adapted to execute contents of the memory.

37. The computer of claim 36, wherein the data warehouse and the mapping data structure are incorporated into a database management system.

38. The computer of claim 36, further comprising:

a network interface unit adapted to establish a network connection to at least one of the first and second data sources; and

a receiving unit adapted to receive data from the first and second data sources.

39. The computer of claim 36, wherein the memory further comprises:

an updating system adapted to update the mapping data structure if changes in the data of the first and second data sources occur.

40. The computer of claim 39, wherein the updating system comprises:

a detecting unit for detecting the changes; and

a triggering unit for performing updates.