Building statistical models and scoring with UDFs

Multidimensional statistical models are generally computed outside a relational DBMS, exporting data sets. This ar-ticle explains how fundamental multidimensional statistical models are computed inside the DBMS in a single table scan exploiting SQL and User-Dened Functions (UDFs). The techniques described herein are used in a commercial data mining tool, called Teradata Warehouse Miner. Specically, we explain how correlation, linear regression, PCA and clus-tering, are integrated into the Teradata DBMS. Two major database processing tasks are discussed: building a model and scoring a data set based on a model. To build a model two summary matrices are shown to be common and essen-tial for all linear models: the linear sum of points and the quadratic sum of cross-products of points. Since such ma-trices are generally signicantly smaller than the data set, we explain how the remaining matrix operations to build the model can be quickly performed outside the DBMS. We rst explain how to eciently compute summary matrices with plain SQL queries. Then we present two sets of UDFs that work in a single table scan: an aggregate UDF to compute summary matrices and a set of scalar UDFs to score data sets. Experiments compare UDFs and SQL queries (run-ning inside the DBMS) with C++ (running outside on ex-ported les). In general, UDFs are faster than SQL queries and UDFs are more ecient than C++, due to long export times. Statistical models based on the summary matrices can be built outside the DBMS in just a few seconds. Ag-gregate and scalar UDFs scale linearly and require only one table scan, making them ideal to process large data sets