Wednesday, July 20, 2011

Data Exploration using Oracle Data Miner 11gR2

Before beginning any data mining task we need to performs some data investigation. This will allow us to explore the data and to gain a better understanding of the data values. We can discover a lot by doing this can it can help us to identify areas for improvement in the source applications, as well as identifying data that does not contribute to our business problem (this is called feature reduction), and it can allow us to identify data that needs reformatting into a number of additional features (feature creation). A simple example of this is a date of birth field provides no real value, but by creating a number of additional attributes (features) we can now use the date of birth field to determine what age group they fit into.

As with most of the interface in Oracle Data Miner 11gR2, there is a new Data Exploration interface. In this blog post I will talk you through how to set-up and use the new Data Exploration interface and show you how you can use the data exploration features to gain an understanding of the data before you begin using the data mining algorithms.

The examples given here are based on my previous blog posts and we will use the same sample data sets, that were set-up as part of the install and configuration.

See my other blog post and videos on installing and setting up Oracle Data Miner.

Data Set-up

Before we can begin the data exploration we need to identify data we are going to use. To do this we need to select the Data tab from the Component Palette, and then select Data Source.image

To create the Data Node on our Workflow we need to click and drag the Data Source onto the workflow. Select the MINING_DATA_BUILD_V and select all the data.image

The next step is to create the Explore Data node on our workflow. From the Data tab in the Component Palette, select and drag the Explore Data node onto the workflow. Now we need to link the Data node to the Explore Data node.


Right-click on the Explore Data mode and click Run. This will make the ODM tool go to the database and analyse the data that is specified in our Data node. The analyse results will be used in the Explore Data note.

Exploring the Data

When the Explore Data node has finished we can look at the data it has generated. Right-click the Explore Data node and select View Data.


A lot of statistical information has been generated for each of the attributes in our Data node. In addition to the statistical information we also get a histogram of the attribute distributions.

We can work through each attribute taking the statistical data and the histograms to build up a picture of the data.

The data we are using is for an Electronics Goods store.

A few interesting things in the data are:

  • 90% of the data comes from the United States of America
  • PRINTER_SUPPLIES attribute only has one value. We can eliminate this from our data set as it will not contribute to the data mining algorithms
  • Similarly for OS_DOC_SET_KENJI, which also has one one value

The histograms are based on predetermined number of bins. This is initially set to 10, but you may need to changed this value up or down to see if a pattern exists in the data.

An example of this is if we select AGE and set the number of bins to 10. We get a nice histogram showing that most of our customers are in the 31 to 46 age ranges. So maybe we should be concentrating on these.


Now if we change the number of bins to 30 can get a completely different picture of what is going on in the data.

To change the number of bin we need to go to the Workflow pane and select the Property Inspector. Scroll down to the Histogram section and change the Numerical Bins to 25. You then need to rerun the Explore Data node.


Now we can see that there are a number of important age groups what stand out more than others. If we look at the 31 to 46 age range, in the first histogram we can see that there is not much change between each of the age bins. But when we look at the second histogram for the 25 bins for the same 21 to 34 age range we get a very different view of the data. In this second histogram we see that that the ages of the customers vary a lot. What does mean. Well it can mean lots of different things and it all depends on the business scenario. In our example we are looking at an electronic goods store. What we can deduce from this second histogram is that there are a small number of customers up to about age 23. Then there is an increase. Is this due to people having obtained their main job after school having some disposable income. This peak is followed by a drop off in customers followed by another peak, drop off, peak, drop off etc. Maybe we can build a profile of our customer based on their age just like what our financial organisations due to determine what products to sell to use based on our age and life stage.

Conclusions on the data

From this histogram we can maybe categorise the customers into the follow

• Early 20s – out of education, fist job, disposable income
• Late 20s to early 30s – settling down, own home
• Late 30s – maybe kids, so have less disposable income
• 40s – maybe people are trading up and need new equipment. Or maybe the kids have now turned into teenagers and are encouraging their parents to buy up todate equipment.
• Late 50s – These could be empty nesters where their children have left home, maybe setting up home by themselves and their parents are building things for their home. Or maybe the parents are treating themselves with new equipment as they have more disposable income
• 60s + – parents and grand-parents buying equipment for their children and grand-children. Or maybe we have very techie people who have just retired
• 70+ – we have a drop off here.

As you can see we can discover a lot in the day by changing the number of bins and examining the data. The important part of this examination is trying to relate what you are seeing from the graphical representation of the data on the screen, back to the type of business we are examining. A lot can be discovered but you will have to spend some time looking for it.

ODM 11gR2 Extra Data Exploration Functionality

In ODM 11gR2 we now have an extra feature for our data analysis feature. We can now produce the histograms that are grouped by one of the other attributes. Typically this would be the Target or Class attribute but you can also use it with the other attributes.

To set this extra feature, double click on the Explore Data node. The Group By drop down lets you to select the attribute you want to group the other attributes by.


Using our example data, the target variable is AFFINITY_CARD. Select this in the drop down and run the Explore Data node again. When you look at the newly generated histograms you will now see each bin has two colours. If you hover the mouse of each coloured part you will be able to get the number of records in each group. You can use other attributes, such as the CUST_GENDER, COUNTRY_NAME, etc. Only use the attributes where it would make sense to analyse the data by.


This is a powerful new feature that allows you to gain a deeper level of insight into the data you are analysing

Brendan Tierney

Paper at UKOUG annual conference 2011

I recently received word that one of my two submissions has been accepted for the annual UKOUG conference in Birmingham (UK).

The paper is titled ‘How to deploy your Oracle Data Miner 11gR2 Workflow in a Live Environment’.  This presentation is scheduled to be on Wednesday 7th December between 3:20pm and 4:05pm. This is a 40 minute presentation, which is not a lot of time really given the topic to be covered. I’ll have to see what I can squeeze in.

My second submission is on the reserve list. This means if someone drops out of the schedule or decides that they do not want to give their presentation then I can give my presentation called ‘Oracle Data Miner – New Features’. This presentation is the same as my VirtaThon presentation on July 18th, 2011.

Monday, July 18, 2011

VirtaThon Presentation

Today I gave my VirtaThon presentation on the new Oracle Data Miner 11gR2 tool.

It was an interesting experience as VirtaThon was a virtual conference. The organisation and administration of the conference was excellent.

I had over 25 participants for my presentation, including Carolyn Hamm who has written a book on using Oracle Data Miner 10g.  She seemed to enjoy my presentation as she was asking for more at the end, but we had run out of time.

The presentation was an unusual but interesting experience. All the participants were muted, so I could not hear anyone or be asked questions as the presentation progressed. I was not able to judge the body language or facial expressions, for me to work out how the presentation was going.

I was sitting in my living room when giving the presentation and spent almost an hour talking to myself. At time the concentration levels dipped and I have to refocus and used some visualisation to help me concentrate.

The presentation was divided into 2 parts. The first part was a presentation consisting of some background to ODM, how to get setup and running with ODM, and finally a discussion of some of the new features. This first part took approx. 30 minutes which surprised me as during my rehearsals it was talking 16 minutes. The second part of the presentation was a demo of using ODM to create a workflow, generating a classification model and then applying this model to some new data. During my rehearsals this was taking approx. 40 minutes.

I only had 50-55 minutes for my VirtaThon presentation so after my presentation I had 20-25 minutes for the demo. So I had to get through the demo quickly and I had to cut out a discussion of how the data exploration functionality in ODM can be used to get an insight into the data before you start using the data mining features. I will put together a blog post and video of this in a couple of weeks time that will explain it in more detail.

I managed to finish at 49 minutes, which left 6 minutes for questions. There was only a couple of questions, plenty of Thank You’s along with Good Presentation, which is always good to hear.

Thank you to everyone who attended the presentation and to the organisers of VirtaThon.

Brendan Tierney

Friday, July 15, 2011

My Presentation at VirtaThon 2011

I will be giving a presentation on the Oracle Data Miner New Features at the online conference VirtaThon, on Monday 18th July.

VirtaThon is a FREE 6 day conference with 2 parallel sessions with world leading speakers on Oracle Java and MySQL. 

Previously attendance at the conference cost $100, which was good value considering the quality of the speakers. But this year it is Free.

The VirtaThon conference runs from 16th July to 21st July

The schedule is available at

To sign up to attend some or all of the sessions go to

Attend4FREE! Jul 16-21: 6 Days of Expert+ Sessions #VirtaThon The Online Conference for the Oracle, Java & MySQL Domains

Wednesday, July 13, 2011

My first Oracle Scene planning meeting

I had my first meeting, as a deputy editor, with the Oracle Scene team on Tuesday 12th July. The meeting was organised by Suzanne Gaunt, who is leaving the UKOUG on Friday. She will be sadly missed. Lavinia and Karina are taking over the role of producing the magazine.

Also at the meeting was Neil Jarvis, the editor, and Gillian and Philip Adams from, who look after the whole production of the magazine.

The main topic of discussion was the production process for the magazine and an outline of the timelines involved.

There was some discussions on how to improve the magazine with new material. Some of the suggestions included

  • From the editors section to be added back in. This is a half page from each of the deputy editors. For myself this will be an introduction/bio in the next edition
  • Ask the editors section, where the readers can submit questions to the editorial team for them to attempt to answer and hopefully in a humorous way
  • Articles from the different regions and SIGs. This is to allow the user group to get to know what the other parts of the group are up to
  • A Blog summary. This will be a round up of some of the blogs from user group community
  • Introduce a competition in each edition
  • Unusual Photo competitions
  • Book reviews, which maybe Book giveaways from the publishers

In addition to these suggestions the main content of articles from the user community. Everyone is invited to write an article. It does not matter how long or short it is. The main idea is for you to share some of your knowledge. An article can be of any length, for example it could be 1/4 page, 1/2 page, 1, 2, 3 or 5 pages.

Have you come across a new or unusual feature, did you do something interesting with a feature, how and why you implemented it, case studies, new tools, etc.

So articles could be technical or non-technical, or on anything that might be of interest to the user community.

The next deadline for submitting articles is 26th August and with all deadlines it is good to submit early!

The finished magazine will be ready for distribution around the end of October.

If you have any suggestions of changes, additions, articles or adverting, let me know.

Friday, July 8, 2011

Exporting & Importing Oracle Data Miner (11gR2) Workflows

As with all development environments there will be need to move your code from one schema to another or from one database to another.

With Oracle Data Miner 11gR2, we have the same requirement. In our case it is not just individual procedures or packages, we have a workflow consisting of a number of nodes. With each node we may have a number of steps or functions that are applied to the data.

Exporting an ODM (11gR2) Workflow

In the Data Miner navigator, right-click the name of the workflow that you want to export.

The Save dialog opens. Specify a location on you computer where the workflow is saved as an XML file.

The default name for the file is workflow_name.xml, where workflow_name is the name of the workflow. You can change the name and location of the file.


Importing an ODM (11gR2) Workflow

Before you import your ODM workflow, you need to make sure that you have access the the same data that is specified in the workflow.

All tables/views are prefixed with the schema where the table/view resides.

You may want to import the data into the new schema or ensure that the new schema has the necessary grants.

Open the connection in ODM.

Select the project under with you want to import the workflow, or create a new project.

Right click the Project and select Import Workflow.

Search for the XML export file of the workflow.

Preserve the objects during the import.

When you have all the data and the ODM workflow imported, you will need to run the entire workflow to ensure that you have everything setup correctly.

It will also create the models in the new schema.

Data encoding in Workflow

All of the tables and views used as data sources in the exported workflow must reside in the new account

The account from which the workflow was exported is encoded in the exported workflow e.g. the exported workflow was exported from the account DMUSER and contains the data source node with data MINING_DATA_BUILD. If you import the schema into a different account (that is, an account that is not DMUSER) and try to run the workflow, the data source node fails because the workflow is looking for USER.MINING_DATA_BUILD_V.

To solve this problem, right-click the data node (MINING_DATA_BUILD_V in this example) and select Define Data Wizard. A message appears indicating that DMUSER.MINING_DATA_BUILD_V does not exist in the available tables/views. Click OK and then select MINING_DATA_BUILD_V in the current account.


I have created a video of this blog. It illustrates how you can Export a workflow and Import the workflow into a new schema.

ODM 11gR2 - Exporting and Importing ODM Workflows

Make sure to check out my other Oracle Data Miner (11gR2) videos.

Wednesday, July 6, 2011

Installing Oracle Apex 4.0 on 11.2g

Over the past few days I’ve been trying to install Oracle Apex 4 on my 11.2g database.  I say trying as I’ve made a number of attempts with no success. I started with the install instructions that come with Apex 4. Generally Oracle installs and install instructions have improved greatly since the 6, 7 and 8i versions.

I had high hopes of an easy install (as indicated by the various Oracle Apex books), but no matter what version of the install instructions I found there always seemed to be a step missing.

I finally came across one set of instructions that worked for me. The following steps are what I performed to get Apex 4 working.

1. Download Apex 4 from OTN to the directory


2. Unzip the Apex 4 download file. It will create the directory


3. Login into SQL*Plus as SYS with SYSDBA

4. Run the Apex 4 install script

c:\apex_download\Apex\apexins.sql SYSAUX SYSAUX TEMP /i/

where SYSAUX is the tablespace for Apex, TEMP is the temporary tablespace and /i/ is needed for possible upward compatability

This steps can take up to 30 minutes to run

5. Load the Apex images into the database.

    - Got to the c:\apex_download\Apex directory.

    - Log into SQL*Plus as SYS with SYSDBA

    - run   @apxldimg.sql

    - You will be asked to enter the directory for the images. Make sure that you enter the correct directory, otherwise it will not work. In my case it is


6. Run the Configuration script. This will set up the XDB HTTP connection details.


   - enter the port number : 8080

   - password

7. Unlock the required schema


8. Open Apex. Open your browser and enter


     there is a default workspace created

      Workspace = internal

      Username = admin

       Password = admin

6. Change the password. The fist time you login you will be prompted to change the password. The new password needs to have a number, upper and lower case characters and one special character

7. To get the the Apex Admin page


Friday, June 24, 2011

Irish BI SIG–23rd June 2011

On Thursday 23rd June the Irish BI SIG had a networking event on the Mv Cillairne boat. This is a former training boat that has been converted into a restaurant and bar. The boat is moored beside the new convention centre on the quays in Dublin near the O2.cill_airne_at_night

There was a good turn out, with a mixture of people from An Post, ICON, Vertice, Fijitsu and some independent consultants/contractors.

There was a few fee drinks and some food provided by the UKOUG. Many thanks for these.

There was lots of sharing of what BI related projects people have worked. There was some discussions of how the SIG can progress in the future, with the consensus that people will need to be willing to present on their projects and experiences.

Tony Cassidy, the SIG Chair, is hoping to get a few volunteers to present at the next SIG or maybe have another social networking event.

I also did my bit for the Oracle Scene magazine, in asking people would they write an article (even if it is a short one) for the next edition. I’ve recently joined the editorial team of Oracle Scene.

Tuesday, June 21, 2011

OOW Oracle Mix 2011–End Result

After 3 weeks the voting on Oracle Mix has come to an end.  There was a record number of submissions and voting this year on Oracle Mix.

Based on the results from previous years anyone with a vote count over 30 seemed to have gotten they presentation accepted for Oracle Open World.

With this years increase interest, number of presentations and the total number of votes cast it looks like the you will need at least 87 votes to get into the top 20.

The organisers say that the winners are select based on the total number of votes case, but they can adjust things as this see fit – whatever that means.

I had submitted 3 presentations – it was my first attempt at submitting to Oracle Open World. 

My best place (or most popular) presentation was ‘Using Oracle Data Miner 11gR2 & SQL Dev. for Data Mining’. For this presentation I received 56 votes, which puts me in 52nd place. 

So it looks like I’m way outside making to short list for OOW11.  Maybe next year.

I’ve also submitted 2 of my presentations to the annual UKOUK conference in December. The closing date was the 5th June and the results should be out in August.

Fingers crossed.

Friday, June 3, 2011

Vote for my Oracle Open World Presentation on Oracle Mix

I’ve submitted the following 3 presentations on Oracle Mix, to be included in the voting process.

Voting finishes on 19th June.

Please vote for my presentations.  As they stay about elections in Ireland, Vote early and Often.

Upskilling a Data Developer for Data Mining

Oracle Data Mining has been available for many years now and has proven to be a powerful tool but seems to be over looked by longer established products that are a lot more expensive. Many companies have put significant work into development their BI environments. But what can they do now to improve their organisational knowledge. This presentation will look at how a database developer is more suited to doing data mining than someone with a PhD in statistics. Using the ODM tool and the CRIP-DM life-cycle it will be demonstrated how a data mining project can be conducted

See my YouTube channel for a my videos on Oracle Data Miner 11g R2


Using Oracle Data Miner 11gR2 & SQL Dev. for Data Mining

With the release of the new Oracle Data Mining tool and it being part of SQL Developer, this presentation will have a look at how these too tools can be used in combination. In particular the presentation will focus on the Data Understanding stage of the CRISP-DM Life Cycle. Using the key elements of the Data Understand stage the presentation will look at how a database developer can use the new features of the new Oracle Data Mining tool in conjunction with SQL Developer to explore the data with the aim of gaining a key insight into the data.

See my YouTube channel for my videos on Oracle Data Miner 11g R2


How to Deploy your Oracle Data Miner 11g R2 Workflows in a Live Environment

The new Oracle Data Miner 11g R2 tool is now easier to develop your Data Mining models and workflows. A Data Mining project has two main stages. This presentation will look at how you take your Data Mining workflow and Data Mining Model, that have been developed using the new Oracle Data Miner 11g R2 tool. It will show you have to extract the SQL code from the work flow to perform the Data Transformations, execute the Data Mining Model, how you can link these to your new data and finally how you can apply the model.

See my YouTube channel for my videos on the Oracle Data Miner 11g R2 tool

Thursday, June 2, 2011

Irish Oracle BI SIG Meeting - 23rd June

The next Irish Oracle BI SIG meeting will be on Thursday 23rd June starting at 6:30pm.

The format of this SIG meeting is a bit different from the previous ones.

This time the SIG meeting will be an informal networking event and there will be no demos or presentations.

The SIG event will be in the River View Bistro Bar, which is on the the MV Cillairne boat, that is moored beside the new convenion center on the quays. Check out its website

Friday, May 27, 2011

Creating ODM Schemas & Repository for ODM 11g R2

Before you can start using the Oracle Data Miner features that are now available in SQL Developer 3, there are a few steps you need to perform. This post will walk you through these steps and I have put together a video which goes into more detail. The video is available on my YouTube channel.

Oracle Data Miner 11g R2 : Creating ODM User & Repository video

I will be posting more How To type videos over the coming weeks and months. Each video will focus in one one particular feature within the new Oracle Data Mining tool.

So following steps are necessary before you can start using the ODM tool

Set up of Oracle Data Miner tabs

To get the ODM tabs to display in SQL Developer, you need to go to the View menu and select the following from the Data Miner submenu

  • Data Miner Connections
  • Workflow Jobs
  • Property Inspector


Create an ODM Schema

There are two main ways to create a Schema. The first and simplest way is to use SQL Developer. To do this you need to create a connection to SYS. Right click on the Other Users option and select Create User.

The second option is to use SQL*Plus to create the user. Using both methods you need to grant Connect & Resource privileges to the user.

Create the Repository

Before you can start using Oracle Data Mining, you need to create an Oracle Data Miner Repository in the database. Again there are two ways to do this. The simplest is to use the inbuilt functionality in SQL Developer. In the Oracle Data Miner Connections tab, double click on the ODM schema you have just created. SQL Developer will check the database to see if the ODM Repository exists in the database. If it will create the repository for you. But you will need to provide the SYS password.

The other way to create the repository is to run the installodmr.sql script that in available in the ‘datamining’ directory.

@installodmr.sql <default tablespace> <temp tablespace>

example:   @installodmr.sql USER TEMP

Create another ODM Schema

It is typical that you would need to have more than one schema for your data mining work. After creating the default Oracle schema, the next step is to grant the schema the privileges to use the Data Mining Repository. This script is called

usergrants.sql <DM Schema>

example:    @usergrants.sql DMUSER

Hint: The schema name needs to be in upper case. 

IMPORTANT: The last grant statement in the script may give an error. If this occurs then it is due to an invalid hidden character on the line. If you do a cut and paste of the grant statement and execute this statement, everything should run fine.

If you want to demo data to be created for this new ODM schema then you need to run

@instdemodata.sql <DM Schema>

example:    @instdemodata.sql DMUSER

All of these scripts can be found in SQL developer directories