Provides routines for predictive analytics operations
useful in data mining
Source
{ORACLE_HOME}/rdbms/admin/dbmsdmpa.sql
Data Mining Option Installation
Check
col parameter format
a30
col value format a30
SELECT parameter, value
FROM gv$option
WHERE parameter = 'Data Mining';
col comp_name format a35
SELECT comp_name, version, status
FROM dba_registry
ORDER BY 1;
Dependencies
DBMS_ASSET
DBMS_RANDOM
DM_QGEN
DBMS_DATA_MINING
DBMS_SQL
ODM_MODEL_UTIL
DBMS_DATA_MINING_INTERNAL
DBMS_SYS_ERROR
PLITBLM
DBMS_DATA_MINING_TRANSFORM
DMP_SEC
SYS
DBMS_JDM_INTERNAL
DMP_SYS
Security
execute is granted to PUBLIC
EXPLAIN
Used for identifying attributes that are important/useful for
explaining the variation on an attribute of interest (e.g., a measure of an OLAP
fact table). Only known cases (i.e. cases where the value of the explain column is
not null) will be taken into consideration when assessing the importance of the input
attributes upon the dependent attribute. The resulting table will contain one row for each of the input attributes.
dbms_predictive_analytics.explain(
data_table_name IN VARCHAR2,
explain_column_name IN VARCHAR2,
result_table_name IN VARCHAR2,
data_schema_name IN VARCHAR2 DEFAULT NULL);
--
session 1
conn sh/sh
CREATE VIEW sales_view AS
SELECT country_name country, prod_name prod, calendar_year year,
SUM(amount_sold) sale, COUNT(amount_sold) cnt
FROM sales, times, customers, countries, products
WHERE sales.time_id = times.time_id AND
sales.prod_id = products.prod_id
AND sales.cust_id = customers.cust_id
AND customers.country_id = countries.country_id
GROUP BY country_name, prod_name, calendar_year;
SELECT view_name
FROM user_views;
-- session 2
conn sh/sh
CREATE SEQUENCE seq;
CREATE TABLE pa_demo AS
SELECT seq.NEXTVAL ID, country, prod, year, sale, cnt
FROM sales_view;
UPDATE pa_demo
SET country = 'US'
WHERE country = 'United States of America';
COMMIT;
set linesize 121
col country format a20
SELECT *
FROM pa_demo;
BEGIN
dbms_predictive_analytics.explain ('PA_DEMO','COUNTRY','PA_RESULTS');
END;
/
-- session 1: while session 2 is running
SELECT view_name
FROM user_views;
-- the actual view names will be different
desc DM$TCAS_BLWUXAMINMARTTNMWABYPH
desc DM$TPBV_ZQIVNCNBKYGLMXHYJSVUSD
-- session 2: continue
desc pa_results
set linesize 121
col attribute_name format a30
col attribute_subname format a20
SELECT *
FROM pa_results;
drop table pa_results purge;
BEGIN
dbms_predictive_analytics.explain('PA_DEMO',
'PROD', 'PA_RESULTS');
END;
/
SELECT *
FROM pa_results;
drop table pa_results purge;
BEGIN
dbms_predictive_analytics.explain ('PA_DEMO',
'YEAR', 'PA_RESULTS');
END;
/
SELECT *
FROM pa_results;
drop table pa_results purge;
BEGIN
dbms_predictive_analytics.explain ('PA_DEMO','SALE','PA_RESULTS');
END;
/
SELECT *
FROM pa_results;
drop table pa_results purge;
BEGIN
dbms_predictive_analytics.explain ('PA_DEMO','CNT','PA_RESULTS');
END;
/
SELECT *
FROM pa_results;
-- session 1: when procedure run is completed
SELECT view_name
FROM user_views;
desc pa_results
SELECT *
FROM pa_results;
-- the higher the value the more important the
attribute is to
-- whether any specific record in SALES is related to the sale.
-- another example
SELECT promo_id, COUNT(DISTINCT TO_CHAR(time_id, 'DDD'))
FROM sales
GROUP BY promo_id;
BEGIN
dbms_predictive_analytics.explain ('SALES',
'PROMO_ID', 'RTAB');
END;
/
SELECT promo_id, COUNT(DISTINCT TO_CHAR(time_id, 'DDD'))
FROM sales
GROUP BY promo_id;
SELECT DISTINCT promo_id FROM sales ORDER BY 1;
SELECT promo_id, COUNT(DISTINCT TO_CHAR(time_id, 'DDD'))
FROM sales
GROUP BY promo_id;
SELECT DISTINCT TO_CHAR(time_id, 'DDD')
FROM sales
WHERE promo_id = 33
INTERSECT
SELECT DISTINCT TO_CHAR(time_id, 'DDD')
FROM sales
WHERE promo_id = 350;
SELECT DISTINCT TO_CHAR(time_id, 'DDD')
FROM sales
WHERE promo_id = 33
INTERSECT
SELECT DISTINCT TO_CHAR(time_id, 'DDD')
FROM sales
WHERE promo_id = 351;
PREDICT
Produces predictions for unknown targets.
The input data table should contain records where the target value is known (not null).
The known cases will be used to train and test a model. Any cases where the target
is unknown, i.e. where the target value is null, will not be considered during model training. Once
a mining model is built internally, it will be used to score
the records from the input data (both known and unknown), and a table will be persisted
containing the results. In the case of binary classification, an ROC analysis of the
results will be performed, and the predictions will be adjusted to support the optimal probability threshold
resulting in the highest True Positive Rate (TPR) versus False Positive Rate (FPR).
dbms_predictive_analytics.predict(
accuracy OUT NUMBER,
data_table_name IN VARCHAR2,
case_id_column_name IN VARCHAR2,
target_column_name IN VARCHAR2,
result_table_name IN VARCHAR2,
data_schema_name IN VARCHAR2 DEFAULT NULL);
conn sh/sh
set serveroutput on
CREATE SEQUENCE seq;
CREATE TABLE pa_demo AS
SELECT seq.NEXTVAL ID, country, prod, year, sale, cnt
FROM sales_view;
UPDATE pa_demo
SET country = 'US'
WHERE country = 'United States of America';
COMMIT;
SELECT prediction, COUNT(*)
FROM pa_results
GROUP BY prediction
ORDER BY 2;
DROP TABLE pa_results PURGE;
PROFILE
Segment data based on some target attribute and
value. Creates profiles or rules for records where the specific attribute and value exist, in some sense it can be seen directed or
supervised segmentation.
dbms_predictive_analytics.profile(
data_table_name IN VARCHAR2,
target_column_name IN VARCHAR2,
result_table_name IN VARCHAR2,
data_schema_name IN VARCHAR2 DEFAULT NULL);
Free