2 Replies Latest reply: Jan 24, 2013 4:17 PM by Mark Kelly-Oracle RSS

    Association rule (market basket analysis)

    ArunkumarGunasekaran
      Hello Folks,

      I have done some market basket analysis (association rule) using oracle data-miner (with oracle sql developer as UI). I get the results all fine using the UI. But how can I prepare a SQL code that will give me the sames results as my data-miner UI? In other words, I am saying that I know how to run to association rule using the data-miner UI but I don't know how to achieve the same thing by coding it in SQL.

      Please note : I have to run this code each time against an update customer sales transaction table and generate the results. So I am looking at a generic code that will run against a sales table of fixed data structure. Since I have to periodically run the association rule again and again over updated data, I need the SQL code.

      Kindly help.

      Thanks!
      Arun
        • 1. Re: Association rule (market basket analysis)
          Mark Kelly-Oracle
          Hi Arun,
          You can download sample code on how to create any model from the following link:
          http://www.oracle.com/technetwork/database/options/advanced-analytics/odm/odm-samples-194497.html
          Look for the script file: dmardemo.sql

          ODMr will be including a new feature to generate all the script files necessary to regenerate workflow results.
          The script above will at least give you some guidance on how to build AR models via code in the meanwhile.

          As a preview of the script generation for AR, here is one of the scripts that would be generated from a workflow that is building a AR model.
          Thanks, Mark

          [ code ]


          EXECUTE dbms_output.put_line('');
          EXECUTE dbms_output.put_line('Assoc Build 1 node started: ' || SYSTIMESTAMP);
          EXECUTE dbms_output.put_line('');

          DECLARE
          v_caseid VARCHAR2(30);
          v_input_data VARCHAR2(30);
          v_build_data VARCHAR2(30);
          v_data_usage VARCHAR2(30);
          v_build_setting VARCHAR2(30);
          v_lexer_name VARCHAR2(30);
          v_stoplist_name VARCHAR2(30);
          v_policy_name VARCHAR2(30);
          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_input_data := generateUniqueName;
          v_sql :=
          'CREATE VIEW '||v_input_data||' AS (
          SELECT RANK() OVER (ORDER BY "MARKET_BASKET", "CUST_ID") '||v_caseid||', t.*
          FROM '||getInputSource('10008')||' t
          )';
          execSQL(v_sql);
          recordOutput('10006', 'Assoc Build 1', 'Assoc Build', NULL, NULL, NULL, v_input_data, 'VIEW', NULL, 'Input Data');

          v_build_data := v_input_data;




          -- data usage view
          v_data_usage := generateUniqueName;
          v_sql :=
          'CREATE VIEW '||v_data_usage||' AS SELECT '||v_caseid||',
          "PROD_ID"
          FROM '||v_build_data;
          execSQL(v_sql);
          recordOutput('10006', 'Assoc Build 1', 'Assoc Build', '10005', '&MODEL_1', 'Apriori', v_data_usage, 'VIEW', NULL, 'Data Usage');


          -- 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.ASSO_MAX_RULE_LENGTH||''', ''4'')');
          execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.ASSO_MIN_CONFIDENCE||''', ''0.1'')');
          execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.ASSO_MIN_SUPPORT||''', ''0.01'')');
          execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.ODMS_ITEM_ID_COLUMN_NAME||''', ''PROD_ID'')');
          execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.PREP_AUTO||''', '''||DBMS_DATA_MINING.PREP_AUTO_OFF||''')');

          recordOutput('10006', 'Assoc Build 1', 'Assoc Build', '10005', '&MODEL_1', 'Apriori', 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.ASSOCIATION,
          data_table_name => v_data_usage,
          case_id_column_name => v_caseid,
          target_column_name => NULL,
          settings_table_name => v_build_setting,
          xform_list => v_xlst);
          recordOutput('10006', 'Assoc Build 1', 'Assoc Build', '10005', '&MODEL_1', 'Apriori', '&MODEL_1', 'MODEL', NULL, 'Model');


          EXCEPTION WHEN OTHERS THEN
          dbms_output.put_line('Assoc Build 1 node failed: '||DBMS_UTILITY.FORMAT_ERROR_STACK());
          RAISE_APPLICATION_ERROR(-20000, 'Assoc Build 1 node failed: '||DBMS_UTILITY.FORMAT_ERROR_STACK());
          END;
          /
          EXECUTE dbms_output.put_line('');
          EXECUTE dbms_output.put_line('Assoc Build 1 node completed: ' || SYSTIMESTAMP);
          EXECUTE dbms_output.put_line('');



          [ /code ]
          • 2. Re: Association rule (market basket analysis)
            Mark Kelly-Oracle
            Reposting AR Build example with proper xml markup:
            EXECUTE dbms_output.put_line('');
            EXECUTE dbms_output.put_line('Assoc Build 1 node started: ' || SYSTIMESTAMP);
            EXECUTE dbms_output.put_line('');
            
            DECLARE
              v_caseid            VARCHAR2(30);
              v_input_data        VARCHAR2(30);
              v_build_data        VARCHAR2(30);
              v_data_usage        VARCHAR2(30);
              v_build_setting     VARCHAR2(30);
              v_lexer_name        VARCHAR2(30);
              v_stoplist_name     VARCHAR2(30);
              v_policy_name       VARCHAR2(30);
              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_input_data := generateUniqueName;
              v_sql := 
                'CREATE VIEW '||v_input_data||' AS (  
                  SELECT RANK() OVER (ORDER BY "MARKET_BASKET", "CUST_ID") '||v_caseid||', t.* 
                  FROM '||getInputSource('10008')||' t
                )'; 
              execSQL(v_sql);
              recordOutput('10006', 'Assoc Build 1', 'Assoc Build', NULL, NULL, NULL, v_input_data, 'VIEW', NULL, 'Input Data');
            
              v_build_data := v_input_data;
            
            
            
            
              -- data usage view
              v_data_usage := generateUniqueName;
              v_sql := 
                'CREATE VIEW '||v_data_usage||' AS SELECT '||v_caseid||', 
            "PROD_ID" 
                FROM '||v_build_data;
              execSQL(v_sql);
              recordOutput('10006', 'Assoc Build 1', 'Assoc Build', '10005', '&MODEL_1', 'Apriori', v_data_usage, 'VIEW', NULL, 'Data Usage');
            
            
              -- 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.ASSO_MAX_RULE_LENGTH||''', ''4'')'); 
              execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.ASSO_MIN_CONFIDENCE||''', ''0.1'')'); 
              execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.ASSO_MIN_SUPPORT||''', ''0.01'')'); 
              execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.ODMS_ITEM_ID_COLUMN_NAME||''', ''PROD_ID'')'); 
              execSQL('INSERT INTO '||v_build_setting||' VALUES ('''||DBMS_DATA_MINING.PREP_AUTO||''', '''||DBMS_DATA_MINING.PREP_AUTO_OFF||''')'); 
            
              recordOutput('10006', 'Assoc Build 1', 'Assoc Build', '10005', '&MODEL_1', 'Apriori', 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.ASSOCIATION,
                data_table_name     => v_data_usage,
                case_id_column_name => v_caseid,
                target_column_name  => NULL,
                settings_table_name => v_build_setting,
                xform_list          => v_xlst);
              recordOutput('10006', 'Assoc Build 1', 'Assoc Build', '10005', '&MODEL_1', 'Apriori', '&MODEL_1', 'MODEL', NULL, 'Model');
            
            
            EXCEPTION WHEN OTHERS THEN
              dbms_output.put_line('Assoc Build 1 node failed: '||DBMS_UTILITY.FORMAT_ERROR_STACK());
              RAISE_APPLICATION_ERROR(-20000, 'Assoc Build 1 node failed: '||DBMS_UTILITY.FORMAT_ERROR_STACK());
            END;
            /
            EXECUTE dbms_output.put_line('');
            EXECUTE dbms_output.put_line('Assoc Build 1 node completed: ' || SYSTIMESTAMP);
            EXECUTE dbms_output.put_line('');
            
            
            {code}