Predicting using ORE package

In a previous post I gave a an overview of the various in-database data mining algorithms that you can use in your Oracle R Enterprise scripts.

To create data mining models based on those algorithms you need to use the ore.odm functions.

After you have developed and tested your models you will select one of these to score your new data.

How can you do this using ORE? There is a suite of ORE functions called ore.predict that you can use to apply your data mining model to score or label new data.

The following table lists the ore.predict functions:

ORE Predict Function	Description
ore.predict-glm	Generalized linear model
ore.predict-kmeans	k-Means clustering mode
ore.predict-lm	Linear regression model
ore.predict-matrix	A matrix with no more than 1000 rows
ore.predict-multinom	Multinomial log-linear model
ore.predict-nnet	Neural network models
ore.predict-ore.model	An Oracle R Enterprise model
ore.predict-prcomp	Principal components analysis on a matrix
ore.predict-princomp	Principal components analysis on a numeric matrix
ore.predict-rpart	Recursive partitioning and regression tree model

As you will see from the above table there are more ore.predict functions than there are ore.odm functions. The reason for this is that ORE comes with some additional data mining algorithms. These are in addition to the sub-set of Oracle Data Mining algorithms that it uses. These include the ore.glm, ore.lm, ore.neural and ore.stepwise.

You also need to watch out for the data mining algorithms that are not used in prediction. These include the Minimum Description Length, Apriori and Non-Negative Matrix Factorization.

Remember that these ore.predict functions are run inside the Oracle Database. No data is extracted to the data analyst laptop or desktop. All the data stays in the database. The ORE functions are run in the database on the data in the database

Using the in-database ODM algorithms in ORE

Oracle R Enterprise is the version of R that Oracle has that runs in the database instead of on your laptop or desktop.

Oracle already has a significant number of data mining algorithms in the database. With ORE they have exposed these so that they can be easily called from your R (ORE) scripts.

To access these in-database data mining algorithms you will need to use the ore.odm package.

ORE is continually being developed with new functionality being added all the time. Over the past 2 years Oracle have released and updated version of ORE about every 6 months. ORE is generally not certified with the latest version of R. But is slightly behind but only a point or two of the current release. For example the current version of ORE 1.4 (released only last week) is certified for R version 3.0.1. But the current release of R is 3.0.3.

Will ORE work with the latest version of R? The simple answer is maybe or in theory it should, but is not certified.

Let's get back to ore.dm. The following table maps the ore.odm functions to the in-database Oracle Data Mining functions.

ORE Function	Oracle Data Mining Algorithm	What Algorithm can be used for
ore.odmAI	Minimum Description Length	Attribute Importance
ore.odmAssocRules	Apriori	Association Rules
ore.odmDT	Decision Tree	Classification
ore.odmGLM	Generalized Linear Model	Classification and Regression
ore.odmKMeans	k-Means	Clustering
ore.odmNB	Naïve Bayes	Classification
ore.odmNMF	Non-Negative Matrix Factorization	Feature Extraction
ore.odmOC	O-Cluster	Clustering
ore.odmSVM	Support Vector Machines	Classification and Regression

As you can see we only have a subset of the in-database Oracle Dat Miner algorithms. This is a pity really, but I'm sure as we get newer releases of ORE these will be added.

Issues with using latest release of ODM

The title of this blog post makes it sound more dramatic than it actually is.

The reason for this blog post is down to me receiving a recent comment on the blog, plus having received numerous emails and a recent OTN Discussion Forum topic for Oracle Data Mining.

The main thing that they have in common is that if I use the latest version of Oracle Data Mining (ODM) it tells me that I need to upgrade my ODM Repository. What impact will this have?

The ODM Repository stores lots of information about the workflows you create using the (free) Oracle Data Mining tool that comes as part of SQL Developer. Yes you do have to pay for the OAA option, so is it really free? Well some part are like the explore node and the graph node.

If you download and want to use the latest version of the ODM tool or you want to try it out before rolling it out to others then you will need to upgrade your ODM repository.

And this the problem that people are facing.

If you upgrade then the ODM Repository it is updated to work with the latest version of the ODM tool. But what happens to everyone else who is using the previous release of the tool? The answer to that is they can no longer use ODM against their database.

Why is that? Well the version of the tool is tied to a version of the Repository. If you upgrade to the newer tool and repository then your older versions of the ODM tool no longer work.

The result of all of this is that you cannot have a mixture of versions of the ODM tool (SQL Developer) being used in your team/company.

There is a very simple solution to all of this. Everyone uses the same version of the ODM tool (i.e. the same version of SQL Developer). For example your team might be using SQL Dev 4 that was released last December. But in early March there was a new patch release 4.1. In order to use this new version of the tool all of your team needs to start using it at the same time. The first person to use it will be prompted to migrate the ODM repository. This is automatically done once you enter the password for SYS.

But in some teams this is not possible to do, you want to try out the tool to see that it works correctly before getting others to use it. The way around this is to have a separate database and use it for your testing. You can easily copy across your workflows and ODM objects to the test database.

This might not be possible for everyone, so what can you do. Create a Virtual Machine and try it out on your own desktop is one way.

The answer to this problem is not ideal, but hopefully you have a better idea of why things are happening this way and what you can or cannot do about it.

Like I said at the topic of this blog post that the title is a bit more dramatic than is really the case :-)

My next blog post will be on another question I've been asked a few times and this is 'When I go to use the ODM tool it tells me that the Oracle Text feature of Oracle needs to be enabled'

ORE 1.4 New Parallel feature

Oracle R Enterprise (ORE) 1.4 has just been released and can downloaded from here. Remember there is a client and server side install required and ORE 1.4 is certified against R 3.0.1 and the Oracle R Distribution

One of the interesting new features is the PARALLEL option. You can set this to significantly improve the performance of your R server side code by using the PARALLEL database option. You can set the degree of PARALLEL at a global level in your code by using the ore.parallel setting.

The default setting for this ore.parallel setting is FALSE or 1. Otherwise it must be set to a minimum of 2 of more to enable the Parallel database option.

Alternatively you can set the ore.parallel setting to TRUE to use the default degree of parallelism that is set for the database object or set to NULL to use the default database setting

You will also be able to set the degree of parallel (DOP) using the parallel enabled functions ore.groupApply, ore.rowApply and ore.indexApply.

They have also made available or as they say exposed some more of the in-database Oracle Data Mining algorithms. These include the ODM algorithms for Association rules (ore.odmAssocRules), the feature extraction algorithm called Non-Negative Matrix Factorization (NMF) (ore.odmNMF) and the ODM Clustering algorithm O-Cluster (ore.odmOC)

Watch out of some blog posts on these over the coming weeks.

Check out the OTN page for the R Technologies from Oracle

ODM: Changing the bar chart format in Explore Node

In Oracle Data Miner you can use the Explore Node to gather an initial set of statistics for your dataset. As part of this you will also get a bar chart that shows the distributions of the values contained within each attribute. The following example shows the default layout of the bar charts.

These graphs a very useful for presenting the initial data exploration results from to your business users. In addition to these graphs you can also use the Graph node to give some additional graphical representations.

But the default bar chart that is produced by the Explore Node can appear to be a bit basic.

So what if we could change the layout to have a 3-D effect. People like 3-D bar charts.

Is this possible in Oracle Data Miner? If so then how can we do it?

Well it is possible and you can use the following steps to change your bar charts to 3-D.

To access the Explore Node settings go the the Tools menu and then select Preferences from the drop down menu.

Then the Preferences window opens scroll down to the Data Miner option and expand the available options.

The Explorer Data Viewer allows you to change the Precision settings. The section option is the Graphical Settings. You can change the Depth Radius setting. By default this is set to Zero. By increasing this value you can change the degree of the 3-D effect of the bar charts. You can also change the colour scheme too.

I'm not a fan of the other colour schemes that are available and mu favourite is still the default Nautical. The following bar chart is the same as the one at the top of this post but has the 3-D effect.

ODM Graph Node new feature

With the recent release of SQL Developer 4.0.1 there has been some very minor bug fixes for Oracle Data Miner. But there has been one particular enhancement that I wanted to have a look at. This blog post will look at this new feature and how you can use it too. In the previously released version of the Oracle Data Miner tool we had a Graph Node. This is really a new feature that came with SQL Developer 4 and was available in the Early Adopter releases since July 2013. During the Early Adopter releases and with the official release of SQL Dev 4, the Line Graph feature of the Graph Node only allowed you to have one line drawn i.e. for a single Y attribute. Now in SQL Dev/ODM 4.0.1 the Graph Node has been updated to allow you to have multiple Line Graphs that are stacked. To illustrate this we will need to define what data source we are going to use and to create a shell of a Graph Node in Oracle Data Miner. The following diagram illustrates this. In this example I'm using the CARS data set that is available on OTN.

When you have these nodes created you are now ready to create your graphs. To do this double click on the Graph Node. You can now set the attribute to use for the X-axis, in my example this will be MODEL_YEAR. Then for the Y-Axis select the attributes you want to include in the stacked graph by holding down the control key as you select each attribute

You are now ready to create the graph. To do this click on the OK button and you will have your stacked line graph.

OUG Ireland

The annual OUG Ireland Conference (or special event) will be on Tuesday 11th March.  Actually this year there are sessions spread over 2 days, for the first time ever in the 10+ year history of OUG Ireland. In addition to 2 days of sessions there are 7 streams of presentations on the Tuesday and then there is the RAC AttacK for the first time in Ireland.

The main conference event is on Tuesday 11th March in the DCC in Dublin. Things kick off at 9:20 with Debra Lilley welcoming everyone to the event. Then Jon Paul from Oracle in Ireland will do the opening keynote. Then we can break into the 7 streams with lots of local case studies and some well known speakers from around the world including many Oracle  ACEs and ACE Directors (my presentation is at 12:15).

The day ends up with 2 keynote presentations. There will be a keynote that will be focused on the App streams (Nadia Bendjedou, Oracle) and a separate keynote for Tech streams (by Tom Kyte).

Throughout the day there will be RAC Attack event. Look out for their tables in the exhibition hall. Again there will be some well known experts from around the world who will be on hand to help you get RAC setup and running on your own laptop, answer your questions and engage in lots of discussions about all thing Oracle. The RAC Attack Ninja will include Osama Mustafa, Philippe Fierens, Marcin Przepiorowski, Martin Bach and Tim Hall. Some of these are giving presentations throughout the day, so when they are not presenting you will find them at the RAC Attack table. Even if you are not going to install RAC drop by and have a chat with them.

On Wednesday 12th March the OUG Ireland Conference ventures into a second day of sessions. These sessions will be a full day of topics by Tom Kyte. This is certainly a day not to be missed. As they say places are limited so book your place today.

Click on the following image to view the agenda for the 2 days and to book your place on the 11th and 12th March.

I hope to see you there and make sure you say hello to me.

Ralph Kimball talks about Hadoop and the Enterprise Data Warehouse

On April 2nd Cloudera is hosting a webinar featuring Ralph Kimball who will "describe how Apache Hadoop complements and integrates effectively with the existing enterprise data warehouse. The Hadoop environment's revolutionary architectural advantages open the door to more data and more kinds of data than are possible to analyze with conventional RDBMSs, and additionally offer a whole series of new forms of integrated analysis.

Dr. Kimball will explain how Hadoop can be both:

•   A destination data warehouse, and also

•   An efficient staging and ETL source for an existing data warehouse

You will also learn how enterprise conformed dimensions can be used as the basis for integrating Hadoop and conventional data warehouses."

When you sign up to the event you will be able to download a chapter from Ralph Kimball's book called The Data Warehouse ToolKit, called Big Data Analytics.

SQL Developer 4.0.1 is out

Oracle have released an updated version of SQL Developer and it is available to download from here.

Jeff Smyth has a blog post on some of the bug fixes in SQL Developer.

and Kris Rice also has a blog post on the new updated release.

So what about Oracle Data Miner. There seems to be a couple of minor new features on being able to select statistical outputs for the transform node. Also the model and test results viewers now automatically refresh if they are open. ODM can not be installed on Oracle Personal Edition (I haven't tried this out yet). 

Plus the Graph node can not have line charts based on mulitiple y axis attributes.  I'll have a blog post on this soon.

World Experts discuss Testing for Accuracy of Predictive Models

A week or two ago there was an article on  www.softwareadvice.com called Predictive Analytics : Testing for Accuracy.  In this article there was 3 very well know data scientist/data miners/predictive modellers. One of these is a Karl Rexter (a friend of mine form the BIWA world) along with Dean Abbott and John Elder.

People keep asking me what is the best way to test their data mining model, with most people expecting that they have to do lots and lots of statistics. They are then confused when I say ‘Oh No you Don’t’, all you need to do is ….   All you need to do is to follow the approaches that are detailed in their article. One thing that they all have in common is that they keep in mind the business problem and how/what the results they obtain mean for the business problem.

They shared their knowledge on testing predictive models for accuracy in a report from Software Advice, a company that reviews business intelligence software. They offer helpful information for other data miners on when and how to use: 

• Lift charts and decile tables to compare performance against random results
• Target shuffling to determine validity of the results
• Bootstrap sampling to test the consistency of the model

Ok Some statistics are used but not too many!!

View highlights from the report below or read it in its entirety here.  Alternatively have a look at the article summary on SlideShare.

3 Tests Experts Use to Validate Predictive Model Accuracy from Software Advice

OUG Ireland Presenters on Twitter

The agenda for OUG Ireland 2014 is now live. You can view the agenda and to register for the event by clicking on the following link.

Over the past couple of weeks some of the presenters have been using Twitter to share the news that they will be presenting at OUG Ireland. If you are not following them on twitter now is time to follow them. So here is the list (in no particular order) and I’ll start it off with myself

Brendan Tierney       @brendantierney

Debra Lilley               @debralilley

Tom Kyte                  @OracleAskTom

Tim Hall                    @oraclebase

Jon Paul                   @jonpauldublin

Roel Hartman           @RoelH

Uli Bethke                 @ubethke

Antony Heljula           @aheljula

Stewart Bryson         @stewartbryson

Patrick Hurley            @phurley

Joel Goodman          @JoelJGoodman

Philippe Fierens       @pfierens

Simon Haslam          @simon_haslam

Martin Nash               @mpnsh

Uwe Hesse                @UswHesse

Martin Bach               @MartinDBA

 

If I’m missing anyone let me know and I’ll add you to the list

Busy start to 2014 Speaking at Conferences **Updated

UPDATED list of conferences

We are just a few weeks into 2014 and it has been a busy time with Oracle User Group Conferences.

January : BIWA Summit

In January I gave 2 presentations at the BIWA Summit. This conference was held in the Oracle Convention Centre at Oracle Head Office.

March : OUG Ireland

I also have one presentation at the OUG Ireland conference in Dublin on the 11th March. As always this is a great day fill with sessions for well known speakers from around the world. This year we will have 6 tracks packed full. It is also a great opportunity to catch up with some friends I have known for 20+ years. Click on the following image for details of the agenda and how to register for the conference.

April : OUG Norway

I’ve also received notice that I will have 2 presentations at the Norway Oracle User Group. I’m delighted with this, as I was at this conference last year and really enjoyed it. This conference will be on the ship again this year between April 3-5, getting back into Oslo around 10am on the 5th April. Click on the following image for details of the agenda and how to register for the conference.

June : OUG Finland

In the past few days I’ve also received news that I will have 2 presentations at the Finland Oracle User Group conference. This will be my first time in Finland and I hope to get a few hours to do some exploring of Helsinki when I’m there. One of my presentations will be on using Oracle Data Miner and the second presentation will be on using R in the Oracle Database (or more correctly Oracle R Enterprise). Click on the following link for more details of the conference.

Hopefully I will see you at one of these conferences. Do make sure you say hello to me and let me know if you have any questions about the Oracle Advanced Analytics Option.

 

Second half of 2014

This second half of 2014 will probably be a bit quieter, but hopefully I’ll be at Oracle Open World in September (speaking or not) and also at the UKOUG Annual Conference (TECH14 or whatever it will be called) in December (speaking or not).

 

My travels (flights and hotel costs) to present at these conferences is made possible thanks to the Oracle ACE Director program. Also to DIT for allowing me to go.