Oracle Data Miner is an extension to Oracle SQL Developer. Oracle Data Miner is a graphical user interface to Oracle Data Mining, a feature of Oracle Database. Data analysts can use the intuitive Oracle Data Miner graphical user interface (GUI) to discover hidden patterns, relationships, and insights in their data. With Oracle Data Miner, everything occurs in an Oracle Database—in a single, secure, scalable platform for advanced business intelligence. Oracle Data Miner eliminates data movement and duplication, maintains security, and minimizes latency time from raw data to valuable information. Enterprises can use Oracle Data Miner for knowledge discovery to better compete on analytics.
Oracle Data Miner helps you perform the data preparation and model building required by the data mining process.
See Also:"About the Data Mining Process"
Data mining requires a problem definition, collection and cleansing of data, and model building. Much of the time spent in a typical data mining project is devoted to understanding and processing of data.
See Also:Oracle Data Mining Concepts for more information about the data mining process.
Oracle Data Miner 4.1 has been enhanced with new features, along with some general enhancements:
Oracle Data Miner is an extension to Oracle SQL Developer. Oracle Data Miner is a graphical user interface to Oracle Data Mining, a feature of Oracle Database. Oracle Data Miner enables users to build descriptive and predictive models to:
Predict customer behavior
Target best customers
Discover customer clusters, segments, and profiles
Identify customer retention risks
Identify promising selling opportunities
Detect anomalous behavior
Oracle Data Miner provides an Application Programming Interface (API) that enables programmers to build and use models.
Oracle Data Miner workflows capture and document the analytical methodology of the user. It can be saved and shared with others to automate advanced analytical methodologies.
The Oracle Data Miner GUI is an extension to Oracle SQL Developer 3.0 or later that enables data analysts to:
Work directly with data inside the database
Explore the data graphically
Build and evaluate multiple data mining models
Apply Oracle Data Miner models to new data
Deploy Oracle Data Miner predictions and insights throughout the enterprise
Figure 1-1 shows a sample workflow of Oracle Data Miner.
Oracle Data Miner creates predictive models that application developers can integrate into applications to automate the discovery and distribution of new business intelligence—predictions, patterns, and discoveries—throughout the enterprise.
See Also:"Oracle Data Miner Documentation" to access the documentation.
Before you install Oracle Data Miner, you must understand the architecture of Oracle Data Miner. Oracle Data Miner consists of a server and one or more clients:
Oracle Data Miner, the client, is an integrated feature of Oracle SQL Developer 3.0 or later.
Oracle Database 12c Release 1 (12.1) Enterprise Edition (includes Oracle Database Personal Edition) or an earlier version of Oracle Database Enterprise Edition is the server. In addition to the database, Oracle Data Miner requires the installation of a Data Miner repository account. The repository is a separate account in the database named ODMRSYS. This repository is shared by all user accounts in the database that have been granted the appropriate privileges to use the Data Miner repository.
Figure 1-2 shows the components of Oracle Data Miner.
Oracle Database Enterprise Edition includes these services that are critical to the support of Oracle Data Miner:
Oracle Data Miner: A component of the Oracle Advanced Analytics option to Oracle Database Enterprise Edition. Oracle Data Miner provides model building, testing, and scoring capabilities for Oracle Data Miner.
Oracle XML DB: Provides services to manage the Oracle Data Miner repository metadata, such as the details about the workflow specifications.
Oracle Scheduler: Provides the engine for scheduling the Oracle Data Miner workflows.
Oracle Text: Provides services necessary to support text mining.
SQL Developer provides Snippets to help you write PL/SQL programs. Snippets are code fragments, such as SQL functions, Optimizer hints, and miscellaneous PL/SQL programming techniques. Some snippets are just syntax, and others are examples. You can insert and edit snippets when you are using the SQL Worksheet or creating or editing a PL/SQL function or procedure using SQL Worksheet or SQL Query node.
Open SQL worksheet: To open SQL Worksheet, go to Connections for SQL Developer, right-click the connection to use, and select Open SQL Worksheet.
See the SQL Developer Online Help for more information about SQL Worksheet.
Insert snippet: To insert a snippet into your code in a SQL Worksheet or in a PL/SQL function or procedure, drag the snippet from the snippets window and drop it into the desired place in your code. Then edit the syntax so that the SQL function is valid in the current context. To see a brief description of a SQL function in a tool tip, hold the pointer over the function name. Oracle Data Miner provides snippets for the EXPLAIN, PREDICT, and PROFILE procedures in DBMS_PREDICTIVE_ANALYTICS and for the Data Mining functions for scoring data using prediction, clustering, or feature extraction.
See Also:"Predictive Analytics Snippets" for more information about how to use snippets.
To view the list of Predictive Analytics functions, and to use a snippet:
Open Oracle SQL Developer.
Select the connection that you are using for Oracle Data Miner.
From the SQL Developer menu, go to View and then select Snippets.
From the drop-down list, select Predictive Analytics.
The Predictive Analytics group of snippets includes the following snippets:
DBMS_PREDICTIVE_ANALYTICS.EXPLAIN() to rank attributes in order of influence when explaining a target column.
DBMS_PREDICTIVE_ANALYTICS.PREDICT() to predict the value of a target column based on values in the input data.
Prediction Anomaly Function: Use the anomaly detection predictive query to predict the anomalous customers.
Prediction Classification Function: Makes predictions using dynamic classifications.
Prediction Cluster Function: Predicts the cluster a customer belongs to.
Prediction Feature Set Function: Predicts feature sets to provide a general characterization of the underlying customer data.
Prediction Regression Function: Predicts the age of customers who are likely to use an affinity card.
DBMS_PREDICTIVE_ANALYTICS.PROFILE() to generate rules that identify the records that have the same target value.
To use a snippet, drag the snippet to the SQL Worksheet or to a place in a PL/SQL program.
Note:The Explain, Predict, and Profile snippets have one or more commented-out DROP statements, such as:
--DROP TABLE mining_explain_result;
If you run one of these snippets more than once, remove the comment characters for the DROP statement.
If you drag the Explain snippet to SQL Worksheet, you see:
--Available in Oracle Enterprise DB 10.2 and later
--Ranks attributes in order of influence to explain a target column.
--For more info go to: http://www.oracle.com/pls/db112/vbook_subject?subject=dma
--Remove comment on the Drop statement if you want to rerun this script
--DROP TABLE mining_explain_result;
--Perform EXPLAIN operation
data_table_name => '"CUSTOMERS"',
explain_column_name => '"CUST_GENDER"',
result_table_name => 'mining_explain_result',
data_schema_name => '"SH"');
--output first 10 rows from resulting table mining_explain_result
COLUMN ATTRIBUTE_NAME FORMAT A30
COLUMN ATTRIBUTE_SUBNAME FORMAT A30
COLUMN EXPLANATORY_VALUE FORMAT 0D999999
COLUMN RANK FORMAT 999999
select * from mining_explain_result where rownum < 10;
When you run this code, you get the following results (in Script Output):
anonymous block completed
ATTRIBUTE_NAME ATTRIBUTE_SUBNAME EXPLANATORY_VALUE RANK
----------------- ------------------------------ ----------------- ------
CUST_LAST_NAME 0.151359 1
CUST_MARITAL_STATUS 0.015043 3
CUST_INCOME_LEVEL 0.002592 4
CUST_CREDIT_LIMIT 0.000195 5
CUST_EMAIL 0.000000 6
CUST_TOTAL 0.000000 6
CUST_TOTAL_ID 0.000000 6
CUST_FIRST_NAME 0.000000 6
9 rows selected
Oracle Database PL/SQL Packages and Types Reference for more information.
"Snippets in Oracle Data Miner" for information about how to write code using some of these snippets.
The Oracle Data Miner Repository resides in the database that the Oracle Data Miner client connects to. The repository stores metadata about workflows.
The Oracle by Example tutorial Setting Up Oracle Data Miner 4.1 describes how to install the Data Miner Repository and grants rights to an account. The tutorial describes the following steps:
Create a Data Miner User Account using SQL Developer.
Create a SQL Developer connection for the Data Miner User.
Install the Data Miner Repository.
By default, sample data is installed. You can deselect loading these tables and views.
You can drop the repository through GUI, if necessary.
If you have dropped a Repository or if you do not want to use the OBE, you can reinstall the repository.
When you install the repository, rights are automatically granted to the user account that you connect with.
To grant rights to another account, define a database connection for the account and open the account in the Data Miner navigator. The GUI tells you that the account does not have the correct grants. Click OK for the grants to be created. You must log in using an administrative (SYS) account.
"About Dropping the Oracle Data Miner Repository" for information about how to drop the repository using the GUI.
"Installing the Data Miner Repository Using the GUI" for information about how to reinstall the repository.
To install the repository:
Double-click a connection. If the repository is not installed, the Repository not Installed warning message is displayed. You also see this message if you try to create a project when the repository is not installed.
Click Yes to install a Data Miner repository in the database to which you are connected.
Note:If you have several database connections, you need to install the repository using only one connection. You must grant privileges to the other connections.
Log in using an administrative account for the database that you are connected to.
The Repository Installation Settings dialog box opens.
Name of the repository—ODMRSYS. You cannot change this name.
Select a default tablespace and a temporary tablespace, and click OK.
If you are connected to Oracle Database 11g Release 2 (220.127.116.11) or later, permanent tablespaces are filtered so that only Oracle ASM tablespaces are displayed. (The temporary tablespace does not have to be Oracle ASM.)
If you are connected to Oracle Database 11g Release 2 (18.104.22.168) or earlier, no filtering takes place.
The Install Data Miner Repository dialog box opens. By default, demo data is installed used by the Oracle By Example tutorials. Click Start to begin the installation.
Note:The sample data requires the SH schema.
The installation may take several minutes. After the installation completes, you get a message indicating that the task completed successfully. You can examine the log of the task by clicking Show Log. Click Close when done.
If you have more than one Connection, you must grant privileges to each connection as follows:
Click the connection.
In the Required Privileges Missing dialog box, click Yes to grant privileges.
Log in using an administrative account for the database that you connect to.
In the Data Miner Grants dialog box, click Start to grant privileges and to install sample data.
After the tasks completes, a message is displayed indicating that the task has completed successfully. You can examine the log of the task by clicking Show Log.
Click Close when done.
See Also:Oracle Data Miner Installation and Administration Guide for information about how to install the Data Miner Repository using scripts.
If you plan to stop using Oracle Data Miner, you should drop the Repository. You may have to drop the repository when you upgrade from one version of Oracle Database to another.
When you drop the Repository, all workflows and internal tables are dropped. Models created by Oracle Data Miner are dropped. Tables created by the Create Table or View node are not dropped.
Note:Drop Repository cannot be undone. When you drop a repository, the repository and all internal user objects created by Data Miner are permanently removed. The objects removed include models, tables or views generated by the Create Table node, hidden tables used store results (model viewers), and text specification objects used to support text transformations. In addition all Data Miner workflows are dropped. To save a workflow, export it and later import it.
Before you drop the Repository, check that no projects or workflows are open. If any connections are open, the process may fail.
For Drop Repository to complete successfully, no sessions with the role of ODMRUSER can be active. Active ODMRUSER sessions can result in object locks that block dropping of the repository.
During the process of dropping the repository:
The database does not allow any new connections to be established during this process.
All sessions with the ODMRUSER role are automatically disconnected.
All workflows and internal tables are dropped. Models created by Oracle Data Miner are dropped. Tables created by the Create Table or View node are dropped.
Note:After you drop the repository, you must install the repository again before you can perform any data mining.
There is one Oracle Data Miner repository per database. If you have several database connections, it is necessary to drop the repository for one connection only. To drop the repository:
In SQL Developer UI, go to Tools and click Data Miner. Then click Drop Repository.
The Drop Repository dialog box opens. Select the connection where you want to drop the repository. You can add and edit connections here, if required.
Log in as SYS, and click OK.
A list os sessions that will be disconnected is displayed. Click OK to continue.
The Drop Data Miner Repository dialog box opens. Click Start to begin the drop of the repository. This process may take several minutes. Specific messages indicate which steps are being performed.
After the operation completes, examine the log files to see which operations were performed.
See Also:Oracle Data Miner Installation and Administration Guide for information about how to drop the repository using scripts.
After you download a patch or a new version or you update the database that you connect to, you are notified that upgrade is necessary when you open a connection used for data mining. The GUI issues a message describing the problem and asks you if it should perform necessary migration. If you answer yes, you are prompted for the administrative (SYS) password.
Migration may require conversion of the repository. If migration is required, then the Migrating the Oracle Data Miner Repository Using the GUI dialog box opens.
Migration of the Oracle Data Miner repository arises in the following cases:
A version of SQL Developer before 4.0 is installed on Oracle Database 11g Release 2 (22.214.171.124) or later, and you want to connect using a SQL Developer 4.0 or later client, that is, you upgrade the client.
SQL Developer 4.0 installed on a version of Oracle Database 11g Release 2 (126.96.36.199) or earlier, and the database is upgraded to Oracle Database 11g Release 2 (188.8.131.52) or later.
Either of these conditions is detected when you open the connection in the Data Miner Navigator. If the ODMRSYS default permanent tablespace is not an ASM tablespace, then a dialog is displayed requesting an ASM tablespace. The ASM tablespace does not replace the existing default permanent tablespace already specified for ODMRSYS, but instead, it converts the
workflow_data column that stores the workflow XML data in the
ODMR$_WORKFLOWS table. The
workflow_data is usually the largest data component stored in ODMRSYS. This approach reduces the amount of time required to perform the migration.
The Repository Migration does not request temporary tablespace.
See Also:Oracle Data Miner Installation and Administration Guide for information about how to migrate the repository using scripts.
You can learn how to use Oracle Data Miner in the following ways:
By using Oracle By Example for Oracle Data Miner 4.0 tutorials
By experimenting on your own using with Sample Data
By using the Oracle Data Miner Online Help
By asking questions on the Oracle Data Mining Forum
By referring to Oracle Data Miner Documentation for reference information about data mining in general and Oracle Data Miner in particular.
The Oracle By Example tutorials teach you how to install and use Oracle Data Miner:
Setting Up Oracle Data Miner 4.0: This tutorial covers the process of setting up Oracle Data Miner for use within Oracle SQL Developer 4.0 connected to Oracle Database 12c.
Using Oracle Data Miner 4.0: This tutorial covers the use of Oracle Data Miner 4.0 to perform data mining on Oracle Database 12c. In this lesson, you examine and solve a data mining business problem by using the Oracle Data Miner graphical user interface (GUI). The Oracle Data Miner GUI is included as an extension of Oracle SQL Developer, version 4.0.
Using Feature Selection and Generation with GLM: This tutorial covers the use of Oracle Data Miner 4.0 to leverage enhancements to the Oracle implementation of Generalized Liner Models (GLM) for Oracle Database 12c. These enhancements include support for Feature Selection and Generation.
Text Mining with an Expectation Maximization Clustering Model: This tutorial covers the use of Oracle Data Miner 4.0 to leverage new text mining enhancements while applying a clustering model. In this lesson, you learn how to use the Expectation Maximization (EM) algorithm in a clustering model.
Using Predictive Queries With Oracle Data Miner 4.0: This tutorial covers the use of Predictive Queries against mining data by using Oracle Data Miner 4.0.
Using the SQL Query Node in an Oracle Data Miner Workflow: This tutorial covers the use of the new SQL Query Node in an Oracle Data Miner 4.0 workflow.
The tutorials are in Oracle Learning Library under the Oracle Data Miner 12c OBE Series at
Sample Data is loaded in your account when you install Oracle Data Miner. You also have access to sample data in
SH and other schemas. See Oracle Data Miner Installation and Administration Guide for information about how to add tables and views from
SH and other schemas to you account.
The online help specific to Oracle Data Miner is in the help folder Oracle Data Miner Concepts and Usage.
To view or search the online help for Oracle Data Miner click Help and then click Table of Content. Then expand the Table of Content and go to Oracle Data Miner Concepts and Usage on the Contents tab of Help Center.
To get help for a specific dialog box, click the Help button or press F1. To get help for objects in a workflow, select the object and press F1.
Online help contains reference topics and the topics that describe how the GUI works. To see reference topics, either expand the help contents in the online help or search in the online help.
See Also:"Search the Online Help"
To search the online help, use the search box at the top of Help Center:
Type the word or words that you want to search for in the search box and press Enter.
Select one of the following search options: case sensitive (Match case) or case insensitive; and whether to match topics based on all specified words, any specified words, or a Boolean expression.
Search performs a full-text search of all Oracle Data Miner online help topics, including Oracle Data Miner Release Notes.
To cancel a search, click .
Participate in the Oracle Data Miner Discussion Forum
http://forums.oracle.com/community/developer/english/business_intelligence/data_warehousing/data_mining to discuss using Oracle Data Miner and data mining.
Oracle Data Miner is the graphical user interface for Oracle Data Miner. Oracle Data Miner is a component of the Oracle Advanced Analytics option to Oracle Database Enterprise Edition.
Oracle Data Miner documentation is included in the Oracle Database Documentation Library for the version of the database that you have installed. Documentation Libraries are posted at the Documentation site at
http://docs.oracle.com in the Database section. To go directly to the Business Intelligence and Data Warehousing documentation, use
http://www.oracle.com/pls/topic/lookup?ctx=db112&id=dwbitab if you connect to Oracle Database 11g Release 2 (11.2) or
http://www.oracle.com/pls/topic/lookup?ctx=db121&id=dwbitab if you connect to Oracle database 12c Release1 (12.1).
Oracle Data Mining Concepts for more information about Oracle Data Miner.
Documentation Library for:
Oracle Database 12c Release 1 (12.1) at:
Oracle Database 11g Release 2 (11.2) at:
Oracle Data Miner documentation is on the Business Intelligence and Data Warehousing page under the heading Oracle Advanced Analytics. See "Related Documentation" in the preface of Oracle Data Mining Concepts for other documentation.