2 Managing Data

2.1 Data Types

The input to ODM is a table or a view. The columns can have numeric or character data types: NUMBER, FLOAT, VARCHAR2, or CHAR.

2.2 Columns and Attributes

ODM interprets the columns of the input table as attributes for data mining. Attributes are the predictors or descriptors on which the model is based.

A model may additionally identify a case ID column, a target column, or both.

• Case ID

  A case ID column holds a unique identifier for each record (row) of data. The case ID must be specified at model build time for all algorithms except Decision Tree. If a case ID is present in a Decision Tree model, it is not considered a possible predictor.

  In the PL/SQL API and Java APIs, the case ID must be specified at apply time for all algorithms. The SQL scoring functions do not use a case ID.

  The case ID column can be of type VARCHAR2, CHAR, or NUMBER, and its maximum length is 128 bytes.

• Target

  Predictive algorithms (Classification, Regression, and Attribute Importance) require that one column be designated as a target. The name of the target column is supplied as an argument when the model is created. The target column holds the predictions generated by the model. The target column must be of type VARCHAR2, CHAR, NUMBER, or FLOAT. SVM Regression supports only numeric targets. One-Class SVM does not use a target.

2.3 Nested Tables

ODM accepts data in single-record case format, where all the information (attributes) concerning an individual is contained in one row. Single-record case, also known as non-transactional format, is illustrated in the table in Example 2-1. This table contains descriptive information about customers. CUSTOMER_ID is the case ID column.

CUSTOMER_ID      GENDER      AGE     MARITAL_STATUS
------------     ------      ---     --------------
1                Male        30       Married
2                Female      35       Single
3                Male        21       Single
4                Female      20       Single
5                Female      35       Married

Sometimes data is organized in multi-record case, also known as transactional, format. A typical example is market basket data. In transactional format, the data pertaining to an individual is distributed across multiple records. The table in Example 2-2 illustrates transactional format. This table contains information about products purchased by a group of customers on a given day. CUSTOMER_ID is the case ID column.

CUST_ID    PROD_ID  PROD_NAME 
--------   -------  --------- 
1          524      brand x icecream
1          530      brand y frozen dinners
1          109      brand z dog food     
2          578      brand a orange juice 
2          191      brand x frozen dinners

ODM does not support multi-record case format. However, there could be circumstances in which you want to construct a model using transactional data. For example, you might want to use transactional data like that in Example 2-2 to predict the products that each customer is likely to buy on his next visit to the store. Discount coupons for these or similar products could then be generated with the checkout receipt.

If you want to construct a model using transactional data, you must first convert the data to single-record case. You must do this by defining columns of nested tables using the ODM fixed collection types, DM_NESTED_NUMERICALS and DM_NESTED_CATEGORICALS. These types define collections of numerical attributes and categorical attributes respectively. The data type descriptions are shown as follows.

SQL> describe dm_nested_numerical
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ATTRIBUTE_NAME                                     VARCHAR2(30)
 VALUE                                              NUMBER
 
SQL> describe dm_nested_numericals
 DM_NESTED_NUMERICALS TABLE OF DMSYS.DM_NESTED_NUMERICAL
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ATTRIBUTE_NAME                                     VARCHAR2(30)
 VALUE                                              NUMBER
 
SQL> describe dm_nested_categorical
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ATTRIBUTE_NAME                                     VARCHAR2(30)
 VALUE                                              VARCHAR2(4000)
 
SQL> describe dm_nested_categoricals
 DM_NESTED_CATEGORICALS TABLE OF DMSYS.DM_NESTED_CATEGORICAL
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ATTRIBUTE_NAME                                     VARCHAR2(30)
 VALUE                                              VARCHAR2(4000)

For a given case identifier, attribute names must be unique across all the collections and individual columns. The fixed collection types enforce this requirement. However, the attribute naming requirements, described in "Attribute Names", do not apply to the attribute_name column of a nested table.

The attributes in Example 2-2 could be stored in nested table columns, as illustrated in Example 2-3. The column PRODUCT_IDENTIFIERS is of type DM_NESTED_NUMERICALS, and the column PRODUCT_NAMES is of type DM_NESTED_CATEGORICALS.

case_ID      NUMBER
gene         VARCHAR2(30)
expr         NUMBER

case_ID      NUMBER
name         VARCHAR2(30)
type         VARCHAR2(30)
subtype      VARCHAR2(30)
gender       CHAR(1)
age          NUMBER
status       VARCHAR2(30)

case_id      NUMBER
gender       CHAR(1)
age          NUMBER
gene_expr    DM_NESTED_NUMERICALS
status       VARCHAR2(30)

CREATE OR REPLACE VIEW gene_expr_build AS
SELECT C.case_id,
       C.gender,
       C.age,
       CAST(MULTISET(
       SELECT gene, expr
         FROM gene_expression_data
        WHERE case_id = C.case_id) AS DM_NESTED_NUMERICALS
       ) gene_expr,
       C.status
 FROM  clinical_data_table C

2.4 Data Storage Optimization

If there are a few hundred mining attributes and your application requires the attributes to be represented as columns in the same row of the table, data storage must be carefully designed.

For a table with several columns, the key question to consider is the (average) row length, not the number of columns. Having more than 255 columns in a table built with a smaller block size typically results in intrablock chaining.

Oracle stores multiple row pieces in the same block, but the overhead to maintain the column information is minimal as long as all row pieces fit in a single data block. If the rows don't fit in a single data block, you may consider using a larger database block size (or use multiple block sizes in the same database).