Data mining assists in a relational database management system

• US 7,162,464 B1
• Filed: 10/01/1999
• Issued: 01/09/2007
• Est. Priority Date: 10/02/1998
• Status: Expired due to Term

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: NCR Corporation
• Inventors: Miller, Timothy Edward, Brye, Todd Michael, Rollins, Anthony Lowell, Tate, Brian Don, Herman, Miriam H.
• Primary Examiner(s): Knight; Anthony
• Assistant Examiner(s): Holmes; Michael B.
• Application Number:
  US09/806,743
• Time in Patent Office: 2,657 Days
• Field of Search: 706/50, 707/4
• US Class Current: 706/50
• CPC Class Codes: G06F 16/2465 Query processing support for facilitating data mining operations in structured databases
• CPC Class Codes: G06F 2216/03 Data mining

Abstract

A method, apparatus, and article of manufacture for performing data mining applications in a relational database management system (114). Scalable Data Mining Functions (202) are performed directly against a relational database (116) by executing queries dynamically generated by an application programming interface (204) according to requested parameters.

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 by executing Structured Query Language (SQL) statements;

(c) an analytic application programming interface (API), executed by the computer, for generating one or more scalable data mining functions comprised of SQL statements for execution by the relational database management system, wherein the scalable data mining functions identify and interpret patterns in the relational database; and

(d) one or more analytic algorithms, executed by the computer, for interfacing to the analytic API to generate the scalable data mining functions, wherein the scalable data mining functions process data collections stored in the relational database and produce results that are stored in the relational database.

2. The system of claim 1 above, wherein the computer comprises a parallel processing computer comprised of a plurality of nodes, and each node executes one or more threads of the relational database management system to provide parallelism in the data mining operations.

3. The system of claim 1, wherein the scalable data mining functions are created by parameterizing and instantiating the analytic API.

4. The system of claim 1, wherein the scalable data mining functions are dynamically generated SQL statements comprised of combined phrases with substituting values therein based on parameters supplied to the analytic API.

5. The system of claim 1, wherein the scalable data mining functions comprise Data Description functions that provide descriptive statistical functions.

6. The system of claim 5, wherein the Data Description functions comprise:

(1) descriptive statistics for one or more numeric columns, wherein the statistics are selected from a group comprising count, minimum, maximum, mean, standard deviation, standard mean error, variance, coefficient of variance, skewness, kurtosis, uncorrected sum of squares, corrected sum of squares, and quantiles,

(2) a count of values for a column,

(3) a calculated modality for a column,

(4) one or more bin numeric columns of counts with overlay and statistics options,

(5) one or more automatically sub-binned numeric columns giving additional counts and isolated frequently occurring individual values

(6) a computed frequency of one or more column values,

(7) a computed frequency of values for pairs of columns in a column list,

(8) a Pearson Product-Moment Correlation matrix,

(9) a Covariance matrix,

(10) a sum of squares and cross-products matrix, or

(11) a count of overlapping column values in one or more combinations of tables.

7. The system of claim 1, wherein the scalable data mining functions comprise Data Derivation functions that provide column derivations or transformations.

8. The system of claim 7, wherein the Data Derivation functions comprise:

(1) a derived binned numeric column wherein a new column is bin number,

(2) a n-valued categorical column dummy-coded into “

n”

0/1 values,

(3) a n-valued categorical column recoded into n or less new values,

(4) one or more numeric columns scaled via range transformation,

(5) one or more columns scaled to a z-score that is a number of standard deviations from a mean,

(6) one or more numeric columns scaled via a sigmoidal transformation function,

(7) one or more numeric columns scaled via a base 10 logarithm function,

(8) one or more numeric columns scaled via a natural logarithm function,

(9) one or more numeric columns scaled via an exponential function,

(10) one or more numeric columns raised to a specified power,

(11) one or more numeric columns derived via user defined transformation function,

(12) one or more new columns derived by ranking one or more columns or expressions based on order,

(13) one or more new columns derived with quantile 0 to n−

1 based on order and n,

(14) a cumulative sum of a value expression based on a sort expression,

(15) a moving average of a value expression based on a width and order,

(16) a moving sum of a value expression based on a width and order,

(17) a moving difference of a value expression based on a width and order,

(18) a moving linear regression value derived from an expression, width, and order,

(19) a multiple account/product ownership bitmap,

(20) a product ownership bitmap over multiple time periods,

(21) one or more counts, amount, percentage means and intensities derived from a transaction summary,

(22) one or more variabilities derived from transaction summary data,

(23) one or more derived trigonometric values and their inverses, including sin, arcsin, cos, arccos, csc, arccsc, sec, arcsec, tan, arctan, cot, and arccot, or

(24) one or more derived hyperbolic values and their inverses, including sinh arcsinh, cosh, arccosh, csch, arccsch, secth, arcsech, tanh, arctanh, coth, and arecoth.

9. The system of claim 1, wherein the scalable data mining functions comprise Data Reduction functions that provide matrix building operations to reduce the amount of data required for analytic algorithms.

10. The system of claim 9, wherein the Data Reduction functions comprise:

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

(i) a Pearson-Product Moment Correlations matrix;

(ii) a Covariances matrix; and

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

(2) export a resultant matrix, or

(3) restart a matrix operation.

11. The system of claim 1, wherein the scalable data mining functions comprise Data Reorganization functions that provide an ability to reorganize data by joining or de-normalizing pre-processed results into a wide analytic data set.

12. The system of claim 11, wherein the Data Reorganization functions comprise:

(1) create a de-normalized new table by removing one or more key columns, or

(2) join a plurality of tables or views into a combined result table.

13. The system of claim 1, wherein the scalable data mining functions comprise a Data Sampling function that provides an ability to construct a new table containing a randomly selected subset of the rows in an existing table or view.

14. The system of claim 13, wherein the Data Sampling function selects one or more data samples of specified sizes from a table.

15. The system of claim 1, wherein the scalable data mining functions comprise a Data Partitioning function that provides an ability to construct a new table containing at least one randomly selected subset of the rows in an existing table or view, wherein the subsets are mutually distinct but all-inclusive subsets of data.

16. The system of claim 15, wherein the Data Partitioning function selects one or more data partitions from a table using a database internal hashing technique.

17. The system of claim 1, wherein results of the data mining operations are stored in the relational database.

18. The system of claim 1, wherein the relational database management system further comprises an analytical logical data model that stores metadata and processing results from the Scalable Data Mining Functions.

19. A computer-implemented 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 by executing Structured Query Language (SQL) statements on the computer;

(c) executing an analytic application programming interface (API), on the computer, for generating one or more scalable data mining functions comprised of SQL statements for execution by the relational database management system, wherein the scalable data mining functions identify and interpret patterns in the relational database; and

(d) executing one or more analytic algorithms, on the computer, for interfacing to the analytic API to generate the scalable data mining functions, wherein the scalable data mining functions process data collections stored in the relational database and produce results that are stored in the relational database.

21. The method of claim 19 above, wherein the computer comprises a parallel processing computer comprised of a plurality of nodes, and each node executes one or more threads of the relational database management system to provide parallelism in the data mining operations.

22. The method of claim 19, wherein the scalable data mining functions are created by parameterizing and instantiating the analytic API.

23. The method of claim 19, wherein the scalable data mining functions are dynamically generated SQL statements comprised of combined phrases with substituting values therein based on parameters supplied to the analytic API.

24. The method of claim 19, wherein the scalable data mining functions comprise Data Description functions that provide descriptive statistical functions.

25. The method of claim 24, wherein the Data Description functions comprise:

(1) descriptive statistics for one or more numeric columns, wherein the statistics are selected from a group comprising count, minimum, maximum, mean, standard deviation, standard mean error, variance, coefficient of variance, skewness, kurtosis, uncorrected sum of squares, corrected sum of squares, and quantiles,

(2) a count of values for a column,

(3) a calculated modality for a column,

(4) one or more bin numeric columns of counts with overlay and statistics options,

(5) one or more automatically sub-binned numeric columns giving additional counts and isolated frequently occurring individual values

(6) a computed frequency of one or more column values,

(7) a computed frequency of values for pairs of columns in a column list,

(8) a Pearson Product-Moment Correlation matrix,

(9) a Covariance matrix,

(10) a sum of squares and cross-products matrix, or

(11) a count of overlapping column values in one or more combinations of tables.

26. The method of claim 24, wherein the scalable data mining functions comprise Data Derivation functions that provide column derivations or transformations.

27. The method of claim 26, wherein the Data Derivation functions comprise:

(1) a derived binned numeric column wherein a new column is bin number,

(2) a n-valued categorical column dummy-coded into “

n”

0/1 values,

(3) a n-valued categorical column recoded into n or less new values,

(4) one or more numeric columns scaled via range transformation,

(5) one or more columns scaled to a z-score that is a number of standard deviations from a mean,

(6) one or more numeric columns scaled via a sigmoidal transformation function,

(7) one or more numeric columns scaled via a base 10 logarithm function,

(8) one or more numeric columns scaled via a natural logarithm function,

(9) one or more numeric columns scaled via an exponential function,

(10) one or more numeric columns raised to a specified power,

(11) one or more numeric columns derived via user defined transformation function,

(12) one or more new columns derived by ranking one or more columns or expressions based on order,

(13) one or more new columns derived with quantile 0 to n−

1 based on order and n,

(14) a cumulative sum of a value expression based on a sort expression,

(15) a moving average of a value expression based on a width and order,

(16) a moving sum of a value expression based on a width and order,

(17) a moving difference of a value expression based on a width and order,

(18) a moving linear regression value derived from an expression, width, and order,

(19) a multiple account/product ownership bitmap,

(20) a product ownership bitmap over multiple time periods,

(21) one or more counts, amount, percentage means and intensities derived from a transaction summary,

(22) one or more variabilities derived from transaction summary data,

(23) one or more derived trigonometric values and their inverses, including sin, arcsin, cos, arccos, csc, arccsc, sec, arcsec, tan, arctan, cot, and arccot, or

(24) one or more derived hyperbolic values and their inverses, including sinh, arcsinh, cosh, arccosh, csch, arccsch, sech, arcsech, tanh, arctanh, coth, and arccoth.

28. The method of claim 19, wherein the scalable data mining functions comprise Data Reduction functions that provide matrix building operations to reduce the amount of data required for analytic algorithms.

29. The method of claim 28, wherein the Data Reduction functions comprise:

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

(i) a Pearson-Product Moment Correlations matrix;

(ii) a Covariances matrix; and

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

(2) export a resultant matrix, or

(3) restart a matrix operation.

30. The method of claim 19, wherein the scalable data mining functions comprise Data Reorganization functions that provide an ability to reorganize data by joining or de-normalizing pre-processed results into a wide analytic data set.

31. The method of claim 30, wherein the Data Reorganization functions comprise:

(1) create a de-normalized new table by removing one or more key columns, or

(2) join a plurality of tables or views into a combined result table.

32. The method of claim 19, wherein the scalable data mining functions comprise a Data Sampling function that provides an ability to construct a new table containing a randomly selected subset of the rows in an existing table or view.

33. The method of claim 32, wherein the Data Sampling function selects one or more data samples of specified sizes from a table.

34. The method of claim 19, wherein the scalable data mining functions comprise a Data Partitioning function that provides an ability to construct a new table containing at least one randomly selected subset of the rows in an existing table or view, wherein the subsets are mutually distinct but all-inclusive subsets of data.

35. The method of claim 34, wherein the Data Partitioning function selects one or more data partitions from a table using a database internal hashing technique.

36. The method of claim 19, wherein results of the data mining operations are stored in the relational databases.

37. The method of claim 19, wherein the relational database management system further comprises an analytical logical data model that stores metadata and processing results from the Scalable Data Mining Functions.

20. An article of manufacture tangibly embodying logic for performing a computer-implemented 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 by executing Structured Query Language (SQL) statements on the computer;

(c) executing an analytic application programming interface (API), on the computer, for generating one or more scalable data mining functions comprised of SQL statements for execution by the relational database management system, wherein the scalable data mining functions identify and interpret patterns in the relational database; and

(d) executing one or more analytic algorithms, on the computer, for interfacing to the analytic API to generate the scalable data mining functions, wherein the scalable data mining functions process data collections stored in the relational database and produce results that are stored in the relational database.

38. The article of claim 20 above, wherein the computer comprises a parallel processing computer comprised of a plurality of nodes, and each node executes one or more threads of the relational database management system to provide parallelism in the data mining operations.

39. The article of claim 20, wherein the scalable data mining functions are created by parameterizing and instantiating the analytic API.

40. The article of claim 20, wherein the scalable data mining functions are dynamically generated SQL statements comprised of combined phrases with substituting values therein based on parameters supplied to the analytic API.

41. The article of claim 20, wherein the scalable data mining functions comprise Data Description functions that comprise descriptive statistical functions.

42. The article of claim 41, wherein the Data Description functions comprise:

(1) descriptive statistics for one or more numeric columns, wherein the statistics are selected from a group comprising count, minimum, maximum, mean, standard deviation, standard mean error, variance, coefficient of variance, skewness, kurtosis, uncorrected sum of squares, corrected sum of squares, and quantiles,

(2) a count of values for a column,

(3) a calculated modality for a column,

(4) one or more bin numeric columns of counts with overlay and statistics options,

(5) one or more automatically sub-binned numeric columns giving additional counts and isolated frequently occurring individual values

(6) a computed frequency of one or more column values,

(7) a computed frequency of values for pairs of columns in a column list,

(8) a Pearson Product-Moment Correlation matrix,

(9) a Covariance matrix,

(10) a sum of squares and cross-products matrix, or

(11) a count of overlapping column values in one or more combinations of tables.

43. The article of claim 20, wherein the scalable data mining functions comprise Data Derivation functions that provide column derivations or transformations.

44. The article of claim 43, wherein the Data Derivation functions comprise:

(1) a derived binned numeric column wherein a new column is bin number,

(2) a n-valued categorical column dummy-coded into “

n”

0/1 values,

(3) a n-valued categorical column recoded into n or less new values,

(4) one or more numeric columns scaled via range transformation,

(5) one or more columns scaled to a z-score that is a number of standard deviations from a mean,

(6) one or more numeric columns scaled via a sigmoidal transformation function,

(7) one or more numeric columns scaled via a base 10 logarithm function,

(8) one or more numeric columns scaled via a natural logarithm function,

(9) one or more numeric columns scaled via an exponential function,

(10) one or more numeric columns raised to a specified power,

(11) one or more numeric columns derived via user defined transformation function,

(12) one or more new columns derived by ranking one or more columns or expressions based on order,

(13) one or more new columns derived with quantile 0 to n−

1 based on order and n,

(14) a cumulative sum of a value expression based on a sort expression,

(15) a moving average of a value expression based on a width and order,

(16) a moving sum of a value expression based on a width and order,

(17) a moving difference of a value expression based on a width and order,

(18) a moving linear regression value derived from an expression, width, and order,

(19) a multiple account/product ownership bitmap,

(20) a product ownership bitmap over multiple time periods,

(21) one or more counts, amount, percentage means and intensities derived from a transaction summary,

(22) one or more variabilities derived from transaction summary data,

(23) one or more derived trigonometric values and their inverses, including sin, arcsin, cos, arccos, csc, arccsc, sec, arcsec, tan, arctan, cot, and arccot, or

(24) one or more derived hyperbolic values and their inverses, including sin, arcsinh, cosh, arccosh, csch, arccsch, sech, arcsech, tanh, arctanh, coth, and arccoth.

45. The article of claim 20, wherein the scalable data mining functions comprise Data Reduction functions that provide matrix building operations to reduce the amount of data required for analytic algorithms.

46. The article of claim 45, wherein the Data Reduction functions comprise:

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

(i) a Pearson-Product Moment Correlations matrix;

(ii) a Covariances matrix; and

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

(2) export a resultant matrix, and or

(3) restart a matrix operation.

47. The article of claim 20, wherein the scalable data mining functions comprise Data Reorganization functions that provide an ability to reorganize data by joining or de-normalizing pre-processed results into a wide analytic data set.

48. The article of claim 47, wherein the Data Reorganization functions comprise:

(1) create a de-normalized new table by removing one or more key columns, or

(2) join a plurality of tables or views into a combined result table.

49. The article of claim 20, wherein the scalable data mining functions comprise a Data Sampling function that provides an ability to construct a new table containing a randomly selected subset of the rows in an existing table or view.

50. The article of claim 49, wherein the Data Sampling function selects one or more data samples of specified sizes from a table.

51. The article of claim 20, wherein the scalable data mining functions comprise a Data Partitioning function that provides an ability to construct a new table containing at least one randomly selected subset of the rows in an existing table or view, wherein the subsets are mutually distinct but all-inclusive subsets of data.

52. The article of claim 51, wherein the Data Partitioning function selects one or more data partitions from a table using a database internal hashing technique.

53. The article of claim 20, wherein results of the data mining operations are stored in the relational database.

54. The article of claim 20, wherein the relational database management system further comprises an analytical logical data model that stores metadata and processing results from the Scalable Data Mining Functions.