Big Data, R and SAP HANA: Analyze 200 Million Data Points and Later Visualize in HTML5 Using D3 - Part II

Technologies: SAP HANA, R, HTML5, D3, JQuery and JSON

In my last blog, Big Data, R and SAP HANA: Analyze 200 Million Data Points and Later Visualize Using Google Maps, I analyzed historical airlines performance data set using R and SAP HANA and put the aggregated analysis on Google Maps.  Undoubtedly, Map is a pretty exciting canvas to view and analyze big data sets. One could draw shapes (circles, polygons) on the map under a marker pin, providing pin-point information and display aggregated information in the info-window when a marker is clicked.  So I enjoyed doing all of that, but I was craving for some old fashion bubble charts and other types of charts to provide comparative information on big data sets.  Ultimately, all big data sets get aggregated into smaller analytical sets for viewing, sharing and reporting.  An old fashioned chart is the best way to tell a visual story!

On bubble charts, one could display 4 dimensional data for comparative analysis. In this blog analysis, I used the same data-set which had 200M data points and went deeper looking at finer slices of information.  I leveraged D3, R and SAP HANA for this blog post.  Here I am publishing some of this work:  

In this first graphics, the performance of top airlines is compared for 2008.  As expected, Southwest, the largest airlines (when using total number of flights as a proxy), performed well for its size (1.2M flights, 64 destinations but average delay was ~10 mins.)  Some of the other airlines like American and Continental were the worst performers along with Skywest.  Note, I didn't remove outliers from this analysis.  Click here to interact with this example.

In the second analysis, I replaced airlines dimension with airports dimension but kept all the other dimensions the same.  To my disbelief, Newark airport is the worst performing airport when it comes to departure delays.  Chicago O'Hare, SFO and JFK follow.  Atlanta airport is the largest airport but it has the best performance. What are they doing differently at ATL?  Click here to interact with this example.

It was hell of a fun playing with D3, R and HANA, good intellectual stimulation if nothing else!  Happy Analyzing and remember possibilities are endless!

As always, my R modules are fairly simple and straightforward:
###########################################################################################  
#ETL - Read the AIRPORT Information, get major aiport informatoin extracted and upload this 
#transfromed dataset into HANA
###########################################################################################
major.airports <- data.table(read.csv("MajorAirports.csv",  header=TRUE, sep=",", stringsAsFactors=FALSE))
setkey(major.airports, iata)


all.airports <- data.table(read.csv("AllAirports.csv",  header=TRUE, sep=",", stringsAsFactors=FALSE)) 
setkey(all.airports, iata)


airports.2008.hp <- data.table(read.csv("2008.csv",  header=TRUE, sep=",", stringsAsFactors=FALSE)) 
setkey(airports.2008.hp, Origin, UniqueCarrier)


#Merge two datasets
airports.2008.hp <- major.airports[airports.2008.hp,]


###########################################################################################  
# Get airport statisitics for all airports
###########################################################################################
airports.2008.hp.summary <- airports.2008.hp[major.airports,     
    list(AvgDepDelay=round(mean(DepDelay, na.rm=TRUE), digits=2),
    TotalMiles=prettyNum(sum(Distance, na.rm=TRUE), big.mark=","),
    TotalFlights=length(Month),
    TotalDestinations=length(unique(Dest)),
    URL=paste("http://www.fly", Origin, ".com",sep="")), 
                    by=list(Origin)][order(-TotalFlights)]
setkey(airports.2008.hp.summary, Origin)
#merge two data tables
airports.2008.hp.summary <- major.airports[airports.2008.hp.summary, 
                                                     list(Airport=airport, 
                                                          AvgDepDelay, TotalMiles, TotalFlights, TotalDestinations, 
                                                          Address=paste(airport, city, state, sep=", "), 
                                                          Lat=lat, Lng=long, URL)][order(-TotalFlights)]




airports.2008.hp.summary.json <- getRowWiseJson(airports.2008.hp.summary)
writeLines(airports.2008.hp.summary.json, "airports.2008.hp.summary.json")                 
write.csv(airports.2008.hp.summary, "airports.2008.hp.summary.csv", row.names=FALSE)

Big Data, R and HANA: Analyze 200 Million Data Points and Later Visualize Using Google Maps

Technologies: SAP HANA, R, HTML5, D3, Google Maps, JQuery and JSON

For this fun exercise, I analyzed more than 200 million data points using SAP HANA and R and then brought in the aggregated results in HTML5 using D3, JSON and Google Maps APIs.  The 2008 airlines data is from the data expo and I have been using this entire data set (123 million rows and 29 columns) for quite sometime. See my other blogs

The results look beautiful:

Each airport icon is clickable and when clicked displays an info-window describing the key stats for the selected airport:

I then used D3 to display the aggregated result set in the modal window (light box):

Unfortunately, I can't provide the live example due to the restrictions put in by Google Maps APIs and I am approaching my free API limits.

Fun fact:  The Atlanta airport was the largest airport in 2008 on many dimensions: Total Flights Departed, Total Miles Flew, Total Destinations.  It also experienced lower average departure delay in 2008 than Chicago O'Hare. I always thought Chicago O'Hare is the largest US airport.

As always, I just needed 6 lines of R code including two lines of code to write data in JSON and CSV files:

################################################################################
airports.2008.hp.summary <- airports.2008.hp[major.airports,     
    list(AvgDepDelay=round(mean(DepDelay, na.rm=TRUE), digits=2),
    TotalMiles=prettyNum(sum(Distance, na.rm=TRUE), big.mark=","),
    TotalFlights=length(Month),
    TotalDestinations=length(unique(Dest)),
    URL=paste("http://www.fly", Origin, ".com",sep="")), 
                    by=list(Origin)][order(-TotalFlights)]
setkey(airports.2008.hp.summary, Origin)
#merge the two data tables
airports.2008.hp.summary <- major.airports[airports.2008.hp.summary, 
                                                     list(Airport=airport, 
                                                          AvgDepDelay, TotalMiles, TotalFlights, TotalDestinations, 
                                                          Address=paste(airport, city, state, sep=", "), 
                                                          Lat=lat, Lng=long, URL)][order(-TotalFlights)]


airports.2008.hp.summary.json <- getRowWiseJson(airports.2008.hp.summary)
writeLines(airports.2008.hp.summary.json, "airports.2008.hp.summary.json")                 
write.csv(airports.2008.hp.summary, "airports.2008.hp.summary.csv", row.names=FALSE)
##############################################################################

Happy Coding and remember the possibilities are endless!

Tracking SFO Airport's Performance Using R, HANA and D3

Visualize Big Data Using R, HANA, D3, JSON and HTML5/JavaScript

This is my first introduction to D3 and I am simply blown away.  Mike Bostock (@mbostock), you are genius and thanks for creating D3!  With HANA, R, D3, HTML5 and iPad, and you got yourself a KILLER combo!

I have been burning my midnight oil on piecing together my big data story using HANA, R, JSON and HTML5.  If you recall, I did a technical session on R and SAP HANA at DKOM, SAP's Development Kickoff Event last week where I showcased the supreme powers of R and HANA when analyzing 124 million records in real time.  R and SAP HANA: A Highly Potent Combo for Real Time Analytics on Big Data

Since last week, I have been looking for other creative ways to analyze and then visualize this airlines data. I am very fortunate to come across D3.  After spending couple of hours with D3, I decided to build the calendar view for the airlines data I have.  The calendar view is the first example Mike shows on his D3 page. Amazingly awesome!

I created this calendar view capturing the percent of delayed flight from SFO airports that departed daily between 2005-2008.  For this analysis, I used HANA to get the data out for SFO (out of 250 plus airports) over this 4 years period in seconds and then did all the aggregation in R including creating a JSON and .CSV file in seconds again.  Later, I moved to HTML5 and D3 to generate this beautiful calendar view showing SFO's performance.  Graphics is presented below:

As expected, December and January are two notorious months for flights delay.  Have fun with the live example hosted in the Amazon cloud..

Once again, my R code is very simple:


## Depature Delay for SF Airport
ba.hp.sfo <- ba.hp[Origin=="SFO",]


ba.hp.sfo.daily.flights <- ba.hp.sfo[,list(DailyFlights=length(DepDelay)), by=list(Year, Month, DayofMonth)][order(Year,Month,DayofMonth)]
ba.hp.sfo.daily.flights.delayed <- ba.hp.sfo[DepDelay>15,list(DelayedDailyFlights=length(DepDelay)), by=list(Year, Month, DayofMonth)][order(Year,Month,DayofMonth)]
setkey(ba.hp.sfo.daily.flights.delayed, Year, Month, DayofMonth)
response <- ba.hp.sfo.daily.flights.delayed[ba.hp.sfo.daily.flights]
response <- response[,list(Date=as.Date(paste(Year, Month, DayofMonth, sep="-"),"%Y-%m-%d"), 
                           #DailyFlights,DelayedDailyFlights,
                           PercentDelayedFlights=round((DelayedDailyFlights/DailyFlights), digits=2))]
objs <- apply(response, 1, toJSON)
res <- paste('{"dailyFlightStats": [', paste(objs, collapse=', '), ']}')
writeLines(res, "dailyFlightStatsForSFO.json")                 
write.csv(response, "dailyFlightStatsForSFO.csv", row.names=FALSE)


For D3 and HTML code, please take a look at this example from D3 website. 

Happy Analyzing and Keep That Mid Night Oil Burning!

Geocode and reverse geocode your data using, R, JSON and Google Maps' Geocoding API

(Reposting the previous blog with additional module on reverse geocoding added here.)

First and foremost, I absolutely love the topic of Location Analytics (Geo-Spatial Analysis) and see tremendous business potential in not so distant future.  I would go out on a limb to predict that the Location Analytics will soon go viral in the enterprise space because it has the capability to WOW us. Look no further than your iPhone or an Android phone and count how many location aware apps you have. We all have at lease one app - Google Maps.  Mobile is one of the strongest catalyst for enterprise adoption of Location aware apps. All right, enough of business talk, let's get dirty with the code.

Over the last year and half, I have faced numerous challenges with geocoding and reverse geocoding the data that I have used to showcase my passion for location analytics.  In 2012, I decided to take thing in my control and turned to R.  Here, I am sharing a simple R script that I wrote to geo-code my data whenever I needed it, even BIG Data.

To geocode and reverse geocode my data, I use Google's Geocoding service which returns the geocoded data in a JSON. I will recommend that you register with Google Maps API and get a key if you have large amount of data and would do repeated geo coding.

Geocode:

getGeoCode <- function(gcStr)  {

  library("RJSONIO") #Load Library

  gcStr <- gsub(' ','%20',gcStr) #Encode URL Parameters

 #Open Connection

 connectStr <- paste('http://maps.google.com/maps/api/geocode/json?sensor=false&address=',gcStr, sep="") 

  con <- url(connectStr)

  data.json <- fromJSON(paste(readLines(con), collapse=""))

  close(con)

  #Flatten the received JSON

  data.json <- unlist(data.json)

  if(data.json["status"]=="OK")   {

    lat <- data.json["results.geometry.location.lat"]

    lng <- data.json["results.geometry.location.lng"]

    gcodes <- c(lat, lng)

    names(gcodes) <- c("Lat", "Lng")

    return (gcodes)

  }

}

geoCodes <- getGeoCode("Palo Alto,California")

> geoCodes
           Lat            Lng 
  "37.4418834" "-122.1430195" 

Reverse Geocode:

reverseGeoCode <- function(latlng) {

latlngStr <-  gsub(' ','%20', paste(latlng, collapse=","))#Collapse and Encode URL Parameters

  library("RJSONIO") #Load Library

  #Open Connection

  connectStr <- paste('http://maps.google.com/maps/api/geocode/json?sensor=false&latlng=',latlngStr, sep="")

  con <- url(connectStr)

  data.json <- fromJSON(paste(readLines(con), collapse=""))

  close(con)

  #Flatten the received JSON

  data.json <- unlist(data.json)

  if(data.json["status"]=="OK")

    address <- data.json["results.formatted_address"]

  return (address)

}

address <- reverseGeoCode(c(37.4418834, -122.1430195))

> address
                    results.formatted_address 
"668 Coleridge Ave, Palo Alto, CA 94301, USA" 

Happy Coding!

R and SAP HANA: A Highly Potent Combo for Real Time Analytics on Big Data

Lets Talk Code

SAP DKOM 2012 kicks off in San Jose today and I can’t be more excited than this.  For the past three months Jens Doerpmund, Chief Development Architect of Analytics at SAP and I have been working on this topic of R and SAP HANA and all our hard work (upwards of 400 hours) is about to pay off (fingers crossed).

It has been a stunning journey and an incredible learning experience. Both R and HANA are fascinating technologies and bringing them together is analogous to bringing Google and Apple together. We are gearing up for our session and in the true spirit of DKOM, we will be only talking code, yes code and lots of it.  We just wrapped up our slides with lots of code snippets to share with fellow DKOMers.  Here is a quick sneak preview of what we are going to cover today:

Big Data Analytics (Really Big)

• Airlines sector in the travel industry
• 22 years (1987-2008) of airlines on time performance data on US airlines
• 123 million records
• Extract Transform Load – ETL work to combine this data with data on airports, data on carriers with this data to setup for Big Data analysis in R and HANA
• D20 with 96GB of RAM and 24 Cores
• Massive amount of data crunching using R and HANA

 We will be covering lots and lot of topics, here is a short list:

• Sentiment Analysis on #DKOM and a WordCloud
• Cluster Analysis using K-Mean
• Geo Code Your Data – Google Maps API
• SP100 - XML Parsing and Historical Stock Data
• R and HANA integration
• Moving big-data from one HANA to another HANA (Replication)
• Server side Java Scripting
• and an HTML5 App built with R, HANA and Server Side Java Script

Here is a wordcloud straight from R on #DKOM. There will be lot more to discuss today. Looking forward to meeting you all DKOMers.

Lets Talk Code Everyone and Happy Coding!

 Jitender Aswani

 Jens Doerpmund

Learn more on this session topic in my previous blog:  Advance Analytics with R and HANA at DKOM 2012 San Jose

Advanced Analytics with R and HANA at DKOM 2012 San Jose

Advanced Analytics with R and SAP HANA

R has become the open source language of choice for statistical data analysis / data mining, advanced algorithms, credit risk scoring and for other forms of predictive analytics.  R is already an in-memory based scripting language and is capable of handling big data, tens of gigabytes and hundreds of millions of rows.  And when combined with SAP's in-memory platform technology called HANA, R offers the potential to take the in-memory analytics to a whole new level.  Imagine performing advanced statistical analysis such as decision tree, game-theory, linear and multiple regressions and much more inside SAP HANA on millions of rows and turning around with critical business insights at the speed of thought. 

This is possible now with R and HANA.  This combination has the potential to completely revolutionize and advance the game of analytics in your enterprise.  This is not it yet.  Imagine taking the output from R and using the Advanced Visualization techniques available in Business Intelligence 4.0 suite based on HTML5 to create stunning visualization for today’s business users.

Just to tease you, here is a one-liner in R that processed 120 million records and brought back aggregated data under 20 seconds:

averageDelay <- dt[,list(AvgArrDelay=round(mean(ArrDelay, na.rm=TRUE), digits=2),

                  AvgDepDelay=round(mean(DepDelay, na.rm=TRUE), digits=2),

                  DistanceTravelled=sum(Distance, na.rm=TRUE),

                  FlightCount=length(Month)), 

                  by=list(UniqueCarrier, Year)][order(Year, -AvgArrDelay)][AvgArrDelay > 10 | AvgDepDelay > 10]

The machine I used for this analysis had 24 cores and 96GB of memory! More to follow over next few days.

Join me and my fellow colleagues for this session at DKOM 2012 San Jose (March 14th at 11 AM at San Jose Convention Center)

You Get What You Pay For - Tale of Two Acquisitions - SAP-SFSF and ORCL-TLEO

Two months ago, SAP made an offer to acquire SuccessFactors("SFSF"), the leading cloud based Human Capital Management ("HCM") company for $3.4B, a multiple of 10.2 on 2011 on expected 2011 revenue of $332M.  I published the following two blogs on this development back in December:

• Tale of Two Companies - SFSF and RNOW - Why would anyone compare SAP-SuccessFactors deal with Oracle-RightNow deal?
• SuccessFactors - An amazing tech story through its financials and a solid grab by SAP!

Salesforce followed suite and acquired Rypple, a company that employs badges and achievements to imbue the employee review process with a collaborative, social media-like experience.  Financial terms were not disclosed. (Source: EnterpriseAppToday)

Oracle was long due after the RNOW acquisition and it decided to follow SAP (for the first time) and Salesforce by acquiring Taleo ("TLEO"), the #2 company in the business, for $1.9B, a multiple of 6.15 on 2011 revenues of $309M. (Source: BusinessWeek)

As usual, folks are reaching out and saying whether SAP's SFSF acquisition is expensive due to a higher multiple it paid to SFSF shareholders and whether it rushed in too early.  I don't believe that SAP's SFSF acquisition is expensive by any stretch of the imagination.  "You get what you pay for" - this notion is quite true in this case. 

The business rationale SAP announced when it made the decision to acquire SFSF was that SFSF is:

• #1 HCM solution in the cloud
• has 15m users from company of all sizes (SalesForce has only 3m users) in diverse 60 industries from across the globe (Example: Siemens has 450K seats)
• 3,500 customers in 168 countries
• 60% recurring revenues from existing customers
• 90% of the growth is organic as oppose to Salesforce
• Has just 14% overlap with SAP customers – a tremendous upside for both companies (with total addressable market of 500m employees of all SAP customers)

On the other end, this is what TLEO disclosed it has: 

• one of the world’s largest cloud deployments with nearly 16 billion transactions per year
• manages 15 percent of all hires in the US 
• has a customer base comprised of 5,000 businesses 
• its Talent Exchange boasts 240 million candidates 
• of the top 30 career sites, nearly half are powered by its technology.

(Source: Taleo)

The two companies can hardly be compared on these business metrics, so I am going to focus purely on financials.  SAP put a forward multiple of 8 on SFSF's expected 2012 revenues of $420M while Oracle is paying a forward multiple of 5 on TLEO's expected 2012 revenues of $379M.  There is this informal "rule of thumb" in place that states that one should pay a multiple of six to eight times of forward earnings for acquiring growth companies. 

Through following series of comparison charts, one could clearly see why SFSF will fetch a higher premium over TLEO.  Everything boils down to just couple of financial metrics and these metrics are: growth and operational efficiencies:

1. SFSF is a better growth story with CAGR more than DOUBLE than that of TLEO:

2. SFSF has far better cost structure than TLEO even though SFSF has grown revenues more than TWICE as fast:

 3. SFSF has somewhat better operating structure and is rapidly becoming more efficient with every dollar it spends on its operating cost. TLEO has done a good job of keeping its cost structure the same, one must wonder, why TLEO is not becoming operationally more efficient:

4. Making money from the cloud apps has been very tough business but this is very quickly starting to change as economies of scale kick in and both companies improve their net-income. SFSF definitely has done a good job in trimming its losses: 

5. The last two charts just compare the growth in revenue for the two companies since inception:

The bottom line is that SFSF is a better growth story and is operationally more efficient than TLEO so a higher multiple for SFSF is fully justified in my opinion. 

Did you know that, Oracle paid a multiple of 10x on Endeca's 2011 revenues? It is not just other companies (SAP or HP) that pay a forward multiple of 10x.

“Though Oracle and Endeca haven't talked about the acquisition price, I reported in October that California-based Oracle had agreed to pay $1.075 billion for the company (based on a document I obtained related to the deal).” (Source: boston.com) 

Happy Browsing!