5 Working With Mining Model Schema Objects

In this chapter, you will learn how to retrieve information about mining models from the data dictionary and perform various operations on mining models.

This chapter contains the following topics:

Obtaining Information from the Data Dictionary
Data Definition Language for Mining Models
Adding a Comment to a Mining Model
Auditing Mining Models
Exporting and Importing Mining Models
Importing From PMML

Obtaining Information from the Data Dictionary

Mining models are database schema objects. They can be queried in the ALL, DBA, and USER data dictionary views.

The data dictionary views in Table 5-1 reveal information about mining models created by Oracle Data Mining.

Table 5-1 Mining Model Data Dictionary Views

ALL_ Views	DBA_ Views	USER_ Views
`ALL_MINING_MODELS`	`DBA_MINING_MODELS`	`USER_MINING_MODELS`
`ALL_MINING_MODEL_ATTRIBUTES`	`DBA_MINING_MODEL_ATTRIBUTES`	`USER_MINING_MODEL_ATTRIBUTES`
`ALL_MINING_MODEL_SETTINGS`	`DBA_MINING_MODEL_SETTINGS`	`USER_MINING_MODEL_SETTINGS`

Obtaining Information about Mining Models

You can query the ALL_MINING_MODELS data dictionary view to obtain information about all accessible mining model objects.

SQL> DESCRIBE all_mining_models
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 MODEL_NAME                                NOT NULL VARCHAR2(30)
 MINING_FUNCTION                                    VARCHAR2(30)
 ALGORITHM                                          VARCHAR2(30)
 CREATION_DATE                             NOT NULL DATE
 BUILD_DURATION                                     NUMBER
 MODEL_SIZE                                         NUMBER
 COMMENTS                                           VARCHAR2(4000)

The information in the COMMENTS column is generated by a SQL COMMENT statement. The comment provides descriptive information about the model. If no comment was generated by SQL COMMENT, the COMMENTS column is empty. See "Adding a Comment to a Mining Model".

See Also:

Oracle Data Mining Concepts for information about mining functions and algorithms

Oracle Data Mining Application Developer's Guide for more information about ALL_MINING_MODELS

Obtaining Information about Mining Model Attributes

You can query the ALL_MINING_MODEL_ATTRIBUTES data dictionary view to obtain information about all accessible mining model attributes.

SQL> DESCRIBE all_mining_model_attributes
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 MODEL_NAME                                NOT NULL VARCHAR2(30)
 ATTRIBUTE_NAME                            NOT NULL VARCHAR2(30)
 ATTRIBUTE_TYPE                                     VARCHAR2(11)
 DATA_TYPE                                          VARCHAR2(12)
 DATA_LENGTH                                        NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 USAGE_TYPE                                         VARCHAR2(8)
 TARGET                                             VARCHAR2(3)

Note:

The attributes listed in this data dictionary view are the physical columns in the build data that were used to construct the model. Some or all of these columns should be present for scoring. These data attributes are referred to as the model signature.

The term attribute is more accurately used to designate the numericals and categoricals derived from the data attributes for manipulation by the algorithm. These model attributes may or may not correspond to data attributes, depending on transformations and on whether or not the column is nested. The model attributes can be viewed in the model details returned by the GET_MODEL_DETAILS functions in the DBMS_DATA_MINING package.

See Also:

Oracle Data Mining Application Developer's Guide for more information about attributes

Oracle Data Mining Application Developer's Guide for more information about ALL_MINING_MODEL_ATTRIBUTES

Oracle Database PL/SQL Packages and Types Reference for details about the GET_MODEL_DETAILS functions

Obtaining Information about Mining Model Settings

You can query the ALL_MINING_MODEL_SETTINGS data dictionary view to obtain information about all accessible mining model settings.

SQL> DESCRIBE all_mining_model_settings
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 MODEL_NAME                                NOT NULL VARCHAR2(30)
 SETTING_NAME                              NOT NULL VARCHAR2(30)
 SETTING_VALUE                                      VARCHAR2(4000)
 SETTING_TYPE                                       VARCHAR2(7)

Model settings are specified in a settings table that is used in the model build. The settings all have default values. The defaults are used when they are not overridden by settings specified in the settings table, or when there is no settings table.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about model settings

Oracle Data Mining Application Developer's Guide for more information about ALL_MINING_MODEL_SETTINGS

Data Definition Language for Mining Models

The DDL operations for mining models are described in Table 5-2. The operations are procedures in the DBMS_DATA_MINING PL/SQL package.

Table 5-2 DDL for Mining Models

DDL	DBMS_DATA_MINING Procedure	Description
Add cost matrix	`ADD_COST_MATRIX`	Adds a cost matrix to a classification model
Alter reverse expression	`ALTER_REVERSE_EXPRESSION`	Alters the reverse transformation expression associated with a model
Create model	`CREATE_MODEL`	Creates a model
Drop model	`DROP_MODEL`	Drops a model
Remove cost matrix	`REMOVE_COST_MATRIX`	Removes the cost matrix from a classification model
Rename model	`RENAME_MODEL`	Renames a model

See:

Oracle Database PL/SQL Packages and Types Reference for details

Adding a Comment to a Mining Model

Comments can be used to associate descriptive information with a database object. You can associate a comment with a mining model using a SQL COMMENT statement.

COMMENT ON MINING MODEL schema_name.model_name IS string;

Note:

To add a comment to a model in another schema, you must have the COMMENT ANY MODEL system privilege.

See Table 4-1, "System Privileges for Data Mining".

To drop a comment, set it to the empty '' string.

The following statement adds a comment to the model DT_SH_CLAS_SAMPLE in your own schema.

SQL> COMMENT ON MINING MODEL dt_sh_clas_sample IS
           'Decision Tree model predicts promotion response';

You can view the comment by querying the catalog view USER_MINING_MODELS.

SQL> COLUMN comments FORMAT a22
SQL> SELECT model_name, mining_function, algorithm, comments FROM user_mining_models; 

MODEL_NAME        MINING_FUNCTION  ALGORITHM      COMMENTS
----------------- ---------------- -------------- -----------------------------------------------
DT_SH_CLAS_SAMPLE CLASSIFICATION   DECISION_TREE  Decision Tree model predicts promotion response

To drop this comment from the database, issue the following statement:

SQL> COMMENT ON MINING MODEL dt_sh_clas_sample '';

Auditing Mining Models

The SQL auditing system is a DBA tool for tracking operations on database objects in a production environment. Data mining models can be tracked with the SQL auditing system.

Note:

To audit a mining model in another schema, you must have the AUDIT ANY system privilege.

See Table 4-1, "System Privileges for Data Mining".

Enabling Auditing in the Database

DBAs use auditing to monitor activity in the database. Audit records can be generated to monitor various database operations. They include information such as the operation that was audited, the user performing the operation, and the date and time of the operation.

Audit records can be stored in either a data dictionary table, called the database audit trail, or in operating system files, called an operating system audit trail.

The database initialization parameter AUDIT_TRAIL controls auditing capabilities in the database. To enable auditing, set AUDIT_TRAIL to one of the following:

DB
DB,EXTENDED
OS
XML
XML,EXTENDED

Set AUDIT_TRAIL to NONE to prevent auditing information from being recorded. By default, AUDIT_TRAIL is set to DB.

See Also:

Oracle Database Security Guide for details about the AUDIT_TRAIL settings

Opening an Audit Trail on Mining Models

Use the SQL AUDIT statement to open an auditing trail on a data mining model.

AUDIT {operation|ALL} ON MINING MODEL schema_name.model_name;

You can track the following operations on mining models.

Audit Operation	Description
`AUDIT`	Generate an audit trail for a mining model
`COMMENT`	Add a comment to a mining model
`GRANT`	Give permission to a user to access the model
`RENAME`	Change the name of the model
`SELECT`	Apply the model or view its signature.

For example, this statement generates an audit trail for all GRANT operations on the model NB_SH_CLAS_SAMPLE in the DMUSER schema.

SQL> AUDIT GRANT ON MINING MODEL dmuser.nb_sh_clas_sample;

This statement generates an audit trail for all operations on the same model.

SQL> AUDIT GRANT,AUDIT,COMMENT,RENAME,SELECT 
            ON MINING MODEL dmuser.nb_sh_clas_sample;

You can refine the criteria for auditing with the following additional semantics.

AUDIT {operation|ALL} ON MINING MODEL schema_name.model_name
                                    [BY [SESSION|ACCESS]]
                                    [WHENEVER [NOT] SUCCESSFUL]];

Closing the Audit Trail

Use the NOAUDIT statement to stop one or more auditing operations previously enabled by the AUDIT statement.

NOAUDIT {operation| ALL} ON MINING MODEL model_name
                                      [WHENEVER [NOT] SUCCESSFUL]];

Viewing the Audit Trail

For each audited operation, Oracle Database produces an audit record containing:

The name of the user performing the operation
The type of operation
The object involved in the operation
The date and time of the operation

Several data dictionary views present auditing information. Some examples are:

DBA_AUDIT_OBJECT displays audit trail records for all objects in the database.
USER_AUDIT_OBJECT displays audit trail records for all objects accessible to the current user
DBA_OBJ_AUDIT_OPTS describes auditing options for all objects in the database.
USER_OBJ_AUDIT_OPTS describes auditing options for all objects owned by the current user.

Note:

The Oracle Database auditing system is a powerful, highly configurable tool for tracking operations on schema objects. Refer to the following manuals for more information:

Oracle Database Security Guide for a comprehensive discussion of database auditing
Oracle Database SQL Language Reference for documentation of the AUDIT and NOAUDIT statements
Oracle Database Reference for documentation of the AUDIT_TRAIL initialization parameter and the data dictionary views for querying the database audit trail

Exporting and Importing Mining Models

You can export data mining models to flat files to back up work in progress or to move models to a different instance of Oracle Database Enterprise Edition (such as from a development database to a production database). All methods for exporting and importing models are based in Oracle Data Pump technology.

Using Oracle Data Pump

Oracle Data Pump consists of two command-line clients and two PL/SQL APIs. The command-line clients, EXPDP and IMPDP, provide an easy-to-use interface to the Data Pump export and import utilities. EXPDP and IMPDP can be used to export/import entire schemas or databases.

The Data Pump export utility writes the schema objects, including the tables and metadata that constitute mining models, to a dump file set. The Data Pump import utility retrieves the schema objects, including the model tables and metadata, from the dump file set and restores them in the target database.

EXPDP and IMPDP cannot be used to export/import individual mining models.

See Also:

Oracle Database Utilities for information about Oracle Data Pump and the expdp and impdp utilities

Using EXPORT_MODEL and IMPORT_MODEL

The DBMS_DATA_MINING package includes the EXPORT_MODEL and IMPORT_MODEL procedures for exporting and importing individual mining models. EXPORT_MODEL and IMPORT_MODEL use the export and import facilities of Oracle Data Pump.

See Also:

Oracle Database PL/SQL Packages and Types Reference for detailed information about the export and import procedures in the DBMS_DATA_MINING package

Database Privileges for Export/Import

Your options for exporting and importing mining models depend on your access rights in the database:

A DBA can use EXPDP to export a full database and IMPDP to import a full database. All mining models in the database are included in the export/import.
A DBA or individual user can use EXPDP to export a schema and IMPDP to import a schema. All mining models in the schema are included in the export/import.
A DBA or individual user can use DBMS_DATA_MINING.EXPORT_MODEL to export individual models and DBMS_DATA_MINING.IMPORT_MODEL to import individual models. These procedures can export/import a single mining model, all the mining models in a schema, or mining models that match specific criteria.

By default, EXPORT_MODEL and IMPORT_MODEL support export and import within the same schema. You may need special privileges in the database to import models into a different schema. These privileges are granted by the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles, which are only available to privileged users (such as SYS or a user with the DBA role). You do not need these roles to export/import models within your own schema.

To import models, you must have the same database privileges as the user who created the dump file set. Otherwise, a DBA with full system privileges must import the models.

Directory Object for EXPORT_MODEL and IMPORT_MODEL

EXPORT_MODEL and IMPORT_MODEL use a directory object to identify the location of the dump file set. A directory object is a logical name in the database for a physical directory on the host computer.

To export Data Mining models, you must have write access to the directory object and to the file system directory that it represents. To import Data Mining models, you must have read access to the directory object and to the file system directory. Also, the database itself must have access to file system directory.

You must have the CREATE ANY DIRECTORY privilege to create directory objects.

The following SQL command creates, or re-creates if it already exists, a directory object named dmtest. The file system directory (in this example, C:\oracle\product\11.1.0\dmining) must already exist and have shared read/write access rights granted by the operating system.

CREATE OR REPLACE DIRECTORY dmtest AS 'c:\oracle\product\11.1.0\dmining';

This SQL command gives user dmuser both read and write access to dmtest.

GRANT READ,WRITE ON DIRECTORY dmtest TO dmuser;

Note:

For security reasons, use caution in granting permissions.

See Also:

Oracle Database SQL Language Reference for information about the CREATE DIRECTORY statement

Tablespace for IMPORT_MODEL

Mining models are stored in the default tablespace of the mining model owner, or in a tablespace to which the owner has access. The tablespace must also exist in the target database, and the target user must have access to it. If the tablespace does not exist in the target database, you must create it before importing the models.

For example, if the models were created in schema DMUSER and the default tablespace for DMUSER is USERS, then the USERS tablespace must exist in the target database. You can create the USERS tablespace and grant access to a target user with appropriate tablespace quota as follows.

connect / as sysdba;
create tablespace USERS datafile 'data_file_name' size 200M autoextend on;
alter user target_user quota unlimited on USERS;

Tables Created By EXPORT_MODEL and IMPORT_MODEL

The Data Mining export and import utilities create tables in the user's schema that are for internal use only:

DM$P_MODEL_EXPIMP_TEMP. Used for internal purposes during export and import, and provides a job history.
DM$P_MODEL_IMPORT_TEMP. Used only for internal purposes during import.
DM$P_MODEL_TABKEY_TEMP. Used only for internal purposes during export and import.

Do not alter these tables. However, you may drop them when no export or import job is running. The utilities will re-create them for the next job.

Examples: Exporting and Importing Mining Models

This example creates a dump file containing two models and imports the models from the dump file.

This example was generated on a Linux system. The directory object mydir identifies the path /scratch/dumpfiles. (See "Directory Object for EXPORT_MODEL and IMPORT_MODEL".)

Exporting Models from the DMUSER Schema

In this example, the dmuser schema contains two mining models.

SQL> CONNECT dmuser
Enter password: password

SQL> SELECT model_name FROM user_mining_models;
 
MODEL_NAME
------------------------------
GLMR_SH_REGR_SAMPLE
SVMC_SH_CLAS_SAMPLE

The following commands create the directory object mydir and export all models from dmuser to mydir.

SQL> CREATE OR REPLACE DIRECTORY mydir AS '/scratch/dumpfiles';
SQL> EXECUTE dbms_data_mining.export_model('all_dmuser_models.dmp', 'mydir');

An export or import creates a log file in the same directory as the dump file. Error messages are returned to the current output device (such as the screen), and the log file may provide additional information.

This sample export created two files in the mydir directory:

A dump file named ALL_DMUSER_MODELS01.DMP (note the 2-digit suffix added to the name)
A log file with the name DMUSER_EXP_920.LOG

For detailed information about the default names of files, see Oracle Database PL/SQL Packages and Types Reference.

You can view the log file using a system command or editor. You must know the path of the physical directory in order to locate the file.

DMUSER_EXP_920.LOG lists the two exported mining models and supporting objects .

Starting "DMUSER"."DMUSER_exp_17":  DM_EXPIMP_JOB_ID=17
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.062 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
. . exported "DMUSER"."DM$PIGLMR_SH_REGR_SAMPLE"         7.085 KB      14 rows
. . exported "DMUSER"."DM$PISVMC_SH_CLAS_SAMPLE"         7.507 KB      17 rows
. . exported "DMUSER"."DM$PCGLMR_SH_REGR_SAMPLE"         53.27 KB    2278 rows
. . exported "DMUSER"."DM$PAGLMR_SH_REGR_SAMPLE"         5.796 KB      24 rows
. . exported "DMUSER"."DM$PBGLMR_SH_REGR_SAMPLE"         15.76 KB      67 rows
. . exported "DMUSER"."DM$PDGLMR_SH_REGR_SAMPLE"             8 KB      66 rows
. . exported "DMUSER"."DM$PDSVMC_SH_CLAS_SAMPLE"         9.023 KB      88 rows
. . exported "DMUSER"."DM$PFGLMR_SH_REGR_SAMPLE"         5.656 KB      10 rows
. . exported "DMUSER"."DM$POSVMC_SH_CLAS_SAMPLE"         5.320 KB       8 rows
. . exported "DMUSER"."DM$PXSVMC_SH_CLAS_SAMPLE"         7.265 KB      77 rows
. . exported "DMUSER"."DM$PZSVMC_SH_CLAS_SAMPLE"         6.164 KB       1 rows
. . exported "DMUSER"."DM$P_MODEL_EXPIMP_TEMP"           5.921 KB       2 rows
Master table "DMUSER"."DMUSER_exp_17" successfully loaded/unloaded
******************************************************************************
Dump file set for DMUSER.DMUSER_exp_17 is:
  /scratch/dumpfiles/all_dmuser_models01.dmp
Job "DMUSER"."DMUSER_exp_17" successfully completed at 10:30:19

Importing Models Into the Same Schema

The exported models still exist in DMUSER. In this example, we drop the models before importing from the dump file. An import will not overwrite an existing model with the same name.

SQL> EXECUTE dbms_data_mining.drop_model('GLMR_SH_REGR_SAMPLE');
SQL> EXECUTE dbms_data_mining.drop_model('SVMC_SH_CLAS_SAMPLE');

The following command restores all models from the dump file to the DMUSER schema.

SQL> EXECUTE dbms_data_mining.import_model('all_dmuser_models01.dmp', 'mydir');

Importing Models Into a Different Schema

A user with the necessary privileges can load the models from a dump file into a different schema. The target schema must have the same permissions and have access to the same tablespace as the schema from which the models were exported.

CREATE USER dmuser2 IDENTIFIED BY password
        default tablespace dmuser
        temporary tablespace temp
        quota unlimited on dmuser;

@ $ORACLE_HOME/RDBMS/demo/dmshgrants dmuser2

The import command, also executed as SYS, loads the two models into the DMUSER2 schema.

SQL> EXECUTE dbms_data_mining.import_model('all_dmuser_models01.dmp', 'mydir', null, null, null, 'todmuser2', 'dmuser:dmuser2');

A parameter specifies TODMUSER2.LOG as the name of the log file; the .LOG extension is added automatically to the name. The log file shows the names of the imported models and supporting metadata.

Master table "SYS"."todmusr2" successfully loaded/unloaded
Starting "SYS"."todmusr4":  DM_EXPIMP_JOB_ID=21
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DMUSER2"."DM$PIGLMR_SH_REGR_SAMPLE"        7.085 KB      14 rows
. . imported "DMUSER2"."DM$PISVMC_SH_CLAS_SAMPLE"        7.507 KB      17 rows
. . imported "DMUSER2"."DM$PCGLMR_SH_REGR_SAMPLE"        53.27 KB    2278 rows
. . imported "DMUSER2"."DM$PAGLMR_SH_REGR_SAMPLE"        5.796 KB      24 rows
. . imported "DMUSER2"."DM$PBGLMR_SH_REGR_SAMPLE"        15.76 KB      67 rows
. . imported "DMUSER2"."DM$PDGLMR_SH_REGR_SAMPLE"            8 KB      66 rows
. . imported "DMUSER2"."DM$PDSVMC_SH_CLAS_SAMPLE"        9.023 KB      88 rows
. . imported "DMUSER2"."DM$PFGLMR_SH_REGR_SAMPLE"        5.656 KB      10 rows
. . imported "DMUSER2"."DM$POSVMC_SH_CLAS_SAMPLE"        5.320 KB       8 rows
. . imported "DMUSER2"."DM$PXSVMC_SH_CLAS_SAMPLE"        7.265 KB      77 rows
. . imported "DMUSER2"."DM$PZSVMC_SH_CLAS_SAMPLE"        6.164 KB       1 rows
. . imported "DMUSER2"."DM$P_MODEL_EXPIMP_TEMP"          5.921 KB       2 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Job "SYS"."todmusr2" successfully completed at 11:15:26

Exporting and Importing Specific Models

You can export/import a single model, a list of models, or a group of models that share certain characteristics. For example, the following command exports two models, NMF_SH_SAMPLE and SVMR_SH_REGR_SAMPLE to the directory specified by directory object EXP_OUT.

SQL>execute dbms_data_mining.export_model ( 'models2_out', 'EXP_OUT',
            'name in (''SVMR_SH_REGR_SAMPLE'')');

See:

Oracle Database PL/SQL Packages and Types Reference for more examples

Importing From PMML

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2) Data Mining.

You can import a mining model represented in Predictive Model Markup Language (PMML). The model must be of type RegressionModel, either linear regression or binary logistic regression.

PMML is an XML-based standard specified by the Data Mining Group (http://www.dmg.org). Applications that are PMML-compliant can deploy PMML-compliant models that were created by any vendor. Oracle Data Mining supports the core features of PMML 3.1 for regression models.

See:

Oracle Database PL/SQL Packages and Types Reference for more information about PMML import