Example: Transforming Transactional Data for Mining

Example 3-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 3-6 shows how this data could be transformed for mining. The case ID column would be PRODUCT. SALES_PER_REGION, a nested column of type DM_NESTED_NUMERICALS, would be 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.

Oracle Data Mining treats each nested row as a separate model attribute, as shown in Example 3-7. (Note that the presentation in this example is conceptual only. The data is not actually pivoted before being processed.)

Example 3-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 3-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 3-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
.
.