Oracle 12.2]

ODM Model View Details Views in Oracle 12.2

Posted on

A new feature for Oracle Data Mining in Oracle 12.2 is the new Model Details views.

In Oracle 11.2.0.3 and up to Oracle 12.1 you needed to use a range of PL/SQL functions (in DBMS_DATA_MINING package) to inspect the details of a data mining/machine learning model using SQL.

Check out these previous blog posts for some examples of how to use and extract model details in Oracle 12.1 and earlier versions of the database

Association Rules in ODM-Part 3

Extracting the rules from an ODM Decision Tree model

Cluster Details

Viewing Decision Tree Details

Instead of these functions there are now a lot of DB views available to inspect the details of a model. The following table summarises these various DB Views. Check out the DB views I’ve listed after the table, as these views might some some of the ones you might end up using most often.

I’ve now chance of remembering all of these and this table is a quick reference for me to find the DB views I need to use. The naming method used is very confusing but I’m sure in time I’ll get the hang of them.

NOTE: For the DB Views I’ve listed in the following table, you will need to append the name of the ODM model to the view prefix that is listed in the table.

table, th, td {
border: 1px solid black;
border-collapse: collapse;
text-align: left;
}

Data Mining Type Algorithm & Model Details 12.2 DB View Description
Association Association Rules DM$VR generated rules for Association Rules
Frequent Itemsets DM$VI describes the frequent itemsets
Transaction Itemsets DM$VT describes the transactional itemsets view
Transactional Rules DM$VA describes the transactional rule view and transactional itemsets
Classification (General views for Classification models) DM$VT

DM$VC

describes the target distribution for Classification models

describes the scoring cost matrix for Classification models

Decision Tree DM$VP

DM$VI

DM$VO

DM$VM

describes the DT hierarchy & the split info for each level in DT

describes the statistics associated with individual tree nodes

Higher level node description

describes the cost matrix used by the Decision Tree build

Generalized Linear Model DM$VD

DM$VA

describes model info for Linear Regres & Logistic Regres

describes row level info for Linear Regres & Logistic Regres

Naive Bayes DM$VP

DM$VV

describes the priors of the targets for Naïve Bayes

describes the conditional probabilities of Naïve Bayes model

Support Vector Machine DM$VL describes the coefficients of a linear SVM algorithm
Regression ??? Doe 80 50
Clustering (General views for Clustering models) DM$VD

DM$VA

DM$VH

DM$VR

Cluster model description

Cluster attribute statistics

Cluster historgram statistics

Cluster Rule statistics

k-Means DM$VD

DM$VA

DM$VH

DM$VR

k-Means model description

k-Means attribute statistics

k-Means historgram statistics

k-Means Rule statistics

O-Cluster DM$VD

DM$VA

DM$VH

DM$VR

O-Cluster model description

O-Cluster attribute statistics

O-Cluster historgram statistics

O-Cluster Rule statistics

Expectation Minimization DM$VO

DM$VB

DM$VI

DM$VF

DM$VM

DM$VP

describes the EM components

the pairwise Kullback–Leibler divergence

attribute ranking similar to that of Attribute Importance

parameters of multi-valued Bernoulli distributions

mean & variance parameters for attributes by Gaussian distribution

the coefficients used by random projections to map nested columns to a lower dimensional space

Feature Extraction Non-negative Matrix Factorization DM$VE

DM$VI

Encoding (H) of a NNMF model

H inverse matrix for NNMF model

Singular Value Decomposition DM$VE

DM$VV

DM$VU

Associated PCA information for both classes of models

describes the right-singular vectors of SVD model

describes the left-singular vectors of a SVD model

Explicit Semantic Analysis DM$VA

DM$VF

ESA attribute statistics

ESA model features

Feature Section Minimum Description Length DM$VA describes the Attribute Importance as well as the Attribute Importance rank

Normalizing and Error Handling views created by ODM Automatic Data Processing (ADP)

  • DM$VN : Normalization and Missing Value Handling
  • DM$VB : Binning

Global Model Views

  • DM$VG : Model global statistics
  • DM$VS : Computed model settings
  • DM$VW :Alerts issued during model creation

Each one of these new DB views needs their own blog post to explain what informations is being explained in each. I’m sure over time I will get round to most of these.

Advertisements

12.2 DBaaS (Extreme Edition) possible bug/issue with the DB install/setup

Posted on

A few weeks ago the 12.2 Oracle Database was released on the cloud. I immediately set an account and got my 12.2 DBaaS setup. This was a relatively painless process and quick.

For me I wanted to test out all the new Oracle Advanced Analytics new features and the new features in SQL Developer 4.2 that only become visible when you are using the 12.2 Database.

When you are go to use the Oracle Data Miner (GUI tool) in SQL Developer 4.2, it will check to see if the ODMr repository is installed in the database. If it isn’t then you will be promoted for the SYS password.

This is the problem. In previous version of the DBaaS (12.1, etc) this was not an issue.

When you go to create your DBaaS you are asked for a password that will be used for the admin accounts of the database.

But when I entered the password for SYS, I got an error saying invalid password.

After using ssh to create a terminal connection to the DBaaS I was able to to connect to the container using

sqlplus / as sysdba

and also using

sqlplus sys/ as sysdba

Those worked fine. But when I tried to connect to the PDB1 I got the invalid username and/or password error.

sqlplus sys/@pdb1 as sysdba

I reconnected as follows

sqlplus / as sysdba

and then changed the password for SYS with containers=all

This command completed without errors but when I tried using the new password to connect the the PDB1 I got the same error.

After 2 weeks working with Oracle Support they eventually pointed me to the issue of the password file for the PDB1 was missing. They claim this is due to an error when I was creating/installing the database.

But this was a DBaaS and I didn’t install the database. This is a problem with how Oracle have configured the installation.

The answer was to create a password file for the PDB1 using the following

% orapwd file=$ORACLE_HOME/dbs/orapwPDB1 password= entries=10

I then changed the password again for SYS, then tried to connect as SYS to the PDB1, and if by magic I was connected.

I then tried installing the ODMr repository again (in SQL Developer) and when I entered the new password for SYS, it worked !

It's a pity that it took Oracle Support 2 weeks to get me to this point.

As 12.2 is a cloud service hopefully Oracle will get that issue fixed soon so that one one else has to suffer like I did.

New OAA features in Oracle 12.2 Database

Posted on

The Oracle 12.2c Database has been released and is currently available as a Cloud Service. The on-site version should be with us soon.

A few weeks ago I listed some of the new features that you will find in the Oracle Data Miner GUI tool (check out that blog post). I’ll have another blog post soon that looks a bit closer at how the new OAA features are exposed in this tool.

In this blog post I will list most of the new database related features in Oracle 12.2. There is a lot of new features and a lot of updated features. Over the next few months (yes it will take that long) I’ll have blog posts on most of these.

The Oracle Advanced Analytics Option new features include:

  • The first new feature is one that you cannot see. Yes that sound a bit odd. But the underlying architecture of OAA has been rebuilt to allow for the algorithms to scale significantly. This is also future proofing OAA for new features coming in future releases of the database.
  • Explicit Semantic Analysis. This is a new algorithm allows us to perform text similarity comparison. This is a great new addition and and much, much easier now compared to what we may have had to do previously.
  • Using R models using SQL. Although we have been able to do this in the previous version of the database, the framework and supports have been extended to allow for greater and easier usage of user defined R scripts and R models with the in-database environment.
  • Partitioned Models. We can now build partitioned mining models. This is where you can specify an attribute and a separate model will be created based on each value in the attribute.
  • Partitioned scoring. Similarly we can now dynamically score the data based on an partition attribute.
  • Extentions to Association Rules. Over the past few releases of the database, additional insights to the workings and decision making of the algorithms have been included. In 12.2 we now have some additional insights for the Association Rules aglorithm where we can now get to see the calculation of values associated with rules.
  • DBMS_DATA_MINING package extended. This PL/SQL package has been extended to include the functionality for the new features listed above. Additional it can now process R algorithms and models.
  • SQL Function changes: Change to the followi ODM related SQL functions to allow for partitioned models. CLUSTER_DETAILS, CLUSTER_DISTANCE, CLUSTER_ID, CLUSTER_PROBABILITY, CLUSTER_SET, FEATURE_COMPARE, FEATURE_DETAILS, FEATURE_ID, FEATURE_SET, FEATURE_VALUE, ORA_DM_PARTITION_NAME, PREDICTION, PREDICTION_BOUNDS, PREDICTION_COST, PREDICTION_DETAILS, PREDICTION_PROBABILITY, PREDICTION_SET
  • New SQL Hint for ODM models. We have had hints in SQL for many, many versions now, but with 12.2c we now have a hint for partitioned models, called GROUPING hint.
  • New CREATE_MODEL function. With the existing CREATE_MODEL function the input data set for the function needed to be defined in a table or accessed using a view. Basically the data needed to resist somewhere. With CREAETE_MODEL2 you can now define the input data set based on a SELECT statement.

In addition to all of these changes there are also some new interesting DB, SQL and PL/SQL new features that are of particular interest for your data science, machine learning, advanced analytics (or whatever the current favourite marketing term is today) projects.

It is going to be a busy few months ahead, working through all of these new features and write blog posts on how to use each of them.