This discussion is archived
2 Replies Latest reply: Jan 7, 2013 10:29 AM by Mark Kelly RSS

record result of DBMS_DATA_MINING.GET_FREQUENT_ITEMSET in a relationl table

user1299751 Newbie
Currently Being Moderated
Database : Oracle 11g R2

I have developed a data mining model on SH.SALES table and get the below output from DBMS_DATA_MINING.GET_FREQUENT_ITEMSET. (this is not the complete data).

ITEMSET_ID SUPPORT NUMBER_OF_ITEMS
---------- ---------- ---------------
ITEMS(ATTRIBUTE_NAME, ATTRIBUTE_SUBNAME, ATTRIBUTE_NUM_VALUE, ATTRIBUTE_STR_VALU
--------------------------------------------------------------------------------
46 .111111111 4
DM_ITEMS(DM_ITEM('ITEM_ID', '125', NULL, NULL), DM_ITEM('ITEM_ID', '128', NULL,
NULL), DM_ITEM('ITEM_ID', '127', NULL, NULL), DM_ITEM('ITEM_ID', '126', NULL, NU
LL))


I cannot use these results directly in my analytics system. I have to some how record it in a table. Anybody has an idea how it can be recorded in a relational table.
  • 2. Re: record result of DBMS_DATA_MINING.GET_FREQUENT_ITEMSET in a relationl table
    Mark Kelly Oracle ACE
    Currently Being Moderated
    Hi,
    Just FYI.
    If you are using Data Miner and build a AR model using the AR Build node, you can then connect a Model Details node to it.
    This allows you to extract out details of the AR model as a flow of data, that you can then persist using Create Table if you wish.
    Edit the Model Details node to select itemsets and then right click the Model Details node View Data option.
    You can then view the generated SQL to see an example of how to access the itemset data as shown below.
    You can follow the same approach for other models to get a feel for how to write the sql necessary to extract out model information.
    Thanks, Mark

    WITH /* Start of sql for node: Model Details */
    "N$10003" as (select "MODEL_SCHEMA",
    "MODEL_NAME",
    "ID",
    "SUPPORT",
    "NUMBER_OF_ITEMS",
    "ATTRIBUTE_NAME",
    "ATTRIBUTE_SUBNAME",
    "ATTRIBUTE_STR_VALUE",
    "ATTRIBUTE_NUM_VALUE" from (SELECT CAST('DMUSER' AS VARCHAR2(30)) as "MODEL_SCHEMA",
    CAST('AR_SH_SAMPLE' AS VARCHAR2(30)) as "MODEL_NAME",
    itemset_id as "ID",
    support as "SUPPORT",
    number_of_items as "NUMBER_OF_ITEMS",
    item.attribute_name as "ATTRIBUTE_NAME",
    item.attribute_subname as "ATTRIBUTE_SUBNAME",
    item.attribute_num_value as "ATTRIBUTE_NUM_VALUE",
    item.attribute_str_value as "ATTRIBUTE_STR_VALUE"
    FROM TABLE(dbms_data_mining.get_frequent_itemsets('"DMUSER"."AR_SH_SAMPLE"', NULL, NULL)) t,
    TABLE(t.items) item
    ) )
    /* End of sql for node: Model Details */
    select * from "N$10003";

Legend

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