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!

Big Four and the Battle of Sentiments - Oracle, IBM, Microsoft and SAP

In this battle of sentiments or opinions for the four software giants - Oracle, IBM, Microsoft and SAP, SAP is generating a lot of positive buzz with its message of "innovation without disruption" and leading the pack with a 95% sentiment score.

Tag	TweetsFetched	+ve Tweets	-ve Tweets	Avg.Score	Tweets	Sentiment
@IBM	198	49	45	0.081	94	52%
@Microsoft	893	307	78	0.484	385	80%
@Oracle	297	90	17	0.313	107	84%
@SAP	98	55	3	0.673	58	95%

Few days ago, I published this blog "Updated Sentiment Analysis and a Word Cloud for Netflix" and the underlying R code.  I used the same R program to compare the sentiments for the four software giants.  Now, technically speaking, IBM and Oracle are not pure software companies anymore since they both package hardware (server and storage hardware) along with the software but the rivalry between these four companies persuaded me to put a comparative analysis  here.  I originally included HP in this analysis but then dropped it as I didn't consider HP in the same league as these fours in the software category.

What surprised me the most was the lowest score IBM received, lower than Oracle!  What went wrong here?  I am also surprised to see Oracle occupying the second spot with 84% sentiment score.  So besides all the negative publicity Oracle attracts, the sentiment is overwhelmingly positive.

The one improvement I would like to make to this analysis is to get more tweets.  Twitter API restricts the number of tweets that one can fetch and doesn't allow you to fetch older tweets.  I would love to run this analysis over a year worth of tweets and also show a time series of sentiment score.  That will be fantastic!

Here are the four histograms, one each for four candidates, showing the distribution of opinion scores:

SAP

IBM

Microsoft

Oracle








Happy Analyzing!


The underlying data can be downloaded here.

Agile BI, Simple BI, Self-Serve BI - Okay, What the Hell This Thing is?

In layman's terms, anyone, including my mom, who is suffering from information overload should be able to analyze any data using simple and easy to use data visualization tools, get insights (like growth in milk usage at our home) and then share the results with my dad who should cut feeding expensive organic milk to his two cats.

Wow, that sounds pretty simple, isn't it? Yes, and precisely for that reason IDC says that this phenomenon presents a big market opportunity:

“We are at the forefront of an evolutionary market that is fraught with opportunity for innovative tools and solutions that can help users handle the information overload plaguing every major organization around the globe.” IDC Market Analysis, Worldwide Interactive Data Visualization Tools Forecast

How big of a market opportunity? $1 Billion big by 2013 and $1.6 Billion by 2015 says Gartner. See this graphics:

So someone asked me few weeks ago, how would you define simple, self-serve BI and I gave him the following definition -

Agile BI is a simple yet power-packed solution which is easy-to-use, cost-effective and offers full 360 degree experience and above all my mom should be able to use it without bugging me...

And here is my definition of a power-packed solution:

There are ZERO products that fulfill this vision today.  Products like QlikTech, Spotfire and Tableau do a pretty good job and therefore enjoy more that 70% of the market share. Where are the big guys?

"Agile" and "Big" doesn't go together I guess!

Here is how I contrasted Qlik against a large enterprise BI player:



This story is universal and gives competitive advantage to younger more agile players over their older and aging brethren because they have offered one single self-serve BI tool that could serve to many personas!





Qlik and Tableau have seen pretty solid growth over the past few years as a result of keeping their strategy simple.  Here is an older blog on Qlik showing its amazing growth: http://goo.gl/cyV7a


The most recent evidence of double digit growth in the Agile BI market was seen in Tableau's 2011 earnings: (http://apandre.wordpress.com/)

• sales doubled year over year to $72M in 2011
•  104% growth in bookings in Q4’11 and 94% growth YoY,
• WW customer base grew by 40% in 2011
•  more than 7,000 organizations use its analytics product
•  big growth with customers in Europe, where base grew by 67 percent

2011 was the year of Agile (Simple) BI and the momentum is gaining further strength. Do you know now what Agile BI a.k.a Simple BI a.k.a self-serve BI is defined as?

Happy Simplifying!