ORE video : Demo Code Part 2

The following is the second set of demo code from my video on using R in the Oracle Database. Check out the video before using the following code. The blog post for the video will be updated to contain links to all blog posts that have the various demo code.

The following code gives a very quick demonstration of using the RORACLE R package to access the data in your Oracle schema. ROracle has a number of advantages over using RJDBC and most of the advantages are about the performance improvements. Typically when using ROracle you will see a many fold improvement with selecting data and moving it to your R client, processing data in the database and also writing data back to the Oracle Database. In some tests you can see a 7 times improvement in performance over RJDBC. Now that is a big difference.

But the problem with ROracle is that it is only available on certain platforms/OS. For example it is not officially available for the Mac. But if you google this issue carefully you will find unofficial ways over coming this problem.

ROracle is dependent on Oracle Client. So you will need to have Oracle Client installed on you machine and have it available on the search path.

When you have Oracle Client installed and the ROracle R package installed you are ready to start using it.

So here is the demo code from the video.

> library(ROracle)
> drv <- dbDriver("Oracle")
> # Create the connection string
> host <- "localhost"
> port <- 1521
> sid <- "orcl"
>connect.string <- paste("(DESCRIPTION=”, "(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
>    "(CONNECT_DATA=(SID=", sid, ")))", sep = "")

> con <- dbConnect(drv, username = "dmuser", password = "dmuser",dbname=connect.string)

> rs <- dbSendQuery(con, "select view_name from user_views")
> # fetch records from the resultSet into a data.frame
> data <- fetch(rs)
> # extract all rows
> dim(data)
[1] 6 1
> data
                  VIEW_NAME
1       MINING_DATA_APPLY_V
2       MINING_DATA_BUILD_V
3        MINING_DATA_TEST_V
4  MINING_DATA_TEXT_APPLY_V
5  MINING_DATA_TEXT_BUILD_V
6   MINING_DATA_TEXT_TEST_V
> dbCommit(con)
> dbClearResult(rs)
> dbDisconnect(con)

ORE Video : Demo Code part 1

In a previous blog post I posted a video on using R with the Oracle Database and using Oracle R Enterprise. This is a part 1 extension of that blog post that gives the first set of demo code.

This first set of demonstration code is for using RJDBC to connect to the Oracle Database. Using RJDBC relies on using the JDBC jar file for Oracle. It is easily found in various installations of Oracle products and will be called something like ojdbc.jar. I like to take a copy of this file and place it in the root/home directory.

> library(RJDBC)
> # Create connection driver and open 
> connectionjdbcDriver <- JDBC(driverClass="oracle.jdbc.OracleDriver", classPath="c:/ojdbc6.jar")
> jdbcConnection <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@//localhost:1521/orcl", "dmuser", "dmuser")
> #list the tables in the schema
> #dbListTables(jdbcConnection)
> #get the DB connections details - it get LOTS of info - Do not run unless it is really needed
> dbGetInfo(jdbcConnection)
> # Query on the Oracle instance name.
> #instanceName <- dbGetQuery(jdbcConnection, "SELECT instance_name FROM v$instance")
              TABLE_NAME1 
1  INSUR_CUST_LTV_SAMPLE2            
2              OUTPUT_1_2
> #print(instanceName)tableNames <- dbGetQuery(jdbcConnection, "SELECT table_name from user_tables where  
                                                 table_name not like 'DM$%' and table_name not like 'ODMR$%'")
> print(tableNames)
> viewNames <- dbGetQuery(jdbcConnection, "SELECT view_name from user_views")print(viewNames)
1       MINING_DATA_APPLY_V
2       MINING_DATA_BUILD_V
3        MINING_DATA_TEST_V
4  MINING_DATA_TEXT_APPLY_V
5  MINING_DATA_TEXT_BUILD_V
6   MINING_DATA_TEXT_TEST_V

> v <- dbReadTable(jdbcConnection, "MINING_DATA_BUILD_V")
> names(v)
[1] "CUST_ID"                 "CUST_GENDER"             "AGE"                     
[4] "CUST_MARITAL_STATUS"     "COUNTRY_NAME"            "CUST_INCOME_LEVEL"       
[7] "EDUCATION"               "OCCUPATION"              "HOUSEHOLD_SIZE"         
[10] "YRS_RESIDENCE"           "AFFINITY_CARD"           "BULK_PACK_DISKETTES"    
[13] "FLAT_PANEL_MONITOR"      "HOME_THEATER_PACKAGE"    "BOOKKEEPING_APPLICATION”
[16] "PRINTER_SUPPLIES"        "Y_BOX_GAMES"             "OS_DOC_SET_KANJI" 
> dim(v)
[1] 1500   18
> summary(v)
    CUST_ID       CUST_GENDER             AGE        CUST_MARITAL_STATUS COUNTRY_NAME       
Min.   :101501   Length:1500        Min.   :17.00   Length:1500         Length:1500        
1st Qu.:101876   Class :character   1st Qu.:28.00   Class :character    Class :character   
Median :102251   Mode  :character   Median :37.00   Mode  :character    Mode  :character   
Mean   :102251                      Mean   :38.89                                          
3rd Qu.:102625                      3rd Qu.:47.00                                          
Max.   :103000                      Max.   :90.00                                          
CUST_INCOME_LEVEL   EDUCATION          OCCUPATION        HOUSEHOLD_SIZE     YRS_RESIDENCE    
Length:1500        Length:1500        Length:1500        Length:1500        Min.   : 0.000   
Class :character   Class :character   Class :character   Class :character   1st Qu.: 3.000   
Mode  :character   Mode  :character   Mode  :character   Mode  :character   Median : 4.000                                                                               
                                                                            Mean   : 4.089                                                                               
                                                                            3rd Qu.: 5.000                                                                               
                                                                            Max.   :14.000 
> hist(v$RESIDENCE)
> hist(v$AGE)
> dbDisconnect(jdbcConnection)

Make sure to check out the other demonstration scripts that are shown in the video.

Oracle R Enterprise 1.5 (new release)

The Oracle Santa had a busy time just before Christmas with the release of several new version of products. One of these was Oracle R Enterprise version 1.5.

Oracle R Enterprise (1.5) is part of the Oracle Advanced Analytics option for the enterprise edition of the Oracle Database.

As with every new release of a product there are a range of bug fixes. But with ORE 1.5 there are also some important new features. These important new features include:

• New Random Forest specific for ORE.
• New ORE Data Store functions and privileges.
• Partitioning on multiple columns for ore.groupApply.
• Multiple improvements to ore.summary.
• Now performs parallel in-database execution for functions prcomp and svd.
• BLOB and CLOB data types are now supported in some of the ORE functions.

Check out the ORE 1.5 Release Notes for more details on the new features.

ORE 1.5 is only certified (for now) on R 3.2.x in both the open source version and the Oracle R Distribution version 3.2.

Check out the ORE 1.5 Documentation.

You can download ORE 1.5 Server side and Client side software here.

Running R in the Oracle Database video

Earlier this year I was asked by the Business Analysics & Big Data SIG (of the UKOUG) to give a presentation on Oracle R Enterprise. Unfortunately I had already committed to giving the same presentation at the OUG Norway conference on the same day.

But then they asked me if I could record a video of the presentation and they would show it at the SIG. The following video is what I recorded.

At the UKOUG annual (2015) conferences I was supposed to give a 2 hour presentation during their Super Sunday event. Unfortunately due to a storm passing over Ireland on the Saturday all flights going to the UK were cancelled. This meant that I would miss my 2 hour presentation.

Instead of trying to find an alternative speaker for my presentation slot at such sort notice, the committee suggested that they would show the video.

Based on the feedback and the people who thanked me in person during the rest of the conference, I've decided to make it available to everyone. Hopefully you will find it useful.

The following are the links to the demo code that is shown or referred to in the video.

• Demo Code 1
• Demo Code 2
• Demo Code 3
• Demo Code 4
• Demo Code 5

People have been asking me if the demo scripts I used in video are available. You will probably find some of these on various blog posts. So to make it easier for everyone I will post the demo scripts in one or more blog posts over the coming weeks. When these are available I will update this blog post with the links.

I have a few new presentations on Oracle R Enterprise in 2016 so watch out for these at an Oracle User Group conference.

KScope 2016 Acceptances

I've never been to KScope. Yes never.

I've always wanted to. Each year you hear of all of these stories about how much people really enjoy KScope and how much they learn.

So back in October I decided to submit 5 presentations to KScope. 4 of these presentations are solo presentations and 1 joint presentation.

This week I have received the happy news that 2 of my solo presentations have been accepted, plus my joint presentation with Kim Berg Hansen.

So at the end of June 2016 I will be making my way to Chicago for a week of Oracle geekie fun at KScope.

My presentations will be:

• Is Oracle SQL the best language for Statistic?
• Running R in your Oracle Database using Oracle R Enterprise

and my join presentations is called

Forecasting in Oracle using the Power of SQL (this will talk about ROracle, Forecasting in R, Using Oracle R Enterprise and SQL)

I was really hoping that one of my rejected presentations would have been accepted. I really enjoy this presentation and I get to share stories about some of my predictive analytics projects. Ah well, maybe in 2017.

The last time I was in Chicago was over 15 years ago when I sent 5 days in Cellular One (The brand was sold to Trilogy Partners by AT&T in 2008 shortly after AT&T had completed its acquisition of Dobson Communications). I was there to kick off a project to build them a data warehouse and to build their first customer churn predictive model. I stayed in a hotel across the road from their office which was famous because a certain person had stayed in it why one the run. Unfortunately I didn't get time to visit downtown Chicago.

OUG Ireland 2015 : Call for Papers

We finally have confirmed dates for the OUG Ireland Annual Conference. It will be on the 3rd & 4th March, 2016.

In 2016 we are expanding the conference and marking it a 2 day conference. Over the past few years some of the feedback from delegates has been, can we have more sessions and add a second day. So we have listened and will now have a 2 day conference on 3rd & 4th March, 2016.

We also have a new venue too. The 2016 conference will be held in the Gresham Hotel, on O'Connell Street in central Dublin. It is so close to lots of the tourist attractions, lots of hotels and lots of entertainment.

The call for papers is now open and we are looking for presentations for the typical Business Analytics/Big Data, Hardcore DBA, Development, Cloud and Applications.

Do you have a story to share? Have you discovered something recently? Have you used a new product? Is there anything about some part of an Oracle Product you would like to share?

If so then click on the image below to go to the Submissions Website and get submitting. Closing date for submissions is 9am on 4th January.

Hopefully I'll see you in Dublin in March.

My presentations at UKOUG 2015

The annual conferences of the UKOUG are coming up soon and I just wanted to give you a quick overview of the 3 presentations I will be giving at these conferences in Birmingham between Sunday 6th and Wednesday 9th December.

I'll be making references to 50 Shades of Gray and Lord of the Rings in some of these presentations. Come along to find out why.

It all kicks off on the Sunday (12:30-14:20) with an almost 2 hour presentation titled 'Predictive Analytics in Oracle: Mining the Gold & Creating Valuable Products'. We have being hearing about Predictive Analytics for a few years now. But most people are still unsure of what it really means and how it could be used in their company. This presentations aims to simplify what Predictive Analytics means and to illustrate how various companies from a wide range of industries have been using Predictive Analytics to gain deeper, valuable and actionable insights of their data. A number of case studies will be presented, based on some of my recent projects, illustrating how predictive analytics has been used to generate significant benefits for each company. These case studies will include examples from Banking, Telecommunications, Financial Fraud Detection, Retail and Merchandising. The second half of this seminar will look at what functionality is available from Oracle that allows you to build and deploy Predictive Analytics in your applications. We will at what you can do with SQL, PL/SQL, Oracle Data Miner, Oracle R Enterprise and Real-Time Decisions. We will then look at how you can build these features and tools into your front end applications. In the final section of the seminar we will look at what you need to do to get started with Predictive Analytics and how you can avoid some of the typical pitfalls.

On Monday 7th, I have a presentation between 9:00 and 9:50 titled 'Is Oracle SQL the Best Language for Statistics?'. Did you know that Oracle comes with over 280+ statistical functions? and that these statistical functions are available in all versions of the Database? Most people do not seem to know this.When we hear about people performing statistical analytics we can hear them talking about Excel and R. But what if we could do statistical analysis in the database, without having to extract any data onto client machines. That would be really good and just think of the possible data security issues with using Excel, R and other tools.This presentation will explore the various statistical areas available in SQL and I will give a number of demonstrations on some of the more commonly used statistical techniques. With the introduction of Big Data SQL we can now use these same 280+ Oracle statistical functions on all our data including Hadoop and NoSQL. We can also greatly expand our statistical capabilities by using Oracle R Enterprise using the embedded capabilities in SQL.

My final presentation will on Wednesday (14:30-15:20) titled 'Automating Predictive Analytics in Your Applications'. What does your Oracle DBA and Oracle Developer need to know to implement Data Mining? How can the Oracle DBA and Oracle Developer use Data Mining. This presentation will look at what the Oracle DBA and Oracle Developer needs to know to deploying, implementing and integrating the predictive models produced by the Data Miner. These predictive models can be used in multiple databases and across their many applications. Examples will be given of the typical tasks data mining task for the DBA and for the Oracle Developer. These examples will include SQL, PL/SQL and embedded Oracle R Enterprise. I will show how easy it is to build automated Predictive Analytics into, not only your Dashboards, but your everyday applications. Here is a short video that gives an overview of my presentations for TECH15.

Error when trying to use GLM in ORE

If you have tried to use the ore.odmGLM function in ORE and some other functions in ORE and you are using the Oracle 12.1.0.2 Database you will be getting an error something like the following in R.

> GLMmodel <- ore.odmGLM(AFFINITY_CARD ~., ANALYTIC_RECORD)
Error in .oci.GetQuery(conn, statement, data = data, prefetch = prefetch,  : 
  ORA-29400: data cartridge error
ORA-40024: solution to least squares problem could not be found
ORA-06512: at "SYS.DBMS_DATA_MINING", line 2153
ORA-06512: at line 1

or you get something like the following when using ore.glm

Error in .External(C_pt, q, df, lower.tail, log.p) :
  NULL value passed as symbol address

Similarly if you are using these functions with embedded R execution when the R code is warpped in SQL, you will see similar errors.

What you need to do is to download and install the stand alond Patch 20173897.

Alternatively this patch is included in the latest Oracle 12.1.2 Database patch.

Slide from my OOW15 Presentations

At Oracle Open World (OOW15) I gave 2 presentations on the Sunday during the Oracle User Group Forum. The slides are now available for download from the Oracle Open World website.

Go get them now!

More Than Another 12 on Oracle Database 12c [UGF3190]

During this sessions I was one of 16 presenters talking about various features in the Oracle Database. All of the presenters where from the EOUC region.

Real Business Value from Big Data and Advanced Analytics [UGF4519]

I co-presented with Antony Heljula from Peak Indicators. During this presentation we talked about some of the Advanced Analytics projects we have worked on over the past 18-24 months. We also announced a new Analytics-as-a-Service offering.

The slides are also available for most of the other Oracle Open World Presentations and these can be accessed here. Just go search for the topic you are interested in.

Check out my previous blog post that summarises just a small part of what I got up to at OOW15.

My OOW15 is now over

It seems to be traditional for people to write a blog post that summaries their Oracle Open World (OOW) experience. Well here is my attempt and it really only touches on a fraction of what I did at OOW, which was one of busiest OOWs I've experienced.

It all began back on Wednesday 21st October when I began my journey. Yes that is 9 days ago, a long long 9 days ago. I will be glad to get to home.

The first 2 days here in San Francisco was down at Oracle HQ were the traditional Oracle ACE Directors briefings are held. I'm one of the small (but growing) number of ACEDs and it is an honour he take part in the programme. The ACE Director briefings are 2 days of packed (did I say they are packed) full on talks by the leading VPs, SVPs, EVPs and especially Thomas Kurian. Yes we had Thomas Kurian come into talk to us for 60-90 minutes of pure gold. We get told all the things that Oracle is going to release or announce at OOW and for the next few months and beyond. Some of things that I was particularly interested in was the 12.2 database stuff.

Unfortunately we are under NDA for all of the stuff we were told, until Oracle announce it themselves.

On the Thursday night a few of us meet up with Bryn Llewellyn (the god father of PL/SQL) for a meal. Here is a photo to prove it. It was a lively dinner with some "interesting" discussions.

On the Friday we all transferred hotels into a hotel beside Union Square.

We had the Saturday free, and I'm struggling to remember what I actually did. But it did consist of going out and about around San Francisco. Later that evening there was a meet up arranged by "That" Jeff Smyth in a local bar.

Sunday began with me walking across (and back) the Golden Gate Bridge with a few other ACEDs and ACEs.

The rest of the Sunday was spent at the User Groups Forum. I was giving 2 presentations. The first presentation was part of the "Another 12 things about Oracle 12c". For this session there was 15 presenters and we each had 7 minutes to talk about a topic. Mine was on Oracle R Enterprise. It was an almost full room, which was great.

Then I had my second presentation right afterwards and for that we had a full room. I was co-presenting with Tony Heljula and we were talking about some of the projects we have done using Oracle Advanced Analytics.

After that my conference duties were done and I got to enjoy the rest of the conference.

Monday and Tuesday were a bit mental for me. I was basically in sessions from 8am until 6pm without a break. There was lots of really good topics, but unfortunately there was a couple presentations that were total rubbish. Where the title and abstract had no relevance to what was covered in the presentation, and even the presentation was rubbish. There was only a couple of these.

Wednesday was the same as Monday and Tuesday but this time I had time for lunch.

As usual the evenings are taken up with lots of socials and although I had great plans to go to lots of them, I failed and only got to one or two each evening.

On Wednesday night we ended up out at Treasure Island to be entertained by Elton John and Becks, and then back into Union Square for another social.

Thursday was very quiet and as things started to wind down at OOW finishing up at 3pm. A few of us went down to Fishermans Wharf and Pier39 for a wander around and a meal. Here are the some photos from the restaurant.

Friday has finally arrived and it is time to go home. OTN are very generous and put on a limo for the ACEDs to bring us out to the airport.

As always there are lots of people to thank but I won't start naming names here as I'm sure I will forget someone. But I do want to thank all the gang in OTN that look after the Oracle ACE programme. You really look after us, not just at OOW, but all year round. It is with your support that I get to go to wonderful conferences and to OOW.

People from Ireland who are Speaking at #OOW15

Oracle Open World 2015 will be kicking off in a few days time. There will be over 50K people attending this event, with a couple of hundred speakers.

I'm one of the lucky ones to have been selected to speak at Oracle Open World. This will be my third or fourth year in a row that I'm speaking at Oracle Open World.

Am I the only person from Ireland who is speaking at Oracle Open World ? No, I'm not, but there is a small number of us this year. There are basically 2, yes 2 other people from Ireland speaking at Oracle Open World. These are Richard Corbridge from the HSE, and Debra Lilley.

If you are interesting in attending these sessions here is the schedule.

Please share this information to allow your colleagues and help spread the news about he Irish Speakers.

If you are attending OOW, then please tweet, blog, Facebook, etc about these presentations.

Hopefully I will see you are one or both of my presentations on the Sunday.

And I will be having a draw/raffle for a copy of my book at my second presentation on the Sunday at 15:30 in Moscone South - 307

My schedule for OOW15

It seems to be a things that people blog about their schedule for Oracle Open World and talk about how busy they will be.

So to join the club this is my current schedule.

The boxes that are (a kind of) orange with red text are when I have MY presentations.

The purple boxes indicate some fun event and entertainment.

When attending conferences sometime you get to hear about a good presentation or some other events just happen. So this schedule is tentative and will probably only reflect 40%-60% of what I will really attend/do. There is so much going on each day.

You will see I have some Hands-on-Labs booked. These are a great way to try out some product for an hour. I highly recommend you doing some too.

By far my favourite part of my OOW trip is the Oracle ACE Director briefing that OTN arranges for us. This briefing will happen on the Thursday (22nd) and Friday (23rd) before OOW, and this is held in Oracle HQ in Redwood Shores.

We have been given a tentative schedule for the ACED Briefing and for me I highlight of this briefing is when Thomas Kurian comes in an very openly talks to us, telling us lots and lots and lots about what is happening across the Oracle product set. I would lover to be able to share what we are told but we are all under NDA. After Thomas we have some Cloud updates, then we get onto some updates on Oracle Development Tools, lots of updates on what is coming next in the Oracle Database. So we get the likes of Penny Avril, Andy Mendelsohn, Tom Michelini, Steven Feurerstein, Roland Smart and Wim Coekaerts, among others. So lots and lots of EVPs, Sr VPs and VPs.

After the briefing is over we then hop on a bus and head to our hotel in download San Francisco, near Union Square, where we will be based for the OOW.

Thanks to all in OTN and to those who run the Oracle ACE programme for arranging and paying for my flights, hotels and transportation.