This discussion is archived
3 Replies Latest reply: Jan 24, 2013 2:41 PM by Mark Kelly RSS

Missing Attribute in ODM Dictionary (all_mining_model_attributes)

983652 Newbie
Currently Being Moderated
I have problem with ODM Dictionary (all_mining_model_attributes)

When build classification model, I use 14 predictor attributes, 1 target attribute and 1 case id attribute, Total 16 attributes
after build the model, I checked in ODM Dictionary (all_mining_model_attributes)

select * from all_mining_model_attributes where model_name='My_model_name';

The result only show 13 attributes, 3 attributes missing is 2 predictor attributes (OFFICE_CODE, OBJECT_MODEL) and 1 case id attribute
I use this 2 predictor attributes (OFFICE_CODE, OBJECT_MODEL) as parameter because using ATTRIBUTE IMPORTANCE, importance ranking for this attributes is number 1 and 4

OFFICE_CODE is categorical attribute with 156 distinct value and OBJECT_MODEL is categorical attribute with 209 distinct value

Then I'm doing some experiment:
1. I try to filter OFFICE_CODE only 2 disctint value to build the model, I checked in ODM Dictionary, OFFICE_CODE was exist
So I think ODM have problem with categorical with distinct value > 150
2. I try to build model without OFFICE_CODE and OBJECT_MODEL, the result of model (predictive confidence and AUC) same with if I include OFFICE_CODE and OBJECT_MODEL
So I think ODM realy not used OFFICE_CODE and OBJECT_MODEL as parameter, but Why? is there any documentation of it?

We need to include OFFICE_CODE and OBJ_MODEL to build the model, because we believe of Oracle Attribute Importance to increase our Classification Model

Anyone?
  • 1. Re: Missing Attribute in ODM Dictionary (all_mining_model_attributes)
    Mark Kelly Oracle ACE
    Currently Being Moderated
    Hi,
    It is hard to say what is happening in your case.
    First some questions:
    1) What version of the db are you using?
    2) Are you doing this through the api directly writing pl/sql code or are you using Data Miner.
    If you are using Data Miner, what version are you using?
    3)What Classification algorithms are you having this problem with?
    Have you tried all of them for the same case data?


    ODM has a Automatic Data Preparation feature that will address any required binning for algorithms.
    So it may be that you are turning this off.
    I know the old sample code originally published for ODM 10.2 does not even demonstrate using the ADP setting.
    Without the ADP set on, the user then has the responsibility of preparing the data to suite each algorithm's need.
    So that can get into binning,normalization etc.
    There should be new sample code availble in the ODM 12.1 release.
    As you probably know, but I just wanted to make it clear, Attribute Importance does provide its own attribute selection profile.
    But each algorithm also has their own algorithm selection process built in as well.
    So it may or may not be a good idea to use AI up front to reduce the set of attributes.
    You could just let the algorithms make that determination on their own.

    The models all_mining_model_attributes results indicate what the model "kept" as part of its scoring signature.
    So if it is not present there, then it removed it due to lack of significance.

    Thanks,Mark
  • 2. Re: Missing Attribute in ODM Dictionary (all_mining_model_attributes)
    983652 Newbie
    Currently Being Moderated
    Thanks for fast response Mark,

    1. My DB Version Oracle 11gR2 11.2.0.3
    2. Using SqlDeveloper Version 3.2.09
    3. All Classification algorithms (default sqldeveloper settings): Decision Tree, SVM, Naive Bayes and GLM.

    I already using all algorithms to my dataset.
    I already try to turn off and turn on Automatic Data Preparation, the result was same, OFFICE_CODE and OBJ_MODEL still not exist.
    Total Records my dataset : 1521694 records (760847 for class A, and 760847 for class B), only have 2 class target for classification

    I already use sqldeveloper new version (3.2.20.09.87), and get the same result.

    FYI,
    Attribute OFFICE_CODE -> varchar2(5)
    Attribute OBJ_MODEL -> varchar2(15)
    I use this 2 predictor attributes (OFFICE_CODE, OBJECT_MODEL) as parameter because using ATTRIBUTE IMPORTANCE, importance ranking for this attributes is number 1 and 4

    Is there any limitation to content of predictor attributes, example only limit to < 100 distinct value of categorical attribute contents?
    I'm looking for reasons why my attributes predictor not shown in ALL_MINING_MODEL_ATTRIBUTES?

    in Attributes Importance, there is value of importance so we can accept it as reason that attributes is importance or not?
    is there any value like this so I can take it as reason my attributes not show in all_data in ALL_MINING_MODEL_ATTRIBUTES?

    My UPDATED:
    I already use PL-SQL to create model

    CREATE TABLE my_settings(
    setting_name VARCHAR2(30),
    setting_value VARCHAR2(4000));

    BEGIN
    INSERT INTO settings_tbl VALUES
    (dbms_data_mining.algo_name,
    dbms_data_mining.algo_generalized_linear_model);
    COMMIT;
    END;

    BEGIN
    DBMS_DATA_MINING.CREATE_MODEL(
    model_name => 'MY_MODEL_CLASS',
    mining_function => dbms_data_mining.classification,
    data_table_name => 'MY_DATASET',
    case_id_column_name => 'CUST_ID',
    target_column_name => 'FLAG_TARGET',
    settings_table_name => 'settings_tbl');
    END;

    The Result, OFFICE_CODE now exist in all_mining_model_attributes ( I already test using GLM and SVM algorithms)
    I Think the problem in Oracle Data Miner (using SqlDeveloper)., because attribute OFFICE_CODE not shown in all_mining_model_attributes

    Next problem, I need information classification model performances, such as predictive confidence, confusion matrix, ROC as simple as in Oracle Data Miner.


    Thanks

    -Sbasuki-

    Edited by: RELIXER on Jan 13, 2013 11:15 PM

    Edited by: RELIXER on Jan 13, 2013 11:59 PM
  • 3. Re: Missing Attribute in ODM Dictionary (all_mining_model_attributes)
    Mark Kelly Oracle ACE
    Currently Being Moderated
    Hi,
    Your PL/SQL example is not the same as what is done via Data Miner. For starters, it is not setting ADP.
    Also there is no performance settings (use of weights) in build, to insure that the average accuracy is increased.
    Unfortunately ODMr does not currently generate all the code necessary to recreate all of the objects (models, test results) you are interested in .
    This feature will be available in the next release of SQL Dev.'
    So it is not a Data Miner bug, but just a apples to oranges comparison.

    As for the model not including the columns OFFICE_CODE and OBJ_MODEL:
    Each algorithm has their own feature selection algorithm, so it is not going to replicate what the Attribute Importance produces.
    As long as you insure that you are setting the data as input in ODMr, then it is being passed to the model.
    In particular, setting ADP on is important, as that insures better model building experience then if you take it on yourself.
    The model will decide at that point whether to keep it or not.
    The attributes that are kept, can then be viewed via the model viewer, to see what level of importance each has.
    You can only determine from the removal of the attribute that it did not past the model's test for usefulness.

    ODM will have a new option on GLM in 12.1 to "force include" an attribute, whether the model thinks it is good or not. That might be useful to you for binary class models using GLM or regression models.

    Ultimately, it comes down to how predictive and accurate the model is. If it does a better job without attributes that you thought might be useful, then it makes that decision.

    I pasted in part of what ODMr will be generating to build a DT model below.
    It will at least give you a bit more insight on how to create these objects on your own if wish.
    Thanks, Mark

    EXECUTE dbms_output.put_line('');
    EXECUTE dbms_output.put_line('Class Build node started: ' || SYSTIMESTAMP);
    EXECUTE dbms_output.put_line('');
    
    DECLARE
      v_caseid            VARCHAR2(30);
      v_caseid_alias      VARCHAR2(30);
      v_target            VARCHAR2(30);
      v_input_data        VARCHAR2(30);
      v_build_data        VARCHAR2(30);
      v_test_data         VARCHAR2(30);
      v_apply_data        VARCHAR2(30);
      v_data_usage        VARCHAR2(30);
      v_weights_setting   VARCHAR2(30);
      v_build_setting     VARCHAR2(30);
      v_cost_setting      VARCHAR2(30);
      v_test_metric       VARCHAR2(30);
      v_confusion_matrix  VARCHAR2(30);
      v_lift              VARCHAR2(30);
      v_roc               VARCHAR2(30);
      v_lexer_name        VARCHAR2(30);
      v_stoplist_name     VARCHAR2(30);
      v_policy_name       VARCHAR2(30);
      v_row_diag_table    VARCHAR2(30);
      v_accuracy          NUMBER;
      v_avg_accuracy      NUMBER;
      v_predictive_conf   NUMBER;
      v_tot_cost          NUMBER;
      v_area_under_curve  NUMBER;
      v_num_row_alias     VARCHAR2(30);
      TYPE ODMR_OBJECT_VALUES is TABLE OF VARCHAR2(4000);
      v_targets           ODMR_OBJECT_VALUES;
      v_xlst              dbms_data_mining_transform.TRANSFORM_LIST;
      v_sql               CLOB;
      v_user_session      VARCHAR2(30) := SYS_CONTEXT ('USERENV', 'SESSION_USER');
      v_drop              VARCHAR2(30) := '&DROP_MODELS';
      
      FUNCTION generateUniqueName RETURN VARCHAR2 IS
        v_uniqueName  VARCHAR2(30);
      BEGIN
        SELECT 'ODMR$'||TO_CHAR(SYSTIMESTAMP,'HH24_MI_SS_FF')||dbms_random.string(NULL, 7) INTO v_uniqueName FROM dual;
        RETURN v_uniqueName;
      END;
        
      FUNCTION getInputSource(p_nodeId VARCHAR2) RETURN VARCHAR2 IS
        v_output  VARCHAR2(30);
      BEGIN
        SELECT OUTPUT_NAME INTO v_output FROM "&WORKFLOW_OUTPUT" WHERE NODE_ID = p_nodeId AND COMMENTS = 'Output Data';
        RETURN v_output;
      END;
    
      FUNCTION getTextPolicy(p_nodeId VARCHAR2, p_column VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
        v_output  VARCHAR2(30);
      BEGIN
        IF (p_column IS NULL) THEN
          SELECT OUTPUT_NAME INTO v_output FROM "&WORKFLOW_OUTPUT" WHERE NODE_ID = p_nodeId AND OUTPUT_TYPE = 'POLICY' AND ADDITIONAL_INFO IS NULL;
        ELSE
          SELECT OUTPUT_NAME INTO v_output FROM "&WORKFLOW_OUTPUT" WHERE NODE_ID = p_nodeId AND OUTPUT_TYPE = 'POLICY' AND ADDITIONAL_INFO = p_column;
        END IF;
        RETURN v_output;
      END;
    
      PROCEDURE recordOutput(p_NODE_ID VARCHAR2, p_NODE_NAME VARCHAR2, p_NODE_TYPE VARCHAR2, 
                             p_MODEL_ID VARCHAR2, p_MODEL_NAME VARCHAR2, p_MODEL_TYPE VARCHAR2, 
                             p_OUTPUT_NAME VARCHAR2, p_OUTPUT_TYPE VARCHAR2, p_ADDITIONAL_INFO VARCHAR2, p_COMMENTS VARCHAR2) IS
      BEGIN
        INSERT INTO "&WORKFLOW_OUTPUT" VALUES (p_NODE_ID, p_NODE_NAME, p_NODE_TYPE, p_MODEL_ID, REPLACE(REPLACE(p_MODEL_NAME,'"',''), (v_user_session||'.'), ''), p_MODEL_TYPE, p_OUTPUT_NAME, p_OUTPUT_TYPE, p_ADDITIONAL_INFO, SYSTIMESTAMP, p_COMMENTS);
        COMMIT;
      END;
    
      PROCEDURE execSQL(p_sql CLOB) IS
        curid         INTEGER;
        ignoreid      INTEGER;    
      BEGIN
        curid := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(curid, p_sql, DBMS_SQL.NATIVE);
        ignoreid := DBMS_SQL.EXECUTE(curid);
        DBMS_SQL.CLOSE_CURSOR(curid);
      EXCEPTION WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(curid) THEN
          DBMS_SQL.CLOSE_CURSOR(curid);
        END IF;
      END;  
    BEGIN
      -- input view
      v_caseid := generateUniqueName;
      v_target := 'AFFINITY_CARD';
      
      v_input_data := generateUniqueName;
      v_sql := 
        'CREATE VIEW '||v_input_data||' AS (  
          SELECT "CUST_ID" '||v_caseid||', t.* 
          FROM '||getInputSource('10001')||' t
        )'; 
      execSQL(v_sql);
      recordOutput('10006', 'Class Build', 'Class Build', NULL, NULL, NULL, v_input_data, 'VIEW', NULL, 'Input Data');
    
      v_build_data := generateUniqueName; 
      v_caseid_alias := generateUniqueName; 
      v_num_row_alias := generateUniqueName; 
      v_sql :=  
        'CREATE VIEW '||v_build_data||' AS SELECT * FROM ( 
        WITH  
        "A" as (SELECT /*+ inline */ * FROM '||v_input_data||'), 
        "B.1" as (SELECT /*+ inline */ row_number() OVER(partition by "'||v_target||'" ORDER BY ORA_HASH('||v_caseid||')) '||v_caseid_alias||', '||v_caseid||' FROM "A" t), 
        "B.2" as (SELECT /*+ inline */ t.*, p.'||v_caseid_alias||' FROM "A" t, "B.1" p WHERE t.'||v_caseid||' = p.'||v_caseid||'), 
        "B.3" as (SELECT "'||v_target||'", COUNT(*) '||v_num_row_alias||' FROM "A" GROUP BY "'||v_target||'"), 
        "B" as ( 
          SELECT /*+ inline */ v1.* FROM "B.2" v1 ,"B.3" v2 
          WHERE v1."'||v_target||'" = v2."'||v_target||'" 
          AND ORA_HASH(v1.'||v_caseid_alias||', v2.'||v_num_row_alias||', 0) < (v2.'||v_num_row_alias||' * 60.0 / 100) 
        ) 
        SELECT * from "B" 
        )'; 
      execSQL(v_sql); 
      
      v_test_data := generateUniqueName; 
      v_sql := 
        'CREATE VIEW '||v_test_data||' AS SELECT * FROM ( 
        WITH 
        "A" as (SELECT /*+ inline */ * FROM '||v_input_data||'), 
        "B.1" as (SELECT /*+ inline */ row_number() OVER(partition by "'||v_target||'" ORDER BY ORA_HASH('||v_caseid||')) '||v_caseid_alias||', '||v_caseid||' FROM "A" t), 
        "B.2" as (SELECT /*+ inline */ t.*, p.'||v_caseid_alias||' FROM "A" t, "B.1" p WHERE t.'||v_caseid||' = p.'||v_caseid||'),  
        "B.3" as (SELECT "'||v_target||'", COUNT(*) '||v_num_row_alias||' FROM "A" GROUP BY "'||v_target||'"),  
        "B" as ( 
          SELECT /*+ inline */ v1.* FROM "B.2" v1 ,"B.3" v2 
          WHERE v1."'||v_target||'" = v2."'||v_target||'" 
          AND ORA_HASH(v1.'||v_caseid_alias||', v2.'||v_num_row_alias||', 0) >= (v2.'||v_num_row_alias||' * 60.0 / 100) 
        ) 
        SELECT * from "B" 
        )'; 
      execSQL(v_sql); 
      recordOutput('10006', 'Class Build', 'Class Build', NULL, NULL, NULL, v_build_data, 'VIEW', NULL, 'Build Data');
      recordOutput('10006', 'Class Build', 'Class Build', NULL, NULL, NULL, v_test_data, 'VIEW', NULL, 'Test Data');
    
    
    
    
      -- data usage view
      v_data_usage := generateUniqueName;
      v_sql := 
        'CREATE VIEW '||v_data_usage||' AS SELECT '||v_caseid||', 
    "AGE"
    , "OCCUPATION"
    , "CUST_INCOME_LEVEL"
    , "FLAT_PANEL_MONITOR"
    , "YRS_RESIDENCE"
    , "HOME_THEATER_PACKAGE"
    , "HOUSEHOLD_SIZE"
    , "BULK_PACK_DISKETTES"
    , "Y_BOX_GAMES"
    , "AFFINITY_CARD"
    , "CUST_MARITAL_STATUS"
    , "EDUCATION"
    , "CUST_GENDER"
    , "COUNTRY_NAME"
    , "OS_DOC_SET_KANJI"
    , "BOOKKEEPING_APPLICATION" 
        FROM '||v_build_data;
      execSQL(v_sql);
      recordOutput('10006', 'Class Build', 'Class Build', '10003', '&MODEL_1', 'Decision Tree', v_data_usage, 'VIEW', NULL, 'Data Usage');
      v_xlst := dbms_data_mining_transform.TRANSFORM_LIST(); 
      DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, '"OCCUPATION"', NULL, 'SUBSTR("OCCUPATION", 1, 4000)', 'SUBSTR("OCCUPATION", 1, 4000)', NULL); 
      DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, '"CUST_INCOME_LEVEL"', NULL, 'SUBSTR("CUST_INCOME_LEVEL", 1, 4000)', 'SUBSTR("CUST_INCOME_LEVEL", 1, 4000)', NULL); 
      DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, '"FLAT_PANEL_MONITOR"', NULL, 'TO_CHAR("FLAT_PANEL_MONITOR")', 'TO_NUMBER("FLAT_PANEL_MONITOR")', NULL); 
      DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, '"HOME_THEATER_PACKAGE"', NULL, 'TO_CHAR("HOME_THEATER_PACKAGE")', 'TO_NUMBER("HOME_THEATER_PACKAGE")', NULL); 
      DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, '"HOUSEHOLD_SIZE"', NULL, 'SUBSTR("HOUSEHOLD_SIZE", 1, 4000)', 'SUBSTR("HOUSEHOLD_SIZE", 1, 4000)', NULL); 
      DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, '"BULK_PACK_DISKETTES"', NULL, 'TO_CHAR("BULK_PACK_DISKETTES")', 'TO_NUMBER("BULK_PACK_DISKETTES")', NULL); 
      DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, '"Y_BOX_GAMES"', NULL, 'TO_CHAR("Y_BOX_GAMES")', 'TO_NUMBER("Y_BOX_GAMES")', NULL); 
      DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, '"CUST_MARITAL_STATUS"', NULL, 'SUBSTR("CUST_MARITAL_STATUS", 1, 4000)', 'SUBSTR("CUST_MARITAL_STATUS", 1, 4000)', NULL); 
      DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, '"EDUCATION"', NULL, 'SUBSTR("EDUCATION", 1, 4000)', 'SUBSTR("EDUCATION", 1, 4000)', NULL); 
      DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, '"CUST_GENDER"', NULL, 'SUBSTR("CUST_GENDER", 1, 4000)', 'SUBSTR("CUST_GENDER", 1, 4000)', NULL); 
      DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, '"COUNTRY_NAME"', NULL, 'SUBSTR("COUNTRY_NAME", 1, 4000)', 'SUBSTR("COUNTRY_NAME", 1, 4000)', NULL); 
      DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, '"OS_DOC_SET_KANJI"', NULL, 'TO_CHAR("OS_DOC_SET_KANJI")', 'TO_NUMBER("OS_DOC_SET_KANJI")', NULL); 
      DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, '"BOOKKEEPING_APPLICATION"', NULL, 'TO_CHAR("BOOKKEEPING_APPLICATION")', 'TO_NUMBER("BOOKKEEPING_APPLICATION")', NULL); 
    
      
      v_weights_setting := generateUniqueName; 
      execSQL('CREATE TABLE '||v_weights_setting||' (ACTUAL_TARGET_VALUE NUMBER, PREDICTED_TARGET_VALUE NUMBER, COST NUMBER)'); 
      execSQL('INSERT INTO '||v_weights_setting||' VALUES (1, 1, 0.0)'); 
      execSQL('INSERT INTO '||v_weights_setting||' VALUES (1, 0, 4.215962441314554)'); 
      execSQL('INSERT INTO '||v_weights_setting||' VALUES (0, 1, 1.3109489051094891)'); 
      execSQL('INSERT INTO '||v_weights_setting||' VALUES (0, 0, 0.0)'); 
      recordOutput('10006', 'Class Build', 'Class Build', '10003', '&MODEL_1', 'Decision Tree', v_weights_setting, 'TABLE', NULL, 'Weights Setting'); 
    
      
      -- build setting
      v_build_setting := generateUniqueName;
      execSQL('CREATE TABLE '||v_build_setting||' (SETTING_NAME VARCHAR2(30), SETTING_VALUE VARCHAR2(128))'); 
      execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.TREE_IMPURITY_METRIC||''', ''TREE_IMPURITY_GINI'')'); 
      execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.TREE_TERM_MAX_DEPTH||''', 7)'); 
      execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.TREE_TERM_MINPCT_NODE||''', 0.05)'); 
      execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.TREE_TERM_MINPCT_SPLIT||''', 0.1)'); 
      execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.TREE_TERM_MINREC_NODE||''', 10)'); 
      execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.TREE_TERM_MINREC_SPLIT||''', 20)'); 
      execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.ALGO_NAME||''', '''||DBMS_DATA_MINING.ALGO_DECISION_TREE||''')'); 
      execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.CLAS_COST_TABLE_NAME||''', '''||v_weights_setting||''')'); 
      execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.PREP_AUTO||''', '''||DBMS_DATA_MINING.PREP_AUTO_ON||''')'); 
    
      recordOutput('10006', 'Class Build', 'Class Build', '10003', '&MODEL_1', 'Decision Tree', v_build_setting, 'TABLE', NULL, 'Build Setting');
    
      -- model build
      IF (v_drop = 'TRUE') THEN -- delete any existing model?
        BEGIN
          DBMS_DATA_MINING.DROP_MODEL('&MODEL_1', TRUE);
        EXCEPTION WHEN OTHERS THEN
          NULL; -- ignore if no existing model to drop
        END;
      END IF;
      DBMS_DATA_MINING.CREATE_MODEL(
        model_name          => '&MODEL_1',
        mining_function     => DBMS_DATA_MINING.CLASSIFICATION,
        data_table_name     => v_data_usage,
        case_id_column_name => v_caseid,
        target_column_name  => '"'||v_target||'"',
        settings_table_name => v_build_setting,
        xform_list          => v_xlst);
      execSQL('COMMENT ON MINING MODEL &MODEL_1 IS ''BALANCED'''); 
    
      recordOutput('10006', 'Class Build', 'Class Build', '10003', '&MODEL_1', 'Decision Tree', '&MODEL_1', 'MODEL', NULL, 'Model');
    
    
    
      -- apply result for test
      v_apply_data := generateUniqueName;
      execSQL('CREATE TABLE '||v_apply_data||' AS SELECT '||v_caseid||', t.* FROM '||v_test_data||' s, TABLE(PREDICTION_SET(&MODEL_1 COST MODEL AUTO USING *)) t');
      recordOutput('10006', 'Class Build', 'Class Build', '10003', '&MODEL_1', 'Decision Tree', v_apply_data, 'TABLE', NULL, 'Apply Data');
    
      -- test metric
      v_test_metric := generateUniqueName;
      execSQL('CREATE TABLE '||v_test_metric||' (METRIC_NAME VARCHAR2(30), METRIC_VARCHAR_VALUE VARCHAR2(128), METRIC_NUM_VALUE NUMBER)');
      recordOutput('10006', 'Class Build', 'Class Build', '10003', '&MODEL_1', 'Decision Tree', v_test_metric, 'TABLE', NULL, 'Test Metric');
    
      execSQL('INSERT INTO '||v_test_metric||' (METRIC_NAME, METRIC_VARCHAR_VALUE) VALUES (''MODEL_NAME'', ''&MODEL_1'')');
      execSQL('INSERT INTO '||v_test_metric||' (METRIC_NAME, METRIC_VARCHAR_VALUE) VALUES (''MINING_FUNCTION'', ''CLASSIFICATION'')');
      execSQL('INSERT INTO '||v_test_metric||' (METRIC_NAME, METRIC_VARCHAR_VALUE) VALUES (''TARGET_ATTRIBUTE'', '''||v_target||''')');
    
      EXECUTE IMMEDIATE 'SELECT "'||v_target||'" FROM '||v_test_data||' GROUP BY "'||v_target||'" ORDER BY count(*) ASC' BULK COLLECT INTO v_targets;
      execSQL('INSERT INTO '||v_test_metric||' (METRIC_NAME, METRIC_VARCHAR_VALUE) VALUES (''LEAST_TARGET_VALUE'', '''||v_targets(1)||''')');
    
      -- confusion matrix
      v_confusion_matrix := generateUniqueName;
      DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX (
        accuracy                    => v_accuracy,
        apply_result_table_name     => v_apply_data,
        target_table_name           => v_test_data,
        case_id_column_name         => v_caseid,
        target_column_name          => '"'||v_target||'"',
        confusion_matrix_table_name => v_confusion_matrix,
        score_column_name           => 'PREDICTION',
        score_criterion_column_name => 'COST',
        score_criterion_type        => 'COST');
      execSQL('INSERT INTO '||v_test_metric||' (METRIC_NAME, METRIC_NUM_VALUE) VALUES (''ACCURACY'', NVL('||v_accuracy||', 0)*100)');
      recordOutput('10006', 'Class Build', 'Class Build', '10003', '&MODEL_1', 'Decision Tree', v_confusion_matrix, 'TABLE', NULL, 'Confusion Matrix');
      
      -- average accuracy
      v_sql := 
      'WITH
      a as
        (SELECT a.actual_target_value, sum(a.value) recall_total
           FROM '||v_confusion_matrix||' a
           group by a.actual_target_value),
      b as
        (SELECT count(distinct b.actual_target_value) num_recalls
           FROM '||v_confusion_matrix||' b),
      c as
        (SELECT c.actual_target_value, value
           FROM '||v_confusion_matrix||' c
           where actual_target_value = predicted_target_value),
      d as
        (SELECT sum(c.value/a.recall_total) tot_accuracy
           FROM a, c
           where a.actual_target_value = c.actual_target_value)
      SELECT d.tot_accuracy/b.num_recalls * 100 avg_accuracy
      FROM b, d';
      EXECUTE IMMEDIATE v_sql INTO v_avg_accuracy;
      execSQL('INSERT INTO '||v_test_metric||' (METRIC_NAME, METRIC_NUM_VALUE) VALUES (''AVG_ACCURACY'', NVL('||v_avg_accuracy||', 0))');
    
      -- predictive confidence
      v_sql := 
      'WITH
      a as
        (SELECT a.actual_target_value, sum(a.value) recall_total
           FROM '||v_confusion_matrix||' a
           group by a.actual_target_value),
      b as
        (SELECT count(distinct b.actual_target_value) num_classes
           FROM '||v_confusion_matrix||' b),
      c as
        (SELECT c.actual_target_value, value
           FROM '||v_confusion_matrix||' c
           where actual_target_value = predicted_target_value),
      d as
        (SELECT sum(c.value/a.recall_total) tot_accuracy
           FROM a, c
           where a.actual_target_value = c.actual_target_value)
      SELECT (1 - (1 - d.tot_accuracy/b.num_classes) / GREATEST(0.0001, ((b.num_classes-1)/b.num_classes))) * 100
      FROM b, d';
      EXECUTE IMMEDIATE v_sql INTO v_predictive_conf;
      execSQL('INSERT INTO '||v_test_metric||' (METRIC_NAME, METRIC_NUM_VALUE) VALUES (''PREDICTIVE_CONFIDENCE'', NVL('||v_predictive_conf||', 0))');
    
      v_sql := 
      'WITH 
      tr_cost AS 
        ( 
          select ACTUAL actual_target_value, PREDICTED predicted_target_value, COST 
          from TABLE(DBMS_DATA_MINING.GET_MODEL_COST_MATRIX(''&MODEL_1'')) 
        ), 
      total_actuals AS 
        (SELECT nvl(a.actual_target_value, b.actual_target_value) actual_target_value, 
           SUM(a.VALUE * b.cost) actuals_cost, 
           SUM(a.VALUE) total_actuals 
         FROM '||v_confusion_matrix||' a, 
           tr_cost b 
         WHERE a.actual_target_value(+) = b.actual_target_value 
         AND a.predicted_target_value(+) = b.predicted_target_value 
         GROUP BY nvl(a.actual_target_value, b.actual_target_value)), 
      total_predicted AS 
        (SELECT nvl(a.predicted_target_value, b.predicted_target_value) predicted_target_value, 
           SUM(a.VALUE * b.cost) predicted_cost, 
           SUM(a.VALUE) total_predicted 
         FROM '||v_confusion_matrix||' a, 
           tr_cost b 
         WHERE a.actual_target_value(+) = b.actual_target_value 
         AND a.predicted_target_value(+) = b.predicted_target_value 
         GROUP BY nvl(a.predicted_target_value, b.predicted_target_value)), 
      right_predictions AS 
        (SELECT actual_target_value, 
           VALUE AS correct 
         FROM '||v_confusion_matrix||' 
         WHERE actual_target_value = predicted_target_value), 
      actuals_total_cost as 
        (SELECT SUM(a.VALUE * b.cost) actuals_total_cost 
         FROM '||v_confusion_matrix||' a, 
           tr_cost b 
         WHERE a.actual_target_value = b.actual_target_value 
         AND a.predicted_target_value = b.predicted_target_value) 
      SELECT 
        SUM(actuals_cost) COST 
      FROM total_actuals a, 
        total_predicted p, 
        right_predictions c, 
        actuals_total_cost tc 
      WHERE a.actual_target_value(+) = p.predicted_target_value AND p.predicted_target_value = c.actual_target_value(+)'; 
      EXECUTE IMMEDIATE v_sql INTO v_tot_cost; 
      execSQL('INSERT INTO '||v_test_metric||' (METRIC_NAME, METRIC_NUM_VALUE) VALUES (''COST'', NVL('||v_tot_cost||', 0))'); 
    
    
      -- targets for test results 
      v_sql := 
      'SELECT /*+ noparallel(t)*/ "'||v_target||'" as prediction FROM 
      ( 
        SELECT "'||v_target||'", 
        RANK() OVER (ORDER BY count("'||v_target||'") DESC) "Rank" 
        FROM '||v_test_data||'  
        GROUP BY "'||v_target||'" 
      ) 
      WHERE rownum <= 5'; 
      EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_targets; 
    
    
      FOR i IN 1..v_targets.COUNT LOOP 
        -- lift for each target 
        v_lift := generateUniqueName; 
        DBMS_DATA_MINING.COMPUTE_LIFT ( 
          apply_result_table_name   => v_apply_data, 
          target_table_name         => v_test_data, 
          case_id_column_name       => v_caseid, 
          target_column_name        => '"'||v_target||'"', 
          lift_table_name           => v_lift, 
          positive_target_value     => v_targets(i), 
          score_column_name         => 'PREDICTION', 
          score_criterion_column_name => 'COST', 
          num_quantiles             => 100, 
          score_criterion_type      => 'COST'); 
        recordOutput('10006', 'Class Build', 'Class Build', '10003', '&MODEL_1', 'Decision Tree', v_lift, 'TABLE', v_target||'='||v_targets(i), 'Lift Result'); 
      END LOOP; 
    
    
      -- roc for each target (only binary target support) 
      IF (v_targets.COUNT <= 2) THEN 
        FOR i IN 1..v_targets.COUNT LOOP 
          v_roc := generateUniqueName; 
          DBMS_DATA_MINING.COMPUTE_ROC ( 
            roc_area_under_curve        => v_area_under_curve, 
            apply_result_table_name     => v_apply_data, 
            target_table_name           => v_test_data, 
            case_id_column_name         => v_caseid, 
            target_column_name          => '"'||v_target||'"', 
            roc_table_name              => v_roc, 
            positive_target_value       => v_targets(i), 
            score_column_name           => 'PREDICTION', 
            score_criterion_column_name => 'PROBABILITY'); 
          recordOutput('10006', 'Class Build', 'Class Build', '10003', '&MODEL_1', 'Decision Tree', v_roc, 'TABLE', v_target||'='||v_targets(i), 'ROC Result'); 
          recordOutput('10006', 'Class Build', 'Class Build', '10003', '&MODEL_1', 'Decision Tree', v_area_under_curve, 'SCALAR', v_target||'='||v_targets(i), 'ROC Area Under Curve'); 
        END LOOP; 
      END IF; 
    
    
    
    
    EXCEPTION WHEN OTHERS THEN
      dbms_output.put_line('Class Build node failed: '||DBMS_UTILITY.FORMAT_ERROR_STACK());
      RAISE_APPLICATION_ERROR(-20000, 'Class Build node failed: '||DBMS_UTILITY.FORMAT_ERROR_STACK());
    END;
    /
    EXECUTE dbms_output.put_line('');
    EXECUTE dbms_output.put_line('Class Build node completed: ' || SYSTIMESTAMP);
    EXECUTE dbms_output.put_line('');
    
    {code}