This discussion is archived
6 Replies Latest reply: Jan 28, 2013 10:12 AM by ArunkumarGunasekaran RSS

Formatting SYS.DM_PREDICATES (in ASSOCIATION model)

ArunkumarGunasekaran Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    It's working!


    Thank you very much!
    Arun

Legend

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