Build Predictive Models Using PL/SQL

Many data analytics professionals/data miners are not aware that predictive modeling can be done within Oracle database. I probably have built more than one hundred predictive models this way in recent years. One of the benefits is that data never leave the database. To do this, we take two steps.

1. Define model parameters. We create a table to store parameters including model types, configurations, etc. This is a one time effort. The parameter table can be reused.
CREATE TABLE glm_settings (
  setting_name  VARCHAR2(30),
  setting_value VARCHAR2(30));
-- The default classification algorithm is Naive Bayes. So override
-- this choice to GLM logistic regression using a settings table.
--
BEGIN
-- Populate settings table
  INSERT INTO glm_settings (setting_name, setting_value) VALUES
    (dbms_data_mining.algo_name, dbms_data_mining.algo_generalized_linear_model);
 -- INSERT INTO  glm_settings (setting_name, setting_value) VALUES
 -- (dbms_data_mining.prep_auto, dbms_data_mining.prep_auto_on);
  -- Examples of other possible overrides are:
  --(dbms_data_mining.glms_ridge_regression, dbms_data_mining.glms_ridge_reg_enable);
  COMMIT;
END;
/
2. Run DBMS_DATA_MINING.CREATE_MODEL function to build a model. A model is called a mining object which is simply a database object type like table, view, index.
-- Build a new GLM Model
BEGIN
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'glm_model_1',
    mining_function     => dbms_data_mining.classification,
    data_table_name     => 'tbl_training_set',
    case_id_column_name => 'record_id',
    target_column_name  => 'is_fraud',
    settings_table_name => 'glm_settings');
END;
/
We can see what models we have built in the database using the following query.
select object_name, object_type, created from all_objects where object_type like '%MINING%' order by created;
OBJECT_NAME OBJECT_TYPE ---------------------------------------- -------------- GLM_CTR MINING MODEL GLM0322A MINING MODEL GLM0322ORG MINING MODEL GLM0322DT MINING MODEL GLM0322EXP MINING MODEL GLM0322V2 MINING MODEL GLM0322V3 MINING MODEL GLM0322V4 MINING MODEL GLM0322V5 MINING MODEL