Start building a HANA Monitoring Dashboard
With SPS7 we finally had our first taste of a HANA Monitoring Dashboard, accessed via HANA studio:
For more details about HANA memory usage including the above dashboard then see https://www.saphana.com/docs/DOC-2299
I’d personally like to see an open source example of this using the XS engine, with a broader range of monitoring SQL statements.
It might then be something people could then also use as inspiration for building customer specific dashboards.
If you’ve already seen a more comprehensive example or are aware that this is in the pipeline in a subsequent revision then please add a comment below and I will update this blog accordingly.
So in my ignorance I thought I could start the ball rolling by creating a very simple dashboard, with a single KPI.
It’s pretty straightforward to use HANA XS to create Pie charts, Tables and Graphs using SAPUI5 objects (see SAPUI5 SDK – Demo Kit)
but every good (and bad) dashboard always seems to use a ‘Gauge’ for something, so I thought that would be a good place to start.
Using D3 to build gauges has also recently been explored by Abesh Bhattacharjee in the following SCN blog in Gauges in SAPUI5
The example code to create a Google style Gauge in Javascript, using D3 was first presented here:
Tomer Doron’s Technology Blog: google style gauges using d3.js
I’ve used an unmodified copy of his gauge.js file in this HANA XS example.
To create my first HANA monitoring KPI I’ve used a D3 gauge to graphically represent Used memory (Resident memory as a percentage of total memory to be more precise):
This KPI is currently set to refresh every 5 seconds.
The corresponding SQL which generates this KPI is:
select round((sum(USED_PHYSICAL_MEMORY) + sum(FREE_PHYSICAL_MEMORY))/1024/1024/1024, 2) as “Physical Memory GB”, round(sum(USED_PHYSICAL_MEMORY)/1024/1024/1024, 2) as “Resident GB” from PUBLIC.M_HOST_RESOURCE_UTILIZATION
To create this KPI in your environment then you need 3 files:
1) gauge.js (copy code from here google style gauges using javascript d3.js)
2) Serverside Javscript ( XSJS)to supply current statistics out of HANA.
PerformanceStats.xsjs |
---|
function getUsed() { var results = []; function formatResults(rs) { return { “resident” : rs.getDecimal(2), “total” : rs.getDecimal(1) }; } try { var query = ‘select round((sum(USED_PHYSICAL_MEMORY) + sum(FREE_PHYSICAL_MEMORY))/1024/1024/1024, 2) as “Physical Memory GB”, round(sum(USED_PHYSICAL_MEMORY)/1024/1024/1024, 2) as “Resident GB” from PUBLIC.M_HOST_RESOURCE_UTILIZATION’; var conn = $.db.getConnection(); var pstmt = conn.prepareStatement(query); var rs = pstmt.executeQuery(); while (rs.next()) { results.push(formatResults(rs)); } rs.close(); pstmt.close(); } catch (e) { $.response.status = $.net.http.INTERNAL_SERVER_ERROR; $.response.setBody(e.message); return {}; } return results; } function getStats() { var body = ”; body = JSON.stringify({ “usedMemory” : getUsed() }); //setTimeout(vDelay(),1000); $.response.contentType = ‘application/json; charset=UTF-8’; $.response.setBody(body); $.response.status = $.net.http.OK; } getStats(); |
3) Create the HTML page in HANA XS
HanaMonitoringDashboard.html |
---|
https://bl.ocks.org/tomerd/1499279 –>
If there is interest in the SCN community I’m happy to expand this example to include multiple KPI’s with different chart types (e.g Gauge, traffic light, line, bar, pie) To make this more interactive I welcome your feedback. If you’d like me to add more HANA monitoring KPI’s then please add a suggestion below, including some or all of the following: – KPI Name – KPI description – Chart type (Gauge, traffic light, line, bar, pie, etc) – SQL statemenent – Javascript Code suggestions If your comment/suggestion gets enough likes from other readers then I’ll endeavor to add it to this example. Please note that any KPI suggestions should focus on core HANA system tables & views. New NetWeaver Information at SAP.comVery Helpfull
|