Real time data warehousing

• US 20060010119A1
• Filed: 09/08/2005
• Published: 01/12/2006
• Est. Priority Date: 12/28/2001
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Jonas, Jeffrey James
• Granted Patent: US 8,615,521 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/20 of structured data, e.g. relational data
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

A method and system for processing data into and in a database and for retrieving the processed data is disclosed. The data comprises identifiers of a plurality of entities. The method and system comprises: (a) processing data into and in a database, (b) enhancing received data prior to storage in a database, (c) determining and matching records based upon relationships between the records in the received data and existing data without any loss of data, (d) enabling alerts based upon user-defined alert rules and relationships, (e) automatically stopping additional matches and separating previously matched records when identifiers used to match records are later determined to be common across entities and not generally distinctive of an entity, (f) receiving data queries for retrieving the processed data stored in the database, (g) utilizing the same algorithm to process the queries and (h) transferring the processed data to another database that uses the same algorithm.

Claims

1. A method for processing data comprising:

receiving data comprising at least one record having at least one identifier, each record representing at least one of a plurality of entities;

utilizing an algorithm to process the received data;

storing the processed data in a database;

receiving data queries for retrieving at least a portion of the data stored in the database; and

utilizing the algorithm to process the queries.

2. The method of claim 1 wherein the entities are one of people, personal property, vehicles, real property, organizations, chemical compounds, organic compounds, proteins, biological structures, biometric values and atomic structures.

3. The method of claim 1 further comprising converting the received data into a standardized message format prior to utilizing the algorithm to process the received data.

4. The method of claim 1 wherein utilizing the algorithm to process the received data includes retaining an attribution of each record.

5. The method of claim 4 wherein retaining the attribution of each record includes retaining an identity of:

a source system providing each record; and

a unique identifier representing each record in the source system.

6. The method of claim 4 wherein retaining the attribution of each record includes retaining an identity of a query system and a particular user.

7. The method of claim 1 wherein utilizing the algorithm to process the received data includes analyzing the received data prior to one of storage in the database and query in the database.

8. The method of claim 7 wherein analyzing the received data prior to one of storage in the database and query in the database includes comparing at least one of the identifiers against one of:

a user-defined criterion; and

at least one data set in one of a secondary database and a list.

9. The method of claim 8 wherein the compared identifier is a name of at least one of the plurality of entities and the data set is in a names root list.

10. The method of claim 8 wherein the compared identifier is an address of at least one of the plurality of entities and the data set is in an address list.

11. The method of claim 8 wherein comparing at least one of the identifiers against a user-defined criterion includes formatting at least one identifier in accordance with the user-defined standard.

12. The method of claim 8 wherein analyzing the received data prior to one of storage in the database or query in the database includes enhancing the received data.

13. The method of claim 12 wherein enhancing the received data includes:

querying at least one data set in one of the secondary database and the list for additional identifiers for the received data; and

supplementing the received data with the additional identifiers.

14. The method of claim 13 wherein querying at least one data set includes:

at least one data set being in the secondary database utilizing the algorithm to query additional databases to locate additional identifiers relating to at least one of the received identifiers; and

supplementing the received data with the additional identifiers located in the secondary database.

15. The method of claim 7 wherein analyzing the received data prior to one of storage in the database and query in the database includes creating hash keys of the identifiers.

16. The method of claim 1 wherein utilizing the algorithm to process received data includes storing in the database processed queries based upon a user-defined criterion.

17. The method of claim 16 wherein the user-defined criterion includes an expiration date.

18. The method of claim 1 performed in real-time.

19. The method of claim 1 performed in batch.

20. The method of claim 1 wherein utilizing the algorithm to process the received data includes:

retrieving from the database a group of additional records having identifiers similar to the identifiers in the received data;

analyzing each identifier of the retrieved group of records for a match to at least a portion of the received data;

matching at least a portion of the received data with at least one analyzed record of the retrieved group of records that is determined to reflect a record having identifiers representing an identical one of the plurality of entities;

analyzing whether at least one identifier is included in the at least a portion of the received data that was not previously stored in the at least one analyzed record of the retrieved group of records that is determined to reflect a record having identifiers representing an identical one of the plurality of entities; and

re-analyzing each identifier of the retrieved group of records for a match to;

at least a portion of the received data; and

the analyzed record of the retrieved group of records that is determined to reflect a record having identifiers representing an identical one of the plurality of entities;

and storing the matched records in the database.

21. The method of claim 20 wherein matching at least a portion of the received data with at least one analyzed record includes assigning a persistent key.

22. The method of claim 20 wherein utilizing the algorithm to process the received data further comprises retrieving from the database an additional group of records having identifiers similar to the identifiers in:

at least a portion of the received data; and

the analyzed record of the retrieved group of records that is determined to reflect a record having identifiers representing an identical one of the plurality of entities prior to re-analyzing each identifier of the retrieved group of records for a match.

23. The method of claim 22 wherein utilizing the algorithm to process the received data includes repeating:

retrieving from the database a group of records;

analyzing each identifier of the retrieved group of records;

matching at least a portion of the received data;

analyzing whether at least one identifier is included in the at least a portion of the received data that was not previously stored;

retrieving from the database an additional group of records; and

re-analyzing each identifier of the retrieved group of records for a match until no additional matches are determined.

24. The method of claim 20 wherein utilizing the algorithm to process the received data includes:

determining whether a particular identifier is one of;

an identifier common across records representing at least two different entities and an identifier generally distinctive of a record representing a particular entity; and

separating records that were previously matched based on a particular identifier if the particular identifier is determined to be an identifier common across records representing at least two different entities and an identifier not generally distinctive of a record representing a particular entity.

25. The method of claim 24 wherein utilizing the algorithm to process the received data includes prohibiting any additional matches of records based on a particular identifier if the particular identifier is determined to be an identifier common across records representing at least two different entities and not an identifier generally distinctive of a record representing a particular entity.

26. The method of claim 24 wherein utilizing the algorithm to process the received data includes re-processing the separated records as received data.

27. The method of claim 24 performed in real-time.

28. The method of claim 24 performed in batch.

29. The method of claim 20 wherein utilizing the algorithm to process the received data includes:

comparing the received data with at least one stored record to determine the existence of a relationship; and

creating a relationship record for each stored record determined to reflect a relationship with at least a portion of the received data.

30. The method of claim 29 wherein utilizing the algorithm to process the received data includes creating at least one confidence indicator for each relationship record.

31. The method of claim 30 performed in real-time.

32. The method of claim 30 performed in batch.

33. The method of claim 30 wherein at least one of the confidence indicators indicates the likelihood of a relationship between:

an entity represented by the particular record having a relationship with the portion of the received data; and

an entity represented by the portion of the received data.

34. The method of claim 30 wherein at least one of the confidence indicators indicates the likelihood that:

an entity represented by the particular record having a relationship with the portion of the received data; and

an entity represented by the portion of the received data are the same.

35. The method of claim 30 wherein utilizing the algorithm to process received data includes analyzing the relationship records to determine whether the relationship records reflect at least one relationship not previously determined.

36. The method of claim 35 wherein analyzing the relationship records includes analyzing relationship records reflecting at least one level of degrees of separation.

37. The method of claim 36 wherein analyzing relationship records reflecting at least one level of degrees of separation includes analyzing relationship records meeting at least one user-defined criterion.

38. The method of claim 37 wherein analyzing relationship records meeting at least one user-defined criterion includes limiting the relationship records analyzed to a maximum level of degrees of separation.

39. The method of claim 37 wherein analyzing relationship records meeting at least one user-defined criterion includes limiting the relationship records analyzed to relationship records that include confidence indicators greater than a minimum amount.

40. The method of claim 35 wherein utilizing the algorithm to process received data further comprises issuing an alert based upon at least one user-defined alert rule.

41. The method of claim 40 wherein issuing the alert based upon at least one user- defined alert rule includes having the alert communicated via electronic communications.

42. The method of claim 41 wherein the electronic communications is in the form of one of an e-mail system, a telephone, a beeper and a personal digital assistant.

43. The method of claim 40 wherein analyzing the relationship records includes:

duplicating the relationship records on at least one secondary database;

distributing received data to the at least one secondary database for analysis based upon a work load criteria; and

issuing the alert meeting the criteria of a user-defined alert rule from the at least one secondary database.

44. The method of claim 1 wherein utilizing the algorithm to process the received data further comprises transferring the stored processed data to at least one secondary database utilizing the algorithm.

45. The method of claim 44 wherein transferring the stored processed data to at least one secondary database is performed in real-time.

46. The method of claim 44 wherein transferring the stored processed data to at least one secondary database is performed in batch.

47. A computer program product comprising a computer usable medium having a computer readable program, wherein the computer readable program when executed on a computer causes the computer to:

receive data comprising at least one record having at least one identifier, each record representing at least one of a plurality of entities;

utilize an algorithm to process the received data;

store the processed data in a database;

receive data queries for retrieving at least a portion of the data stored in the database; and

utilize the algorithm to process the queries.

48. The computer program product of claim 47 wherein the entities are one of people, personal property, vehicles, real property, organizations, chemical compounds, organic compounds, proteins, biological structures, biometric values and atomic structures.

49. The computer program product of claim 47 wherein the computer readable program when executed on a computer further causes the computer to convert the received data into a standardized message format prior to utilizing the algorithm to process the received data.

50. The computer program product of claim 47 wherein utilizing the algorithm to process the received data includes retaining an attribution of each record.

51. The computer program product of claim 50 wherein retaining the attribution of each record includes retaining an identity of:

a source system providing each record and a unique identifier representing record in the source system.

52. The computer program product of claim 51 wherein retaining the attribution of each record includes retaining an identity of a query system and a particular user.

53. The computer program product of claim 47 wherein utilizing the algorithm to process the received data includes analyzing the received data prior to one of storage in the database and query in the database.

54. The computer program product of claim 53 wherein analyzing the received data prior to one of storage in the database and query in the database includes comparing at least one of the identifiers against one of:

a user-defined criterion; and

at least one data set in one of the database and a list.

55. The computer program product of claim 54 wherein the compared identifier is a name of at least one of the plurality of entities and the data set is in a names root list.

56. The computer program product of claim 54 wherein the compared identifier is an address of at least one of the plurality of entities and the data set is in an address list.

57. The computer program product of claim 54 wherein comparing at least one of the identifiers against a user-defined criterion includes formatting at least one identifier in accordance with a user-defined standard.

58. The computer program product of claim 53 wherein analyzing the received data prior to one of storage in the database or query in a database includes enhancing the received data.

59. The computer program product of claim 58 wherein enhancing the received data includes querying at least one data set in one of a database and list for additional identifiers for the received data, and supplementing the received data with the additional identifiers.

60. The computer program product of claim 59 wherein querying at least one data set includes:

at least one data set being in at least one database utilizing the algorithm to query additional databases to locate additional identifiers relating to at least one of the received identifiers; and

supplementing the received data with the additional identifiers located in at least one additional database.

61. The computer program product of claim 53 wherein analyzing the received data prior to one of storage in the database and query in the database includes creating hash keys of the identifiers.

62. The computer program product of claim 47 wherein utilizing the algorithm to process received data includes storing in the database processed queries based upon a user- defined criterion.

63. The computer program product of claim 62 wherein the user-defined criterion includes an expiration date.

64. The computer program product of claim 47 wherein receiving data comprising at least one record having at least one identifier, each record representing at least one of a plurality of entities, utilizing the algorithm to process the received data, and storing the processed data in a database are performed in real-time.

65. The computer program product of claim 47 wherein receiving data comprising at least one record having at least one identifier, each record representing at least one of a plurality of entities, utilizing the algorithm to process the received data, and storing the processed data in a database are performed in batch.

66. The computer program product of claim 47 wherein utilizing the algorithm to process the received data includes:

retrieving from the database a group of additional records having identifiers similar to the identifiers in the received data;

analyzing each identifier of the retrieved group of records for a match to at least a portion of the received data;

matching at least a portion of the received data with at least one analyzed record of the retrieved group of records that is determined to reflect a record having identifiers representing an identical one of the plurality of entities;

analyzing whether at least one identifier is included in the at least a portion of the received data that was not previously stored in the at least one analyzed record of the retrieved group of records that is determined to reflect a record having identifiers representing an identical one of the plurality of entities; and

re-analyzing each identifier of the retrieved group of records for a match to;

at least a portion of the received data and the analyzed record of the retrieved group of records that is determined to reflect a record having identifiers representing an identical one of the plurality of entities; and

storing the matched records in the database.

67. The computer program product of claim 66 wherein matching at least a portion of the received data with at least one analyzed record includes assigning a persistent key.

68. The computer program product of claim 66 wherein utilizing the algorithm to process the received data further comprises retrieving from the database an additional group of records having identifiers similar to the identifiers in:

at least a portion of the received data; and

the analyzed record of the retrieved group of records that is determined to reflect a record having identifiers representing an identical one of the plurality of entities prior to re-analyzing each identifier of the retrieved group of records for a match.

69. The computer program product of claim 68 wherein utilizing the algorithm to process the received data includes repeating:

retrieving from the database a group of records;

analyzing each identifier of the retrieved group of records;

matching at least a portion of the received data;

analyzing whether at least one identifier is included in the at least a portion of the received data that was not previously stored;

retrieving from the database an additional group of records; and

re-analyzing each identifier of the retrieved group of records for a match until no additional matches are determined.

70. The computer program product of claim 66 wherein utilizing the algorithm to process the received data includes:

determining whether a particular identifier is one of;

an identifier common across records representing at least two different entities and an identifier generally distinctive of a record representing a particular entity; and

separating records that were previously matched based on a particular identifier if the particular identifier is determined to be an identifier common across records representing at least two different entities and not an identifier generally distinctive of a record representing a particular entity.

71. The computer program product of claim 70 wherein utilizing the algorithm to process the received data includes prohibiting any additional matches of records based on a particular identifier if the particular identifier is determined to be an identifier common across records representing at least two different entities and not an identifier generally distinctive of a record representing a particular entity.

72. The computer program product of claim 70 wherein utilizing the algorithm to process the received data includes re-processing the separated records as received data.

73. The computer program product of claim 70 wherein determining whether a particular identifier is one of an identifier common across records representing at least two different entities and an identifier generally distinctive of a record representing a particular entity and separating records that were previously matched are performed in real-time.

74. The computer program product of claim 70 wherein determining whether a particular identifier is one of an identifier common across records representing at least two different entities and an identifier generally distinctive of a record representing a particular entity and separating records that were previously matched are performed in batch.

75. The computer program product of claim 66 wherein utilizing the algorithm to process the received data includes:

comparing the received data with at least one stored record to determine the existence of a relationship; and

creating a relationship record for each stored record determined to reflect a relationship with at least a portion of the received data.

76. The computer program product of claim 75 wherein utilizing the algorithm to process the received data includes creating at least one confidence indicator for each relationship record.

77. The computer program product of claim 76 wherein comparing the received data, creating a relationship record, and creating at least one confidence indicator are performed in real-time.

78. The computer program product of claim 76 wherein comparing the received data, creating a relationship record, and creating at least one confidence indicator are performed in batch.

79. The computer program product of claim 76 wherein at least one of the confidence indicators indicates the likelihood of a relationship between:

an entity represented by the particular record having a relationship with the portion of the received data; and

an entity represented by the portion of the received data.

80. The computer program product of claim 76 wherein at least one of the confidence indicators indicates the likelihood that:

an entity represented by the particular record having a relationship with the portion of the received data; and

an entity represented by the portion of the received data are the same.

81. The computer program product of claim 76 wherein utilizing the algorithm to process received data includes analyzing the relationship records to determine whether the relationship records reflect at least one relationship not previously determined.

82. The computer program product of claim 81 wherein analyzing the relationship records includes analyzing relationship records reflecting at least one level of degrees of separation.

83. The computer program product of claim 82 wherein analyzing relationship records reflecting at least one level of degrees of separation includes analyzing relationship records meeting a user-defined criterion.

84. The computer program product of claim 83 wherein analyzing relationship records meeting a user-defined criterion includes limiting the relationship records analyzed to a maximum level of degrees of separation.

85. The computer program product of claim 83 wherein analyzing relationship records meeting a user-defined criterion includes limiting the relationship records analyzed to relationship records that include confidence indicators greater than a minimum amount.

86. The computer program product of claim 81 wherein utilizing the algorithm to process received data further comprises issuing an alert based upon at least one user-defined alert rule.

87. The computer program product of claim 86 wherein issuing the alert based upon at least one user-defined alert rule includes having the alert communicated via electronic communications.

88. The computer program product of claim 87 wherein the electronic communications is in the form of one of an e-mail system, a telephone, a beeper and a- personal digital assistant.

89. The computer program product of claim 86 wherein analyzing the relationship records includes:

duplicating the relationship records on at least one secondary database;

distributing received data to the at least one secondary database for analysis based upon a work load criteria; and

issuing the alert meeting the criteria of a user-defined alert rule from the at least one secondary database.

90. The computer program product of claim 47 wherein utilizing the algorithm to process the received data further comprises transferring the stored processed data to at least one secondary database utilizing the algorithm.

91. The computer program product of claim 90 wherein transferring the stored processed data to at least one secondary database is performed in real-time.

92. The computer program product of claim 90 wherein transferring the stored processed data to at least one secondary database is performed in batch.

93. A method for processing data comprising:

receiving a first data having a first identifier;

utilizing an algorithm to process the first data to form a processed first data record having a processed first identifier;

storing the processed first data record in a database;

receiving a second data having a second identifier;

utilizing the algorithm to process the second data to form a processed second data record having a processed second identifier;

storing the processed second data record in the database;

receiving a third data having a plurality of data identifiers representing an entity;

utilizing the algorithm to process the third data to form a processed third data record having a plurality of processed data identifiers;

determining whether the processed first identifier matches a first one of the plurality of processed data identifiers;

determining whether the processed second identifier matches a second one of the plurality of processed data identifiers; and

matching the first data with the second data if the first processed identifier matches a first one of the plurality of processed data identifiers and the second processed identifier matches a second one of the plurality of processed data identifiers.

94. The method of claim 93 wherein the first data comprises a first data query and the second data comprises a second data query.

95. The method of claim 93 wherein the third data comprises one of a third data query and a third received data.

96. The method of claim 93 further comprising converting the first data, the second data and the third data into a standardized message format prior to utilizing the algorithm.

97. The method of claim 94 including retaining an attribution of each of the-first data query and the second data query.

98. The method of claim 97 wherein retaining the attribution of each of the first data query and the second data query includes retaining an identity of a query system and a particular user.

99. The method of claim 93 including generating a message if the first processed identifier matches a first one of the plurality of processed data identifiers and the second processed identifier matches a s econd one of the plurality of processed data identifiers.

100. The method of claim 99 wherein the message indicates the query system and the particular user of the first data query.

101. The method of claim 93 wherein utilizing the algorithm includes analyzing each of the first data, the second data and the third data prior to storage in the database.

102. The method of claim 101 including enhancing the first data by accessing one or more databases.

103. The method of claim 93 wherein utilizing the algorithm includes storing the processed first data record in the database based upon a user-defined criterion.

104. The method of claim 103 wherein the user-defined criterion includes an expiration date.

105. A computer program product comprising a computer usable medium having a computer readable program, wherein the computer readable program when executed on a computer causes the computer to:

receive a first data having a first identifier;

utilize an algorithm to process the first data to form a processed first data record having a processed first identifier;

store the processed first data record in a database;

receive a second data having a second identifier;

utilize the algorithm to process the second data to form a processed second data record having a processed second identifier;

store the processed second data record in the database;

receive a third data having a plurality of data identifiers representing an entity;

utilize the algorithm to process the third data to form a processed third data record having a plurality of processed data identifiers;

determine whether the processed first identifier matches a first one of the plurality of processed data identifiers;

determine whether the processed second identifier matches a second one of the plurality of processed data identifiers; and

match the first data with the second data if the first processed identifier matches the first one of the plurality of processed data identifiers and the second processed identifier matches the second one of the plurality of processed data identifiers.

106. The computer program product of claim 105 wherein the first data comprises a first data query and the second data comprises a second data query.

107. The computer program product of claim 105 wherein the third data comprises one of a third data query and a third received data.

108. The computer program product of claim 105 wherein the computer readable program when executed causes the computer to convert the first data, the second data and the third data into a standardized message format prior to utilizing the algorithm.

109. The computer program product of claim 108 wherein the computer readable program when executed causes the computer to retain an attribution of each of the first data query and the second data query.

110. The computer program product of claim 109 wherein retaining the attribution of each of the first data query and the second data query includes retaining an identity of a query system and a particular user.

111. The computer program product of claim 105 wherein the computer readable program when executed causes the computer to generate a message if the first processed identifier matches a first one of the plurality of processed data identifiers and the second processed identifier matches a second one of the plurality of processed data identifiers.

112. The computer program product of claim 111 wherein the message indicates the query system and a particular user of the first data query.

113. The computer program product of claim 105 wherein utilizing the algorithm includes analyzing each of the first data, the second data and the third data prior to storage in the database.

114. The computer program product of claim 113 wherein the computer readable program when executed causes the computer to enhance the first data by accessing one or more databases.

115. The computer program product of claim 105 wherein utilizing the algorithm includes storing the processed first data record in the database based upon a user-defined criterion.

116. The method of claim 115 wherein the user-defined criterion includes an expiration date.