2 Connecting to a Data Mining Database

This chapter explains how to connect to an Oracle database to perform data mining and other database activities.

Your User ID

To connect to Oracle Database, you must log on with a user ID and password. To perform data mining activities in the database, you must log on with a user ID that has been granted the necessary database privileges.

If you simply wish to run the Data Mining sample programs, see "Create a Data Mining Demo User".

If you wish to perform broader data mining activities, refer to Chapter 4, "Users and Privileges for Data Mining".

Connect to a Local Database

In Chapter 1, you learned how to install Oracle Database on your personal computer. When you install Oracle Database locally, you do not need to install separate client software to connect to the database.

Note:

There is one exception. If you plan to use Oracle Spreadsheet Add-In for Predictive Analytics, you do need to install Oracle Client — even if the database is local. For more information, see:

Connect to a Local Database Using SQL*Plus

SQL*Plus and other client tools are installed with Oracle Database. You can use SQL*Plus to connect to your local database by simply specifying a user name and password.

In SQL*Plus, you can run the Data Mining PL/SQL sample programs and perform data mining tasks using the PL/SQL API.

To start SQL*Plus:

From the Windows Start menu, select the Oracle home directory of the local database.
Choose Application Development.
Choose SQL*Plus.
Specify the user ID and password of the Data Mining user.

Description of the illustration sqlplus.gif

Connect to a Local Database Using SQL Developer

SQL Developer is a graphical development environment. You can use SQL Developer to run the Data Mining PL/SQL sample programs and perform data mining tasks using the PL/SQL API.

To start SQL Developer:

From the Windows Start menu, select the Oracle home directory of the local database.
Choose Application Development.
Choose SQL Developer.

Description of the illustration sqldev1.gif
To define a connection to the local database, right click Connections and choose New.
The New/Select Database Connection dialog appears.

Description of the illustration sqldev2.gif
- For Connection Name, specify a meaningful name that you will remember.
- Provide a user name and password for the connection.
- For Hostname, specify localhost to indicate that the database is hosted on the local computer.
- For Port, specify the port that the database will use to listen to requests from the client. The default port is 1521.
- For SID (Service Identifier), specify the global database name that you created when you installed Oracle Database.
Click Connect to connect to the database using the specified credentials.

The connection that you created appears in the list of connections in the right-hand pane.In SQL Developer, you can have multiple connections open simultaneously.

Description of the illustration sqldev4.gif

Connect to a Remote Database

If you do not have a local database, you must install Oracle Client to obtain SQL*Plus, SQL Developer, and other tools for connecting to a remote Oracle database.

To connect to a remote database, Oracle Client requires a host name, port number, service identifier or global database name, and a user name and password. If Oracle Client can use the Easy Connect method, these credentials can be presented when the connection request is made. Alternatively, Oracle Client can use a Net Service Name to connect to a remote database. A Net Service Name represents the login credentials for a given remote connection.

In the following sections, you will find instructions for installing Oracle Client and connecting to a remote database for data mining.

Install Oracle Client
Create a Net Service Name
Perform Data Mining on a Remote Computer
Run the Data Mining Sample Programs on a Remote Computer

Note:

These instructions are not intended as a replacement for Oacle Client and Net Management documentation. For additional information, refer to:

Additional documentation is available on the Installing and Upgrading page of the Oracle Database 11g Online Documentation Library:

http://www.oracle.com/pls/db111/db111.homepage

Install Oracle Client

Use the following steps to install Oracle Client on a Windows platform:

Ensure that your computer meets the system requirements described in Oracle Database Client Installation Guide for Microsoft Windows.
Stop any Oracle services that may be running on your computer.

In Windows Control Panel, choose Administrative Tools, then Services. Find the service names that start with "Oracle". Choose Stop for each one.
To start the installation, go to the Client installation directory and run SETUP.EXE.

Oracle Universal Installer opens and displays the Welcome page. Click Next to advance to the next page.
On the Select Installation Type page, choose Administrator .

Description of the illustration clinstall1.gif
On the Specify Home Details page, provide the path of a home directory for Oracle Client. Oracle home is a subdirectory of the Oracle base directory.

You can install Oracle Client in an existing Oracle base, or you can specify a new one. However, you must install Oracle Client in a new Oracle home. Do not install Oracle Client in the Oracle Database home directory, or in any other pre-existing Oracle home.

Oracle Installer creates the directories for Oracle base and Oracle home if they do not already exist on your computer.

Description of the illustration clinstall2.gif
The Installer performs prerequisite checks.

If the checks succeed, choose Next to advance to the next step.

If any of the checks do not succeed, correct the problem and then click Retry. If more extensive changes are needed, cancel the installation, fix the problem, and then restart Oracle Installer
The Summary page displays the settings and components for the installation.

Description of the illustration clinstall4.gif

Click Install.
On the Install page, the Installer displays a progress bar.
When the installation is complete, the Installer invokes the Configuration Assistants to create a database connection.

Description of the illustration clinstall6.gif
Oracle Net Configuration Assistant starts and displays the Welcome page.

Description of the illustration clinstall7.gif

Oracle Net Configuration Assistant determines that it is not necessary to create a specific database connection, because Oracle Client can use the Easy Connect naming method.

The Easy Connect naming method allows a client to present connection credentials at the time of the connection. The connection does not have to be specified as a Net Service Name or configured in advance.

For example, in SQL*Plus, with the Easy Connect method you can connect to a database with a statement like the following.
```
SQL>CONNECT username @ "hostname[:port][/servicename]"
```
The system will prompt for a password and establish a connection.
When the Oracle Net Configuration process is complete, click Finish.

Description of the illustration clinstall8.gif
On the End of Installation page, confirm that the installation was successful. Then click Exit to exit the Installer.

Create a Net Service Name

Even if your Oracle Client installation supports Easy Connect Naming, you may need to create a Net Service Name for specific applications. For example, Oracle Spreadsheet Add-In for Predictive Analytics uses a Net Service Name to connect to an Oracle database. This connection is required whether you intend to work with data stored in Oracle or data stored in Excel.

To create a Net Service Name:

From the Windows Start menu, select the home directory of Oracle Client.
Choose Configuration and Migration Tools.
Choose Net Configuration Assistant.
Oracle Net Configuration Assistant displays the Welcome page.

Description of the illustration netconfig1.gif

Choose Local Net Service Name configuration, then Next.
On the next page, choose Add to add a new Net Service Name. Then click Next.
Provide the name of the Oracle SID (Service Identifier). By default this is the global database name that you specified when you installed Oracle Database.

Choose Next.
On the next page specify the communication protocol. The default is TCP.

Choose Next.
On the next page, choose Yes, perform a test to test the new connection.

The connection will probably fail until you provide a new user ID and password.

Description of the illustration netconfig7.gif

Choose Change Login.

Provide the user ID and password of an account that has data mining privileges on the host.

Description of the illustration netconfig8.gif
If the user name and password were the only problem with the connection, Oracle Net Configuration Assistant makes a successful test connection.

Description of the illustration netconfig9.gif

Choose Next.
Oracle Net Configuration Assistant reports that the connection configuration was successful. When you click Next, you are returned to the Welcome page, where you have the opportunity to perform additional configuration.

Click Finish to exit Oracle Net Configuration Assistant.

Perform Data Mining on a Remote Computer

You can use the Oracle Data Mining PL/SQL API in a remote instance of Oracle Database as long as:

SQL*Plus (or another client tool such as SQL Developer or JDeveloper) has been installed on your computer.
The system administrator has created a user ID for you in the remote database and given you the connection information.
The system administrator has granted the privileges to your user ID to allow the data mining activities that you will perform.

Run the Data Mining Sample Programs on a Remote Computer

If you wish to run the Data Mining sample programs on a remote computer:

Ask the system administrator to install the SH schema in the remote database, if it has not already been installed.
Ask the system administrator to run dmshgrants.sql to grant the appropriate privileges to your user ID.
Start SQL*Plus and log in to the remote database as the Data Mining user. Run dmsh.sql to populate your schema with objects used by the sample programs. (See "Create a Data Mining Demo User" for information about dmshgrants.sql and dmsh.sql.)
Follow the instructions in "Run the Sample Programs" .

Download the Sample Programs from OTN

If you want to use local copies of the sample programs, you can download them from the Oracle Technology Network and run them in the remote database.

The programs are available in two different locations on OTN: the Oracle Data Mining page and the Sample Code page.

Oracle Data Mining page on OTN:

http://www.oracle.com/technology/products/bi/odm/index.html

Sample Code page on OTN:

http://www.oracle.com/technology/sample_code/index.html

To run the local program code in the remote database:

Unzip the sample program files to a directory on your computer.
Follow the instructions in "Run the Sample Programs" , but specify the local path of the sample program instead of the path under the remote Oracle home. For example, this statement would cause the dmnbdemo.sql program in your local directory to execute in the remote database:
```
SQL>@ local_dir\dmnbdemo
```

Connect From Oracle Data Miner

Oracle Data Miner is the graphical user interface to Oracle Data Mining. It can connect to either a local or a remote database. It does not require Oracle Client.

When you start Oracle Data Miner, the Choose Connection dialog appears. To create a new connection, choose New.

Description of the illustration odmrconnect1.gif

The New Connection dialog appears.

Description of the illustration odmrconnect2.gif

For Connection Name, specify a meaningful name that you will remember.
Provide the user name and password for the connection.
For Hostname, specify localhost to indicate that the database is hosted on the local computer.
For Port, specify the port that the database will use to listen to requests from the client. The default port is 1521.
For SID (Service Identifier), specify the global database name that you created when you installed Oracle Database.

Click OK to create the connection and return to the Choose Connection dialog. When you select the connection and click OK, Oracle Data Miner is launched using the specified connection.

Connect From Oracle Spreadsheet Add-In For Predictive Analytics

Oracle Spreadsheet Add-In for Predictive Analytics implements predictive analytics ("one-click" data mining) within Microsoft Excel. You can use the Spreadheet Add-In to analyze data stored in Excel or in Oracle. Either way, the Spreadsheet Add-In requires a connection to an Oracle database, which can be local or remote.

The Spreadsheet Add-In requires Oracle Client. It uses a Net Service Name to connect to the database. Even if the database is local, Oracle Client and a Net Service Name are required.

To connect to Oracle, follow these steps:

Install Oracle Client on your computer as described in "Install Oracle Client".
Create a Net Service Name as described in "Create a Net Service Name".
Add the Add-In to Excel as described in Chapter 6, "Installing the Spreadsheet Add-In for Predictive Analytics".
From the Add-In menu, choose Connect.

Description of the illustration addin4.gif
The Connect (Oracle Database) dialog appears.

Description of the illustration addin2.gif

From the list of pre-defined Net Service Names, choose the connection you want to use. Provide a user name and password and click Connect to activate the Spreadsheet Add-In.

See Also:
Chapter 6, "Installing the Spreadsheet Add-In for Predictive Analytics"