4 Replies Latest reply: Feb 14, 2013 2:17 AM by 990843 RSS

    Simple association analysis (apriori) with sqldeveloper

    990843
      Dear experts

      I try to create a simlpe association analysis with the sqldeveloper. I simply want to know, which products are sold with which product (e.g. if you buy an apple, 30% of these people also buy a banana). I am very new to this topic and saw that this analysis is possible with the sqldeveloper. The structure of the table is as follows:

      pk_articlenumber
      fk_ordernumber
      price
      amount
      amout_sum

      I now want to know, which products are sold together in 25/50/75/...% of the orders.

      Up to now I did the following:

      I created a new Data Mining Connection. I then created a new project and a new workflow. I added the table and an association to the workflow and connected them. In the data source, I only chose pk_articlenumber and fk_ordernumber, because these are the only neccessary values I need for this analysis. In the association I am not sure what to choose exactly.

      Until now, I selected the following values:

      Transaction-ID: pk_articlenumber
      Element-ID: pk_articlenumber
      Value: fk_ordernumber

      But when I run the process now, it results in an error. Can anyone tell me what I am doing wrong?

      Thanks in advance for your help!
        • 1. Re: Simple association analysis (apriori) with sqldeveloper
          Mark Kelly-Oracle
          Hi,
          Can you provide the following:

          Error message: Obtain this from the event viewer which can be viewed from the workflow.
          It will show you error messages etc that occur when the workflow is running.

          DB Version.
          SQL Dev Version

          In case you don't know, there are some OBE Tutorials available:
          http://www.oracle.com/technetwork/database/options/advanced-analytics/odm/index.html
          And go to the Resources section.

          There are not many selections for AR.
          1) Transaction identifier (one or more columns)
          This uniquely identifies the rows of a common transaction.

          2)Item column - such as product_id

          3)Value.
          This defaults to existence. So the presence of the product.
          Now you could also select something like amount_sold.
          The tricky part to doing that is that it requires you to transform the data ahead of time so that the amount_sold column is binned.
          I think currently the UI limits the distinct count to 10 values.

          Thanks, Mark
          • 2. Re: Simple association analysis (apriori) with sqldeveloper
            990843
            Hi,

            I will try to translate the error messages for further analysis:

            1. START(VALIDATE): Element value fk_articlenumber has too many explicit values. The maximum number of explicit values is 10.
            2. END(VALIDATE): Build not successful. Reason: ORA-20003: Validation not successful. Reason: Illegal Element value.
            Detail: ORA-06512

            The versions are:
            DB: 11.2.0.1.0
            SQL Dev.: 3.2.20.09.87

            I guess I have a logic error in my selections. I dont really know what transaction identifier, item column an value means.

            Thanking you in anticipation!
            • 3. Re: Simple association analysis (apriori) with sqldeveloper
              Mark Kelly-Oracle
              Hi,
              It looks like you ran into the problem noted earlier:

              +3)Value.+
              This defaults to existence. So the presence of the product.
              Now you could also select something like amount_sold.
              The tricky part to doing that is that it requires you to transform the data ahead of time so that the amount_sold column is binned.
              I think currently the UI limits the distinct count to 10 values."

              Ok. You have these columns:

              pk_articlenumber
              fk_ordernumber
              price
              amount
              amout_sum

              Transaction id - looks like fk_ordernumber fits the bill
              item-id - looks like pk_articlenumber will work
              value - use <existence>

              Thanks, Mark

              Thanks, Mark

              Edited by: mbkelly on Feb 13, 2013 6:33 PM
              • 4. Re: Simple association analysis (apriori) with sqldeveloper
                990843
                HIi mbkelly,

                thank you very much, this did it! I screwed up the transaction ID and the item ID...

                Thank you very much!