Using Oracle Data Miner for Oracle Database 11g Release 2

Have access to or have Installed Oracle Database 11g Enterprise Edition, Release 11.2.x, with Data Mining Option.

Have access to or have installed Oracle SQL Developer, version 3.0, or later. Note: The Oracle Data Miner interface is included within SQL Developer.

Have access to or have installed the sample schema

Note: The sample schema has been installed for you.

Create a connection to the data mining schema.

First, in the Data Miner Navigator, right-click the Connection node select New Connection, as shown below.

In the New / Select Database Connection window, enter the connection information required to connect to your data mining schema.

In this example, you connect to a schema named "dmuser", using the following options:

• Connection Name: dmuser
• Username: dmuser
• Password: dmuser
• Connection Type: Basic
• Role: default
• Host Name (Enter the appropriate host name for your database server)
• JDBC Port: (Enter the apropriate port number)
• SID or Service name: (Enter the approriate value)

Second, click Test Connection to verify the database connection. Note: "Status: Success" should appear in the bottom left corner of the window.

Finally, click Connect.

To create a Project, right-click the connection that you just created and select New Project, as shown here:

In the Create Project window, enter a project name and then click OK.

Note: You may optionally enter a comment that describes the intentions for this project. This description can be modified at any time.

Result: The new project appears below the connection node.

Right-click your project (ABC Insurance) and select New Workflow from the menu.

Result: The Create Workflow window appears.

In the Create Workflow window, enter Targeting Best Customers as the name and click OK.

Result:

• In the middle of the SQL Developer window, an empty workflow canvas opens with the name that you specified.
• In addtion, the Component Palette tab of the Workflow Editor appears on the right-hand side of the interface (shown below with a red border).

The first element of any workflow is the source data. Here, you add a Data Source node to the workflow, and select the CUST_INSUR_LTV_SAMPLE table as the data source.

A. In the Component Palette, click the Data category. A list of data nodes appear, as shown here:

B. Drag and drop the Data Source node onto the Workflow pane.

Result: A Data Source node appears in the Workflow pane and the Define Data Source wizard opens.

Notes:

• Workspace node nameds and model names are generated automatically by Oracle Data Miner. In this example, the name "Data Source 1" is generated. You may not get exactly the same node and model names as shown in this lesson.
• You can change the name of any workspace node or model using the Property Inspector.

In Step 1 of the wizard:

A. Select CUST_INSUR_LTV_SAMPLE from the Available Tables/Views list, as shown here:

Note: You may use the two tabs in the bottom pane in the wizard to view and examine the selected table. The Columns tab displays information about the table structure, and the Data tab shows a subset of data from the selected table or view.

B. Click Next to continue.

In Step 2 of the wizard, you may remove specific columns from the table or view that you don't need in your data source. In our case, we'll keep all of the attributes that are defined in the table.

At the bottom of the wizard window, click Finish.

Result: As shown below, the data source node name is updated with the selected table name, and the properties associated with the node are displayed in the Property Inspector, located below the Component Palette pane.

Notes:

• You can resize nodes in the workflow canvas by entering or selecting a different value from the Zoom options.
• You can add descriptive information about any node by clicking the Details tab in the Property Inspector.

A. First, click on Models in the Component Palette to display the available list:

B. Then, drag the Classification node from the paleltte to the Workflow pane, like this:

C. Drop the node onto the workflow. After a moment, a "Class Build" node appears in the workflow:

Notes:

• As stated previously, a yellow exclamation mark on the border indicates that more information needs to be specified before the node is complete.
• In this case, two actions are required:
  • A link must be created between the source data node and the classification build node.
  • Two attributes should be specified for the classification build process.

First, link the data source node to the classification build node using the same technique described previously.

Result: the Edit Classification window appears.

Note: Notice that a red "X" indicator is displayed the Target field. This means that an attribute must be selected for this item.

In the Edit Classification window:

A. Select BUY_INSURANCE as the Target attribute.

B. Select CUSTOMER_ID as the Case Id attribute.

Notes:

• Although not required, it is advised that you define a Case Id to uniquely define each record. This helps with model repeatability and is consistent with good data mining practicies.
• As stated previously, all four algorithms for Classification modeling are selected by default. They will be automatically run unless you specify otherwise.

Optionally, you can modify specific settings for each of the algorithms by using the Advanced button.

A. Click Advanced at the bottom of the Edit Classification window to display the Advanced Settings window, as shown here:

Notes:

• The Advanced Settings window enables you to specify data usage, algorithm settings, and performance settings for each of the four classificaiton algorithms.
• You can also de-select (and re-select) any algorithm from this window.

B. Select the Support Vector Machine algorithm and click the Algorithm Settings tab.

C. Then, In the Kernel Function option, select Linear, as shown here:

Notes:

• We want to change this Supprt Vector Machine (SVM) algorithm setting from the system determined to Linear in order to ensure model transparancy for the user.
• Model transparancy refers to the ability of the model to communicate logic or rationale to the user.

D. Feel free to view any of the tabs for each algorithm, however do not modify any of the other default settings.

E. When you are done browsing, click OK to save the SVM algorithm setting close the Advanced Settings window.

Finally, click OK in the Edit Classification window to save your changes.

Result: The classification build node is ready to run.

Note: In the Models tab of the Properties Inspector, you can see the current status for each of the selected algorithms, as shown below:

Save the workflow by clicking the Save All icon in main toolbar.

Right-click the classification build node and select Run from the pop-up menu.

As before, the Workflow Jobs tab shows the status of the build. When the build is complete, the status column displays a green check mark.

In the workflow pane, the border of the build node displays a green gear turning as the build is in process. When complete, the border of the build node shows a green check mark, like this:

Note: After the build is complete, the workflow is automatically saved.

Once the build process is complete, you can view several pieces of information about the build using the property inspectory.

A. Select the classification build node in the workflow, and then choose the Models tab in the Property Inspector.

Notes:

• All four models have been succesfully built.
• The models all have the same target (BUY_INSURANCE) but use different algorithms.
• The source data is automatically divided into test data and build data.

Right-click the build node and select Compare from the menu.

Results: A Class Build display tab opens, showing a graphical comparison of the four models, as shown here:

Notes:

• The histogram colors that you see may be different then those shown in this example.
• The comparison results include five tabs: Performance, Preformance matrix, ROC, Lift, and Profit.
• The Performance tab provides numeric and graphical information for each model on Predictive Confidence, Average Accuracy, and Overall Accuracy.
• The Performance tab seems to indicate that the Decision Tree (DT) and Generalized Linear Model (GLM) are providing the highest confidence and accuracy results.

Select the Lift tab.

Notes:

• The Lift tab provides a graphical presentation showing lift for each model, a red line for the random model, and a vertical blue line for threshold.
  • Lift is a different type of model test. It is a measure of how “fast” the model finds the actual positive target values.
  • The Lift viewer compares lift results for the given target value in each model.
  • The Lift viewer displays Cumulative Positive Cases and Cumulative Lift.
• In the example above:
  • At the 20th quantile, the GLM model provides the greatest Cumulative Lift
  • However, starting at about the 52nd quantile, the Naive Bayes (NB) and Decision Tree models provide greater Cumulative Lift.
• You can move the Quanitile measure point line along the X axis of the graph by using the slider tool. The data in the Models pane at the bottom updates automatically as you move the slider left or right.

Next, select the Performance Matrix tab.

Note: The Performance Matrix shows that the GLM and DT models have a significantly higher Correct Prediction percentage than the other models, at over 80% each.

Compare the details for the GLM and DT models.

First, select the GLM model to view the Target Value Details for this model. Recall that the "Target Value" for each of the models is the BUY_INSURANCE attribute.

Notes: The GLM model indicates an 96.6% correct prediction outcome for customers that don't buy insurance, but only a 43.3% correct prediction outcome for customers that do buy insurance.

Next, select the DT model.

Notes: The DT model indicates an 80.9% correct prediction outcome for customers that don't buy insurance, and an 81.5% correct prediction outcome for customers that do buy insurance.

After considering the initial analysis, you decide to investigate the Decision Tree model more closely.

First, dismiss the Class Build - Compare Models tab, as shown here:

Back in the workflow canvas, right-click the Class Build node again, and select View Models > CLAS_DT_1_2 (Note: The name of your Decision Tree model may be different).

Result: A window opens that displays a graphical presentation of the Decision Tree.

In the interface provides several methods of viewing navigation:

• The Thumbnail tab provides a high level view of the entire tree. You can move the viewer box around within the Thumbnail tab to dynamically locate your view in the primary window.
• You can also use the scroll bars in the primary display window to select to a different location within the decision tree display.
• Finally, you can change the viewer percentage zoom in the primary display window to increase or descrease the size of viewable content.

For example, set the the primary viewer window for the decision tree to 75% zoom. This provides a wider view than 100%, but the content within the view is somewhat smaller.

At each level within the decision tree, an IF/THEN statement is executed. As each level within the tree is added, another condition is added to the statment.

Notes: Commonly, a decision tree model would show a much larger set of levels and also nodes within each level in the decision tree. However, the data set used for this lesson is significantly smaller than a normal data mining set, and therefore the decision tree is also small.

First, navigate to and select Node 3 in the tree.

Notes:

• Summary information about the particular node is shown in the box, and the IF/THEN statement rule appears in the Rule tab, as shown below.
• In this example, the first split is based on the BANK_FUNDS attribute, and the second split is based on the CHECKING_AMOUNT attribute.
• Node 3 indicates that if BANK_FUNDS are greater than 225.5, and CHECKING_AMOUNT is less than or equal to 167.5, then there is a 59% chance that the customer will buy insurance.
  

Next, select Node 4 in the tree.

Notes:

• Note that the splits in the Rule tab are the same, with the first split is based on the BANK_FUNDS attribute, and the second split is based on the CHECKING_AMOUNT attribute.
• Node 4 indicates that if BANK_FUNDS are less than 225.5, and CHECKING_AMOUNT is greater than 167.5, then there is a 83% chance that the customer will not buy insurance.

Close the Decision Tree display (CLAS_DT_1_2 tab in this example).

In the workflow, select the Class Build node and uncheck all of the models except for the DT model (using the Models tab of the Property Inspector), like this:

Next, create a new Data Source node in the workflow.

Note: Even though we are using the same table as the "Apply" data source, you must still add a second data source node to the workflow.

A. From the Data list in the Component Palette, drag and drop the Data Source node to the workflow canvas, like this:

B. In the Define Data Source wizard, select the CUST_INSUR_LTV_SAMPLE table, and then click FINISH.

Result: A new data souce node appears on the workflow canvas.

C. Select the new data source node, and using the Details tab of the Property Inspector, change the Node Name to CUST_INSUR_LTV_APPLY, like this:

Result: The new table name is reflected in workflow.

Next, open the Evaluate and Apply list in the Components Palette, like this:

A. Drag and drop the Apply node to the workflow canvas, like this:

Result: An Apply node is added to the workflow with a yellow exclamation mark in its border. This, of course, indicates that more information is required before this node may be run.

B. Using the Details tab of the Property Inspectory, rename the Apply node to APPLY MODEL.

Using the techniques described previously, connect the Class Build node to the APPLY MODEL node, like this:

Then, connect the CUST_INSUR_LTV_APPLY node to the APPLY MODEL node:

Notes:

• The yellow exclamation mark disappears from the Apply node border once the second link is completed.
• This indicates that the node is ready to be run.

Before you execute the apply model node, consider the resulting output. By default, an apply node creates two columns of information for each customer:

• The prediction (Yes or No)
• The probability of the prediction

However, you really want to know this information for each customer, so that you can readily associate the preditive information with a given customer.

To get this information, you need to add a third column to the apply output: CUSTOMER_ID. Follow these instructions to add the customer id to the output:

A. Right-click the APPLY MODEL node and select Edit.

Result: The Edit Apply Details window appears. Notice that the Prediction and Probability columns are defined automatically in Apply Columns tab.

B. In the Edit Apply Details window, select the Data Columns tab, and then click the green "+" sign.

C.In the Edit Output Data Column Dialog: select CUSTOMER_ID in the Available Attributes list; move it to the Selected Attributes List using the shuttle control; and then click OK.

Result: the CUSTOMER_ID column is added to the Data Columns tab.

D. Finally, click OK in the Edit Apply Details window to save the change.

Now, you are ready to execute the Apply node. Right-click the APPLY MODEL node and select Run from the menu.

Result:

• As before, small green gear icons appear in each of the nodes that are being processed, and the Workflow Jobs tab displays the progress.
• When the process is complete, green check mark icons are displayed in the border of all workflow nodes, and the workflow document is automatically saved.

Optionally, you can create a database table to store the the model prediction results (the "apply" results).

The table may be used for any number of reasons. For example, an application could read the predictions from that table, and suggest an appropriate response, like sending the customer a letter, offering the customer a discount, or some other appropriate action.

To create a table of model prediction results, perform the following:

A. Using the Data list in the Component Palette, drag the Create Table node to the workflow canvas, like this:

Result: an OUTPUT node is created.

B. Rename the OUTPUT node PREDICTIONS, by using the Details tab of the Property Inspector.

C. Link the APPLY MODEL node to the PREDICTIONS node.

D. Specify a specific name for the table that will be created (otherwise, Data Miner will create a default name):

1. Right-click the PREDICTIONS node and select Edit from the menu.
2. Then, in the Edit Create Table window, change the default table name to the same name as the Create Table node in the workspace, as shown here:

3. Finally, click OK.

E. Lastly, right-click the PREDICTIONS node and select Run from the menu.

Result: When the process is complete, the workflow document is automatically saved, and all nodes contain a green check mark in the border, like this

Note: After you run the OUTPUT (PREDICTIONS) node, the table is created in your schema.

A. To view the results, right-click the PREDICTIONS Table node and select View Data from the Menu.

Result: A new tab opens with the contents of the table.

Notes:

• The table contains three columns: one for the customer id, one for the predicted outcome (Yes or No), and one for the probability of the prediction.
• You can sort the table results on any of the columns using the Sort button, as shown here. In this case, the table will be sorted using the Predicted outcome, in descending order.

B. Click Apply Sort to view the results:

Notes:

• Each time you run an "Apply" node, Oracle Data Miner takes a different sample of the data to display. With each Apply, both the data and the order in which it is displayed may change. Therefore, the sample in your table may be different from the sample shown here. This is particularly evident when only a small pool of data is available, which is the case in the schema for this lesson.
• You can also filter the table by entering a Where clause in the Filter box.
• The table contents can be displayed using any Oracle application or tools, such as Oracle Application Express, Oracle BI Answers, Oracle BI Dashboards, and so on.

C. When you are done viewing the results, close the tab for the PREDICTIONS Table.