7 The Data Mining Sample Programs

Install the Sample Programs

Follow these steps to install the sample programs:

1. Install Oracle Database with the sample schemas, or obtain access to a database that includes the sample schemas.

   • If you followed the instructions in "Install Oracle Database", the sample schemas are installed automatically in the starter database. Be sure to unlock the SH schema, as described in step 11.

   • If the database does not include the sample schemas, you can install them manually or by using Oracle Database Configuration Assistant. See Oracle Database Sample Schemas for instructions.

2. Determine whether or not Database Examples was installed with Oracle Database. Database Examples provides a set of sample programs that illustrate numerous features of Oracle Database, including Oracle Data Mining. The programs are loaded into the RDBMS/demo subdirectory of Oracle home.

   If Database Examples was not installed, you can perform the installation by following the instructions in "Install Oracle Database Examples". Alternatively, you can download the Data Mining sample programs from the Oracle Technology Network.

   http://www.oracle.com/technology/products/bi/odm/index.html
   

Run the Configuration Scripts

Follow these steps to configure the sample data and grant the necessary privileges to your data mining user ID.

1. Log in to SQL*Plus with system privileges.

   Enter user-name: sys / as sysdba
       Enter password: password
   

2. If you do not have a user ID for your data mining activities, you can create one by following the instructions in "Example: Create a Database User in SQL*Plus".

3. Run dmshgrants.sql to grant data mining privileges and SH access to your user ID. Several tables in SH are used by the Data Mining sample programs. Specify the data mining user name as the parameter. Specify the full path of the Oracle home directory for ORACLE_BASE\ORACLE_HOME.

   @ ORACLE_BASE\ORACLE_HOME\RDBMS\demo\dmshgrants dmuser
   

4. Now connect to the database as the Data Mining user.

   CONNECT dmuser
   Enter password: password
   

5. Run dmsh.sql to populate the schema of the Data Mining user with tables, views, and other objects needed by the sample programs. Specify the full path of the Oracle home directory for OACLE_BASE\ORACLE_HOME.

   @ ORACLE_BASE\ORACLE_HOME\RDBMS\demo\dmsh
   COMMIT;
   

Locate the Sample Programs

This section explains how to locate the sample programs if they were installed with Database Examples.

To locate the PL/SQL programs, navigate to the parent directory and search for the files that start with dm and end with .sql.

For example, if Database Examples was installed in Oracle home C:\app\demotest\product\11.2.0\db_1\, then navigate to C:\app\demotest\product\11.2.0\db_1\RDBMS\demo\ and use Windows Search to find the files named dm*.sql. Windows Search returns the list of Data Mining PL/SQL programs, as shown in Figure 7-1.

Figure 7-1 The Data Mining Sample PL/SQL Programs

Description of "Figure 7-1 The Data Mining Sample PL/SQL Programs"

Use Windows Search to find the files named dm*.java in the same directory. Windows Search returns the Data Mining Java programs, as shown in Figure 7-2.

Figure 7-2 The Data Mining Sample Java Programs

Description of "Figure 7-2 The Data Mining Sample Java Programs"

Run the Sample Programs

You can run the sample programs as many times as you wish. The programs clean up the results of the previous run before executing the current run.

While the program is running, it displays the program code and the program output.

>java program_name host_name:port_number:database_identifier user password

List the Sample Models

The mining models created by the sample programs can be viewed with a query like the one shown in Example 7-1.

SQL> SELECT model_name, mining_function, algorithm FROM user_mining_models
            ORDER BY model_name;
 
MODEL_NAME                     MINING_FUNCTION                ALGORITHM
------------------------------ ------------------------------ ------------------------------
ABNMODEL_JDM                   CLASSIFICATION                 ADAPTIVE_BAYES_NETWORK
ABN_SH_CLAS_SAMPLE             CLASSIFICATION                 ADAPTIVE_BAYES_NETWORK
AIMODEL_JDM                    ATTRIBUTE_IMPORTANCE           MINIMUM_DESCRIPTION_LENGTH
AI_SH_SAMPLE                   ATTRIBUTE_IMPORTANCE           MINIMUM_DESCRIPTION_LENGTH
APMODEL_JDM                    CLASSIFICATION                 NAIVE_BAYES
ARMODEL_JDM                    ASSOCIATION_RULES              APRIORI_ASSOCIATION_RULES
AR_SH_SAMPLE                   ASSOCIATION_RULES              APRIORI_ASSOCIATION_RULES
AR_SH_SAMPLE_STR_XNAL          ASSOCIATION_RULES              APRIORI_ASSOCIATION_RULES
AR_SH_SAMPLE_XNAL_SVAL         ASSOCIATION_RULES              APRIORI_ASSOCIATION_RULES
DT_SH_CLAS_SAMPLE              CLASSIFICATION                 DECISION_TREE
GLMCMODEL_JDM                  CLASSIFICATION                 GENERALIZED_LINEAR_MODEL
GLMC_SH_CLAS_SAMPLE            CLASSIFICATION                 GENERALIZED_LINEAR_MODEL
GLMRMODEL_JDM                  REGRESSION                     GENERALIZED_LINEAR_MODEL
GLMR_SH_REGR_SAMPLE            REGRESSION                     GENERALIZED_LINEAR_MODEL
KMMODEL_JDM                    CLUSTERING                     KMEANS
KM_SH_CLUS_SAMPLE              CLUSTERING                     KMEANS
NBEXPIMPMODEL_JDM              CLASSIFICATION                 NAIVE_BAYES
NBMODEL_JDM                    CLASSIFICATION                 NAIVE_BAYES
NB_SH_CLAS_SAMPLE              CLASSIFICATION                 NAIVE_BAYES
NMFMODEL_JDM                   FEATURE_EXTRACTION             NONNEGATIVE_MATRIX_FACTOR
NMF_SH_SAMPLE                  FEATURE_EXTRACTION             NONNEGATIVE_MATRIX_FACTOR
OCMODEL_JDM                    CLUSTERING                     O_CLUSTER
OC_SH_CLUS_SAMPLE              CLUSTERING                     O_CLUSTER
SVMCMODEL_JDM                  CLASSIFICATION                 SUPPORT_VECTOR_MACHINES
SVMC_SH_CLAS_SAMPLE            CLASSIFICATION                 SUPPORT_VECTOR_MACHINES
SVMOMODEL_JDM                  CLASSIFICATION                 SUPPORT_VECTOR_MACHINES
SVMO_SH_CLAS_SAMPLE            CLASSIFICATION                 SUPPORT_VECTOR_MACHINES
SVMRMODEL_JDM                  REGRESSION                     SUPPORT_VECTOR_MACHINES
SVMR_SH_REGR_SAMPLE            REGRESSION                     SUPPORT_VECTOR_MACHINES
TREEMODEL_JDM                  CLASSIFICATION                 DECISION_TREE
TXTNMFMODEL_JDM                FEATURE_EXTRACTION             NONNEGATIVE_MATRIX_FACTOR
TXTSVMMODEL_JDM                CLASSIFICATION                 SUPPORT_VECTOR_MACHINES
T_NMF_SAMPLE                   FEATURE_EXTRACTION             NONNEGATIVE_MATRIX_FACTOR
T_SVM_CLAS_SAMPLE              CLASSIFICATION                 SUPPORT_VECTOR_MACHINES

The model names distinguish the models created by the Java programs from those created by the PL/SQL programs. The models created by the Java programs have "_JDM" appended to the name.

PL/SQL Programs: Algorithms

The PL/SQL programs are presented by algorithm in Table 7-1.

PL/SQL Programs: Mining Functions

The PL/SQL sample programs are presented by mining function in Table 7-2. For detailed descriptions of the sample programs, see the comments in the source code.

PL/SQL Text Mining Programs

Three sample programs illustrate the process of text mining using PL/SQL. One program illustrates the preprocessing that is required to transform the text for mining. The other two programs build models that use the transformed text.

The PL/SQL sample text mining programs are:

• dmtxtfe.sql — Illustrates the process of term extraction that prepares text for mining

• dmtxtnmf.sql — Creates a text mining model using the Non-Negative Matrix Factorization algorithm

• dmtxtsvm.sql — Creates a text mining model using SVM classification

Java Programs: Algorithms

The Java programs are presented by algorithm in Table 7-3.

Java Programs: Mining Functions

The Java sample programs are presented by mining function in Table 7-4. For detailed descriptions of the sample programs, see the comments in the source code.

Java Text Mining Programs

Two Java programs illustrate the process of text mining. One builds a feature extraction model, the other builds a classification model.

The Java text mining programs both use the dmtxtnmfdemo.java interface to transform the text for mining. The programs are as follows:

• dmtxtnmf.sql — Creates a text mining model using the Non-Negative Matrix Factorization algorithm

• dmtxtsvmdemo.java — Creates a text mining model using SVM classification

Customer Data for Data Mining

Views in the data mining user's schema define columns of data from the CUSTOMERS, SALES, PRODUCTS, COUNTRIES, and SUPPLEMENTARY_DEMOGRAPHICS tables in the SH schema. You can list these views with the following SQL statements.

SQL>CONNECT dmuser
Enter password: password
SQL>SELECT view_name FROM user_views;

The views are listed in Table 7-5.

You can see the references to tables in SH by listing the view definitions. The definition of the view MINING_DATA_BUILD_V is shown as follows.

SQL> set long 1000000
SQL> set longc 100000
SQL> set pagesize 100
SQL> SELECT text FROM all_views WHERE 
    owner='dmuser3'AND view_name='mining_data_build_v';

      SELECT a.CUST_ID, a.CUST_GENDER, 2003-a.CUST_YEAR_OF_BIRTH AGE, 
             a.CUST_MARITAL_STATUS, c.COUNTRY_NAME, a.CUST_INCOME_LEVEL,
             b.EDUCATION, b.OCCUPATION, b.HOUSEHOLD_SIZE, b.YRS_RESIDENCE,
             b.AFFINITY_CARD, b.BULK_PACK_DISKETTES, b.FLAT_PANEL_MONITOR,
             b.HOME_THEATER_PACKAGE, b.BOOKKEEPING_APPLICATION, 
             b.PRINTER_SUPPLIES, b.Y_BOX_GAMES, b.OS_DOC_SET_KANJI 
       FROM  sh.customers a, 
             sh.supplementary_demographics b, 
             sh.countries c 
       WHERE a.CUST_ID = b.CUST_ID AND a.country_id = c.country_id 
             AND a.cust_id between 101501 and 103000 

The views are used to build, test, and score the sample models. Each view has a CUSTOMER_ID column, which is the case ID, and an AFFINITY_CARD column, which is the target used by the predictive models. Most of the views provide data for 1500 customers (1500 rows). The view used by the One-Class SVM model has data for 940 customers.

The columns of training data in the MINING_DATA_BUILD_V view are as follows.

SQL> DESCRIBE mining_data_build_v

CUST_ID                    NOT NULL            NUMBER
CUST_GENDER                NOT NULL            CHAR(1)
AGE                                            NUMBER
CUST_MARITAL_STATUS                            VARCHAR2(20)
COUNTRY_NAME               NOT NULL            VARCHAR2(40)
CUST_INCOME_LEVEL                              VARCHAR2(30)
EDUCATION                                      VARCHAR2(21)
OCCUPATION                                     VARCHAR2(21)
HOUSEHOLD_SIZE                                 VARCHAR2(21)
YRS_RESIDENCE                                  NUMBER
AFFINITY_CARD                                  NUMBER(10)
BULK_PACK_DISKETTES                            NUMBER(10)
FLAT_PANEL_MONITOR                             NUMBER(10)
HOME_THEATER_PACKAGE                           NUMBER(10)
BOOKKEEPING_APPLICATION                        NUMBER(10)
PRINTER_SUPPLIES                               NUMBER(10)
Y_BOX_GAMES                                    NUMBER(10)
OS_DOC_SET_KANJI                               NUMBER(10)

Market Basket Data for Association Rules

The association demos use the MARKET_BASKET_V data set, which includes columns of products from the PRODUCTS table and the CUSTOMER_ID column from the CUSTOMERS table in SH. The columns of the MARKET_BASKET_V view are as follows.

SQL> DESCRIBE market_basket_v

CUST_ID                     NOT NULL         NUMBER
EXTENSION_CABLE                              NUMBER
FLAT_PANEL_MONITOR                           NUMBER
CD_RW_HIGH_SPEED_5_PACK                      NUMBER
ENVOY_256MB_40GB                             NUMBER
ENVOY_AMBASSADOR                             NUMBER
EXTERNAL_8X_CD_ROM                           NUMBER
KEYBOARD_WRIST_REST                          NUMBER
SM26273_BLACK_INK_CARTRIDGE                  NUMBER
MOUSE_PAD                                    NUMBER
MULTIMEDIA_SPEAKERS_3INCH                    NUMBER
OS_DOC_SET_ENGLISH                           NUMBER
SIMM_16MB_PCMCIAII_CARD                      NUMBER
STANDARD_MOUSE                               NUMBER 

Customer Data for Text Mining

The text mining demos use the same customer data from tables in SH, but they include either an extra text column or a collection type column. The collection type is a nested table of type DM_NESTED_NUMERICALS.

You can list these tables with the following SQL statements.

SQL>CONNECT dmuser
Enter password: password
SQL>SELECT table_name FROM user_tables WHERE table_name LIKE '%MINING%';

The text mining tables are listed in Table 7-6.

In the MINING_BUILD_TEXT, MINING_TEST_TEXT, and MINING_APPLY_TEXT tables, the COMMENTS column is of type VARCHAR2(4000).

SQL> DESCRIBE mining_build_text
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUST_ID                                   NOT NULL NUMBER
 CUST_GENDER                               NOT NULL CHAR(1)
 AGE                                                NUMBER
 CUST_MARITAL_STATUS                                VARCHAR2(20)
 COUNTRY_NAME                              NOT NULL VARCHAR2(40)
 CUST_INCOME_LEVEL                                  VARCHAR2(30)
 EDUCATION                                          VARCHAR2(21)
 OCCUPATION                                         VARCHAR2(21)
 HOUSEHOLD_SIZE                                     VARCHAR2(21)
 YRS_RESIDENCE                                      NUMBER
 AFFINITY_CARD                                      NUMBER(10)
 BULK_PACK_DISKETTES                                NUMBER(10)
 FLAT_PANEL_MONITOR                                 NUMBER(10)
 HOME_THEATER_PACKAGE                               NUMBER(10)
 BOOKKEEPING_APPLICATION                            NUMBER(10)
 PRINTER_SUPPLIES                                   NUMBER(10)
 Y_BOX_GAMES                                        NUMBER(10)
 OS_DOC_SET_KANJI                                   NUMBER(10)
 COMMENTS                                           VARCHAR2(4000)

In the MINING_*_NESTED_TEXT tables, the COMMENTS column is of type DM_NESTED_NUMERICALS.

SQL> DESCRIBE mining_build_nested_text
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUST_ID                                   NOT NULL NUMBER
 CUST_GENDER                               NOT NULL CHAR(1)
 AGE                                                NUMBER
 CUST_MARITAL_STATUS                                VARCHAR2(20)
 COUNTRY_NAME                              NOT NULL VARCHAR2(40)
 CUST_INCOME_LEVEL                                  VARCHAR2(30)
 EDUCATION                                          VARCHAR2(21)
 OCCUPATION                                         VARCHAR2(21)
 HOUSEHOLD_SIZE                                     VARCHAR2(21)
 YRS_RESIDENCE                                      NUMBER
 AFFINITY_CARD                                      NUMBER(10)
 BULK_PACK_DISKETTES                                NUMBER(10)
 FLAT_PANEL_MONITOR                                 NUMBER(10)
 HOME_THEATER_PACKAGE                               NUMBER(10)
 BOOKKEEPING_APPLICATION                            NUMBER(10)
 PRINTER_SUPPLIES                                   NUMBER(10)
 Y_BOX_GAMES                                        NUMBER(10)
 OS_DOC_SET_KANJI                                   NUMBER(10)
 COMMENTS                                           DM_NESTED_NUMERICALS