Rem
Rem $Header: ai_sh.sql 17-dec-2003.18:55:47 ramkrish Exp $
Rem
Rem ai_sh.sql
Rem
Rem Copyright (c) 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      ai_sh.sql - Sample program for DBMS_DATA_MINING package.
Rem
Rem    DESCRIPTION
Rem      This script demonstrates the use of DBMS_DATA_MINING
Rem      for Attribute Importance function using the MDL Algorithm
Rem      against the SH (Sales History) schema in the RDBMS. 
Rem
Rem    NOTES
Rem     
Rem
Rem    MODIFIED   (MM/DD/YY) 
Rem    ramkrish    10/02/03 - Creation
  
SET serveroutput ON
SET trimspool ON  
SET pages 10000

-----------------------------------------------------------------------
--                            BUILD THE MODEL
-----------------------------------------------------------------------

---------------------------------------------------------
-- CLEANUP BUILD DATA PREPARATION OBJECTS (IF APPLICABLE)
--
BEGIN
  -- Drop the Data Transformation Tables
  EXECUTE IMMEDIATE 'DROP TABLE ai_sh_sample_num';
  EXECUTE IMMEDIATE 'DROP TABLE ai_sh_sample_cat';

  -- Drop the Data Transformation Views for Model BUILD
  EXECUTE IMMEDIATE 'DROP VIEW ai_sh_sample_build_prepared';
  EXECUTE IMMEDIATE 'DROP VIEW ai_sh_sample_build_cat';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/

--------------------------------
-- PREPARE BUILD (TRAINING) DATA
--
-- This step is required only if the data is not prepared/transformed
-- to enable creation of an efficient model using the AI algorithm.
-- In this case, the data for attributes 'occupation' and 'age'
-- requires preparation.
--
BEGIN
  -- Bin categorical attributes: occupation
  DBMS_DATA_MINING_TRANSFORM.CREATE_BIN_CAT (
    bin_table_name => 'ai_sh_sample_cat');        

  DBMS_DATA_MINING_TRANSFORM.INSERT_BIN_CAT_FREQ (
    bin_table_name  => 'ai_sh_sample_cat',
    data_table_name => 'mining_data_build_v',
    bin_num         => 7,
    exclude_list    => DBMS_DATA_MINING_TRANSFORM.COLUMN_LIST (
                       'cust_gender',
                       'cust_marital_status',
                       'country_name',
                       'cust_income_level',
                       'education',
                       'household_size')
  );

  -- Bin numerical attributes: age
  DBMS_DATA_MINING_TRANSFORM.CREATE_BIN_NUM (
    bin_table_name => 'ai_sh_sample_num');
                         
  DBMS_DATA_MINING_TRANSFORM.INSERT_BIN_NUM_EQWIDTH (
    bin_table_name  => 'ai_sh_sample_num',
    data_table_name => 'mining_data_build_v',
    bin_num         => 5,
    exclude_list    => DBMS_DATA_MINING_TRANSFORM.COLUMN_LIST (
                       'cust_id',
                       'yrs_residence',
                       'affinity_card',
                       'bulk_pack_diskettes',
                       'flat_panel_monitor',
                       'home_theater_package',
                       'bookkeeping_application',
                       'printer_supplies',
                       'y_box_games',
                       'os_doc_set_kanji')
  );

  -- Create the transformed view
  -- Execute the first transformation (categorical binning)
  DBMS_DATA_MINING_TRANSFORM.XFORM_BIN_CAT (
    bin_table_name  => 'ai_sh_sample_cat',
    data_table_name => 'mining_data_build_v',
    xform_view_name => 'ai_sh_sample_build_cat');    

  -- Provide the result (ai_sh_sample_build_cat)
  -- to the next transformation (numerical binning)
  DBMS_DATA_MINING_TRANSFORM.XFORM_BIN_NUM (
    bin_table_name  => 'ai_sh_sample_num',
    data_table_name => 'ai_sh_sample_build_cat',
    xform_view_name => 'ai_sh_sample_build_prepared');

  DBMS_OUTPUT.PUT_LINE('BUILD DATA PREPARATION OBJECTS:');
  DBMS_OUTPUT.PUT_LINE('1. Categorical Bin Table: ai_sh_sample_cat');
  DBMS_OUTPUT.PUT_LINE('2. Numerical Bin Table: ai_sh_sample_num');
  DBMS_OUTPUT.PUT_LINE(
    '3. Input (view) to CREATE_MODEL: ai_sh_sample_build_prepared');
END;
/

--------------------------------------------
-- CLEANUP OLD MODEL WITH SAME NAME (IF ANY)
--
BEGIN
  DBMS_DATA_MINING.DROP_MODEL('AI_SH_sample');
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/

---------------------
-- CREATE A NEW MODEL
--
BEGIN
  DBMS_OUTPUT.PUT_LINE('Begin CREATE_MODEL - MDL - Attribute Importance');
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'AI_SH_sample',
    mining_function     => dbms_data_mining.attribute_importance,
    data_table_name     => 'ai_sh_sample_build_prepared',
    case_id_column_name => 'cust_id',
    target_column_name  => 'affinity_card',
    settings_table_name => NULL);
  DBMS_OUTPUT.PUT_LINE('End   CREATE_MODEL - MDL - Attribute Importance');
END;
/

------------------------
-- DISPLAY MODEL DETAILS
--
-- If the build data is prepared (as in this example), then the model
-- contains values of prepared data. The query shown below
-- reverse-transforms the model contents to be as close to the original
-- values as possible, based on the build data preparation objects.
-- Reporting the exact original value is impossible since the input
-- build data has already undergone some transformations (binning/
-- normalization etc.)
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display Remapped MODEL DETAILS'); END;
/
SELECT *
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_AI('AI_SH_sample')) 
ORDER BY RANK;
