Skip to Content

This blog explains how to expose standard BW business content for consumption on a 3D globe in a browser.  The relevant source code and HANA build is available in a GitHub repository.

Introduction

Some of the most interesting opportunities opened up when you are in a BW-on-HANA environment are the so-called “hybrid scenarios” or “mixed scenarios.  These mixed scenarios allow BW data to be exposed to HANA and vice-versa.  At first glance this might seem quite boring, but really it’s a very underrated and very real benefit of using BW-on-HANA.  For the past 15 or so years BW has operated in a pretty closed SAP-centric environment, but suddenly when you sit it on HANA, your BW data can be very easily exposed to a whole range of new technologies including the D3 JavaScript libraries for data visualisation.  I say “new” but of course these technologies are not really that new, they’re just new to BW data 😉 .  This isn’t to say you couldn’t do these things with BW-on-Non-HANA, it’s just that now it is trivially easy to do so.

How it will look

Ok, so an example.  Most BW implementations will make use of the BW query usage statistics at one time or another. Normally you’d install business content to get cubes like 0TCT_C01 available to show query usage and performance.  In this example we’re going to adapt this business content to visualise the same data on a 3D globe from a browser.  The result is like this:

StatsOverview.png

On the left above are the usual BW statistics you’d get from business content.  On the right is the result you can get in a browser using the same data with some added user location information. This is developed using HANA, XSJS and the D3 visualisation libraries.  A moving view gives a better idea, you can see the query usage statistics is displayed as animated expanding circles over time, the maximum size represents the number of query navigation steps:

It would be challenging to produce such an output using standard BW reporting tools (certainly using BEx Excel would be tough!), but in a mixed scenario we can do this easily.

How to Build it

So how do we build this?  The data flow diagram looks like this:

DataFlowDiagram.png

On the left hand side of the above diagram, it’s pretty straightforward business content.  The parts on the right are where we’ve exposed the same data to HANA and incoporated user location data from an external table, and make the result available to a browser.  So let’s walk through the steps needed to build each of the items in the above diagram:

Step 1 – Expose Business Content Cube as HANA model

This part is as simple as ticking a tick-box on the business content cube 0TCT_C01 in transaction RSA1 in BW:

BWTickBox.png

Ticking the above generates an Analytic View in HANA, which we can see in the repository:

GeneratedModel.png

We will later use this generated Analytic View in our own custom HANA model.

Step 2 – Add Location Table

Now we need a table to hold user location data.  The BW query usage statistics holds data by user id, and we’ll also hold locations by user id.  In a live scenario you’d probably want to associate a user with an address and then associate that address to a latitude and longitude coordinate.  I created a table called ZUSERLOCATION.hdbtable like this:


table.schemaName = "ZBWSTATS";
table.tableType = COLUMNSTORE;
table.description = "User locations";
table.columns = [
    {name = "USERNAME"; sqlType = NVARCHAR; length = 12; nullable = false; comment = "User name";  },
    {name = "LAT"; sqlType = DECIMAL; nullable = false; precision = 12; scale = 3; comment = "Latitude"; },
    {name = "LON"; sqlType = DECIMAL; nullable = false; precision = 12; scale = 3; comment = "Longitude"; }
];
table.primaryKey.pkcolumns = ["USERNAME"];


I used HANA SP7 to build this and it isn’t possible to enter spatial data types directly.  Jon-Paul Boyd explains why and gives a workaround in his question about spatial data.  So I used that workaround and ALTER the table directly from the SQL console and add the spatial column:


ALTER TABLE "ZBWSTATS"."ZBWSTATS.Data::ZUSERLOCATION" ADD (LATLON ST_POINT(4326));


Then the finished table definition looks like this, with a spatial data type ST_POINT on the end:

ZUSERLOCATION.png

Step 3 – Adding Locations to Location Table

Now we need to add some locations to the location table.  I cobbled location information together from several sources based on cities.  The full city list used together with the latitude and longitude is available in github.  This data was then linked to user id and loaded to the ZUSERLOCATION table.  Because one of the data types is spatial I found it easier to load the data using an SQL script rather than a file upload.  From the city location and user information I used Excel to generate a whole list of SQL statements, one per user, like this:


insert into "ZBWSTATS"."ZBWSTATS.Data::ZUSERLOCATION" values('BWDEVELOPER', 12.463, 41.929, new ST_POINT(12.463, 41.929));


Note the “new ST_POINT” part used to create the spatial data.  Once loaded, the data can be viewed using:


SELECT "USERNAME", "LAT", "LON", "LATLON".ST_AsGeoJSON() from "ZBWSTATS"."ZBWSTATS.Data::ZUSERLOCATION"


The output of the above is:

UserLocationData.png

Step 4 – Join BW Usage Stats with Locations in a Custom HANA Model

Nowe have the base data we need, one part is the generated Analytic View from 0TCT_C01 (which I then wrapped in another view called CV_LA_STATS) and the other part is the ZSERLOCATION table.  The model is simply a join over these:

JoinStatsLocations.png

Now the HANA model is done.  Again because we are in SP7, the data preview fails because we have a spatial data type, so we have to explicity convert it using SQL like this:


SELECT "TCTUSERNM", "CALDAY", "SESSIONCOUNT", "LATLON".ST_AsGeoJSON() FROM "_SYS_BIC"."ZBWSTATS.Models/CV_LE_STATS_PT"


Step 5 – Build and Attach a Frontend

When I started looking around for a suitable frontend for this, I initially looked at Lumira.  On balance, Lumira isn’t really suited for this job, and as a developer I think you look for more control over a frontend tool.  And it doesn’t do 3D globes.  I also looked at a GIS tool called ArcGIS.  This tool was at the other extreme, and is too powerful and had many more features than I needed for this little demo.

For this particular use case, I think it’s hard to beat the frontend developed by Aron McDonald in his HANA Earthquake demo.  So I pretty much stole that, tweaked it a bit to report on query usage instead of earthquakes, and stole a shaded globe from another site to give it a slightly different look and feel.

As far as connecting up the frontend, I’d normally look to use OData to expose a HANA model.  However, I could not get OData to support the ST_POINT spatial type, again I guess this is because I was on SP7.  Therefore I used an XSJS service that does the data selection and provides a JSON output of the data.  Here is the GUI project:

UI.png

The entry point from a browser is: <server:port>/ZBWSTATS/UI/globe/index.html.  The structure of the UI project is as follows.  The services/quakeLocation.xsjs does the data selection (named “quake” because of where I stole it from!).  The usageModel, usageView, usageStyle are an approximation at splitting model and view, and the world-110m.json file contains the data for the globe.  All code for the project is available in the GitHub repository.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Aron MacDonald

    I’m very glad you found another use for my earlier earthquake example.

    Its great fun to see HANA XS used on BW data sources in this way.

    Damn I wish I’d seen that shaded globe effect when I did mine, it looks even cooler  😛

    (0) 
  2. Marcel Jantz

    Hi Kevin,

    I wonder why you are using the spatial column in the first place (as there are so many restrictions on what you can do with them in the outside world and how you get them into the database). Are you using any of the spatial functions like getting the distance or checking whether a location is inside a certain area?

    Best regars, Marcel

    (0) 
    1. Kevin Small Post author

      Hi Marcel,

      You are right the whole thing could be done with latitude and longitude as separate fields, and that is what I have done in other projects.  The spatial type was just a learning experience really, and it is better supported in SP8 onwards.  Perhaps doing this in SP7 makes it look like more hassle than it needs to be.

      A very tiny benefit of using spatial in this example is that when doing the SELECT from the view, it is possible to say: SELECT… LATLON.ST_AsGeoJSON().  When handling that result set in JavaScript it is already in the correct GeoJSON format for passing to the D3 libraries.

      I do not make any use of spatial specific functions, but if a project like this were to be used in anger, then some sort of clustering of points would probably have to happen (rather than every user generating a single point).  I’d expect spatial points would be useful then.

      Kevin.

      (0) 

Leave a Reply