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.
About the Data Mining Process
Data mining is the process of extracting useful information from masses of data by extracting patterns and trends from the data.
Data mining requires a problem definition, collection and cleansing of data, and model building. Most of the time spent in a typical data mining project is devoted to understanding and processing of data.
Overview of Oracle Data Miner
Oracle Data Miner is an extension to Oracle SQL Developer. It 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.
Figure 1-1 Sample Data Miner Workflow
Description of "Figure 1-1 Sample Data Miner Workflow"
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.
Architecture of Oracle Data Miner
Oracle Data Miner consists of a server and one or more clients.
Before you install Oracle Data Miner, you must understand the architecture of Oracle Data Miner:
Oracle Data Miner, the client, is an integrated feature of Oracle SQL Developer 3.0 or later.
Oracle Database 12c Release 2 (12.2) 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.
Figure 1-2 Oracle Data Miner Components
Description of "Figure 1-2 Oracle Data Miner Components"
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.
Snippets in Oracle Data Miner
Snippets are code fragments, such as SQL functions, optimizer hints, and miscellaneous PL/SQL programming techniques.
SQL Developer provides snippets to help you write PL/SQL programs. Some snippets are just syntax, and others are examples. You can insert and edit snippets when you are using the SQL Worksheet or when 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.
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
DBMS_PREDICTIVE_ANALYTICS,and for the Data Mining functions for scoring data using Prediction, Clustering, or Feature Extraction.
See Also:See the SQL Developer Online Help for more information about SQL Worksheet.
Using Predictive Analytics Snippets
In Oracle SQL Developer, you can view the list of Predictive Analytics snippets and use them.
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 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.
The Explain, Predict, and Profile snippets have one or more commented-out
DROPstatements, such as:
--DROP TABLE mining_explain_result;
If you run one of these snippets more than once, then remove the comment characters for the
If you drag the
Explain snippet to SQL Worksheet, then 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
About Oracle Data Miner Repository Installation
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 Oracle 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, then you must 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 DBA privilege.
Installing the Data Miner Repository Using the GUI
The Data Miner Repository installation process starts automatically when you activate a SQL Developer connection for the first time from the Data Miner tab.
To install the repository:
Double-click a connection. If the repository is not installed, then the warning message
Repository not Installedis 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.
If you have several database connections, then 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 (22.214.171.124) or later, then 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 (126.96.36.199) or earlier, then 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.
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, then 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.
About Dropping the Oracle Data Miner Repository
If you plan to stop using Oracle Data Miner, then you must drop the Repository. You may also 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.
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, then 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.
After you drop the repository, you must install the repository again before you can perform any data mining.
Dropping the Data Miner Repository Using GUI
There is one Oracle Data Miner repository per database. If you have several database connections, then 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 with DBA privilege and click OK.
- A list of sessions that will be disconnected is displayed. Click OK to continue.
- The Drop Data Miner Repository dialog box opens. Click Start to begin the process of dropping 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.
About Oracle Data Miner Repository Migration
Migration may require conversion of the repository. If migration is required, then the Migrate Oracle Data Miner Repository dialog box opens.
If you have downloaded a new version of SQL Developer that is not compatible with the installed Data Miner repository, then you will be notified that a Data Miner repository upgrade is necessary when you open a connection used for data mining. The GUI issues a message describing the problem and asks if it should perform necessary migration. If you answer yes, then you are prompted for the administrative password.
Migrating the Oracle Data Miner Repository Using the GUI
Migration of Oracle Data Miner repository arises when upgrading the Oracle Data Miner client, or when upgrading Oracle Database on which SQL Developer is installed.
You must migrate the Oracle Data Miner repository in the following cases:
A version of SQL Developer before 4.0 is installed on Oracle Database 11g Release 2 (188.8.131.52) 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 (184.108.40.206) or earlier, and the database is upgraded to Oracle Database 11g Release 2 (220.127.116.11) 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.
How to Use Oracle Data Miner
Lists the different ways in which you can learn how to use Oracle Data Miner.
You can learn how to use Oracle Data Miner in the following ways:
By using Oracle By Example tutorials
By experimenting on your own using the sample data
By using the Online Help
By asking questions in 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.
Oracle By Example for Oracle Data Miner 4.1
The Oracle By Example tutorials teach you how to install and use Oracle Data Miner.
You can learn how to:
Set Up Oracle Data Miner 4.1: This tutorial covers the process of setting up Oracle Data Miner for use within Oracle SQL Developer 4.1 connected to Oracle Database 12c.
Use Oracle Data Miner 4.1: This tutorial covers the use of Oracle Data Miner 4.1 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.1.
Use Feature Selection and Generation with GLM: This tutorial covers the use of Oracle Data Miner 4.1 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.
Perform Text Mining with an Expectation Maximization Clustering Model: This tutorial covers the use of Oracle Data Miner 4.1 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.
Use Predictive Queries With Oracle Data Miner 4.1: This tutorial covers the use of Predictive Queries against mining data by using Oracle Data Miner 4.1.
Use 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.1 workflow.
The tutorials are in Oracle Learning Library under the Oracle Data Miner 12c OBE Series.
Sample Data is loaded in your account when you install Oracle Data Miner.
Note:The SH sample schema is not shipped with Oracle Database 12.2. To install the sample schema, go to DB Sample Schemas.
Oracle Data Miner Online Help
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 the F1 key.
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.
Search the Online Help
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 .
Oracle Data Mining Forum
Oracle Data Mining forum is a discussion forum to share, participate, and follow discussions related to data mining and Oracle Data Miner. You must log in to participate.
Participate in the Oracle Data Miner Discussion Forum to discuss about Oracle Data Miner and Oracle Data Mining.
Oracle Data Miner Documentation
Oracle Data Miner is the graphical user interface for Oracle Data Mining. 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 in the Database section.