Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
kevin_small
Active Participant

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

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:

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:

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

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:

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:

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:

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 s0000716522 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:

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.

6 Comments
Labels in this area