Use the Open New DBMS Query menu option to work with tables created via SQL queries against a DBMS. Selecting this option causes the dialog box in Figure 3-8 to appear.

Figure 3-8. SQL Query Dialog Box

Selecting a server and DBMS in this dialog box has the same effect as selecting those items in the Open New DBMS Table dialog box.

The SQL query is shown in the panel at the lower left. You can enter the query there, or load it from a disk file using the Load SQL from File button. The names of tables and columns in the current DBMS are shown to help build queries. To have their names transferred to the SQL query panel, double-click on them.

When you have entered the SQL query, click the Submit SQL Query button to send it to the DBMS for execution. The table columns resulting from the query appears on the right.

If you've chosen the Automatic Thresholds tab, the program can use machine learning to suggest bins.

Figure 3-11. Binning With Automatically Computed Thresholds

The first choice under Automatic Thresholds is between the Automatically choose number of bins and the Group into: ___ bins buttons. Click Automatically choose number of bins to let the computer decide the best number of bins. If you choose to specify the number of bins, click Group into: ___ bins, and type the number of bins you want into the field.

There are three ways to categorize data into bins:

Both Uniform Range and Uniform Weight let you specify a trimming fraction, which indicates the fraction of extreme values to be excluded from the value range prior to generating bins. The default trimming fraction is 0.05. This excludes the 5% of the instances with the most extreme values (2.5% with the lowest values in the range, and 2.5% with the highest values in the range). Trimming tends to reduce the influence of outliers on the generation of thresholds.

All of the approaches let you decide whether you want to specify the number of bins or let the algorithm select the number automatically. For the Uniform Range and Uniform Weight approaches, the automatic selection of the bins is based on the number of distinct values: the more distinct values, the more bins are chosen (the relationship is logarithmic).

Typically, all of the available instances are used when identifying thresholds. When binned attributes are later used to induce a classifier, the error estimates for that classifier tend to be overly optimistic. This is because distributional information from the test set was used to identify thresholds. Use training set only prevents the binning approaches from looking at the records in the test set when identifying thresholds. This tends to give a more realistic estimate of the classifiers' error rate. Use training set only requires the user to specify the same Holdout ratio and Random seed (see “Error Options for Inducers” in Chapter 10) that are used to create the holdout set for estimating classifier error.

The Use Weight menu lets you weight the instances by any numeric attribute. Changing instance weight affects both Automatic and Uniform Weight, but has no affect on the Uniform Range.

If you click Apply, the Tool Manager picks bin thresholds and displays them in the “Thresholds for selected column are” text field. The text field at the bottom of the Bin Columns window shows the progress of the binning algorithm and any errors that occur.

If you specify your own thresholds (as shown in Figure 3-10), you can choose between Use custom thresholds or Use evenly spaced thresholds by clicking either button. When you type in the thresholds, you must click Apply to make those thresholds effective for the selected columns.

The Use custom thresholds text box lets you enter the range criteria. For example, you could enter the numbers 18, 30, 50, 60. This results in the following ranges: 0-18, 19-30, 31-50, 51-60, 61+. Note that you enter only the digits and commas, not the ranges.

To specify equally spaced bins over a range of values, click the Equally Spaced Bins button. This activates the three text fields below it. You can type the start of the binning range, the end of the range, and the spacing of the bins, respectively, into these fields. If you are binning a column that is a date, you can specify units of time for the bin spacing (using the “Date units” popup menu under the text fields). This would permit you, for example, to bin a time period into bins of three weeks. Dates entered into these fields must be typed in the form “MM/DD/YY”. Possible time units are as follows:

The Use custom thresholds text box accepts dates either in double quotes (as shown below), or without. If you enter dates without quotes, the quotes are added automatically.

"1/1/96", "2/1/96", "3/1/96", "4/1/96", "5/1/96", "6/1/96"

However, do not put quotes around dates used with Use evenly spaced thresholds.

---

Note: If you enter an invalid parameter, an error message is displayed after you click Apply, informing you of the valid options and letting you either cancel the command or return to the dialog box to make the appropriate changes.

---

The Aggregate button lets you perform simple aggregations (for example, sum, min, max, and so on), make arrays, and distribute columns. (See Table 3-1)

If you make Total $ Spent into an array indexed by the binned column Age_bin, the resulting table has only two columns:

In this case, making an array reduces the number of columns by one, and also reduces the number of rows by four. Arrays are useful for the Tree Visualizer tool; they are necessary if you want to use sliders in Scatter Visualizer and Map Visualizer displays.

Distributing columns is similar, but different in several important ways. Instead of producing a single new column holding many values, distributing produces one new column for each value of the index. For example, if in the first table was not made an array, but instead distributed by Age_bin, the result is:

Thus, distributing increases the number of columns but decreases the number of rows.

If you have more than one binned column (for example, Age_bin and Sex_bin), you can make a two-dimensional array (indexed by combinations of Age_bin and Sex_bin). You also can distribute and make an array at the same time.

This table has two binned columns: one for age, one for sex.:

If you make Total $ Spent an array indexed by age, and remove Sex_bin, the results are:

If you do not remove Sex_bin, the results are:

If you make an array by both Age_bin and Sex_bin, the results are:

Finally, if you distribute by Sex_bin and index by Age_bin, the results are:

The examples above (with the exception of Table 3-5) had exactly one relevant value for each array element, and the distribution merely rearranged existing data values. For the example in Table 3-5, there were two data values for each array element, and these were summed. MineSet provides several aggregation options for datasets containing more than one value to be distributed into a given output array element. The most common option is to add the values (as done in Table 3-5). This is useful when accumulating expenditures into budgets, for example. You also can take the minimum, maximum, and average of the total number of values, as well as count them.

When distributing values for a given dataset, it is possible that there are no values appropriate for a particular bin. In this case, for min, max, avg, and sum aggregations, the DataMover fills in a value of NULL. For count aggregations, the DataMover fills in a value of 0.

You can use the Aggregate button to create simple aggregations, make arrays, or distribute columns. Clicking this button causes the Aggregate dialog box to appear (Figure 3-12). It shows three lists, with the columns in the current table appearing in the middle list. If you want to aggregate, distribute, or turn a column into an array, select the name of the column, and click the left arrow button between the left and center lists. Below are popup menus that let you specify indexes (if the result is to be an array) and a distribution column (if the result is to be distributed). In addition, at the bottom of the dialog box are five toggles that let you specify how different values are to be combined when aggregated: either summed, averaged, the min or max value, or the count. When you are aggregating number-valued columns, you can choose any combination of these options. For other types, only count is permitted. If you choose more than one option, you get more than one result. For example, selecting average and max gives you one result with average values, and another one holding the max values.

Figure 3-12. Aggregate Dialog Box

The three lists of column names are given below:

After you have finished with the additional aggregate criteria dialog box, the Current Columns text box in the Table Processing window shows the new column names that result from applying these criteria.

Some databases store numerical values as strings. Oracle stores all numbers (both integers and real numbers) in a single format, which defaults to the data type double in the Tool Manager. You can use the Change Types button to ensure that these values are processed correctly. To change the type of one or more columns, click the Change Types button. A new dialog box appears (see Figure 3-14). This dialog box contains a window with a list of column headings and their respective types.

Figure 3-14. Change Types Dialog Box

First select a column heading in the window. Then click the New type button. This produces a popup list of the possible types (invalid types are grayed out), as shown in Figure 3-15.

Figure 3-15. Types Popup List

After selecting a new type, click Apply to have the change take effect.

If you try to convert an inappropriate field (such as a name) to a number, the resulting values are all zeroes.

---

Note: When the data source is an existing file, there are fewer possibilities available for changing any given column.

---

Select the original column, type a new name in the text field, and click Apply. Then click Close.

To exit this dialog box, click Close.

The Edit Prev. Op. button allows you to edit the operation shown in the Prev. field. (This button is not active when Current view is: 1 of some number, because that is the original table, with no previous changes.) When you click the Edit Prev. Op. button, the dialog box for the previous operation comes up, and you can make changes to that operation. For example, if the previous operation was binning columns, when you click Edit Prev. Op., the Bin Columns dialog box appears.

Note that by changing a previous operation, you could affect operations you set up subsequent to the current one. For example, if you delete a column that you used in a subsequent binning operation, that binning operation becomes invalid. The Edit History button can help you avoid such problems.

When you click the View History button, the panels showing the current column and data destination are replaced by a panel showing you the complete history of the Data Transformation table (Figure 3-19). Each version of the table appears as a box containing a list of the columns, linked by a smaller box (indicating the operation performed on the table) to the next version of it.

Figure 3-19. View History Dialog Box

As with Edit Prev. Op, changing one operation usually affects (sometimes invalidates) subsequent operations in the history. You can select a specific operation to edit, add, or view. The View History dialog warns you when changes affect the history, shows you the new history. The row of buttons beneath the diagram window of the View History panel allows you to change the size and orientation of the diagram as detailed below.

Under the window displaying this flow chart are the zoom buttons that let you view the flow chart closer up or farther away (Figure 3-20). You can choose the zoom by using the button indicating the percentage, or by clicking the arrow buttons to increase or decrease the size. The increments of change are the same whether you use the percentage button or the arrow buttons.

Figure 3-20. Zoom Buttons

This button (Figure 3-21) creates, in a separate window, an overview of the entire history chart that is synchronized with the Edit History dialog. The overview window shows you which part of the history is currently visible, and lets you pan to other parts of the history.

Figure 3-21. Overview Button

Next to the zoom buttons is a toggle button that lets you view the flow chart vertically or horizontally (Figure 3-22). Clicking the button switches you back and forth between the two points of view.

Figure 3-22. Vertical/Horizontal View Button

Under the Data Source heading is the Change Data Source... button, which lets you change the table on which the history operates. When you hold the button down, a menu appears that lets you choose

Selecting one of these items causes a dialog box to appear that lets you select the new data source.

---

Note: As with editing the history, changing the data source can invalidate history operations.

---

Under the indicator View is a View Single Ops/Dest button. When this button is pressed, the panel showing the history is hidden, and the panels showing current columns and data destinations return to view. The function of this button is the same as choosing the Single Ops and Destination option from the View pulldown menu at the top of the window.

Under the indicator For Selected Operation are three rows of buttons that become active if you click one of the operations or tables in the flow chart. Once you select an operation, you can alter it.

Under the indicator Other there are three buttons that affect the total history file:

If you click the Mining Tools tab, then the Associations tab, the panel lets you take the data file you created in Data Transformations and proceed to the Rule Visualizer. Each step of the process is shown in the subpanels:

If you don't want to go through this process manually, click the Execution button, and the computer will perform the process using defaults.

Figure 3-25. The Associations Tab

The Column Importance (Figure 3-26) allows you to determine how important various columns are in discriminating the different values of the label column you choose. You might, for example, want to find the best three columns for discriminating the label good credit risk so you can choose them for the Scatter Visualizer. When you select the label and click Go!, a popup window appears with the three columns that are the best three discriminators. A measure called “purity” (a number from 0 to 100) informs you how well the columns discriminate the different labels. Adding more columns can only increase the purity.

Figure 3-26. The Column Importance Tab

There are two modes of column importance:

Column Importance Notes

Note that with other columns, the importance of features varies from their ranking alone. For example, while net-income might be a good column individually, it might not be as important together with salary because they are likely to be highly correlated. The best set of three columns is not necessarily composed of the columns that rank highest individually. If two columns give the income in dollars and in another currency, they are ranked equally alone; however, once one of them is chosen, the other adds no discriminatory power to the set of best features.

Column selection is useful for finding the best three axes for the Scatter Visualizer, as well as for finding a good discriminatory hierarchy for the Tree Visualizer.

All floating point values (double or float) are pre-discretized using the automatic discretization. If a column has no value given to it in the left list, the algorithm did not consider it, because it either had a single value (for example, when it is discretized into one interval), or the number of records that it would separate is not statistically significant.