Oracle Data Miner (SQL Dev) 4.1 EA1
A few days ago the first Early Adaptor release of SQL Developer 4.1 (EA1) was made available. You can go ahead and download it from here and make sure to check out the blog post by Jeff Smith on some install and setup that is required around the latest version of Java.
I’ve been using SQL Developer since its very first release, so getting my hands on a new release is very exciting. There are lots and lots of new features in the tool. Again check out the blog posts by Jeff Smith and Kris Rice on some of these new features. I really like the new DBA screens 🙂 But this screen really needs some scroll bars and not everything fits on my screen. So Jeff and Kris if you are reading this, can you add some scroll bars.
In addition they have been working on “new” SQL*Plus that is called SDSQL. This is a new command line tool that is supposed to be bigger and better than SQL*Plus but still gives us a command line tool to run our scripts and demos. To download and install the tool go to here.
As you know I’m a bit of an Oracle Data Miner/Mining fan. There are now new in-database features, but there are a lot of new features in the GUI tool (aka ODMr) along with some improvements and bug fixes. Here is a list of the ODMr 4.1 EA1 new and updated features (taken from the ODMr Help in SQL Dev)
JSON Data Support for Oracle Database 12.1.0.2 and above
In response to the growing popularity of JSON data and its use in Big Data configurations, Data Miner now provides an easy to use JSON Query node. The JSON Query node allows you to select and aggregate JSON data without entering any SQL commands. The JSON Query node opens up using all of the existing Data Miner features with JSON data. The enhancements include:
Data Source Node
Automatically identifies columns containing JSON data by identifying those with the IS_JSON constraint.
Generates JSON schema for any selected column that contain JSON data.
Imports a JSON schema for a given column.
JSON schema viewer.
Create Table Node
Ability to select a column to be typed as JSON.
Generates JSON schema in the same manner as the Data Source node.
JSON Data Type
Columns can be specifically typed as JSON data.
JSON Query Node
Ability to utilize any of the selection and aggregation features without having to enter SQL commands.
Ability to select data from a graphical layout of the JSON schema, making data selection as easy as it is with scalar relational data columns.
Ability to partially select JSON data as standard relational scalar data while leaving other parts of the same JSON document as JSON data.
Ability to aggregate JSON data in combination with relational data. Includes the Sub-Group By option, used to generate nested data that can be passed into mining model build nodes.
General Improvements
Improved database session management resulting in less database sessions being generated and a more responsive user interface.
Filter Columns Node
Combined primary Editor and associated advanced panel to improve usability.
Explore Data Node
Allows multiple row selection to provide group chart display.
Classification Build Node
Automatically filters out rows where the Target column contains NULLs or all Spaces. Also, issues a warning to user but continues with Model build.
Workflow
Enhanced workflows to ensure that Loading, Reloading, Stopping, Saving operations no longer block the UI.
Online Help
Revised the Online Help to adhere to topic-based framework.
Selected Bug Fixes (does not include 4.0 patch release fixes)
GLM Model Algorithm Settings: Added GLM feature identification sampling option (Oracle Database 12.1 and above).
Filter Rows Node: Custom Expression Editor not showing all possible available columns.
WebEx Display Issues: Fixed problems affecting the display of the Data Miner UI through WebEx conferencing.
Denny Wong of the ODM team in Oracle has made available a tutorial on importing JSON data for use with ODMr. Check it out here.
I’ve been told there will be a couple of tutorials on the new features coming out (from the ODMr team) over the next few weeks. So keep an eye out of these.
Check out my blog post on what you need to do to get started/using ODMr 4.1 EA1.
UKOUG 2015 Conferences
The UKOUG annual conferences commence on Sunday 7th December and run until Wednesday 10th.
Like previous years there are two conferences, one called TECH15 and the other is called APPS15. You might guess what each conference is about!!.
This year these conferences are being held at the same time and in the same venue. But they are separate conferences!.
This year I’ve been very lucky (or very unlucky) to have 3 presentations at these conferences. Two of these will on part of the TECH15 conference and one will be part of the APPS15 conference.
Just in case you interested in what I’m presenting about and you might want to attend them, here is the list with the room numbers.
Monday
10:30-11:20 : Oracle Advanced Analytics in Oracle Fusion Apps & Beyond (Apps) (Room : Ex1)
11:30-12:20 : Predictive Queries in Oracle 12c (TECH) (Room : Hall 6)
Wednesday
11:30-12:20 : What are they thinking? With APEX and Oracle Data Miner. (TECH) (Room : Ex4)
(this is a joint presentation with Roel Hartman)
Yes on the Monday I have 2 back-to-back presentation with a 10 minute gap to get from one side of the conference centre to the other side 😦 I’m not looking forward to that transition, but I’m sure it will be fine.
OUG Ireland 2015 : Now open for Submissions
OUG Ireland Call for submissions is now open.
The closing date for submissions is 5th January, 2015.
and the submission webpage can be found here.
The OUG Ireland conference will be on Thursday 19th March. Yes it is only a one day conference 😦 but we will be 5 or 6 or more streams. So there will be something for everyone and plenty of choice.
On Friday 20th March we will have Maria Colgan, formally the Optimizer Lady and now the In-Memory Queen (or something like that), giving a full day workshop on the In-Database option and the Optimizer. She will also be about for the main conference on the 19th, so you can expect a presentation or two from her on the Thursday.
Agenda selection day is the 8th January, 2015. So hopefully you will be getting the acceptance emails soon after that or during week of 12th January.
There is a committee of about 10 people who are involved in selecting presentations and setting the agenda. If it was up to me then I would accept everything/everyone. So if your presentation is not accepted this time, please don’t blame me 🙂 I said YES to your presentation, I really, really did. I fought so hard to have your presentation included. If your presentation is not accepted then the blame is down to the other committee members 🙂
The conference will be held in Croke Park, and is a 15-20 minute taxi ride from the Airport.
You can follow the Conference and other OUG Ireland events using the twitter tag #oug_ire
Approximate Count Distinct (12.1.0.2 new feature)
With the release of the Oracle Database 12.1.0.2 there was a number of new features and options. Most of the publicity has been around the in-Memory option. But there was lots of other features for the DBA and a few for the developer.
One of the new SQL functions is the APPROX_COUNT_DISTINCT(). This function is different to the tradition count distinct, COUNT(DISTINCT expression), in that is performs an approximate count distinct. The theory is that this approximate count is a lot more efficient than performing the full count distinct.
The APPROX_COUNT_DISTINCT() function is really only suitable when you are processing very large volumes of data and when the data set contains a large number of distinct values.
The general syntax of the function is:
… APPROX_COUNT_DISTINCT(expression) …
and returns a Number.
The function returns the approximate number of records that contain distinct value for the expression.
SELECT approx_count_distinct(cust_id)
FROM mining_data_build_v;
The APPROX_COUNT_DISTINCT() function ignores records that contain a null value for the expression. Plus is performs less work on the sorting and aggregations. Just run and Explain Plan and you can see the differences.
In some of the material from Oracle the APPROX_COUNT_DISTINCT() function can be 5x to 50x++ times faster. But it depends on the number of distinct values and the complexity of the SQL query.
As the result / returned value from the function may not be 100% accurate, Oracle says that the functions has an accuracy of >97% (with 95% confidence).
The function cannot be used on the following data types: BFILE, BLOB, CLOB, LONG, LONG RAW and NCLOB
ODMr : Graph Node: Zooming in on Graphs
When Oracle Data Miner (ODMr) 4.0 (which is part of SQL Developer) came out back in late 2013 there was a number of new features added to the tool. One of these was a Graph node that allows us to create various graphs and charts that include Line, Scatter, Bar, Histogram and Box plot.
I’ve been using this node recently to produce graphs and particularly scatter plots. I’ve been using the scatter plots to graph the Actual values in a data set against the Predicted values that was generated by ODMr. In this scenario I had a separate data set for training my ODM data mining models and another testing data set for, well testing how well the model performed against an unseen data set.
In general the graphs produced by the Graph node look good and gives you the information that you need. But what I found was that as you increased the size of the data set, the scatter plot can look a messy. This was in part due to the size of the square used to represent a data point. As the volume of data increased then your scatter plot could just look like a coloured in area of blue squares. This is illustrated in the following image.
What I discovered today is that you can zoom in on this graph to explore different regions and data point on it. This do this you need to select an data that is within the x-axis and y-axis area. When you do this you will see a box form on your graph that selects the area that you indicate by moving your mouse. After you have finished selecting the area, the Graph Node will zooms into this part of the graph and shows the data points. For example if I select the area from about 1000 on the x-axis and 1000 on the y-axis, I will get the following.
Again if I select a similar are area of 350 on the x-axis and 400 on the y-axis I get the following zoomed area.
You can keep zooming in on various areas.
At some point you will have finished zooming in and you will want to return to the original graph. To zoom back outward all you need to do in the graph is to click on it. When you do this you will go back to the previous step or image of the graph. You can keep doing this until you get back to the original graph. Alternatively you can zoom in and out on various parts of the graph.
Hopefully you will find this feature useful.
Something new in 12c: FETCH FIRST x ROWS
In this post I want to show some example of using a new feature in 12c for selecting the first X number of records from the results set of a query.
See the bottom of this post for the background and some of the reasons for this post.
Before we had the 12c Database if we only wanted to see a subset or the initial set of records from the results of a query we could add something like the following to our query
…
AND ROWNUM <= 5;
The could use the pseudo column ROWNUM to restrict the number of records that would be displayed. This was particularly useful when the results many 10s, 100s, or millions of records. It allowed us to quickly see a subset and to see if the results where what we expected.
In my book (Predictive Analytics Using Oracle Data Miner) I had lots of examples of using ROWNUM.
What I wasn’t aware of when I was writing my book was that there was a new way of doing this in 12c. We now have something like the following:
…
FETCH FIRST x ROWS ONLY;
There is an example:
SELECT * FROM mining_data_build_v
FETCH FIRST 10 ROWS ONLY;
There are a number of different ways you can use the row limiting feature. Here is the syntax for it:
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
In most cases you will probably use the number of rows. But there many be cases where you might what to use the PERCENT. In previous versions of the database you would have used SAMPLE to bring back a certain percentage of records.
select CUST_GENDER from mining_data_build_v
FETCH FIRST 2 PERCENT ROWS ONLY;
This will set the first 2 percent of the records.
You can also decide from what point in the result set you want the records to be displayed from. In the previous examples above the results displayed will befing with the first records. In the following example the results set will be processed to record 60 and then the first 5 records will be selected and displayed. This will be records 61, 62, 63, 64 and 65. So the first record processed will be the OFFSET record + 1.
select CUST_GENDER from mining_data_build_v
OFFSET 60 ROWS FETCH FIRST 5 ROWS ONLY;
Similar to the PERCENT example above you can use the OFFSET value, for example.
select CUST_GENDER from mining_data_build_v
OFFSET 60 ROWS FETCH FIRST 2 PERCENT ROWS ONLY;
This query will go to records 61 and return the next 2 percent of the records.
The background to this post
There are a number of reasons that I really love attending Oracle User Group conferences. One of the challenges I set myself is to go to presentations on topics that I think I know or know very well. I can list many, many reasons for this but there are 2 main points. The first is that you are getting someone elses perspective on the topic and hence you might learn something new or understand it better. The second is that you might actually learn something new, like some new command, parameter setting or something else like that.
At Oracle Open World recently I attended the EMEA 12 things about 12c set of presentations that Debra Lilly arranged during the User Group Forum on the Sunday. During these session Alex Nuijten gave an overview of some 12c new SQL features. One of these was the command FETCH FIRST x ROWS. This blog post illustrates some of the different ways of using this command.
Installing Oracle 12.1.0.2 on Windows 64bit
The following steps are what I did for installing 12.1.0.2 on Windows.
1. Download the Oracle installation ZIP files from the Oracle Downloads page.
2. Unzip the two 12c downloads files into the same directory.
3. Go to the newly created directory (it is probably called ‘database’) and you will find a file called setup.exe. Double click on this file.
After a couple of seconds you will see the Oracle Database 12c splash screen.
4. Step 1 : Configure Security Updates : Un-tick the tick-box and click the Next button. A warning message will appear. You can click on the Yes button to proceed.
5. Step 2 : Installation Options : select the Create and Configure a Database option and then click the Next button.
6. Step 3 : System Class : Select the Server Class option and then click the Next button.
7. Step 4 : Grid Installation Options : Select the Single Instance Database Installation option and then click the next button.
8. Step 5 : Select Install Type : Select the Typical install option and then click the Next button.
9. Step 6 : Oracle Home User Selection : Select the Use Windows Built-in Account option and then click the Next button. A warning message appears. Click the Yes button.
10. Step 7 : Typical Install Configuration : Set Global Database Name to cdb12c for the container database name. Set the Administrative password for the container database. Set the name of the pluggable database that will be created. Set this to pdb12c. Or you can accept the default names. Then click the Next button. If you get a warning message saying the password does not conform to the recommended standards, you can click the Yes button to ignore this warning and proceed.
11. Step 8 : Prerequisite Checks : the install will check to see that you have enough space and necessary permissions etc.
12. Step 9 : Summary : When the prerequisite checks (like checking you have enough space and privileges) are finished you will get a window like the following.
13. Step 10 : Install : You are now ready to start the install process. To do this click on the Install button in the Summary screen.
You can now sit back, relax and watch the installation of 12.1.0.2c (with the in-memory option) complete.
You may get some Windows Security Alert windows pop up. Just click on the Allow Access button.
Then the Database Configuration Assistant will start. This step might take a while to complete.
When everything is done you will get something like the following.
Congratulations you now have Oracle Database 12.1.0.2c installed.
But you are not finished yet!!!
14. Add entry to TNSNAMES.ORA : you will need to add an entry to your tnsnames.ora file for the pluggable database. There is be an entry for the container DB but not for the pluggable. Here is what I added to my tnsnames.ora.
The last step you need to do is to tell the container database to start up the pluggables when you reboot the server/laptop/PC/VM. To do this you will need to create the following trigger in the container DB.
sqlplus / as sysdba
CREATE or REPLACE trigger OPEN_ALL_PLUGGABLES
after startup
on database
BEGIN
execute immediate ‘alter pluggable database all open’;
END open_all_pdbs;
Restart your database or machine and you plug gage DB ‘pdb12c’ will no automatically start.
You are all finished now 🙂
Enjoy 🙂
People from Ireland Presenting at OOW14
Oracle Open World is coming up in a few days time. This is a huge event that also incorporates Jave One and various other smaller conferences that are for specific product areas and for partners.
I will be presenting at Oracle Open World this year and I’ll also be taking part in a number of other sessions/events including the Oracle ACE Directors briefing. Check out my blog post that list these sessions/events.
In addition to myself presenting at OOW14 there are a few other people from Ireland who are presenting. The following lists there sessions (including mine for a full list). If you are attending OOW14 then do try to drop along to these sessions.
Sunday 28th September 9:00-9:45
Brendan Tierney & Roel Hartman
Moscone South Room 304
What are they Thinking? With Oracle Application Express and Oracle Data Miner.
Debra Lilley 14:30-15:15
Moscone South Room 304
2 Looks at Oracle Database 12c: EOUC Short Talks [UGF8949]
Debra Lilley 15:30-16:15
Moscone South Room 304
12 Looks at Oracle Database 12c: EOUC Short Talks, Part 2 [UGF9221]
Tuesday 30th September 17:00-17:45
Mina Sagha Zadesh (Oracle Ireland)
Intercontinential – Grand Ballroom A
[CON4259] Unique Advantages of Oracle Solaris for Oracle Database Systems.
Wednesday 1st October 10:15-11:00
Simon Holt (ESB)
Marriott Marquis – Golden Gate C1/C2
[CON5388] An Oracle SuperCluster Engineered System for Oracle Utilities Network Management System.
Debra Lilley
Moscone West – 3018
Deliver Business Innovation while Reducing Upgrades’ Risk [CON8534].
Wednesday 1st October 11:30-12:45
Kevin Callanan (AIB)
Moscone South Room 301
[CON8247] DBA’s New Best Friend for Mistake Free Administration: Oracle Real Application Testing.
I’ll be at these sessions to support my fellow Irish. I hope to see you there too 🙂
Analytics Hands on Labs at OOW 14
I had an previous blog post listing the various Oracle Advanced Analytics sessions/presentation at Oracle Open World 2014.
After trawling through the list of Hands-on-Labs it was disappointing to see that there was no Oracle Data Mining or Oracle R Enterprise hands-on-labs this year.
But there is a hands on lab that looks are how to use the new SQL for Big Data feature (announced over the summer).
Here is the abstract for the session.
Data warehouses contain the critical data required for managing and running organizations. Increasingly, Hadoop and NoSQL databases are capturing additional information—such as web logs, social media, and weather data that can augment the warehouse—enabling users to uncover new insights and opportunities. This hands-on lab illustrates how Oracle Big Data SQL is used to unify these environments. First you will learn how to securely access these big data sources from Oracle Database 12c. Then you will utilize Oracle’s analytical SQL across all your data, regardless of where it resides. Welcome to Oracle’s new big data management system!
There will be a lab session each day for this session and I will certainly be doing my best to get to one of these.
table.myTable { border-collapse:collapse; } table.myTable td, table.myTable th { border:1px solid black;padding:5px; } <!– Be sure to place the above styles (i.e. everything between the tags) into the document ‘head’ (i.e. between the tags. Everything below goes inside the tags) –>
| Date | Time | Location | Hands-on-Lab Session Title |
|---|---|---|---|
| Monday 29th Sept. | 11:45-12:45 | Hotel Nikko – Peninsula | Oracle Big Data SQL: Unified SQL Analysis Across the Big Data Platform [HOL9348] |
| Tuesday 30th Sept. | 15:45-16:45 | Hotel Nikko – Peninsula | |
| Wednesday 1st Oct. | 13:15-14:15 | Hotel Nikko – Peninsula | |
| Thursday 2nd Oct. | 11:30-12:30 | Hotel Nikko – Peninsula |
If any new hands-on-labs appear that are related to the Big Data and Advanced Analytics areas/options I will update the above table.
Some other Hands-on-Labs that you might be interested in include:
table.myTable { border-collapse:collapse; } table.myTable td, table.myTable th { border:1px solid black;padding:5px; } <!– Be sure to place the above styles (i.e. everything between the tags) into the document ‘head’ (i.e. between the tags. Everything below goes inside the tags) –>
| Date | Time | Location | Hands-on-Lab Session Title |
|---|---|---|---|
| Monday 29th Sept. | 17:45-18:45 | Hotel Nikko – Peninsula | Oracle NoSQL Database for Application Developers [HOL9349] |
| Tuesday 30th Sept. | 10:15-11:10 | Hotel Nikko – Peninsula | Oracle NoSQL Database for Application Developers [HOL9349] |
| Tuesday 30th Sept. | 15:45-16:45 | Hotel Nikko – Nikko Ballroom III | Oracle Data Integrator 12c New Features Deep Dive [HOL9439] |
| Tuesday 30th Sept. | 17:15-18:15 | Hotel Nikko – Nikko Ballroom III | Oracle Data Integrator for Big Data [HOL9414] |
| Wednesday 1st Oct. | 13:15-14:15 | Hotel Nikko – Mendocino I/II | Set Up a Hadoop 2 Cluster with Oracle Solaris Zones, Oracle Solaris ZFS, and Unified Archive [HOL2086] |
| Wednesday 1st Oct. | 14:45-15:45 | Hotel Nikko – Peninsula | Oracle NoSQL Database for Administrators [HOL9327] |
| Thursday 2nd Oct. | 14:30-15:30 | Hotel Nikko – Peninsula | Oracle NoSQL Database for Administrators [HOL9327] |
Oracle Advanced Analytics sessions at OOW14
With Oracle Open World just a few days away now, I was going through the list of presentations that are focused on using the Oracle Advanced Analytics Option. These will cover Oracle Data Miner and Oracle R Enterprise.
So I’ve decided to share this list with you 🙂 and hopefully I will get to see you are some or all of these sessions.
table.myTable { border-collapse:collapse; } table.myTable td, table.myTable th { border:1px solid black;padding:5px; } <!– Be sure to place the above styles (i.e. everything between the tags) into the document ‘head’ (i.e. between the tags. Everything below goes inside the tags) –>
| Date | Time | Location | Presentation Title |
|---|---|---|---|
| Sunday 28th Sept. | 9:00-9:45 | Moscone South Room 304 | What Are They Thinking? With Oracle Application Express and Oracle Data Miner [UGF2861]. (This is my presentation with Roel Hartman.) |
| Tuesday 30th Sept. | 17:00-17:45 | Intercontinental – Grand Ballroom C | Advanced Predictive Analytics for Database Developers on Oracle [CON7977] |
| Tuesday 30th Sept. | 18:00-18:45 | Moscone South – 303 | Oracle’s Big Data Management System [MTE9350] |
| Wednesday 1st Oct. | 10:15-11:00 | Moscone South – 301 | Big Data and Predictive Analytics: Fiserv Data Mining Case Study [CON8631] |
| Wednesday 1st Oct. | 10:30-10:50 | Big Data Theater, Moscone South, Big Data Showcase | Big Data: Maximize the Business Impact with Oracle Advanced Analytics [THT10395] |
| Wednesday 1st Oct. | 11:30-12:15 | Moscone South – 300 | A Perfect Storm: Oracle Big Data Science for Enterprise R and SAS Users [CON8331] |
| Wednesday 1st Oct. | 12:45-13:30 | Moscone West – 3002 | Predictive Analytics with Oracle Data Mining [CON8596] |
| Wednesday 1st Oct. | 14:00-14:45 | Moscone South – 308 | Developing Relevant Dining Visits with Oracle Advanced Analytics at Olive Garden [CON2898] |
If I have missed any sessions then do please let me know and I can update the list above.
apropos("^ore")
We have all been in the position of trying to find the name of a command in a language, particularly if you are not totally sure of the full command name.
I’ve been working with R a lot recently and in particular Oracle R Enterprise. I was always trying to remember what the full command name was. Then I found the apropos function. The apropos function allows you to search R for commands based on a part or partial name. You can use regular expression syntax to define what part of the function name you are looking for.
What I ended up using most often was the following command. This function call looks for all functions being with ‘ore‘.
> apropos(“^ore”)
To find out more about how to use the apropos command check out the R help.
> help(apropos)
ORE now available for Multitenant (PDB) version of 12c
Oracle has released an update to their Oracle R Enterprise software. We now have ORE 1.4.1 and this seems to have been released on the past day or so.
Here are the links to the important stuff:
One of the main features of this new release is that it now supports the multi tenant option of the 12c database. Up to now if you wanted to use ORE and 12c then you needed to do a traditional install of the database. That means you would be just installing a single instance of the 12c database with no CDB or PDB.
With ORE 1.4.1 you can now install ORE into a PDB. It needs to be one of your current PDBs and should not be installed into the root PDB, otherwise it will not work. Check out the installation instructions using the links above.
As with all new releases there are a lot of bug fixes and perhaps some new ones too 🙂
Ora-lytics 
You must be logged in to post a comment.