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

Mash-up Airlines Performance Data with Historical Weather Data to Pinpoint Weather Related Delays

For this exercise, I combined following four separate blogs that I did on BigData, R and SAP HANA.  Historical airlines and weather data were used for the underlying analysis. The aggregated output of this analysis was outputted in JSON which was visualized in HTML5, D3 and Google Maps.  The previous blogs on this series are:

1. Big Data, R and HANA: Analyze 200 Million Data Points and Later Visualize in HTML5 Using D3 - Part II
2. Big Data, R and HANA: Analyze 200 Million Data Points and Later Visualize Using Google Maps
3. Getting Historical Weather Data in R and SAP HANA 
4. Tracking SFO Airport's Performance Using R, HANA and D3

In this blog, I wanted to mash-up disparate data sources in R and HANA by combining airlines data with weather data to understand the reasons behind the airport/airlines delay.  Why weather - because weather is one of the commonly cited reasons in the airlines industry for flight delays.  Fortunately, the airlines data breaks up the delay by weather, security, late aircraft etc., so weather related delays can be isolated and then the actual weather data can be mashed-up to validate the airlines' claims.  However, I will not be doing this here, I will just be displaying the mashed-up data.

I have intentionally focused on the three bay-area airports and have used last 4 years of historical data to visualize the airport's performance using a HTML5 calendar built from scratch using D3.js.  One can use all 20 years of data and for all the airports to extend this example.  I had downloaded historical weather data for the same 2005-2008 period for SFO and SJC airports as shown in my previous blog (For some strange reasons, there is no weather data for OAK, huh?).  Here is how the final result will look like in HTML5:

Click here to interact with the live example.  Hover over any cell in the live example and a tool tip with comprehensive analytics will show the break down of the performance delay for the selected cell including weather data and correct icons* - result of a mash-up.  Choose a different airport from the drop-down to change the performance calendar. 

* Weather icons are properties of Weather Underground.

As anticipated, SFO airport had more red on the calendar than SJC and OAK.  SJC definitely is the best performing airport in the bay-area.  Contrary to my expectation, weather didn't cause as much havoc on SFO as one would expect, strange?

Creating a mash-up in R for these two data-sets was super easy and a CSV output was produced to work with HTML5/D3.  Here is the R code and if it not clear from all my previous blogs: I just love data.table package.

###########################################################################################  

# Percent delayed flights from three bay area airports, a break up of the flights delay by various reasons, mash-up with weather data

###########################################################################################  

baa.hp.daily.flights <- baa.hp[,list( TotalFlights=length(DepDelay), CancelledFlights=sum(Cancelled, na.rm=TRUE)), 

                             by=list(Year, Month, DayofMonth, Origin)]

setkey(baa.hp.daily.flights,Year, Month, DayofMonth, Origin)

baa.hp.daily.flights.delayed <- baa.hp[DepDelay>15,

                                     list(DelayedFlights=length(DepDelay), 

                                      WeatherDelayed=length(WeatherDelay[WeatherDelay>0]),

                                      AvgDelayMins=round(sum(DepDelay, na.rm=TRUE)/length(DepDelay), digits=2),

                                      CarrierCaused=round(sum(CarrierDelay, na.rm=TRUE)/sum(DepDelay, na.rm=TRUE), digits=2),

                                      WeatherCaused=round(sum(WeatherDelay, na.rm=TRUE)/sum(DepDelay, na.rm=TRUE), digits=2),

                                      NASCaused=round(sum(NASDelay, na.rm=TRUE)/sum(DepDelay, na.rm=TRUE), digits=2),

                                      SecurityCaused=round(sum(SecurityDelay, na.rm=TRUE)/sum(DepDelay, na.rm=TRUE), digits=2),

                                      LateAircraftCaused=round(sum(LateAircraftDelay, na.rm=TRUE)/sum(DepDelay, na.rm=TRUE), digits=2)), by=list(Year, Month, DayofMonth, Origin)]

setkey(baa.hp.daily.flights.delayed, Year, Month, DayofMonth, Origin)

# Merge two data-tables

baa.hp.daily.flights.summary <- baa.hp.daily.flights.delayed[baa.hp.daily.flights,list(Airport=Origin,

                           TotalFlights, CancelledFlights, DelayedFlights, WeatherDelayed, 

                           PercentDelayedFlights=round(DelayedFlights/(TotalFlights-CancelledFlights), digits=2),

                           AvgDelayMins, CarrierCaused, WeatherCaused, NASCaused, SecurityCaused, LateAircraftCaused)]

setkey(baa.hp.daily.flights.summary, Year, Month, DayofMonth, Airport)

# Merge with weather data

baa.hp.daily.flights.summary.weather <-baa.weather[baa.hp.daily.flights.summary]

baa.hp.daily.flights.summary.weather$Date <- as.Date(paste(baa.hp.daily.flights.summary.weather$Year, 

                                                           baa.hp.daily.flights.summary.weather$Month, 

                                                           baa.hp.daily.flights.summary.weather$DayofMonth, 

                                                           sep="-"),"%Y-%m-%d")

# remove few columns

baa.hp.daily.flights.summary.weather <- baa.hp.daily.flights.summary.weather[, 

            which(!(colnames(baa.hp.daily.flights.summary.weather) %in% c("Year", "Month", "DayofMonth", "Origin"))), with=FALSE]

#Write the output in both JSON and CSV file formats

objs <- baa.hp.daily.flights.summary.weather[, getRowWiseJson(.SD), by=list(Airport)]

# You have now (Airportcode, JSONString), Once again, you need to attach them together.

row.json <- apply(objs, 1, function(x) paste('{\"AirportCode\":"', x[1], '","Data\":', x[2], '}', sep=""))

json.st <- paste('[', paste(row.json, collapse=', '), ']')

writeLines(json.st, "baa-2005-2008.summary.json")                 

write.csv(baa.hp.daily.flights.summary.weather, "baa-2005-2008.summary.csv", row.names=FALSE)

Happy Coding!

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!

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

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!

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

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 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 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.

Here is function that can be called repeatedly by other functions:

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)

  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)

}

Let's put this function to test:

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

> geoCodes
           Lat            Lng 
  "37.4418834" "-122.1430195" 

You can run this on the entire column of a data frame or a data table:

Here  is my sample data frame with three columns - Opposition, Ground.Country and Toss. Two of the columns, you guessed it right, need geocoding.

> head(shortDS,10)

     Opposition              Ground.Country Toss

1      Pakistan            Karachi,Pakistan  won

2      Pakistan         Faisalabad,Pakistan lost

3      Pakistan             Lahore,Pakistan  won

4      Pakistan            Sialkot,Pakistan lost

5   New Zealand    Christchurch,New Zealand lost

6   New Zealand          Napier,New Zealand  won

7   New Zealand        Auckland,New Zealand  won

8       England              Lord's,England  won

9       England          Manchester,England lost

10      England            The Oval,England  won

To geo code this, here is a simple one liner I execute:

shortDS <- with(shortDS, data.frame(Opposition, Ground.Country, Toss,

                  laply(Ground.Country, function(val){getGeoCode(val)})))

> head(shortDS, 10)
    Opposition           Ground.Country Toss  Ground.Lat  Ground.Lng
1     Pakistan         Karachi,Pakistan  won   24.893379   67.028061
2     Pakistan      Faisalabad,Pakistan lost   31.408951   73.083458
3     Pakistan          Lahore,Pakistan  won    31.54505   74.340683
4     Pakistan         Sialkot,Pakistan lost  32.4972222  74.5361111
5  New Zealand Christchurch,New Zealand lost -43.5320544 172.6362254
6  New Zealand       Napier,New Zealand  won -39.4928444 176.9120178
7  New Zealand     Auckland,New Zealand  won -36.8484597 174.7633315
8      England           Lord's,England  won     51.5294     -0.1727
9      England       Manchester,England lost   53.479251   -2.247926
10     England         The Oval,England  won   51.369037   -2.378269

Happy Demoing and Coding!

Closing the loop on Pervasive Location Analytics - an enlightening personal journey for sure!

When I started working on Google Maps deal at SAP in February of this year, I had no clue where it will end and what is next once the deal is done. I fell in love with this Location Analytics/Geo Data Visualization topic, and turned it into an opportunity to discuss this topic and also generate excitement in various different camps along the way.

Five sessions spread across three continents, 200+ attendees,1000 views and numerous downloads later, this topic became more than just a personal interest. I met great people along the way and worked with very smart and driven people to co-present from the likes of Ryan from Centigon Solutions, Nimish from FreshDirect and Brendan from ThinkSmart Technologies. (See links to slides and session evaluation below)

A proud moment arrived this morning when an alert from SlideShare popped up indicating that this topic is hot on Facebook and as a result this topic is being put on SlideShare home page. Wow!


Pervasive Location Analytics: The Next Frontier to Fall in The Enterprise Software?

Session Evaluations Results

Thank you - my next two blogs will be presenting my thoughts on Moblie Analytics and Agile BI - two topics I have spent significant amount of time from strategy, market, customer, competition and product point of view.

Best quotes from Forbes Article SAP-Google Maps Partnership

SAP Partnership with Google Maps Indicates a New Openness

"SAP is overcoming a legacy of an insular engineering culture that could accurately be accused of suffering from a “not invented here” complex in the past."


"The only problem with SAP’s pride in its history is that it has sometimes shut the company’s eyes to new ways of creating software. It appears that this announcement may mark a turning point to increased awareness and use of outside components. If SAP becomes truly open to using more and more outside components, and learns how to use them to create stable, reliable software, SAP could accelerate the pace of change, keeping the stable parts of its applications, but adding the best of what has newly arrived."


There is something more at stake for Google than money - it is likely that Google Maps will be adapted by Google to better meet the needs of enterprise applications.


"What’s next? What other cloud components will SAP start to incorporate? The second is: Is a bigger partnership possible? Google’s mission is “to organize the world’s information and make it universally accessible and useful.” Much of the information that runs the world is in SAP. Why aren’t Google and SAP working together to make it more universally accessible and useful?"