SQL-based analytic algorithm for clustering

• US 6,772,166 B1
• Filed: 10/01/1999
• Issued: 08/03/2004
• Est. Priority Date: 10/02/1998
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: NCR Corporation
• Inventors: Hildreth, James Dean
• Primary Examiner(s): PARDO, THUY N
• Application Number:
  US09/410,531
• Time in Patent Office: 1,768 Days
• Field of Search: 707/2, 707/101, 707/7, 707/3, 707/102, 707/10, 707/204, 707/100, 707/103, 707/104, 395/20
• US Class Current: 1/1
• CPC Class Codes: G06F 16/285 Clustering or classification
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99937 Sorting
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method, apparatus, and article of manufacture for performing data mining applications in a relational database management system. An analytic algorithm for clustering is performed by the computer. The analytic algorithm for clustering includes SQL statements performed by the relational database management system for reducing data retrieved from the relational database in bulk, and programmatic iteration for operating on the reduced data to find clusters therein. The analytic algorithm for clustering creates at least one analytic model within the analytic logical data model from data residing in the relational database.

Claims

1. A system for performing data mining applications, comprising:

(a) a computer having one or more data storage devices connected thereto;

(b) a relational database management system, executed by the computer, for managing a relational database stored on the data storage devices; and

(c) an analytic algorithm for clustering performed by the computer, wherein the analytic algorithm for clustering includes SQL statements performed by the relational database management system for reducing data retrieved from the relational database in bulk by reducing the number of columns or rows in the data, the analytic algorithm for clustering includes programmatic iteration for operating on the reduced data to find clusters therein, and the analytic algorithm for clustering creates at least one analytic model within an analytic logical data model from the reduced data.

2. The system of claim 1, wherein the computer is a massively parallel processing (MPP) computer system, and the analytic algorithm for clustering is executed concurrently in parallel on the massively parallel processing (MPP) computer system.

3. The system of claim 1, wherein the analytic algorithm for clustering uses a category utility (CU) function for its analysis.

4. The system of claim 1, wherein the analytic algorithm for clustering is implemented as a combination of SQL statements performed by the relational database management system and the programmatic iteration is performed by an application program.

5. The system of claim 4, wherein the application program comprises a Data Reduction Utility Program.

6. The system of claim 4, wherein the relational database management system executes the SQL statements to reduce data from the relational database and the application program performs the programmatic iteration to find clusters therein.

7. The system of claim 1, wherein the SQL statements preprocess the data in the relational database to reduce fine numerical details therein to create the reduced data.

8. The system of claim 7, wherein the fine numerical details are reduced by assigning them to ranges.

9. The system of claim 7, wherein the fine numerical details are reduced by assigning them to bins.

10. The system of claim 7, wherein the fine numerical details are reduced by correlating their values.

11. The system of claim 7, wherein the fine numerical details are reduced by determining their covariances.

12. The system of claim 7, wherein the fine numerical details are reduced by scaling.

13. A method for performing data mining applications, comprising:

(a) managing a relational database stored on one or more data storage devices connected to a computer; and

(b) performing an analytic algorithm for clustering in the computer, wherein the analytic algorithm for clustering includes SQL statements performed by the relational database management system for reducing data retrieved from the relational database in bulk by reducing the number of columns or rows in the data, the analytic algorithm for clustering includes programmatic iteration for operating on the reduced data to find clusters therein, and the analytic algorithm for clustering creates at least one analytic model within an analytic logical data model from the reduced data.

14. The method of claim 13, wherein the computer is a massively parallel processing (MPP) computer system, and the performing step further comprises executing the analytic algorithm for clustering concurrently in parallel on the massively parallel processing (MPP) computer systen.

15. The method of claim 13, wherein the analytic algorithm for clustering uses a category utility (CU) function for its analysis.

16. The method of claim 13, wherein the analytic algorithm for clustering is implemented as a combination of SQL statements performed by the relational database management system and the programmatic iteration is performed by an application prograrm.

17. The method of claim 16, wherein the application program comprises a Data Reduction Utility Program.

18. The method of claim 16, wherein the relational database management system executes the SQL statements to reduce data from the relational database and the application program performs the programmatic iteration to find clusters therein.

19. The method of claim 13, wherein the SQL statements preprocesse the data in the relational database to reduce fine numerical details therein to create the reduced data.

20. The method of claim 19, wherein the fine numerical details are reduced by assigning them to ranges.

21. The method of claim 19, wherein the fine numerical details are reduced by assigning them to bins.

22. The method of claim 19, wherein the fine numerical details are reduced by correlating their values.

23. The method of claim 19, wherein the fine numerical details are reduced by determining their covariances.

24. The method of claim 19, wherein the fine numerical details are reduced by scaling.

25. An article of manufacture comprising logic embodying a method for performing data mining applications, comprising:

(a) managing a relational database stored on one or more data storage devices connected to a computer; and

(b) performing an analytic algorithm for clustering in the computer, wherein the analytic algorithm for clustering includes SQL statements performed by the relational database management system for reducing data retrieved from the relational database in bulk by reducing the number of columns or rows in the data, the analytic algorithm for clustering includes programmatic iteration for operating on the reduced data to find clusters therein, and the analytic algorithm for clustering creates at least one analytic model within an analytic logical data model from the reduced data.

26. The article of manufacture of claim 25, wherein the computer is a massively parallel processing (MPP) computer system, and the performing step further comprises executing the analytic algorithm for clustering concurrently in parallel on the massively parallel processing (MPP) computer system.

27. The article of manufacture of claim 25, wherein the analytic algorithm for clustering uses a category utility (CU) function for its analysis.

28. The article of manufacture of claim 25, wherein the analytic algorithm for clustering is implemented as a combination of SQL statements performed by the relational database management system and the programmatic iteration is performed by an application program.

29. The article of manufacture of claim 28, wherein the application program comprises a Data Reduction Utility Program.

30. The article of manufacture of claim 28, wherein the relational database management system executes the SQL statements to reduce data from the relational database and the application program performs the programmatic iteration to find clusters therein.

31. The article of manufacture of claim 25, wherein the SQL statements preprocesse the data in the relational database to reduce fine numerical details therein to create the reduced data.

32. The article of manufacture of claim 31, wherein the fine numerical details are reduced by assigning them to ranges.

33. The article of manufacture of claim 31, wherein the fine numerical details are reduced by assigning them to bins.

34. The article of manufacture of claim 31, wherein the fine numerical details are reduced by correlating their values.

35. The article of manufacture of claim 31, wherein the fine numerical details are reduced by determining their covariances.

36. The article of manufacture of claim 31, wherein the fine numerical details are reduced by scaling.