ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Provides routines for predictive analytics operations useful in data mining
AUTHID
CURRENT_USER
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;
The package exposes APIs which are leveraged by the Oracle Data Mining option.
Users who wish to invoke procedures in this package require the CREATE MINING MODEL system privilege (as well as the CREATE TABLE and CREATE VIEW system privilege).
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@pdbdev
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@pdbdev
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;
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@pdbdev
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;
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);
