6 Replies Latest reply: Jan 28, 2013 12:12 PM by ArunkumarGunasekaran RSS

    Formatting SYS.DM_PREDICATES (in ASSOCIATION model)

    ArunkumarGunasekaran
      Hello,

      Context:
      I have built an ASSOCIATION data mining model against my SALES fact table in my Oracle database. After which I am querying for the results. The expected results is combination of products that the customer purchased together along with their probabilities. In the below output, ANTECEDENT is the product(s) bought first and CONSEQUENT is the product bought after the ANTECEDENT. So I am expecting an output something like :

      123 || milk, eggs || cereals || 0.12876 || 1.927

      Question:
      In my query output I have something called DM_PREDICATES instead or product names like milk, eggs etc. I am aware that it is some kind of an object that contains a set of attributes. What is this DM_PREDICATES and how do I format it to show the names of the products?


      My Query :
      SELECT rule_id, antecedent, consequent, rule_support, rule_confidence
      FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('SALES_DATAMINING_MODEL'));

      Output :

      RULE_ID || ANTECEDENT ||  CONSEQUENT || RULE_SUPPORT || RULE_CONFIDENCE
      90 *||*     SYS.DM_PREDICATES([SYS.DM_PREDICATE],[SYS.DM_PREDICATE]) *||*     SYS.DM_PREDICATES([SYS.DM_PREDICATE]) *||*     0.001213702153868449178486676448743727695959 *||*     0.9054054054054054054054054054054054054059
      125 *||*     SYS.DM_PREDICATES([SYS.DM_PREDICATE],[SYS.DM_PREDICATE]) *||*     SYS.DM_PREDICATES([SYS.DM_PREDICATE])     *||* 0.001014437621143778417839610166114160462294     *||* 0.8235294117647058823529411764705882352944
      113     *||* SYS.DM_PREDICATES([SYS.DM_PREDICATE],[SYS.DM_PREDICATE])     *||* SYS.DM_PREDICATES([SYS.DM_PREDICATE]) *||*     0.002173794902450953752513450355958915276344     *||* 0.8108108108108108108108108108108108108108

      Best,
      Arun
        • 1. Re: Formatting SYS.DM_PREDICATES (in ASSOCIATION model)
          Mark Kelly
          HI,
          I just tweaked your query a bit to expose the concequents and antecedents:

          SELECT rule_id, ant.*, con.*, rule_support, rule_confidence
          FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('ASSOC_AP_1_3')),
          TABLE(antecedent)ant, TABLE(consequent) con
          order by rule_id;

          If you have a rule where there is more than one antecedent, then the rule will have as many rows as antecedents.
          Now, if you have a really large AR model, you should take advantage of the api parameters to extract out only those rules along with appropriate amount of rule detail.
          See the api doc for this info.
          http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_datmin.htm#ARPLS65840

          If you want to try out Data Miner, you can use the Model Details node and attach it to the AR Build node to extract out the data you wish from the model.
          It will also allow you to view the sql utilized for that extract.
          For example, the following query is used to generate a single row rule output:

          SELECT CAST('DMUSER' AS VARCHAR2(30)) as "MODEL_SCHEMA",
          CAST('ASSOC_AP_1_3' AS VARCHAR2(30)) as "MODEL_NAME",
          AR.rule_id as "ID",
          REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(EXTRACT(XMLELEMENT("R", XMLAGG( XMLELEMENT("V", NVL2( ant_pred.attribute_subname, ant_pred.attribute_name || '.' || ant_pred.attribute_subname, ant_pred.attribute_name) || ' ' || ant_pred.conditional_operator || ' '
          ||ant_pred.attribute_str_value
          ||ant_pred.attribute_num_value))), '/R/V').getstringval(),'&gt;','>'),'&lt;','<'), '</V><V>',', '),'IN',' '),'<V>', ''),'</V>','') AS ANTECEDENT_ITEMS,
          MAX(NVL2( cons_pred.attribute_subname, cons_pred.attribute_name || '.' || cons_pred.attribute_subname, cons_pred.attribute_name) || ' ' || cons_pred.conditional_operator || ' '
          ||ant_pred.attribute_str_value
          ||ant_pred.attribute_num_value) consequent_items,
          AVG(ar.rule_support) AS SUPPORT,
          AVG(ar.rule_confidence) AS CONFIDENCE,
          AVG(ar.rule_lift) AS LIFT,
          AVG(ar.antecedent_support) AS ANTECEDENT_SUPPORT,
          AVG(ar.consequent_support) AS CONSEQUENT_SUPPORT,
          AVG(ar.number_of_items) AS LENGTH
          FROM TABLE(dbms_data_mining.get_association_rules('"DMUSER"."ASSOC_AP_1_3"')) AR,
          TABLE(AR.antecedent) ant_pred , TABLE(AR.consequent) cons_pred
          GROUP BY AR.rule_id

          Get some of this a try and review the api doc to get a feel for the options.
          Thanks, Mark
          • 2. Re: Formatting SYS.DM_PREDICATES (in ASSOCIATION model)
            ArunkumarGunasekaran
            Thank you Mark for your reply and I am sorry for the very late response.

            I tried your code but it's not working. I don't know where I am going wrong. I did read through the link you provided. It was helpful in understanding the structure of antecedent and consequent columns. After which I tried to query individual columns within the antecedent and consequent columns. But no luck. Also I am unable to wrap my head around the concept of ORA_MINING_VARCHAR2_NT datatype and the query below.
            SELECT * FROM TABLE (
               DBMS_DATA_MINING.GET_ASSOCIATION_RULES (
                  'AR_SH_SAMPLE', 10, NULL, 0.5, 0.01, 2, 1,
                     ORA_MINING_VARCHAR2_NT (
                     'NUMBER_OF_ITEMS DESC', 'RULE_CONFIDENCE DESC', 'RULE_SUPPORT DESC'),
                     DM_ITEMS(DM_ITEM('CUSTPRODS', 'Mouse Pad', 1, NULL), 
                              DM_ITEM('CUSTPRODS', 'Standard Mouse', 1, NULL)),
                     DM_ITEMS(DM_ITEM('CUSTPRODS', 'Extension Cable', 1, NULL))));
            Anyhow, let me restate what I want now. Just in case if there is a misunderstanding. When I execute the following query, I get the following output.
            SELECT * FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('PROD_MODEL'));
            OUTPUT
             RULE_ID ANTECEDENT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       CONSEQUENT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       RULE_SUPPORT RULE_CONFIDENCE  RULE_LIFT ANTECEDENT_SUPPORT CONSEQUENT_SUPPORT NUMBER_OF_ITEMS
            ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ --------------- ---------- ------------------ ------------------ ---------------
                   163 SYS.DM_PREDICATES(SYS.DM_PREDICATE('CUSTPRODS','32','= ',1,NULL,NULL,NULL),SYS.DM_PREDICATE('CUSTPRODS','4','= ',1,NULL,NULL,NULL))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              SYS.DM_PREDICATES(SYS.DM_PREDICATE('CUSTPRODS','8','= ',1,NULL,NULL,NULL))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       0.00130497825               1 1.844521708      0.00130497825       0.5421459642               2 
                   177 SYS.DM_PREDICATES(SYS.DM_PREDICATE('CUSTPRODS','32','= ',1,NULL,NULL,NULL),SYS.DM_PREDICATE('CUSTPRODS','19','= ',1,NULL,NULL,NULL))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             SYS.DM_PREDICATES(SYS.DM_PREDICATE('CUSTPRODS','8','= ',1,NULL,NULL,NULL))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       0.001159980667    0.9795918367 1.806878408     0.001184146931       0.5421459642               2 
                   169 SYS.DM_PREDICATES(SYS.DM_PREDICATE('CUSTPRODS','32','= ',1,NULL,NULL,NULL),SYS.DM_PREDICATE('CUSTPRODS','17','= ',1,NULL,NULL,NULL))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             SYS.DM_PREDICATES(SYS.DM_PREDICATE('CUSTPRODS','8','= ',1,NULL,NULL,NULL))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       0.001570807153    0.9701492537 1.789461359     0.001619139681       0.5421459642               2 
            I just want to de-construct the antecedent and consequent columns.

            Thanks!
            Arun
            • 3. Re: Formatting SYS.DM_PREDICATES (in ASSOCIATION model)
              Mark Kelly
              Hi,
              Try this:

              SELECT rule_id, ant.*, con.*, rule_support, rule_confidence
              FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('PROD_MODEL')),
              TABLE(antecedent)ant, TABLE(consequent) con
              order by rule_id;

              Remember to change the model name if necessary.
              Let me know what the failure is if it does not work.
              Thanks,Mark
              • 4. Re: Formatting SYS.DM_PREDICATES (in ASSOCIATION model)
                ArunkumarGunasekaran
                Still not working, I am getting the following error.

                YOUR QUERY
                SELECT rule_id, ant., con., rule_support, rule_confidence
                FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('PROD_MODEL')),
                TABLE(antecedent)ant, TABLE(consequent) con
                order by rule_id;
                ERROR
                ORA-01747: invalid user.table.column, table.column, or column specification
                01747. 00000 - "invalid user.table.column, table.column, or column specification"
                *Cause:   
                *Action:
                Error at Line: 1 Column: 21

                But I am getting the unformatted output all fine when I use the below code.
                QUERY
                select * from table (DBMS_DATA_MINING.GET_ASSOCIATION_RULES('PROD_MODEL'));
                OUTPUT
                RULE_ID ANTECEDENT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       CONSEQUENT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       RULE_SUPPORT RULE_CONFIDENCE  RULE_LIFT ANTECEDENT_SUPPORT CONSEQUENT_SUPPORT NUMBER_OF_ITEMS
                ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ --------------- ---------- ------------------ ------------------ ---------------
                       163 SYS.DM_PREDICATES(SYS.DM_PREDICATE('CUSTPRODS','32','= ',1,NULL,NULL,NULL),SYS.DM_PREDICATE('CUSTPRODS','4','= ',1,NULL,NULL,NULL))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              SYS.DM_PREDICATES(SYS.DM_PREDICATE('CUSTPRODS','8','= ',1,NULL,NULL,NULL))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       0.00130497825               1 1.844521708      0.00130497825       0.5421459642               2 
                       177 SYS.DM_PREDICATES(SYS.DM_PREDICATE('CUSTPRODS','32','= ',1,NULL,NULL,NULL),SYS.DM_PREDICATE('CUSTPRODS','19','= ',1,NULL,NULL,NULL))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             SYS.DM_PREDICATES(SYS.DM_PREDICATE('CUSTPRODS','8','= ',1,NULL,NULL,NULL))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       0.001159980667    0.9795918367 1.806878408     0.001184146931       0.5421459642               2 
                • 5. Re: Formatting SYS.DM_PREDICATES (in ASSOCIATION model)
                  Mark Kelly
                  Hi Arun,
                  The sql was getting messed up during the posting.
                  he ant. and con. references require an asterisk.
                  Here is the corrected sql with the proper xml tags to keep the sql from getting messed up.
                  SELECT rule_id, ant.*, con.*, rule_support, rule_confidence
                  FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('PROD_MODEL')),
                  TABLE(antecedent)ant, TABLE(consequent) con
                  order by rule_id;
                  Thanks,Mark
                  • 6. Re: Formatting SYS.DM_PREDICATES (in ASSOCIATION model)
                    ArunkumarGunasekaran
                    It's working!


                    Thank you very much!
                    Arun