8 Replies Latest reply: Dec 18, 2012 11:50 AM by Denny Wong RSS

    Association Rule : Settings in PL/SQL

    979335
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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