Category: Analytics

Oracle for Data Mining!!!! Thats right I am talking of the same Database company that made waves with acquiring Sun ( and the beloved Java) and has been stealing market share left and right.

Here are some techie specific help- if you know SQL ( or Even Proc SQL) you can learn Oracle Data Mining in less than an hour- good enough to clear that job shortlist.

Check out the attached sample code examples.  They are designed to run on the ODM demo data, but you could change that easily.  They are posted on OTN here

Sample Code Demonstrating Oracle 11.1 Data Mining (230KB)
These files include sample programs in PL/SQL and Java illustrating each of the algorithms supported by Oracle Data Mining 11.1. There are examples of automatic data preparation and data transformations appropriate for each algorithm. Several programs illustrate the text transformation and text mining process.

Oracle Data Mining PL/SQL Sample Programs

The PL/SQL sample programs illustrate each algorithm supported by Oracle Data Mining as well as text transformation and text mining using NMF and SVM classification. Transformations that prepare the data for mining are included in the programs.Execute the PL/SQL sample programs.

Mining Function	Algorithm	Sample Program
Anomaly Detection	One-Class Support Vector Machine	dmsvodem.sql
Association Rules	Apriori	dmardemo.sql
Attribute Importance	Minimum Descriptor Length	dmaidemo.sql
Classification	Adaptive Bayes Network (deprecated)	dmabdemo.sql
Classification	Decision Tree	dmdtdemo.sql
Classification	Decision Tree (cross validation)	dmdtxvlddemo.sql
Classification	Logistic Regression	dmglcdem.sql
Classification	Naive Bayes	dmnbdemo.sql
Classification	Support Vector Machine	dmsvcdem.sql
Clustering	k-Means	dmkmdemo.sql
Clustering	O-Cluster	dmocdemo.sql
Feature Extraction	Non-Negative Matrix Factorization	dmnmdemo.sql
Regression	Linear Regression	dmglrdem.sql
Regression	Support Vector Machine	dmsvrdem.sql
Text Mining	Text transformation using Oracle Text	dmtxtfe.sql
Text Mining	Non-Negative Matrix Factorization	dmtxtnmf.sql
Text Mining	Support Vector Machine (Classification)	dmtxtsvm.sql

And

a particularly cute and nifty example of Fraud ( as in Fraud Detection 😉

drop table CLAIMS_SET;

exec dbms_data_mining.drop_model(‘CLAIMSMODEL’);

create table CLAIMS_SET (setting_name varchar2(30), setting_value varchar2(4000));

insert into CLAIMS_SET values (‘ALGO_NAME’,’ALGO_SUPPORT_VECTOR_MACHINES’);

insert into CLAIMS_SET values (‘PREP_AUTO’,’ON’);

commit;

begin

dbms_data_mining.create_model(‘CLAIMSMODEL’, ‘CLASSIFICATION’,

‘CLAIMS’, ‘POLICYNUMBER’, null, ‘CLAIMS_SET’);

end;

/

— accuracy (per-class and overall)

col actual format a6

select actual, round(corr*100/total,2) percent, corr, total-corr incorr, total from

(select actual, sum(decode(actual,predicted,1,0)) corr, count(*) total from

(select CLAIMS actual, prediction(CLAIMSMODEL using *) predicted

from CLAIMS_APPLY)

group by rollup(actual));

— top 5 most suspicious claims where the number of previous claims is 2 or more:

select * from

(select POLICYNUMBER, round(prob_fraud*100,2) percent_fraud,

rank() over (order by prob_fraud desc) rnk from

(select POLICYNUMBER, prediction_probability(CLAIMSMODEL, ‘0’ using *) prob_fraud

from CLAIMS_APPLY

where PASTNUMBEROFCLAIMS in (‘2 to 4’, ‘more than 4’)

where rnk <= 5

order by percent_fraud desc;

Coming up- a series of tutorials on learning the skills by just sitting in your home.

Hat Tip- Karl Rexer , Rexer Analytics and Charlie Berger, Oracle.