OBIEE tips and tricks: Box and Whiskers Plot using R and BIP

I’ve just started experimenting with R in conjunction with BI-Publisher. R provides various statistical packages and plotting utilities and there are numerous resources on the web dedicated to it. The goal is to take a generate a plot using R and render it in BI-Publisher. The installation steps for R enterprise are covered in the Oracle® R Enterprise User's Guide, the main steps are:

Install R: http://cran.r-project.org/bin/windows/base/
Download Oracle R Enterprise are available from http://www.oracle.com/technetwork/database/options/advanced-analytics/r-enterprise/index.html

ore-supporting-windows-1.0.zip, the supporting R packages
ore-windows-1.0.zip, the Oracle R Enterprise packages.

Start R and install the packages, this can be done from the command line or using the R GUI

Install the R server ((LINUX/UNIX)

To connect to R enterprise from R client run the following:
ore.connect(user = "",sid = "",host = "",password = "",port = PORTNUMBER)
# Synchronize R with user's schema in Oracle
ore.sync()
# Attach environment containing visible Oracle tables and views
# to the search path
ore.attach()
Modify (user, sid, host, password, and port) for the database where the R Server is installed

Create the R script

As an example, I’ll generate a box and whiskers plot using the ONTIME_S airline on-time performance data. Box and whiskers plots are not available out of the box in BI-Publisher. The plot will use a subset of the dataset to focus on the DMV Airports: Reagan Airport (DCA), Dulles Airport (IAD) and Baltimore Washington airport (BWI) for 2007 and 2008.
Below is the R script that generates the plot:

f <-function(){
ontime <- ONTIME_S
delay <- ontime$ARRDELAY[ontime$DEST %in% c("BWI", "IAD", "DCA") & ontime$YEAR %in% c(2007,2008)]
dest <- ontime$DEST[ontime$DEST %in% c("BWI", "IAD", "DCA") & ontime$YEAR %in% c(2007,2008)]
dest <- reorder(dest, delay, FUN = median)
bd <- split(delay, dest)
res <- boxplot(bd, ylim=c(-50,60),col=c( "darkolivegreen","coral3","cornflowerblue"),main="Flight Delays by Airport", xlab="Airport", ylab="Delay (minutes)")
}
f()

The R script has to be modified a bit to "escape" the "&" sign (in red), if not ontime will be considered a prompted parameter. This script can be run using SQL*Plus.

BEGIN

sys.rqScriptCreate('DMVAirports',

'f <-function(){

ontime <- ONTIME_S

delay <- ontime$ARRDELAY[ontime$DEST %in% c("BWI", "IAD", "DCA") '||'&'||' ontime$YEAR %in% c(2007,2008)]

dest <- ontime$DEST[ontime$DEST %in% c("BWI", "IAD", "DCA") '||'&'||' ontime$YEAR %in% c(2007,2008)]

dest <- reorder(dest, delay, FUN = median)

bd <- split(delay, dest)

res <- boxplot(bd, ylim=c(-50,60),col=c( "darkolivegreen","coral3","cornflowerblue"),main="Flight Delays by Airport", xlab="Airport", ylab="Delay (minutes)")

} ');

END;