Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
jan_teichmann
Explorer

Motivation

I know there are hundreds of thousands of game developers out there. I believe HANA creates added value. So, I want to create an Android Game on HANA One.
Now, "creating" the whole app is a little boastful.  You actually can download mine and run it - probably even in 5 minutes. Why? I want to keep it simple and small to be able to do it quickly. That's why I don't start from zero this time but I copy a game with some 200 lines of code (and icons etc.) and enhance it to some 600 lines.
The cool games should come from somebody else out there. But usually, from what I have seen, game developers focus on their game experience. They don't care about who is using it when and how many of them are staying away after some time, and why? Here is where HANA can help. I simply store game statistics in HANA and offer one simple way of analyzing the data. - Yes, this can be done on any database. But HANA can offer more – (here I start "selling" my SAP colleagues' products a little bit):
  • a whole set of pre-built, mature statistics and an analytics toolkit that can be used to reduce the churn rate for instance
  • in-game recommendations - for better game experiences and higher customer-conversion rates
  • combination of on-device (SQL Anywhere) and in-cloud (HANA One) databases, being sync'ed asynchronously
But let's get back to the technology stuff. Most of the ideas mentioned above require a game engine and more work. So, I start small and show you an entrance-level HANA support, only.
Why HANA One? Again, because it is so affordable and good enough for this scenario.
So, I looked for the simplest game I could find on the internet. Therefore, it is a device-only game not making use of a game engine. But what I show here can, of course, be used with game-engines as well, even more easily because then the backend-connection is there anyway. The game I use is the Ball Game from Vinay from this website http://www.skill-guru.com/blog/2011/02/14/developing-games-on-android/. All the credits to him.
I was building on my last 2 blogs on HANA Extended Application Services which have content I am making use of here and which are good to read:
  1. How to create an Android app on SAP HANA One in 20 minutes
  2. Extend an Android app on SAP HANA One in 20 minutes
Finally, I will show HANA technology which is only available as of SPS6 (~end of June 2013): Write-back via Odata with http PUT / PATCH. I have tested it on SAP-internal servers. Sorry for making your mouth water before you can try it yourself. So, on HANA One this will work probably as of July/August.

Ball Game - How do you play it?

  • You have to hit the blue dots in the blue circle, green dots in the green circle etc.
  • Every hit scores once per round and is shown with a green checkmark.
  • When you turn the device from landscape to portrait or vice versa or switch it on/off, a new "game" is started counting from zero again.

It should look like this:

Getting Started

I started by downloading the game from that site mentioned above including images etc. You can do that, too. But I will give you the zip files to download my final version from my dropbox. Here are they:
I think that makes it easier. So, the didactics of this blog is a little bit different from my last 2 blogs where you didn't have to download anything. Instead of creating every artifact step-by-step, I make you aware of the different important aspects. While reading the blog you should go through them and see it yourself.

Source Code Upload

There are 2 parts in it:
  • Android project
    • Download this zip file, unzip it (typically in your Android projects' folder / workspace), and then
      • create a File -> New -> Project... -> General -> Project (no Android Application Project), give it some <project name> click "Finish"
      • go File -> Import -> General -> File System, choose the "From directory" where you stored the extracted zip file and import everything with “overwrite existing resources without warning” when asked "Overwrite .project in <new project name>?" select "Yes To All"
    • As soon as this is done, you can double-click <project name> -> src -> com.example.ballgame -> MainActivity.java and "run as" Android Application.
    • It will run the BallGame on your device / in the emulator. The "Score" statistics on top of the screen will always count from zero and the "Total" will always be empty / zero. This is OK as it reflects the fact that we don't have a HANA database connection yet. 
  • HANA XS project
    • Download this zip file, unzip it (typically in your XS projects' folder / workspace), and then
      • create a -> New -> Project -> SAP HANA Development Project -> XS Project -> Next -> Project Name: <new XS project name>
      • go File -> Import... -> General -> File System -> File System, choose the "From directory" where you stored the extracted zip file and import everything with “overwrite existing resources without warning” when asked "Overwrite .project in <new project name>?" select "Yes To All"
    • Then, right-click your XS project -> Team -> Share Project... -> select your <repository workspace> -> Finish
    • Team -> Commit   and   -> Team -> Activate
      • Beware: When you activate everything in 1 go you will have to wait for 30 mins or more. I included csv files containing ~ 80,000 lines of data in order to allow for some reasonable analytics later on. Importing csv files isn't made for large amounts of data, so be patient. So, you should just activate all the rest first and do these (.csv / .hdbtid / .hdbtim) during lunch or so.
      • Probably, during activation an error will occur for the hdbtid files. Just select the hdbtid files again and activate them (after the hdbtim files are active).
  • This is an export of my implementation containing my users, passwords, IP addresses etc. Please change them in MainActivity.java as described below!!!!!! It won't work otherwise!
    • Make sure to replace "<hana.server.name>:80<hana.instance.number>" with your HANA system (for instance "23.20.228.188:8000")
    • Make sure to replace "<hana user name>" (for instance "SYSTEM") and "<your pw>" (for instance "manager")
  • Great! Once you have done this, you can run the Android app from eclipse.

Explanation of Android part

The Android app contains all the artifacts from the original game downloaded from http://www.skill-guru.com/blog/2011/02/14/developing-games-on-android/. So, what did I change or add?
  • I adjusted the AndroidManifest.xml to cope for INTERNET permission, a higher min SDK version (9), debugging and another launch icon.
  • I added 2 icons in -> res -> drawable-*dpi: ballgame.png (launch icon) and tick_circle.png (checkmark icon)
  • I enhanced the -> res -> layout -> activity_main.xml by 1 line, giving it an id.
  • In the -> src -> com.example.ballgame (originally richie.ballgame) I sticked to the two classes:
    • MainActivity.java (originally main.java)
    • ballview.java
  • In MainActivity.java:
    • I added the onTouchEvent method. It reacts on every touch of the device screen, gets its x-y-coordinates and calls the checkIfWithinDrawable method in ballview.java which checks whether the touch was a hit and keeps track of the score within the game only.
    • I added the onSaveInstanceState method. It is being called at the "end of each game" (landscape/portrait rotation or switching on/off).
      • I write back the current game's score to the DB (see explanation of USAGE table below).
      • I add the in-game score to the historic overall score and make it the new historic overall score.
      • I store this total score and the login date/time in the device's memory. I figured out that this is the only way to keep those data before a new game is started and onCreate initializes everything.
    • In turn, in onCreate method I read historic score date/time data - if existing - first. Also, I read the score data from HANA only once ( if (PLAY_START_DATE.isEmpty()) ).
    • In order to lower complexity of the code, I kept the two asynchronous tasks to conect to HANA GetDataAsyncTask and WriteDataAsyncTask separate.
    • In the getData access of HANA, I make use of one http GET: http://:80/BallGameHANAOWS/DataInput.xsodata/GameUsageCS/?$top=1&$format=json. (You can test it in your browser.) It in turn makes use of the GameUsageCS odata service based on the calculationview which just returns aggregated score numbers for that player. After parsing the returned JSON, these numbers are moved to the hitTotal, roundTotal, ErrorTotal, gPlayedTotal variables and then displayed on screen as historic score data.
    • In the writeData access of HANA, I do a http POST $batch call (code line 301). I follow everything explained in this blog.
      • The one thing remarkable is this: Instead of doing a batch of http GET requests, I do a http POST request the first time to create a new DB record (code line 344, inside the POST batch) and a http PUT request for the subsequent times to alter an existing table line (code line 350, via X-HTTP-Method).
      • This is a little bit premature writing about how to write data via the XS odata service interface because it will only be available as of SPS6 which will be released at the end of this June 2013 only! I tested it on internal SAP systems (thanks to my colleagues!) and this is why it won't work on HANA One until it will move to SPS6 (probably sometime in July). So, when testing before that do expect that the total score will only get updated after the first game.
        • Besides that I still do that extra http GET in the http POST batch which I can't get rid of using the MultipartEntity. May be it could work better using some AJAX framework or whatever. Still, the repsonse, of course, contains an error and I just ignore it as I don't even parse the response of the write service call.
    • Because I am using date / time columns in HANA in read and write access, I was facing a little bit of a hassle with format transformations. I am doing that in JsonDateToDate, DateToJsonDate, DatetimeToDate, DatetimeToTime, DateToDatetime, TimeToJsonTime methods. I think, my code is not standard worthy and you might want to work with JodaTime or similar frameworks. But it shows the different date/time formats involved - and worked for this blog.
  • In ballview.java:
    • In the onDraw method, after drawing the 4 circles and the 8 dots, I draw up to 2 more checkmark icons (mDrawable[4]), depending on whether the player hit the rigth dots (possibly up to two blue ones in the blue circle etc. stored in wasHit[]).
      • I draw the 2 score text lines on the device screen (canvas.drawText) containing the current in-game score and the historic total score.
      • Finally, I refresh the screen with a frequency of 200 milliseconds (mRedrawHandler.sleep(200);). Why 200? See my comment on RefreshHandler further down.
    • In the checkIfWithinDrawable method, which I added, I check whether the touch was in one of the big circles and, if yes, on one of the dots (= "a hit") and then add 1 to totalHits or totalErrors variables, the latter should count when the player hit the screen but did not hit a dot in a right-colored circle.
    • In the RefreshHandler method, I am only calculating new positions of the 8 dots (posx, posy) every 5th time. 5 times 200 milliseconds means there is a new round every second - which I want as a game experience. I am using the other 4 possibilities for screen refresh to possibly add the checkmark icons on top of the dots that were hit. - I couldn't figure out an easy other way to "intermediantly" draw a success icon on the screen.
Of course, this game gets boring pretty soon. And you would probably want to add levels of difficulty to it. You could for instance enlarge the frequency, put more dots on-screen, enlarge randomness etc. And of course, you would have to keep track of it. And I tried to avoid any more complexity. So, any game developer will know what to do...

Explanation of HANA part

The HANA part contains:
  • 3 DB table definitions in one DB schema definition (.hdbtable / .hdbschema files).
    • This is straight forward as in other databases as well.
    • PLAYER table contains player specific information. The game can be played by many people and this table allows for analytics on them.
    • GAME table contains soem information on the different possible games. It is a bit of overdesign here because we only have one game.
    • USAGE table contains all information on game statistical information. I decided to use 4 primary key columns: PLAYER_ID, GAME_ID, PLAY_START_DATE, PLAY_START_TIME. While the first 2 are obvious, the last 2 allow for analytical questions like:
      • How long did a player play in 1 login? How many games did he play?
      • Did he improve during that time?
      • How often did he "return"?
      • ...
  • Two HANA calculation views (.calculationview file).
    • The GAME_USAGE_C.calculation view directly returns aggregated game statistic data. I first wanted to use a "select ... group by..." type of odata service call to HANA. But then I realized that - although many other SQL parts are there - there is no aggregation capability built into Odata services. So, this calculation view is a perfect example of how easy HANA can cope for such situations.
      • Also, this "scripted XML version" of a view definition copes for a team development / regi commit and activation of sources. After looking for an exact syntax description of this XML "code", I found that it is actually much easier to create the "SQLScript-based" calculation view in the SAP HANA Systems view in the Content region (below DB Catalog region) by right-clicking your folder -> New -> Calculation View... -> providing a name, selecting View type "SQL Script" -> Finish and then defining it as described in my other blog. Finally, in the right-most area where you define your calculation view, there is a "Display XML" button in the top button area. This gives you the exact XML source code which you can put into your .calculationview file and then commit/activate.
    • The GAME_USAGE_C_A.calculation view has been designed to allow for some analytics (see further down). It contains some original data as Attribute
        • LAST_LOGIN_DATE
        • LASTNAME
      • somes counts or sums of columns showing up as measures
        • AVG_GAMES_PLAYED = column sum(GAMES_PLAYED) / count(PLAYER_ID) - divided by number of players, the same for:
        • AVG_LOGINS_TOTAL,
        • AVG_HITS_TOTAL
        • AVG_ERRORS_TOTAL
        • AVG_ROUNDS_TOTAL
        • AVG_GAMES_TOTAL
      • plus some calculated columns as measures:
        • AVG_DAYS_BETWEEN_LOGINS [ = sum(DAYS_BETWEEN(U.PLAY_DATE, TO_DATE(now() ) )) /count(U.PLAYER_ID)  ]
        • LAST_PLAY_DURATION_MINS and [ = sum(SECONDS_BETWEEN(U.PLAY_START_TIME, U.PLAY_TIME ) ) /60 / count(U.PLAYER_ID) ]
        • DAYS_SINCE_LAST_LOGIN [ = sum(DAYS_BETWEEN(P.FIRST_REGISTRATION_DATE, P.LAST_LOGIN_DATE )) / sum(P.NUMBER_OF_LOGINS) / count(U.PLAYER_ID) ] 
  • An XS application (.xsapp file / .xsaccess / .xsodata files).
    • Pretty straightforward, as described in my other blogs, for instance this blog.
    • I am exposing the first calculationview as an odata service.
  • Data (csv files in the data folder, plus hdbtim and hdbtid files) which will load a lot of test data into your HANA.
    • In my recent blogs I created test data via SQL INSERT statements. Here, I wanted to show some better technique to do it and get more test data. Still, in a productive scenario the initial load would probably be done via tools like SAP Business Objects' Data Services

Game Analytics with Lumira

So, after creating the game experience and connecting it to HANA and gathering data, the most interesting part now is to do some analytics with the data collected. As Lumira, the new Cloud Analytics tool from SAP comes with HANA One, let's install it and use it. You can install a freely available test-version of it out of HANA One or from here: http://scn.sap.com/docs/DOC-31772.
  • After installation, you run it, click on New Document, choose HANA Online, insert your connection data and automatically all calculation and analytic views are offered for analysis. Select -> BallGameHANAOWS -> GAME_STATISTICS_A and click Acquire.
  • For instance you draw AVG_DAYS_BETWEEN_LOGINS to the y axis and LAST_LOGIN_DATE to the x axis: This is shown below.
  • Or you could draw AVG_DAYS_SINCE_LAST_LOGIN on y and LAST_LOGIN_DATE on x. This would tell you about how many you have already lost (away for more than 10 days) and how many players will get lost soon (not with these dumb data).
  • These are examples how you answer your questions from above. Below, I show 2 Lumira screen shots which give you answers on how well your game is doing!
    • Beware of the other stuff. I have created pretty dumb sample data. So, most of the other curves look pretty silly and constant... Sorry for that.

Over time, the average days between logins gets smaller. So, users get more active: Wonderful!

Over time, there is a steep rise in the number of players. Wonderful!

  • So, play around with it and have fun!
  • What did we learn?

    We have learnt - in addition to what we learnt from my last blogs:
    • How and why we could and should run mobile games on HANA or - say - connected to HANA.
    • How to write-back data to HANA.
    • How to use script-based calculation views for aggregation and for analytics.
    • How to use Lumira for easy game analytics.