This discussion is archived
8 Replies Latest reply: Dec 18, 2012 9:50 AM by Denny Wong RSS

Association Rule : Settings in PL/SQL

979335 Newbie
Currently Being Moderated
I've developed a model in ODM GUI for association rules.
I'm now looking to develop the same in PL/SQL.
I need to know how to specify the case id that has 2 attributes (CUST_ID and TIME_ID) and I have the PROD_ID as the ITEM ID. I have the following settings table and PLSQL

BEGIN

INSERT INTO assoc_sample_settings (setting_name, setting_value) VALUES
(dbms_data_mining.algo_name, dbms_data_mining.ALGO_APRIORI_ASSOCIATION_RULES);

INSERT into assoc_sample_settings (setting_name, setting_value) VALUES
(dbms_data_mining.prep_auto, dbms_data_mining.prep_auto_on);

INSERT into assoc_sample_settings (setting_name, setting_value) VALUES
(ODMS_ITEM_ID_COLUMN_NAME, ‘PROD_ID’);

END;

DBMS_DATA_MINING.CREATE_MODEL(
model_name => 'ASSOC_MODEL_2',
mining_function => dbms_data_mining.association,
data_table_name => 'SH.SALES',
case_id_column_name => ‘CUST_ID, TIME_ID’,
target_column_name => null,
settings_table_name => 'assoc_sample_settings');

Is this correct? for setting the CASE ID to 'CUST_ID, TIME_ID', is this correct and/or in the correct place

Also the setting of the PROD_ID also correct?

Do I need to use the ODMS_ITEM_VALUE_COLUMN_NAME setting ?
  • 1. Re: Association Rule : Settings in PL/SQL
    979335 Newbie
    Currently Being Moderated
    I've gone ahead and created the settings table, but when I go to run the CREATE_MODEL I get the following error. Any idea what might be causing this
    SQL> BEGIN
    2 DBMS_DATA_MINING.CREATE_MODEL(
    3 model_name => 'ASSOC_MODEL_2',
    4 mining_function => DBMS_DATA_MINING.ASSOCIATION,
    5 data_table_name => 'SH.SALES',
    6 case_id_column_name => 'CUST_ID, TIME_ID',
    7 target_column_name => null,
    8 settings_table_name => 'assoc_sample_settings');
    9 END;
    10 /
    BEGIN
    *
    ERROR at line 1:
    ORA-44003: invalid SQL name
    ORA-06512: at "SYS.DBMS_DATA_MINING", line 1798
    ORA-06512: at line 2
  • 2. Re: Association Rule : Settings in PL/SQL
    Denny Wong Explorer
    Currently Being Moderated
    Since you have 2 attributes for the case id, you may create a composite case id using the RANK function (see below).

    CREATE VIEW INPUT_DATA_V AS (
    SELECT RANK() OVER (ORDER BY "CUST_ID", "TIME_ID") CASE_ID, t.*
    FROM SH.SALES t
    );

    DBMS_DATA_MINING.CREATE_MODEL(
    model_name => 'ASSOC_MODEL_2',
    mining_function => dbms_data_mining.association,
    data_table_name => 'INPUT_DATA_V',
    case_id_column_name => ‘CASE_ID’,
    target_column_name => null,
    settings_table_name => 'assoc_sample_settings');

    The ODMS_ITEM_VALUE_COLUMN_NAME setting is used only if you specify an attribute as item value.

    You should set dbms_data_mining.prep_auto to dbms_data_mining.prep_auto_off since auto prep is not used in AR model build process.

    Thanks,
    Denny
  • 3. Re: Association Rule : Settings in PL/SQL
    979335 Newbie
    Currently Being Moderated
    Thank you for your reply, but it is still not working. There is the code that I have used for setting up and running. What is missing or incorrect with the following. The data is from the SH.SALES table.

    Settings Table

    BEGIN

    INSERT INTO assoc_sample_settings (setting_name, setting_value) VALUES
    (dbms_data_mining.algo_name, dbms_data_mining.ALGO_APRIORI_ASSOCIATION_RULES);

    INSERT into assoc_sample_settings (setting_name, setting_value) VALUES
    (dbms_data_mining.prep_auto, dbms_data_mining.prep_auto_off);

    INSERT into assoc_sample_settings (setting_name, setting_value) VALUES
    (dbms_data_mining.ODMS_ITEM_ID_COLUMN_NAME, ‘PROD_ID’);

    COMMIT;

    END;


    Create the view

    CREATE VIEW ASSOC_DATA_V AS (
    SELECT RANK() OVER (ORDER BY CUST_ID, TIME_ID) CASE_ID, t.*
    FROM SH.SALES t );


    Create the model

    BEGIN
    DBMS_DATA_MINING.CREATE_MODEL(
    model_name => 'ASSOC_MODEL_2',
    mining_function => DBMS_DATA_MINING.ASSOCIATION,
    data_table_name => 'ASSOC_DATA_V',
    case_id_column_name => ‘CASE_ID’,
    target_column_name => null,
    settings_table_name => 'assoc_sample_settings');
    END;

    BEGIN
    *
    ERROR at line 1:
    ORA-40104: invalid training data for model build
    ORA-06512: at "SYS.DBMS_DATA_MINING", line 1798
    ORA-06512: at line 2

    Any ideas what is causing this?
  • 4. Re: Association Rule : Settings in PL/SQL
    Denny Wong Explorer
    Currently Being Moderated
    Sorry, the build input data should include the necessary columns only, see below:

    CREATE VIEW ASSOC_DATA_V AS (
    SELECT RANK() OVER (ORDER BY "CUST_ID", "TIME_ID") CASE_ID, t.PROD_ID
    FROM SH.SALES t
    );

    Denny
  • 5. Re: Association Rule : Settings in PL/SQL
    979335 Newbie
    Currently Being Moderated
    I've made that correction and I've run the CREATE_MODEL.

    When I run the following
    SELECT rule_id,
    antecedent,
    consequent,
    rule_support,
    rule_confidence
    FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('ASSOC_MODEL_2'));

    I get no records.

    But when I run it for the model generated by the ODM GUI tool I get 20988 records

    Similarly for

    SELECT count(*)
    FROM TABLE(DBMS_DATA_MINING.GET_FREQUENT_ITEMSETS('assoc_model_2'));

    I get 9 records for the model just created. For the ODM model I get 6230 records.

    Can you explain why I'm getting the differences and what the ODM GUI tool is doing differently
  • 6. Re: Association Rule : Settings in PL/SQL
    Denny Wong Explorer
    Currently Being Moderated
    To reproduce the same output as produced from the UI, you may need to add these additional settings:

    INSERT INTO BUILD_SETTING VALUES (DBMS_DATA_MINING.ASSO_MAX_RULE_LENGTH, '4');
    INSERT INTO BUILD_SETTING VALUES (DBMS_DATA_MINING.ASSO_MIN_CONFIDENCE, '0.1');
    INSERT INTO BUILD_SETTING VALUES (DBMS_DATA_MINING.ASSO_MIN_SUPPORT, '0.01');

    If you omit these settings, default values will be used in model build. I believe that's why you got different result.

    Denny
  • 7. Re: Association Rule : Settings in PL/SQL
    979335 Newbie
    Currently Being Moderated
    But they are the default settings, so I should not have to set them. So is it something else?
  • 8. Re: Association Rule : Settings in PL/SQL
    Denny Wong Explorer
    Currently Being Moderated
    The default value for Minimum support for association rules is 0.1, but the Data Miner default is 0.01. The reason for this change is the built model is likely to produce more rules if we lower the bar (min support).

    Denny

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points