23 Preparing the Data
Learn how to create a table or view that can be used to build a model.
23.1 Data Requirements
Understand how data is stored and viewed for data mining.
Data mining activities require data that is defined within a single table or view. The information for each record must be stored in a separate row. The data records are commonly called cases. Each case can optionally be identified by a unique case ID. The table or view itself can be referred to as a case table.
The CUSTOMERS table in the SH schema is an example of a table that could be used for mining. All the information for each customer is contained in a single row. The case ID is the CUST_ID column. The rows listed in the following example are selected from SH.CUSTOMERS.
Note:
Oracle Data Mining requires single-record case data for all types of models except association models, which can be built on native transactional data.
Example 23-1 Sample Case Table
SQL> select cust_id, cust_gender, cust_year_of_birth,
cust_main_phone_number from sh.customers where cust_id < 11;
CUST_ID CUST_GENDER CUST_YEAR_OF_BIRTH CUST_MAIN_PHONE_NUMBER
------- ----------- ---- ------------- -------------------------
1 M 1946 127-379-8954
2 F 1957 680-327-1419
3 M 1939 115-509-3391
4 M 1934 577-104-2792
5 M 1969 563-667-7731
6 F 1925 682-732-7260
7 F 1986 648-272-6181
8 F 1964 234-693-8728
9 F 1936 697-702-2618
10 F 1947 601-207-4099Related Topics
23.1.1 Column Data Types
Understand the different types of column data in a case table.
The columns of the case table hold the attributes that describe each case. In Example 23-1, the attributes are: CUST_GENDER, CUST_YEAR_OF_BIRTH, and CUST_MAIN_PHONE_NUMBER. The attributes are the predictors in a supervised model or the descriptors in an unsupervised model. The case ID, CUST_ID, can be viewed as a special attribute; it is not a predictor or a descriptor.
Oracle Data Mining supports standard Oracle data types as well as the following collection types:
DM_NESTED_CATEGORICALSDM_NESTED_NUMERICALSDM_NESTED_BINARY_DOUBLESDM_NESTED_BINARY_FLOATS
23.1.2 Data Sets for Classification and Regression
Understand how data sets are used for training and testing the model.
You need two case tables to build and validate classification and regression models. One set of rows is used for training the model, another set of rows is used for testing the model. It is often convenient to derive the build data and test data from the same data set. For example, you could randomly select 60% of the rows for training the model; the remaining 40% could be used for testing the model.
Models that implement other mining functions, such as attribute importance, clustering, association, or feature extraction, do not use separate test data.
23.1.3 Scoring Requirements
Most data mining models can be applied to separate data in a process known as scoring. Oracle Data Mining supports the scoring operation for classification, regression, anomaly detection, clustering, and feature extraction.
The scoring process matches column names in the scoring data with the names of the columns that were used to build the model. The scoring process does not require all the columns to be present in the scoring data. If the data types do not match, Oracle Data Mining attempts to perform type coercion. For example, if a column called PRODUCT_RATING is VARCHAR2 in the training data but NUMBER in the scoring data, Oracle Data Mining effectively applies a TO_CHAR() function to convert it.
The column in the test or scoring data must undergo the same transformations as the corresponding column in the build data. For example, if the AGE column in the build data was transformed from numbers to the values CHILD, ADULT, and SENIOR, then the AGE column in the scoring data must undergo the same transformation so that the model can properly evaluate it.
Note:
Oracle Data Mining can embed user-specified transformation instructions in the model and reapply them whenever the model is applied. When the transformation instructions are embedded in the model, you do not need to specify them for the test or scoring data sets.
Oracle Data Mining also supports Automatic Data Preparation (ADP). When ADP is enabled, the transformations required by the algorithm are performed automatically and embedded in the model along with any user-specified transformations.
See Also:
Transforming the Data for more information on automatic and embedded data transformations
23.2 About Attributes
Attributes are the items of data that are used in data mining. In predictive models, attributes are the predictors that affect a given outcome. In descriptive models, attributes are the items of information being analyzed for natural groupings or associations. For example, a table of employee data that contains attributes such as job title, date of hire, salary, age, gender, and so on.
23.2.1 Data Attributes and Model Attributes
Data attributes are columns in the data set used to build, test, or score a model. Model attributes are the data representations used internally by the model.
Data attributes and model attributes can be the same. For example, a column called SIZE, with values S, M, and L, are attributes used by an algorithm to build a model. Internally, the model attribute SIZE is most likely be the same as the data attribute from which it was derived.
On the other hand, a nested column SALES_PROD, containing the sales figures for a group of products, does not correspond to a model attribute. The data attribute can be SALES_PROD, but each product with its corresponding sales figure (each row in the nested column) is a model attribute.
Transformations also cause a discrepancy between data attributes and model attributes. For example, a transformation can apply a calculation to two data attributes and store the result in a new attribute. The new attribute is a model attribute that has no corresponding data attribute. Other transformations such as binning, normalization, and outlier treatment, cause the model's representation of an attribute to be different from the data attribute in the case table.
Related Topics
See Also:
23.2.2 Target Attribute
Understand what a target means in data mining and understand the different target data types.
The target of a supervised model is a special kind of attribute. The target column in the training data contains the historical values used to train the model. The target column in the test data contains the historical values to which the predictions are compared. The act of scoring produces a prediction for the target.
Clustering, Feature Extraction, Association, and Anomaly Detection models do not use a target.
Nested columns and columns of unstructured data (such as BFILE, CLOB, or BLOB) cannot be used as targets. Target attributes must have a simple data type.
Table 23-1 Target Data Types
| Mining Function | Target Data Types |
|---|---|
|
|
|
|
|
You can query the *_MINING_MODEL_ATTRIBUTES view to find the target for a given model.
Related Topics
23.2.3 Numericals, Categoricals, and Unstructured Text
Explains numeric, categorical, and unstructured text attributes.
Model attributes are numerical, categorical, or unstructured (text). Data attributes, which are columns in a case table, have Oracle data types, as described in "Column Data Types".
Numerical attributes can theoretically have an infinite number of values. The values have an implicit order, and the differences between them are also ordered. Oracle Data Mining interprets NUMBER, FLOAT, BINARY_DOUBLE, BINARY_FLOAT, DM_NESTED_NUMERICALS, DM_NESTED_BINARY_DOUBLES, and DM_NESTED_BINARY_FLOATS as numerical.
Categorical attributes have values that identify a finite number of discrete categories or classes. There is no implicit order associated with the values. Some categoricals are binary: they have only two possible values, such as yes or no, or male or female. Other categoricals are multi-class: they have more than two values, such as small, medium, and large.
Oracle Data Mining interprets CHAR and VARCHAR2 as categorical by default, however these columns may also be identified as columns of unstructured data (text). Oracle Data Mining interprets columns of DM_NESTED_CATEGORICALS as categorical. Columns of CLOB, BLOB, and BFILE always contain unstructured data.
The target of a classification model is categorical. (If the target of a classification model is numeric, it is interpreted as categorical.) The target of a regression model is numerical. The target of an attribute importance model is either categorical or numerical.
Related Topics
23.2.4 Model Signature
The model signature is the set of data attributes that are used to build a model. Some or all of the attributes in the signature must be present for scoring. The model accounts for any missing columns on a best-effort basis. If columns with the same names but different data types are present, the model attempts to convert the data type. If extra, unused columns are present, they are disregarded.
The model signature does not necessarily include all the columns in the build data. Algorithm-specific criteria can cause the model to ignore certain columns. Other columns can be eliminated by transformations. Only the data attributes actually used to build the model are included in the signature.
The target and case ID columns are not included in the signature.
23.2.5 Scoping of Model Attribute Name
The model attribute name consists of two parts: a column name, and a subcolumn name.
column_name[.subcolumn_name]
The column_name component is the name of the data attribute. It is present in all model attribute names. Nested attributes and text attributes also have a subcolumn_name component as shown in the following example.
Example 23-2 Model Attributes Derived from a Nested Column
The nested column SALESPROD has three rows.
SALESPROD(ATTRIBUTE_NAME, VALUE) -------------------------------- ((PROD1, 300), (PROD2, 245), (PROD3, 679))
The name of the data attribute is SALESPROD. Its associated model attributes are:
SALESPROD.PROD1 SALESPROD.PROD2 SALESPROD.PROD3
23.2.6 Model Details
Model details reveal information about model attributes and their treatment by the algorithm. There is a separate GET_MODEL_DETAILS routine for each algorithm. Oracle recommends that users leverage the model detail views instead.
Transformation and reverse transformation expressions are associated with model attributes. Transformations are applied to the data attributes before the algorithmic processing that creates the model. Reverse transformations are applied to the model attributes after the model has been built, so that the model details are expressed in the form of the original data attributes, or as close to it as possible.
Reverse transformations support model transparency. They provide a view of the data that the algorithm is working with internally but in a format that is meaningful to a user.
Example 23-3 shows the definition of the GET_MODEL_DETAILS function for an Attribute Importance model.
Example 23-3 Model Details for an Attribute Importance Model
The syntax of the GET_MODEL_DETAILS function for Attribute Importance models is shown as follows.
DBMS_DATA_MINING.GET_MODEL_DETAILS_AI (
model_name VARCHAR2)
RETURN DM_RANKED_ATTRIBUTES PIPELINED;
The function returns DM_RANKED_ATTRIBUTES, a virtual table. The columns are the model details. There is one row for each model attribute in the specified model. The columns are described as follows.
attribute_name VARCHAR2(4000) attribute_subname VARCHAR2(4000) importance_value NUMBER rank NUMBER(38)
23.3 Using Nested Data
A join between the tables for one-to-many relationship is represented through nested columns.
Oracle Data Mining requires a case table in single-record case format, with each record in a separate row. What if some or all of your data is in multi-record case format, with each record in several rows? What if you want one attribute to represent a series or collection of values, such as a student's test scores or the products purchased by a customer?
This kind of one-to-many relationship is usually implemented as a join between tables. For example, you can join your customer table to a sales table and thus associate a list of products purchased with each customer.
Oracle Data Mining supports dimensioned data through nested columns. To include dimensioned data in your case table, create a view and cast the joined data to one of the Data Mining nested table types. Each row in the nested column consists of an attribute name/value pair. Oracle Data Mining internally processes each nested row as a separate attribute.
Related Topics
23.3.1 Nested Object Types
Nested tables are object data types that can be used in place of other data types.
Oracle Database supports user-defined data types that make it possible to model real-world entities as objects in the database. Collection types are object data types for modeling multi-valued attributes. Nested tables are collection types. Nested tables can be used anywhere that other data types can be used.
Oracle Data Mining supports the following nested object types:
DM_NESTED_BINARY_DOUBLESDM_NESTED_BINARY_FLOATSDM_NESTED_NUMERICALSDM_NESTED_CATEGORICALS
Descriptions of the nested types are provided in this example.
Example 23-4 Oracle Data Mining Nested Data Types
describe dm_nested_binary_double Name Null? Type ----------------------------------------- -------- ---------------------------- ATTRIBUTE_NAME VARCHAR2(4000) VALUE BINARY_DOUBLE describe dm_nested_binary_doubles DM_NESTED_BINARY_DOUBLES TABLE OF SYS.DM_NESTED_BINARY_DOUBLE Name Null? Type ------------------------------------------ -------- --------------------------- ATTRIBUTE_NAME VARCHAR2(4000) VALUE BINARY_DOUBLE describedm_nested_binary_floatName Null? Type ----------------------------------------- -------- --------------------------- ATTRIBUTE_NAME VARCHAR2(4000) VALUE BINARY_FLOAT describedm_nested_binary_floatsDM_NESTED_BINARY_FLOATS TABLE OF SYS.DM_NESTED_BINARY_FLOAT Name Null? Type ----------------------------------------- -------- ---------------------------- ATTRIBUTE_NAME VARCHAR2(4000) VALUE BINARY_FLOAT describedm_nested_numericalName Null? Type ----------------------------------------- -------- ---------------------------- ATTRIBUTE_NAME VARCHAR2(4000) VALUE NUMBER describedm_nested_numericalsDM_NESTED_NUMERICALS TABLE OF SYS.DM_NESTED_NUMERICAL Name Null? Type ----------------------------------------- -------- ---------------------------- ATTRIBUTE_NAME VARCHAR2(4000) VALUE NUMBER describedm_nested_categoricalName Null? Type ----------------------------------------- -------- ---------------------------- ATTRIBUTE_NAME VARCHAR2(4000) VALUE VARCHAR2(4000) describedm_nested_categoricalsDM_NESTED_CATEGORICALS TABLE OF SYS.DM_NESTED_CATEGORICAL Name Null? Type ----------------------------------------- -------- ---------------------------- ATTRIBUTE_NAME VARCHAR2(4000) VALUE VARCHAR2(4000)
Related Topics
23.3.2 Example: Transforming Transactional Data for Mining
Example 23-5 shows data from a view of a sales table. It includes sales for three of the many products sold in four regions. This data is not suitable for mining at the product level because sales for each case (product), is stored in several rows.
Example 23-6 shows how this data can be transformed for mining. The case ID column is PRODUCT. SALES_PER_REGION, a nested column of type DM_NESTED_NUMERICALS, is a data attribute. This table is suitable for mining at the product case level, because the information for each case is stored in a single row.
Note:
The presentation in this example is conceptual only. The data is not actually pivoted before being processed.
Example 23-5 Product Sales per Region in Multi-Record Case Format
PRODUCT REGION SALES ------- -------- ---------- Prod1 NE 556432 Prod2 NE 670155 Prod3 NE 3111 . . Prod1 NW 90887 Prod2 NW 100999 Prod3 NW 750437 . . Prod1 SE 82153 Prod2 SE 57322 Prod3 SE 28938 . . Prod1 SW 3297551 Prod2 SW 4972019 Prod3 SW 884923 . .
Example 23-6 Product Sales per Region in Single-Record Case Format
PRODUCT SALES_PER_REGION
(ATTRIBUTE_NAME, VALUE)
------ --------------------------
Prod1 ('NE' , 556432)
('NW' , 90887)
('SE' , 82153)
('SW' , 3297551)
Prod2 ('NE' , 670155)
('NW' , 100999)
('SE' , 57322)
('SW' , 4972019)
Prod3 ('NE' , 3111)
('NW' , 750437)
('SE' , 28938)
('SW' , 884923)
.
.
Example 23-7 Model Attributes Derived From SALES_PER_REGION
PRODUCT SALES_PER_REGION.NE SALES_PER_REGION.NW SALES_PER_REGION.SE SALES_PER_REGION.SW ------- ------------------ ------------------- ------------------ ------------------- Prod1 556432 90887 82153 3297551 Prod2 670155 100999 57322 4972019 Prod3 3111 750437 28938 884923 . .
23.4 Using Market Basket Data
Market basket data identifies the items sold in a set of baskets or transactions. Oracle Data Mining provides the association mining function for market basket analysis.
Association models use the Apriori algorithm to generate association rules that describe how items tend to be purchased in groups. For example, an association rule can assert that people who buy peanut butter are 80% likely to also buy jelly.
Market basket data is usually transactional. In transactional data, a case is a transaction and the data for a transaction is stored in multiple rows. Oracle Data Mining association models can be built on transactional data or on single-record case data. The ODMS_ITEM_ID_COLUMN_NAME and ODMS_ITEM_VALUE_COLUMN_NAME settings specify whether the data for association rules is in transactional format.
Note:
Association models are the only type of model that can be built on native transactional data. For all other types of models, Oracle Data Mining requires that the data be presented in single-record case format.
The Apriori algorithm assumes that the data is transactional and that it has many missing values. Apriori interprets all missing values as sparse data, and it has its own native mechanisms for handling sparse data.
See Also:
Oracle Database PL/SQL Packages and Types Reference for information on the ODMS_ITEM_ID_COLUMN_NAME and ODMS_ITEM_VALUE_COLUMN_NAME settings.
23.4.1 Example: Creating a Nested Column for Market Basket Analysis
The example shows how to define a nested column for market basket analysis.
Association models can be built on native transactional data or on nested data. The following example shows how to define a nested column for market basket analysis.
The following SQL statement transforms this data to a column of type DM_NESTED_NUMERICALS in a view called SALES_TRANS_CUST_NESTED. This view can be used as a case table for mining.
CREATE VIEW sales_trans_cust_nested AS
SELECT trans_id,
CAST(COLLECT(DM_NESTED_NUMERICAL(
prod_name, 1))
AS DM_NESTED_NUMERICALS) custprods
FROM sales_trans_cust
GROUP BY trans_id;
This query returns two rows from the transformed data.
SELECT * FROM sales_trans_cust_nested
WHERE trans_id < 101000
AND trans_id > 100997;
TRANS_ID CUSTPRODS(ATTRIBUTE_NAME, VALUE)
------- ------------------------------------------------
100998 DM_NESTED_NUMERICALS
(DM_NESTED_NUMERICAL('O/S Documentation Set - English', 1)
100999 DM_NESTED_NUMERICALS
(DM_NESTED_NUMERICAL('CD-RW, High Speed Pack of 5', 1),
DM_NESTED_NUMERICAL('External 8X CD-ROM', 1),
DM_NESTED_NUMERICAL('SIMM- 16MB PCMCIAII card', 1))Example 23-8 Convert to a Nested Column
The view SALES_TRANS_CUST provides a list of transaction IDs to identify each market basket and a list of the products in each basket.
describe sales_trans_cust Name Null? Type ----------------------------------------------------- -------- ---------------- TRANS_ID NOT NULL NUMBER PROD_NAME NOT NULL VARCHAR2(50) QUANTITY NUMBER
Related Topics
23.5 Using Retail Analysis Data
Retail analysis often makes use of Association Rules and Association models.
The Association Rules are enhanced to calculate aggregates along with rules or itemsets.
Related Topics
23.6 Handling Missing Values
Oracle Data Mining distinguishes between sparse data and data that contains random missing values. The latter means that some attribute values are unknown. Sparse data, on the other hand, contains values that are assumed to be known, although they are not represented in the data.
A typical example of sparse data is market basket data. Out of hundreds or thousands of available items, only a few are present in an individual case (the basket or transaction). All the item values are known, but they are not all included in the basket. Present values have a quantity, while the items that are not represented are sparse (with a known quantity of zero).
Oracle Data Mining interprets missing data as follows:
-
Missing at random: Missing values in columns with a simple data type (not nested) are assumed to be missing at random.
-
Sparse: Missing values in nested columns indicate sparsity.
23.6.1 Examples: Missing Values or Sparse Data?
The examples in this section illustrate how Oracle Data Mining identifies data as either sparse or missing at random.
23.6.1.1 Sparsity in a Sales Table
A sales table contains point-of-sale data for a group of products that are sold in several stores to different customers over a period of time. A particular customer buys only a few of the products. The products that the customer does not buy do not appear as rows in the sales table.
If you were to figure out the amount of money a customer has spent for each product, the unpurchased products have an inferred amount of zero. The value is not random or unknown; it is zero, even though no row appears in the table.
Note that the sales data is dimensioned (by product, stores, customers, and time) and are often represented as nested data for mining.
Since missing values in a nested column always indicate sparsity, you must ensure that this interpretation is appropriate for the data that you want to mine. For example, when trying to mine a multi-record case data set containing movie ratings from users of a large movie database, the missing ratings are unknown (missing at random), but Oracle Data Mining treats the data as sparse and infer a rating of zero for the missing value.
23.6.1.2 Missing Values in a Table of Customer Data
A table of customer data contains demographic data about customers. The case ID column is the customer ID. The attributes are age, education, profession, gender, house-hold size, and so on. Not all the data is available for each customer. Any missing values are considered to be missing at random. For example, if the age of customer 1 and the profession of customer 2 are not present in the data, that information is simply unknown. It does not indicate sparsity.
Note that the customer data is not dimensioned. There is a one-to-one mapping between the case and each of its attributes. None of the attributes are nested.
23.6.2 Missing Value Treatment in Oracle Data Mining
Missing value treatment depends on the algorithm and on the nature of the data (categorical or numerical, sparse or missing at random). Missing value treatment is summarized in the following table.
Note:
Oracle Data Mining performs the same missing value treatment whether or not Automatic Data Preparation is being used.
Table 23-2 Missing Value Treatment by Algorithm
23.6.3 Changing the Missing Value Treatment
Transform the missing data as sparse or missing at random.
If you want Oracle Data Mining to treat missing data as sparse instead of missing at random or missing at random instead of sparse, transform it before building the model.
If you want missing values to be treated as sparse, but Oracle Data Mining interprets them as missing at random, you can use a SQL function like NVL to replace the nulls with a value such as "NA". Oracle Data Mining does not perform missing value treatment when there is a specified value.
If you want missing nested attributes to be treated as missing at random, you can transform the nested rows into physical attributes in separate columns — as long as the case table stays within the 1000 column limitation imposed by the Database. Fill in all of the possible attribute names, and specify them as null. Alternatively, insert rows in the nested column for all the items that are not present and assign a value such as the mean or mode to each one.
Related Topics