Posts Categorized: Business Intelligence

pastedImage_15

Node.js dashboard for SAP HANA

This is what we will be developing in this blog …

Over the past few years Node.js has really caught my attention. The simplicity of Javascript with server side processing, Non-blocking-IO, Event Driven, and simple integration always intrigued me as a great combination for enterprise applications. (Somehow it sounds similar to HANA XS Engine)
A couple months ago I ran into a similar problem to Jon-Paul Boyd (HANA Forum Post) in which I wanted to use XS Engine for websocket/persistant connections to my HANA Instance, but due to the support not being included in SPS6, I decided to look elsewhere, and ended up using Node.js to fulfill this requirement.In the past, while developing HANA/Node apps, I resorted to creating a XSJS App which really just acted as a middleware layer to push and pull data from my HANA DB, until recently I noticed a great blog post from Ingo Sauerzapf which piqued my interest. The blog mentioned that Holger Koser had created a HANA DB Client for Node making life extremely easy to connect to HANA directly from Node. I thought it would be good share the small project I developed using Node.js and this new client with the community in the hopes that others will share their experiences with the technology.This blog is not necessarily an introduction to Node.js development as there are some nice tutorials and examples out there from Tobias Hoffman and Alessandro Spadoni. The blog is intended to cover a small app developed in Node.js and shows the development process behind it, taking it from conception through to reality. I encourage you to download a few of these components, and also the example out. This app, similar to another app I developed called Metric² (which you can read about here), it is a web based widget showing some friendly KPI’s on the performance of your HANA Instance. The app gets streaming data from our HANA backend displaying this in a friendly, simple dashboard. The dashboard is designed to be shown on a large format monitor in a Ops or IT center and can also very easily be modified to show any KPI’s relevant to your business or needs.

Requirements:

SAP HANA Instance (e.g. AWS Developer Image)

Node.js installed (this does not need to be on the HANA box but same network with access to the HANA port – normally 30015).

Node Dependencies

We will also use a couple of helpful dependencies from the node community including Socket.io, Express and obviously hdb. Installing these packages is as simple as running “npm install hdb”. Once you have the dependencies installed we can start creating our app.

https://nodei.co/npm/hdb.png?compact=true

App Design

For me, I always start mocking up in my favorite Image IDE (Photoshop), I used this image as inspiration for my app. I liked the simplicity, clean layout with focus on the numbers at the bottom. In our case, we will add a small chart in the center, giving us a basic visual representation of the numbers being displayed:

 

Photoshop HTML Mockup
Download the PSD

App Development

Index.html

In this case I decided to use Twitter Bootstrap to help with some of the layout/formatting of the page as well as some mundane tasks like Modal popups. From a coding perspective I started out developing the Single paged “index.html” file, doing my best to stick with my mockup which I previously created. I was sure to “id” all of my elements on this page as we will be updating these values from our node.js backend. This aspect of node development is strictly “traditional” web based development. No need to work with Node or any server for that matter. Once you have your page rendering the way you want, we can move on.

<html>

<head>

<meta http-equiv="X-UA-Compatible" content="IE=edge" />

<meta charset="UTF-8"/>

<meta name="viewport" content="width=device-width, maximum-scale=1.0" />

<title>Metric&#178;</title>

<!-- jQuery -->

<script src="https://code.jquery.com/jquery.js"></script>

<!-- Socket.IO -->

<script src='/socket.io/socket.io.js'></script>

<!-- Peity - a lightweight sparkline package for jQuery -->

<script src='js/jquery.peity.min.js'></script>

<!-- Client side code needed -->

<script src='js/client.js'></script>

<!-- Bootstrap CSS -->

<link rel="stylesheet" href="//netdna.bootstrapcdn.com/bootstrap/3.0.2/css/bootstrap.min.css">

<!-- Latest compiled and minified JavaScript -->

<script src="//netdna.bootstrapcdn.com/bootstrap/3.0.2/js/bootstrap.min.js"></script>

<!-- CSS -->

<link rel="stylesheet" href="css/style.css">

</head>

<body>

<div id="top">

<div>

<div>

<div>

<table>

<tr>

<td rowspan="2" style="text-align: center; width: 10%;" >

<img id="statusicon" src="img/OKIcon.png"/>

</td>

<td style="vertical-align: top;">

<h1><span id="info-name">SAP HANA Instance</span>

<button data-toggle="modal" data-target="#myModal">

<span></span>

</button>

</h1>

</td>

</tr>

<tr>

<td style="padding-top: 20px;">

<span style="margin-left: 0px;" /></span><span id="info-alerts">0</span> Alerts

<span></span><span id="info-version">1.0</span>

<span></span><span id="info-detail">Server Location</span>

</td>

</tr>

</table>

</div>

<div>

<span>0</span>

</div>

<div>

<table>

<tr>

<td id="infoUSERS" onClick="setChart('USERS');">

<!-- The ID of each of our <SPAN> tags is important for updating the data being returned from the server -->

<span id="info-users">0</span><br />

<span>Users</span>

</td>

<td> </td>

<td id="infoDISK" onClick="setChart('DISK');">

<span id="info-disk">0</span> <sup>GB</sup><br />

<span>Free Disk</span>

</td>

<td id="infoMEM" onClick="setChart('MEM');">

<span id="info-mem">0</span> <sup>GB</sup><br />

<span>Free Memory</span>

</td>

<td id="infoCPU" onClick="setChart('CPU');">

<span id="info-cpu">0</span> <sup>%</sup><br />

<span>CPU</span>

</td>

</tr>

</table>

</div> <!-- /.containerfooter -->

</div> <!-- /.container -->

</div> <!-- /.centercontainer -->

</div> <!-- /.top -->

<!-- Modal -->

<div id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">

<div>

<div>

<div>

<button type="button" data-dismiss="modal" aria-hidden="true">&times;</button>

<h4 id="myModalLabel">Settings</h4>

</div>

<div>

<form id="modalbox" role="form">

<div>

<label for="servername">Name</label>

<input type="text" id="servername" placeholder="Enter a reference server name">

</div>

<div>

<label for="serverdetail">Location</label>

<input type="text" id="serverdetail" placeholder="Description, Location or Other Information">

</div>

<div>

<label for="bg">Background</label><br />

<label>

<input type="radio" name="bg" value="../img/bg1.jpg" checked> Background 1

</label>

<label>

<input type="radio" name="bg" value="../img/bg2.jpg"> Background 2

</label>

<label>

<input type="radio" name="bg" value="../img/bg3.jpg"> Background 3

</label>

<label>

<input type="radio" name="bg" value="none;"> None

</label>

</div>

<div>

<label for="colorscheme">Color Scheme</label><br />

<label>

<input type="radio" name="colorscheme" value="Dark" checked> Dark

</label>

<label>

<input type="radio" name="colorscheme" value="Light"> Light

</label>

<label>

<input type="radio" name="colorscheme" value="Fiori"> Fiori

</label>

</div>

<div>

<button type="button" data-dismiss="modal">Close</button>

<button type="button" id="modalSave" onClick="saveSettings();">Save changes</button>

</div>

</div><!-- /.modal-content -->

</div><!-- /.modal-dialog -->

</div><!-- /.modal -->

</body>

</html>

App.js

Next we develop the app.js file which is the brains of our operation. This file is firstly going to act as our web server for our web site, and secondly provide the data from our HANA server to the web page, pushing the data via web sockets.

Below is the app.js code, here you can see how we process each request based on the type and subsequently respond with the requested data. You can also see how simple it is to call the HANA DB and respond with the results.

 

var express = require('express'),

http = require('http'),

hdb = require('hdb');

try {

var app = express();

var server = http.createServer(app);

server.listen(3000);

var io = require('socket.io').listen(server);

app.use(express.static(__dirname + '/'));

// development only

if ('development' == app.get('env')) {

app.use(express.errorHandler());

}

var client = hdb.createClient({

host     : 'Your HANA IP Address or DNS Name',

port     : 30015,

user     : 'username',

password : 'password'

});

client.connect(function (err) {

if (err) {

console.error('Connect Error:', err);

} else {

console.log('Connected to server');

}

});

process.on('uncaughtException', function (err) {

console.log('Caught exception: ' + err);

});

strContent = '';

io.sockets.on('connection', function (socket) {

socket.on('request', function (data) {

// Handle Service Requests

switch (data.service) {

case 'CPU':

client.exec("SELECT ABS(SUM(PROCESS_CPU)) as CPU from SYS.M_SERVICE_STATISTICS", function(err, rows) {

if (err) {

console.error('Error:', err);

} else {

socket.emit('response', {service: 'CPU', response: rows[0].CPU});

}

});

break;

case 'MEM':

client.exec("select TO_VARCHAR(ROUND((FREE_PHYSICAL_MEMORY) /1024/1024/1024, 2)) AS FREEMEM

from PUBLIC.M_HOST_RESOURCE_UTILIZATION", function(err, rows) {

if (err) {

console.error('Error:', err);

} else {

socket.emit('response', {service: data.service, response: rows[0].FREEMEM});

}

});

break;

case 'INFO':

client.exec("SELECT VALUE FROM SYS.M_SYSTEM_OVERVIEW WHERE NAME = 'Version'", function(err, rows) {

if (err) {

console.error('Error:', err);

} else {

socket.emit('response', {service: data.service, response: rows[0].VALUE});

}

});

break;

case 'DISK':

client.exec("select TO_VARCHAR((ROUND(d.total_size/1024/1024/1024, 2) - ROUND(d.used_size/1024/1024/1024,2))) as FREESPACE

from ( ( m_volumes as v1 join M_VOLUME_SIZES as v2 on v1.volume_id = v2.volume_id ) right outer join m_disks as d on d.disk_id = v2.disk_id )

where d.usage_type = 'DATA' group by v1.host, d.usage_type, d.total_size,    d.device_id, d.path, d.used_size", function(err, rows) {

if (err) {

console.error('Error:', err);

} else {

socket.emit('response', {service: data.service, response: rows[0].FREESPACE});

}

});

break;

case 'USERS':

client.exec("SELECT COUNT(CONNECTION_ID) as STATUS FROM SYS.M_CONNECTIONS

WHERE CONNECTION_STATUS = 'RUNNING'", function(err, rows) {

if (err) {

console.error('Error:', err);

} else {

socket.emit('response', {service: data.service, response: rows[0].STATUS});

}

});

break;

case 'ALERTS':

client.exec("SELECT COUNT(ALERT_DETAILS) as ALERTCOUNT FROM _SYS_STATISTICS.STATISTICS_CURRENT_ALERTS", function(err, rows) {

if (err) {

console.error('Error:', err);

} else {

socket.emit('response', {service: data.service, response: rows[0].ALERTCOUNT});

}

});

break;

}

});

});

} catch(err) {

console.log(err);

}

 

The App does have a couple of different themes which will hopefully make it fit with your office decor

Below you can see a couple of images of the app running and showing the output. You can obviously very easily modify the code to show anything relevant to your business case as well. The Node-hdb package for node.js really makes developing HANA connected Node apps a breeze! Thanks Holger!

Fiori Styled
Light Theme
Image5.png
Dark Theme
Image6.png
As usual – please feel free to comment on your experience with Node.js and if you feel like this type of technology is a good fit in the enterprise? Do you have any suggestions on what I could have done differently?You can download the app here: Metric² for Node
Credits: The Bokeh backgrounds are from devientArt

Image+[20]

Microsoft Power View and Netweaver Gateway – A easy com*bi*nation

I was recently at a customer site where they were using a Microsoft Excel based BI Tool, Power View for in house adhoc BI reporting and I was quite impressed. The ease, simplicity and functionality was quite refreshing and I started to do some additional digging into the application and its functionality. My first and unfortunately, my biggest shock, came at the fact it required Silverlight. Yes, you read that right … this means that the reports/functionality is not available on your favorite mobile device. This did not deter me and I was interested on how this could apply to the SAP world from a desktop perspective and was pleasantly surprised when I started scratching the surface.

Firstly, a bit of an introduction: Power View is a relatively new product from the Microsoft BI Stack and was recently included in Excel 2013 (Professional Plus editions). It was previously available in Sharepoint Server 2010 and still is included with SP 2013. You can use Power View directly from a Excel workbook, within Sharepoint, as well as deploy the Power View Excel sheet to a Sharepoint 2013 Server. Power View is an interactive data exploration, visualization, and presentation experience (and can be compared to SAP Lumira). When I started to do some digging I found that this blog around SAP MII tool also uses it as part of its data analysis options. Power View has a variety of functions which are covered on this site and I will not repeat.

A powerful feature of Excel, which is often overlooked, is the fact that you can use OData as a data source and this allows us to use data from REST based services built using SAP Netweaver Gateway. Since Gateway can subsequently expose data from sources like BW, HANA, CRM, ERP and so on, it provides a great reusable foundation for casual BI, mobile devices or web apps (like Fiori) for lightweight consumption. SAP Lumira also offers OData support with its recent v1 SP11 service pack which now makes it a bit more appealing for customers who have implemented Gateway.

What does this mean for SAP customers? With Excel being widely adopted, why not reuse your existing investments and services from Netweaver Gateway to power some casual adhoc BI tools for your business?

Here is a quick walk through which can get you started on developing Power Views for your business (please keep in mind that creating Power View sheets from Excel and Sharepoint is slightly different, in this case we will use Excel). If you don’t have Excel 2013 Pro Plus, you can download a copy here: http://technet.microsoft.com/en-us/evalcenter/jj192782.aspx

Data Source: As mentioned previously we can use a OData service as a source for our Power View, in this case we will use a great new service recently developed by Andre Fischer and available on the Gateway demo servers. If you would like to develop your own Power View Sheets based on these demo services, you will need an account. (Its quick and easy!).

So lets jump in … Open Excel 2013 and click on the “Data tab”, “From Other Sources” and “From OData Data feed” and enter the Gateway service details along with your username and password. In this example we will be using the Sales order collection service : https://sapes1.sapdevcenter.com/sap/opu/odata/sap/ZGWSAMPLE_SRV/SalesOrderCollection

Image [2].png

 

Image [3].png

Click Finish

Image [4].png

Once completed, you should be presented with the Power View Sheet and a basic layout, along with your data source field list on the right side:

Image [5].png

You can kick things off by selecting a few columns from the sales order collections table which will show up on your report in a tabular format.

Image [7].png

First thing we will do is go across to our data set (Our power PowerPivot sheet) and add a SUM function to our Gross Sales Column. Click on PowerPivot -> Manage. Select the GrossAmount Column and be sure to set its type to decimal. (You should do this for all your “numeric” based columns). Then at the bottom in the Calculated field, select the first row and “AutoSum”

Image [6].png

 

If you save and close the Power Pivot, you should have something like this showing:

Image [7].png

Next we will clone this table and use it for a Pie Chart, select the table, copy and paste it. Then with the new table selected, click the “Design” tab -> “Other Chart” and Pie.

Image [8].png

Once again we will take our original table and clone it. We will use this to display a line chart of the average order size by using a Line Chart. Resize your charts appropriately, and select the line chart. Also modify the Value to be Average, rather than the SUM.:

Image [9].png

As I am sure you have noticed by now, is that filtering on charts is easy and they are all linked. Selecting a specific customer will actually change all three of our charts appropriately which is very slick.

Image [10].png

Progress

Next we will be adding a KPI, this can be done by heading back to our PowerPivot sheet, selecting a column and selecting AutoSum -> Count.

Image [11].png

This will add an additional calculated column to your table list:

Image [12].png

Then select PowerPivot -> KPIs -> New KPI

Image [13].png

Your KPI base field should be selected and you can define a Absolute Value (“Goal”) of 250 and any other options you may prefer. Select OK.

Image [14].png

This will convert your calculated column to a KPI, and selecting one of the fields will add it to your Sheet.

Image [16].png

Lastly what we will be doing is adding a few filters to our view, this way we can filter all charts/KPIs with a single selection. You can do this by selecting “View” on the filter sidebar, then dragging and dropping your needed columns.

Image [17].png

In the end, it is a fairly simple process to build a interactive and useful dashboard … here is my finished Power View …

Image [20].png           Image [18].png

A couple other cool things to check out …

-> The Map “Chart” …
-> Drill down features … (hint – you need to create a Hierarchy)

To Do ….

-> Change your column names from PowerPivot as soon as you have your data set created
-> Add Currency prefixes to the columns/dataset
-> Remember that the dataset is stored in your file.

Here is the Power View Chart … download it, enter your credentials under the PowerPivot “Existing Connections” -> Advanced and you should be able to use it as a template …

Image [19].png

Here are a couple more helpful links to get you started:

http://office.microsoft.com/en-us/excel-help/create-a-power-view-sheet-in-excel-2013-HA102899553.aspx

http://www.slideshare.net/idigdata/excel-2013-power-view-and-powerpivot-basics

http://download.microsoft.com/download/A/9/9/A9971C42-ECB6-4059-BA60-7E7B5B98BD40/Microsoft_BI_Interoperability_with_SAP_White_Paper.pdf

http://www.jenunderwood.com/presentations/PowerViewPowerPivotBasics2013SP1.pdf

If you made it this and are still engaged I am curious to know if you are using Excel Power View in your organization? What do you think of it? Would you consider using it in the future?