More on Performing Principal Component Analysis in Oracle

Wiht Oracle UTL_NLA package, we can call routines to perform PCA on vectors and matrices represented as VARRAYs. How ever, vectors and matrices are stored in VARRAYs with a maximum size of one million entries. Given this restriction, UTL_NLA vectors can be up to one million entries but matrices need to be of size RxC <= 1,000,000.


That's why I have developed my own functions to perform PCA. It is much easier to use and can handle unlimited number of records. Data and operations always stay in the database.

Perform Principal Component Analysis (PCA) Using R, SAS and SQL

Perform Principal Component Analysis (PCA) in R.

R1. Using princomp(m1): by default covariance matrix is used. data is center shifted but not scaled. princomp(m1)$score can be precisely replicated by:
scale(m1,scale=F)%*%princomp(m1)$loading
R2. princomp(d, corr=T): correlation matrix is used. data is center shifted and scaled based on standard deviation. However, standard deviation is based on divisor N not N-1. princomp(m1, cor=TRUE)$score can be precisely replicated by:
((m1-princomp(m1, cor=TRUE)$center)/princomp(m1, cor=TRUE)$scale)%*%princomp(m1, cor=TRUE)$loading

Perform PCA in SAS. By default, correlation matrix is used.
SAS 1. proc princomp data=M1 cov out=m1_pca;
         run;

SAS 2. proc princomp data=M1 out=m1_pca_cor;
         run;

Scores from R1 match scores from  SAS 1.
Scores from R2 roughly match scores from SAS 2. The difference is caused by that in R,  standard deviation, used as scaling facor, is based on divisor N not N-1. In SAS,   the divisor for standard deviation is N-1.

Calculating PCA in a database using SQL is a very interesting way. We can perform PCA  on large data sets.