SQL-based data reduction techniques for delivering data to analytic tools

• US 6,421,665 B1
• Filed: 10/01/1999
• Issued: 07/16/2002
• Est. Priority Date: 10/02/1998
• Status: Expired due to Term

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: NCR Corporation
• Inventors: Brye, Todd Michael, Rollins, Anthony Lowell, Tate, Brian Don
• Primary Examiner(s): Vu, Kim
• Assistant Examiner(s): TO, BAOQUOC N
• Application Number:
  US09/411,809
• Time in Patent Office: 1,019 Days
• Field of Search: 707/3, 707/2, 707/102, 707/4, 707/100, 707/200, 705/10, 705/7, 705/34, 705/35, 705/37
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries
• CPC Class Codes: G06F 16/2465 Query processing support for facilitating data mining operations in structured databases
• CPC Class Codes: G06F 16/3346 using probabilistic model
• CPC Class Codes: G06F 2216/03 Data mining
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/961 Associative
• 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/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99936 Pattern matching access
• 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 executed by a massively parallel processing (MPP) computer system. An analytic application programming interface (API), executed by the computer, generates a data reduction function performed directly within the relational database management system. The data reduction function builds at least one matrix within the relational database management system from data stored in the relational database.

Claims

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

(a) a computer having one or more data storage devices connected thereto, wherein a relational database is stored on one or more of the data storage devices;

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

(c) an analytic application programming interface (API), executed by the computer, that generates a data reduction function performed directly within the relational database management system, wherein the data reduction function comprises a dynamically generated query executed by the relational database management system that is comprised of combined phrases with substituting values therein based on parameters supplied to the analytic API, and the data reduction function builds at least one matrix within the relational database management system from data stored in the relational database.

2. The system of claim 1, wherein the computer system is a massively parallel processing (MPP) computer system, and the data reduction function is decomposed into a plurality of steps that are executed concurrently in parallel by the relational database management system.

3. The system of claim 1, wherein the data reduction function processes data stored in the relational database and produce results that are stored in the relational database.

4. The system of claim 1, wherein the data reduction function is created by parameterizing and instantiating the analytic API.

5. The system of claim 1, wherein the data reduction function is selected from a group comprising:

(1) a data reduction function to build one or more data reduction matrices from a group comprising;

(i) a Pearson-Product Moment Correlations matrix;

(ii) a Covariance matrix; and

(iii) a Sum of Squares and Cross Products (SSCP) matrix, and

(2) a data reduction function to export a resultant matrix.

6. The system of claim 1, wherein the data reduction function delivers the matrix to a statistical software program in order to perform multivariate statistical analysis.

7. The system of claim 1, wherein the data reduction function builds a data reduction matrix directly in the relational database management system using SQL statements.

8. The system of claim 1, wherein one or more numeric columns in at least one table in the relational database are reduced to an n-by-n matrix by the data reduction function, when there are n-columns in the table.

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

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

(b) accessing the relational database stored on the data storage devices using a relational database management system; and

(c) invoking an analytic application programing interface (API) in the computer, wherein the analytic API generates a data reduction function performed directly within the relational database management system, the data reduction function comprises a dynamically generated query executed by the relational database management system that is comprised of combined phrases with substituting values therein based on parameters supplied to the analytic API, and the data reduction function builds at least one matrix within the relational database management system from data stored in the relational database.

11. The method of claim 9, wherein the computer system is a massively parallel processing (MPP) computer system, and the method further comprises decomposing the data reduction function into a plurality of steps that are executed concurrently in parallel by the relational database management system.

12. The method of claim 9, wherein the data reduction function processes data stored in the relational database and produce results that are stored in the relational database.

13. The method of claim 9, wherein the data reduction function is created by parameterizing and instantiating the analytic API.

14. The method of claim 9, wherein the data reduction function is selected from a group comprising:

(1) a data reduction function to build one or more data reduction matrices from a group comprising;

(i) a Pearson-Product Moment Correlations matrix;

(ii) a Covariance matrix; and

iii) a Sum of Squares and Cross Products (SSCP) matrix, and

(2) a data reduction function to export a resultant matrix.

15. The method of claim 9, wherein the data reduction function delivers the matrix to a statistical software program in order to perform multivariate statistical analysis.

16. The method of claim 9, wherein the data reduction function builds a data reduction matrix directly in the relational database management system using SQL statements.

17. The method of claim 9, wherein one or more numeric columns in at least one table in the relational database are reduced to an n-by-n matrix by the data reduction function, when there are n-columns in the table.

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

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

b) accessing the relational database stored on the data storage devices using a relational database management system; and

(c) invoking an analytic application programming interface (API) in the computer, wherein the analytic API generates a data reduction function performed directly within the relational database management system, the data reduction function comprises a dynamically generated query executed by the relational database management system that is comprised of combined phrases with substituting values therein based on parameters supplied to the analytic API, and the data reduction function builds at least one matrix within the relational database management system from data stored in the relational database.

18. The article of manufacture of claim 10, wherein the computer system is a massively parallel processing (MPP) computer system, and the method further comprises decomposing the data reduction function into a plurality of steps that are executed concurrently in parallel by the relational database management system.

19. The article of manufacture of claim 10, wherein the data reduction function processes data stored in the relational database and produce results that are stored in the relational database.

20. The article of manufacture of claim 10, wherein the data reduction function is created by parameterizing and instantiating the analytic API.

21. The article of manufacture of claim 10, wherein the data reduction function is selected from a group comprising:

(1) a data reduction function to build one or more data reduction matrices from a group comprising;

(i) a Pearson-Product Moment Correlations matrix;

(ii) a Covariance matrix; and

(iii) a Sum of Squares and Cross Products (SSCP) matrix, and

(2) a data reduction function to export a resultant matrix.

22. The article of manufacture of claim 10, wherein the data reduction function delivers the matrix to a statistical software program in order to perform multivariate statistical analysis.

23. The article of manufacture of claim 10, wherein the data reduction function builds a data reduction matrix directly in the relational database management system using SQL statements.

24. The article of manufacture of claim 10, wherein one or more numeric columns in at least one table in the relational database are reduced to an n-by-n matrix by the data reduction function, when there are n-columns in the table.