2 Day Oracle Data Miner training course by Oracle University

In the past few days Oracle University has advertised a new 2 Day instructor led training course on Oracle Data Miner.

There are no advertised dates or locations for this course yet. I suppose it will depend on the level of interest in the product.

There is the overview from the Oracle University webpage

“In this course, students review the basic concepts of data mining and learn how leverage the predictive analytical power of the Oracle Database Data Mining option by using Oracle Data Miner 11g Release 2. The Oracle Data Miner GUI is an extension to Oracle SQL Developer 3.0 that enables data analysts to work directly with data inside the database.

The Data Miner GUI provides intuitive tools that help you to explore the data graphically, build and evaluate multiple data mining models, apply Oracle Data Mining models to new data, and deploy Oracle Data Mining's predictions and insights throughout the enterprise. Oracle Data Miner's SQL APIs automatically mine Oracle data and deploy results in real-time. Because the data, models, and results remain in the Oracle Database, data movement is eliminated, security is maximized and information latency is minimized”

Click on the following link to access the details of the training course

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getCourseDesc?dc=D73528GC10

To view a PDF of the course details – click here

Oracle Magazine–Winter 1994

The headline articles of the Winter 1994 edition of Oracle Magazine included topics on how an energy company migrated to client/server, towards the paperless office (it never really happened!!), Business Processing Reengineering and document management.

There was a noticeable number of articles and advertisements on reporting tools. The tools mentioned included Oracle Browser version 2.0, Oracle Data Query, Impromptu, Intelligent Query (IQ), Visual CyberQuery, R&R Report Writer, ReportSmith and SQL Assist

There was also an article called ‘An Elegant Approach to Report Generation’ and looked at how you could take the ASCII file produced by a SQL report and load it into a WordPerfect template.

We have the first mention of on Oracle Certification. ‘Database administrators and application developers can now become Oracle Certified to demonstrate their skill and competence in using Oracle systems. ……. Upon successfully completing the exam – which covers such areas as using SQL and Oracle Forms, tuning applications, and administrating and Oracle database – students will be awarded with the title of Oracle Certified Administrator and/or Oracle Certified Application Developer’.

Oracle announces the launch of their Real-Time Support System. Unlike My Oracle Support we have today, back in 1994 you needed a modem and remote communications package that could emulate a SQL*Forms support terminal type.

There was an article, by Kevin Loney, on how you could add Help documentation to your Oracle Forms applications using Oracle*Text Retrieval 2.0.

Finally there was an article by the IOUG about their International Oracle User Week in 1993. This event was held in the Orange County Convention Center in Orlando FL, where there was an attendance of 3,300 participants. This conference has since been expanded and is now call COLLABORATE and will be in Las Vegas in 2012 on 22-26 April (I wish I could be there).

To view the cover page and the table of contents click on the above image or click here.

My Oracle Magazine Collection can be found here. You will find links to my blog posts on previous editions.

My OUG Ireland Conference Presentations

Wednesday 21st March an important for the OUG Ireland as it is the annual conference. This year we are in a new venue, the Dublin Convention Centre, on the river Liffey.

After many, many years of being an attendee of my local conference, this year I will be presenting 2 presentations. Actually, I’ll be presenting one and co-presenting another.

My first presentation, will be an introduction to Oracle Data Miner, which is now part of SQL Developer. I will be talking about the new features and some features that be part of a future presentation. Most of the presentation time will be taken up with a Demonstration of using Oracle Data Miner (ODMr). I will step through a the main steps of data mining using the ODMr tool. The data set that I will be using is based on a University in the UK who wanted to look at how data mining could be used to help them manage student retention/churn.

The second presentation will be lead by Antony Heljula, of Peak Indications, with me co-presenting or butting in on some topics. This presentation will be at a much higher level. This presentation will be aimed at analysts and managers who are looking at data mining and what it can do for them.We will look at what it can be used for, who are the main people, some sample case studies/application areas, data quality issues, etc. There will be a demonstration on how you can incorporate the data mining model, developed in the first presentation, into OBIEE Dashboards. We will be using the same UK University scenario here and we will show how data mining has helped to identify specific types students that could not be identified using other means.

Check out the full conference agenda – here

There are plenty of excellent presentations, with lots of Oracle ACE’s and Oracle ACE Directors.

Some of my other activities on the day will be:

• Talking to people about writing articles for the Oracle Scene, the user group manage. I’m the deputy editor of Oracle Scene.
• I’m also deputy chair of the Irish BI & EPM SIG, so I’ll be trying to persuade people take part in and present at future meetings.
• Finally and perhaps most importantly, I will be meeting other people in the Oracle world here in Ireland. Some of these people I know for 20+ years. Because of busy schedules sometimes the only time we get to catch-up is at the annual conference.

If you would like to talk to me about the topics covered in the presentations or about any of the about activities, look out for me during the day. I will be at the (free) drinks reception at the end of the day, so you can talk to me then. If that does not suit, then drop me an email and we can arrange to meet up.

Oracle Magazine–Fall 1993

The headline articles of the Fall 1993 edition of Oracle Magazine included topics on Multimedia Servers, Free Space Defragmentation and Geographic Information Systems.

Apart from the head line articles most of the other articles were about product announcements/updates and about how certain types of companies were using IT.

The main technical article was by Kevin Loney on freeing up free space in an Oracle 6 database using defragmentation. This was a popular topic for Oracle version 6 and 7, when disk space was expensive. It is less of an issue today.

There was an article on the Digital Highway and in Multimedia Servers. These were early indicators of Larry’s investing in the Video On-Demand servers.

The first maintenance release of Oracle 7 database was announced (Oracle 7.1), with enhancements centred around:

• Parallel Operations : performance increases, splitting of query execution, data loading and index creation tasks and execute them concurrently on multiple CPS. Permits multiple sessions running SQL*Loader using the fast bulk load i.e.direct path
• Application Development : Users can now embed PL/SQL functions in SQL statements and reference those functions in SQL expressions as if they are build-in.
• Administration : Tablespaces can be placed in read-only mode. Provides a parallel recovery mechanism.
• Standards Compliance : Oracle 7.1 is compliance with the SQL92 Entry Level standard. The ORDER BY clause can not reference SELECT list items names with a column alias.

Some of the products discussed in articles included Pro*C version 2.0, SQL*Module version 1.0, OracleWare, Oracle Graphics version 2.0, SQL*Net version 2 on MVS, SQL*Connect to SQL/400 and Oracle Card 2.0.

Apart from these product related articles, some others of interest included an advertisement for ERwin which was a product owned at that time by Logic Works.

There was a short article on the IOUG 12th Annual User Group Conference in September.

To view the cover page and the table of contents click on the above image or click here.

My Oracle Magazine Collection can be found here.

My previous posts on Oracle Magazine
  - Winter 1993
  - Fall 1992

Rewards received in the Post today

Today I arrived home from work to find that I had received 2 parcels.

In the first parcel was

Thank you Oracle ACE Programme and OTN.

In the second parcel was an IOUG Select Contributors T-shirt. I received this because I had an article in the Q1 2012 edition of IOUG Select, on Oracle Data Miner.  Thank you IOUG.

Oracle Magazine–Winter 1993

The lead articles in the first Oracle Magazine of 1993 (winter) were about how the Oracle database was being used in the Toronto Stock Exchange and in the Municipio de Panama of Panama City.

Other articles of interest included

• Cary Millsap on the Optimal Flexible Architecture. Was this Cary’s first article in Oracle Magazine ? OFA provided a set of guidelines on how to install Oracle so that performance is not affected as your database environment grows. Cary gives 3 basic rules for creating and ODA compliant Oracle database (the full set is in the article).
1. Establish and orderly operating system directory structure in which any database file can be stored on any disk resources (applicable to operating systems with hierarchical directory structures only).
2. Separate groups of segments with different behavior into different tablespaces.
3. Maximize database reliability and performance by separating database components across disk resources
• There was 2 articles that looked at OODBMS. Oracle made their first purchase of an OODBMS company in 1994 and were trying to release their first OODBMS product in 1995. This never happened and their reverted to integrating some OODBMS features into the database instead.
• Oracle Glue is released. This product has gone through a number of name changes since. What is it called now ?
• There was a couple of articles discussing using UNIX or ALPHA server machines, when VLDBs were considered extremely large at 32GB. I remember there was one Bank who had the largest database in Ireland in 1993 and it was 4GB. I wonder what it is now.
• Business Object announces the release of their DOS and Windows end user data access tool for relational database tool.
• There was a number of adverts from companies providing tools and support for converting your Oracle Forms Version 2 to Forms 3

 

Click on the image above to view the PDF of the table of contents page.

My Oracle Magazine Collection can be found here.

ODM–Attribute Importance using PL/SQL API

In a previous blog post I explained what attribute importance is and how it can be used in the Oracle Data Miner tool (click here to see blog post).

In this post I want to show you how to perform the same task using the ODM PL/SQL API.

The ODM tool makes extensive use of the Automatic Data Preparation (ADP) function. ADP performs some data transformations such as binning, normalization and outlier treatment of the data based on the requirements of each of the data mining algorithms. In addition to these transformations we can specify our own transformations.  We do this by creating a setting tables which will contain the settings and transformations we can the data mining algorithm to perform on the data.

ADP is automatically turned on when using the ODM tool in SQL Developer. This is not the case when using the ODM PL/SQL API. So before we can run the Attribute Importance function we need to turn on ADP.

Step 1 – Create the setting table

CREATE TABLE Att_Import_Mode_Settings (
  setting_name  VARCHAR2(30),
  setting_value VARCHAR2(30));

Step 2 – Turn on Automatic Data Preparation

BEGIN
   INSERT INTO Att_Import_Mode_Settings (setting_name, setting_value)
   VALUES (dbms_data_mining.prep_auto,dbms_data_mining.prep_auto_on);
  COMMIT;
END;

^{Step 3 – Run Attribute Importance}

BEGIN
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name => 'Attribute_Importance_Test',
    mining_function  => DBMS_DATA_MINING.ATTRIBUTE_IMPORTANCE,
    data_table_name  > 'mining_data_build_v',
    case_id_column_name => 'cust_id',
    target_column_name  => 'affinity_card',
    settings_table_name => 'Att_Import_Mode_Settings');
END;

Step 4 – Select Attribute Importance results

SELECT *
FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_AI('Attribute_Importance_Test'))
ORDER BY RANK;

ATTRIBUTE_NAME       IMPORTANCE_VALUE       RANK
-------------------- ---------------- ----------
HOUSEHOLD_SIZE             .158945397          1
CUST_MARITAL_STATUS        .158165841          2
YRS_RESIDENCE              .094052102          3
EDUCATION                  .086260794          4
AGE                        .084903512          5
OCCUPATION                 .075209339          6
Y_BOX_GAMES                .063039952          7
HOME_THEATER_PACKAGE       .056458722          8
CUST_GENDER                .035264741          9
BOOKKEEPING_APPLICAT       .019204751         10
ION

CUST_INCOME_LEVEL                   0         11
BULK_PACK_DISKETTES                 0         11
OS_DOC_SET_KANJI                    0         11
PRINTER_SUPPLIES                    0         11
COUNTRY_NAME                        0         11
FLAT_PANEL_MONITOR                  0         11

What has Oracle done to R to give us ORE

Oracle R Enterprise (ORE) was officially launched over the past couple of days and it has been receiving a lot of interest in the press.

We now have the Oracle Advanced Analytics (OAA) option which comprises, the already existing, Oracle Data Mining and now Oracle R Enterprise. In addition to the Oracle Advanced Analytics option we also 2 free set of tools available to use to use. The first of these free tools are the statistical functions which are available in all versions of the Oracle Database and the second free tool is the Oracle Data Miner tool that is part of the newly released SQL Developer 3.1 (7th Feb).

What has Oracle done to Oracle to make Oracle R Enterprise ?

The one of the main challenges with using R is that it is memory constrained, resulting in the amount of data that it can process. So the ORE development team have worked ensuring R can work transparently with data within the database. This removes the need extract the data from the database before it can be used by R. We still get all the advanced on in-Database Data Mining.

They have also embedded R functions within the database, so we an run R code on data within the database. By having these functions with the database, this allows R to use the database parallelism and so we get quicker execution of our code. Most R implementation are constrained to being able to process dataset containing 100Ks of records. With ORE we can now process 10M+ records

In addition to the ORE functions and algorithms that are embedded in the database we can also use the R code to call the suite of data mining algorithms that already exist as part of Oracle Data Miner.

For more details of what Oracle R Enterprise is all about check out the following links.

Oracle Advanced Analytics Options website

ORE Webpage

ORE Blog

ORE Download

ORE Forum

Oracle Magazine-Fall 1992

I’ve been collecting Oracle Magazine for almost 20 years now. I have almost the entire collection, but I’m still missing some of the editions. Some people have donated some of the editions I was missing. But I still missing some.  Can you help me ? Check out my Oracle Magazine Collection.

Every 2 to 3 weeks I intend to write a blog post on each of the Oracle Magazines that I have, starting with the earliest edition that is from Fall 1992.

The main theme of the Fall 1992 edition was about the new Oracle 7 Database. I didn’t get using an Oracle 7 Database until mid-1994.There are a few customer case studies of Oracle 7 implementations. In the article Migrating to Oracle 7, they list some of the new feature. The following is an extract from the new features section.

Standard Oracle 7 provides the functionality and performance to tackle most mission critical applications:

- Multithreaded server
- Shared SQL
- Cost Based optimiser
- Row level locking
- Declarative integrity
- Role based security
- 100% ANSI/ISO standard SQL
- Enhanced national language support

The procedural option for Oracle 7 provides additional capabilities that enhance the database server in the application environment:

- Stored Procedures and Functions
- Procedure packages
- Database Triggers
- Lock manager package
- Database alerts

The distributed option for Oracle 7 makes a physically distributed database appear as a single, logical database. Features of this option include:

- Distributed updates
- TP monitor (XA) interface
- Transparent two-phase commit
- Remote procedure calls
- Table replication (snapshots)
- Oracle Mail Interface

Another article that stands out is by Richard Barker (do you remember his?) who was responsible for the CASE*Method and Oracle’s CASE Tools.

There was several articles on the new Oracle Forms 3 and Oracle Menu 5. Talking about some of the new features like List of Values (LOVs), pop-up Field editor and Pop-up Pages.

There also also the first articles on using Oracle a Microsoft Windows environment. Oh how I remember the frequent blue screens when developing and compiling my forms and in particular my report, with does early releases on Windows.

 

The editorial staff of Oracle Magazine have kindly given me permission to make a PDF of the front cover and the table of contents available for each edition. To get this PDF click on the above image or follow this link to see what Oracle Magazine used to look like 20 years ago.

My next blog post on Oracle Magazine, will look at the Winter 1993 edition.

If you have any of the editions that I’m missing from my collection and you would like to donate then, then drop me an email and we can arrange delivery.  You wont see any of them on eBay, I promise.

ODM 11gR2–Attribute Importance

I had a previous blog post on Data Exploration using Oracle Data Miner 11gR2. This blog post builds on the steps illustrated in that blog post.

After we have explored the data we can identity some attributes/features that have just one value or mainly one value, etc.  In most of these cases we know that these attributes will not contribute to the model build process.

In our example data set we have a small number of attributes. So it is easy to work through the data and get a good understanding of some of the underlying information that exists in the data. Some of these were pointed out in my previous blog post.

The reality is that our data sets can have a large number of attributes/features. So it will be very difficult or nearly impossible to work through all of these to get a good understanding of what is a good attribute to use, and keep in our data set, or what attribute does not contribute and should be removed from the data set.

Plus as our data evolves over time, the importance of the attributes will evolve with some becoming less important and some becoming more important.

The Attribute Importance node in Oracle Data Miner allows use to automate this work for us and can save us many hours or even days, in our work on this task.

The Attribute Importance node using the Minimum Description Length algorithm.

The following steps, builds on our work in my previous post, and shows how we can perform Attribute Importance on our data.

1. In the Component Palette, select Filter Columns from the Transforms list

2. Click on the workflow beside the data node.

3. Link the Data Node to the Filter Columns node. Righ-click on the data node, select Connect, move the mouse to the Filter Columns node and click. the link will be created

4. Now we can configure the Attribute Importance settings.Click on the Filter Columns node. In the Property Inspector, click on the Filters tab.

- Click on the Attribute Importance Checkbox

- Set the Target Attribute from the drop down list. In our data set this is Affinity Card

5. Right click the Filter Columns node and select Run from the menu

After everything has run, we get the little green box with the tick mark on the Filter Column node. To view the results we right clicking on the Filter Columns node and select View Data from the menu. We get the list of attributes listed in order of importance and their Importance measure.

We see that there are a number of attributes that have a zero value. It algorithm has worked out that these attributes would not be used in the model build step. If we look back to the previous blog post, some of the attributes we identified in it have also been listed here with a zero value.

Oracle Ireland Security Seminar–Dublin 8th March 2012

Oracle Ireland will be hosting an security event on Thursday 8th March, between 9:30-13:30, in their East Point offices.

The seminar is titled Defending Against Hackers and Hacking.

There will be a couple of demos what show some of the simple and not so simple techniques that hack and compromise your systems. This can lead to loss of confidential information.

To register for the event go to

http://www.oracle.com/webapps/events/ns/EventsDetail.jsp?p_eventId=143168&src=7302155&src=7302155&Act=58

Update on Exalytics Pricing

In my previous blog post (Exalytics : How much will it cost me ?) I gave an outline of the pricing you might expect for an Exalytics machine.

The final pricing that I gave of approx $3+M was based on the per processor licencing.

Yesterday (24th Jan) the Oracle Business Intelligence blog by Manan, included the pricing based on the per user licences.

The following is a breakdown of the Exalytics pricing based on the minimum 100 user licencing.

Licence Costs (100 users)

Exalytics machine = $135,000

TimesTen = $300 x 100 users = $30,000

BI Foundation Suite = $3,675 x 100 users = $367,500

Giving a grand total of $532,500.

Support Costs (100 users)

But we need to add the annual support costs to this.

Exalytics machine support = $29,700.

TimesTen support = $66 x 100 users = $6,600

BI Foundations suite = $809 x 100 users = $80,900

Total support costs (100 users) = $116,500

First year & on-going costs costs

Total first year cost for an Exalytics machine = $532,500 + $117,200 = $649,700

Plus on going annual support costs of $117,200 in year 2 and subsequent years.

Discounted Costs

If you are one of the lucky customer who can If I use the same discounts, as I did in my previous blog post, of 25% discount on hardware and 60% discount on the software, we get:

Year 1 cost of : ($135,000*0.75) + ($397,500*0.40) = $260,250

So it might be possible to get an Exalytics machine for $260+K, plus annual support costs.