This story is about how I created my very first Mobile App on top of SAP HANA DB. I don’t reckon my app is a state of the art app, however I think the journey illustrates how it is possible to accomplish things nowadays. So I decided to share with you all how it happened.
I could not think of two more distant from each other events to spark my willingness to build a mobile app. Being an SAP BI/BW Consultant, I am aware of all the fuzz about HANA and how it could mean (or not) the end of BW, at least as we have known it so far. In May 2012, thanks to twitter, I came across juergen.schmerder Schmerder’s blog ‘Get your own SAP HANA on AWS’. After reading the blog, I went straight to AWS and created my own SAP HANA instance. No better way to understand a technology than hands on experience. After following Juergen's walk-through my instance was kicking and running.
The second event is related to one of my biggest passions: Baseball. For the last five years I've been using Citizen Sports Baseball Fantasy Application. Sadly, they announced last year that season 2012 was going to be their last one. Having migrated to Australia in 2006, I had been able to share my baseball passion with relatives and friends back in Venezuela thanks to CS Baseball Fantasy App. I had a complaint about Citizen Sport’s app, though. It didn't work on my IOS devices.
One plus one is two. I needed to learn more about HANA, Cloud, and Mobile. Also, I needed a Fantasy Baseball App. So I decided to develop the app by myself. How hard could it be?
As soon as I set my mind on it, all I had was a bunch of questions. All I knew was that I wanted to use HANA as the database. No idea about what to use for developing an IOS compatible UI, where to source the data from, how to load the data (once sourced) into HANA, application hosting, user authentication, scheduling jobs, etc. How was I supposed to figure out all these questions? What better way but using Google search. I started by choosing a tool for documenting my questions and the answers founded along the way, easy one: Evernote.
Step one: create a data model for my application. Free tool out there where to create an ER Diagram: Lucidchart. Step two; create the app tables. SAP HANA Studio is very intuitive however nothing replaces SQL scripting, therefore I added SAP HANA SQL Reference to my favourite links.
Time for the very first real hurdle: where to source the data from and how to load it into HANA. MLB maintain all the baseball players’ stats therefore it was the natural place where to start digging. After many searches I stumble into a chat room where a MLB API that provides players’ stats in JSON format was mentioned. Home Run!
How to create my own API was the next question. The answer, ScrapperWiki If you haven’t heard about it, ScrapperWiki is a web-based platform where programmers write scripts to obtain data sets by scrapping web pages. Scripts can then be scheduled to run automatically and data exposed with a flexible API.
Once my APIs were set up next step was to load the data into HANA. I can't afford a Data Services license, so I chose to write my own Java program using the same JDBC driver that comes with SAP HANA STUDIO client to connect to my instance and load the data. After adding library ngdbc.jar to my app JARfiles I was able to set up a Data Source:
<sql:setDataSource
driver = "com.sap.db.jdbc.Driver"
url = "jdbc:sap://XXX.XX.XX.XXX:30015?reconnect=true"
user = "USER2"
password = "YourPassword" />
...from there you can query and/or update the tables in your HANA instance.
DATA loading was pretty much sorted out. It was time to exploit HANA real power: Attribute, Analytic and Calculation Views. I wanted to push down to the DB most of the application’s logic; therefore procedures were also part of the arsenal. I created several procedures, one of them for allowing fantasy managers to trade players. A very complex one, as fantasy managers are not suppose to trade players from baseball games that already started. No way I would have completed the procedure without using dynamic SQL - EXECUTE IMMEDIATE:
EXECUTE IMMEDIATE 'INSERT INTO TLI' || :fyteam || '' || :pkey || '
SELECT DISTINCT "PLAYERKEY", "PLAYERNAME", "TEAMABB", "BATS", "THROWS", "LASTNAME"
FROM "_SYS_BIC"."fantasy.mlb.salarycap/CAV_FBSC_PLYNOTINFYT"
(''PLACEHOLDER'' = (''$$FYTEAM$$'', ''' || :fyteam || '''),
''PLACEHOLDER'' = (''$$POSTX$$'', ''' || :postx || '''))
$$FYTEAM$$,$$POSTX$$ are variables defined in the calculation view , :fyteam, :postx are parameters received when the procedure is invoked. That way I can use the same procedure to query my calculation views using different values during execution time. More on this topic at http://help.sap.com/hana/hana_dev_sqlscript_en.pdf - Chapter "Dynamic SQL"
Finally it was time to work out the UI. Something simple, sleek, IOS and Android compatible was the order of the day. After watching Alexis Guinebertiere (@aguinebe) SAP HANA Academy video series about developing an HTML5 mobile app on top of a HANA DB: http://www.youtube.com/playlist?list=PL3Bj5pSnuytG9IKmIkGwguFMpebf7hpX4 , I had no doubt in my mind: SENCHA.
I had to sort out a couple more things. I didn't want to implement authentication features with all the hassle it takes. Although I have a preference for Google, most of my fantasy mates don’t use Google+ but Facebook. So keeping in mind that I didn't want to ask my mates to enroll in another social network to access my app, I implemented a Facebook login button. Google+ and Twitter authentication will be there in future app versions for sure.
Scheduling jobs in HANA was the last of all the hurdles I had to work around. Please go to SAP Idea place and promote the idea of having job scheduling capabilities incorporated into HANA. While that happens the oldies cron/at Unix commands were my best arms. Using Linux scheduling commands along hdbsql command line utility I was able to execute SAP HANA procedures at specific times of the day. Perfect for taking snapshots of fantasy teams' rosters at the baseball games' start times.
It was time to make my Baseball Fantasy App available for everybody out there. Twelve months introductory Amazon Web Services Free Tier is enough to have a tomcat server where to expose most apps, including mine.
Hopefully you will find useful some of the sites and learning material I have ran into, if that is the case I will have fulfilled my goal of sharing these links, not just in a boring list but with some context around them. Questions are welcome. In case you would like to try my fantasy app by yourself go to: http://goo.gl/5LG1c feel free to create your own baseball fantasy team. PLAYBALL!
- SAP Managed Tags:
3 Comments
