3 Replies Latest reply: Mar 1, 2012 1:34 PM by Mark Kelly-Oracle RSS

    Multiple Regression in ODM

    920802
      Hello,

      I'm looking for solution to make multiple regression in Oracle Data Miner (Oracle 11g).
      Multiple means for me build regression (value as a target, month number as case id) for multiple prod_id. Each product should be separately valuated and for each product regression function should be calculated separately.

      I've got data in rows - with product_id, month, quantity and amount.
      I filtered one product_id and set it as a data source for building regression model and it works.
      But in data table I have multiple products and I wan't to avoid situation, that I should build model in workflow for each product separately.

      So, I want to build regression model to forecast amount for next month base on values for previous months for each product_id in one flow in workflow.
      I know that it is possible for example in R language.

      Is it possible also in ODM ?
        • 1. Re: Multiple Regression in ODM
          Mark Kelly-Oracle
          Hi,
          At this time, you can either build a single model and use the multiple target value capabilities of the classification model to handle this use case. The Build node incorporates a "balanced" performance setting, that does a pretty good job at insuring an high average accuracy. This can be further tweaked by customizing the performance setting. Or you can create a workflow that splits the data using the product_id, and build a model per product_id. You can then use the persisted models and reference them using the Apply node predictive sql functions. However, we do recognize the need for a more automated partitioning framework.
          Thanks, Mark
          • 2. Re: Multiple Regression in ODM
            920802
            Hello,


            Thanks for fast and comprehensive answering.
            I just want to ensure: models can be build also by SQL commands, but data source name for DBMS_DATA_MINING.CREATE_MODEL can be set only as table name or view name.
            Consequence is that I should build for each product in my case new view and for each product build new model with different model name.

            Question is:
            is there any possibility to set data_table_name with parameter, or except of table name put sql select clause ?
            I didn't find that possibility in documentation, so I guess that there is no such possibility.
            Could you confirm ?


            Thanks in advance,
            Paul.
            • 3. Re: Multiple Regression in ODM
              Mark Kelly-Oracle
              Hi Paul,
              Nope, the pl/sql api requires a table or view.
              If you can define the view such as it can be parameterized, then you could try using a Parameterized view.
              See example below.
              This would just save you in creating and dropping views for each model build.
              You would have manage the state of the session context userenv.
              You can use the Model Node to view models created manually via pl/sql.
              For more info on Parameterized Views see the following thread:
              http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1448404423206

              Thanks,Mark

              -- example of creating a view:

              create view my_parameter_view
              as select * from mining_data_build_v where cust_gender = userenv('client_info');

              exec dbms_application_info.set_client_info('F');

              select * from my_parameter_view;



              --example of using it to build models


              set serveroutput on
              DROP TABLE svm_mk_model_settings;
              -- Create the settings table
              CREATE TABLE svm_mk_model_settings (
              setting_name VARCHAR2(30),
              setting_value VARCHAR2(30));

              BEGIN
              INSERT INTO svm_mk_model_settings (setting_name, setting_value) VALUES
              (dbms_data_mining.SVMS_KERNEL_FUNCTION, dbms_data_mining.svms_linear);
              INSERT INTO svm_mk_model_settings (setting_name, setting_value) VALUES
              (dbms_data_mining.algo_name, dbms_data_mining.algo_support_vector_machines);
              INSERT INTO svm_mk_model_settings (setting_name, setting_value) VALUES
              (dbms_data_mining.prep_auto,dbms_data_mining.prep_auto_on);
              COMMIT;
              END;
              /
              -- Create the model using the specified settings
              exec dbms_application_info.set_client_info('F');
              exec DBMS_DATA_MINING.drop_model('svm_mk_model_female');

              BEGIN
              DBMS_DATA_MINING.CREATE_MODEL(
              model_name => 'svm_mk_model_female',
              mining_function => dbms_data_mining.CLASSIFICATION,
              data_table_name => 'my_parameter_view',
              target_column_name => 'affinity_card',
              case_id_column_name => 'cust_id',
              settings_table_name => 'svm_mk_model_settings');
              END;
              /
              -- Create the model using the specified settings
              exec dbms_application_info.set_client_info('M');
              exec DBMS_DATA_MINING.drop_model('svm_mk_model_male');

              BEGIN
              DBMS_DATA_MINING.CREATE_MODEL(
              model_name => 'svm_mk_model_male',
              mining_function => dbms_data_mining.CLASSIFICATION,
              data_table_name => 'my_parameter_view',
              target_column_name => 'affinity_card',
              case_id_column_name => 'cust_id',
              settings_table_name => 'svm_mk_model_settings');
              END;
              /