Table of Contents
Greetings experts,
My name is Ilias Katsabalos and I completed my internship in SAP Hellas as a HANA Developer. During the last month, I was assigned with an IoT project using a Raspberry Pi. After the development, I decided to share the documentation with the community, since it describes an end to end scenario and includes all the basic steps for leveraging the IoT services. I really hope that you will find it interesting and please, feel free to share your thoughts on this.
In this documentation, we will describe the processes of creating an application on SAP HANA Cloud Platform (HCP) that leverages the Internet of Things Services embedded within the HCP. The device we will use is the Raspberry Pi, which captures the data via a web camera, sends them to the HCP and then they are rendered in a SAPUI5.
This documentation is separated under 4 components.
Prerequisites:
Disclaimer: This documentation has an educational purpose, so people can get familiar with the IoT services and the Raspberry Pi. It also presents a small spectrum of the native capabilities offered by HANA. You may need to change some components of the application so it can be used in a productive environment.
The application serves the purpose of a real time recommendation system. The Raspberry Pi is connected with a simple web camera (preferably with an autofocus feature) that reads the barcode of a product. Using an HTTP Post request, the Raspberry Pi sends the barcode of that product to the HCP using the IoT services.
In the meantime, in our schema, we have already executed the Apriori algorithm (details chapter 3.5). This algorithm is responsible for mining association rules according to our sales transactions. For example, the association rule [bread -> jam] translates to "people who buy bread (pre rule), usually buy jam (post rule)".
By joining the barcodes of the products that we scan on our raspberry pi, with the apriori prerules in our schema, we can retrieve the post rules which are basically recommendations based on the items we have just scanned. These postrules are rendered in SAPUI5, using a wordcloud. Below you will find the dataflow diagram of the application.
Let us go through an example so we can make it more transparent. Let's say that a consumer scans the product with the barcode "100". The information of the barcode is sent to the HCP via HTTP POST Request. Because we have already executed the Apriori algorithm, our database includes the derived association rules. As a result, the consumer is presented with the products "101" and "107", which are the the recommendations for the prerule "100". Those recommendations are rendered using SAPUI5.
In this section we will describe all the steps for configuring our MDC schema according to the application.
For this section, you will be able to find lots of helpful tutorials in SAP HANA Academy and IotStarterPack on GitHub. We will not cover this process here, since SAP HANA Academy includes all the steps for enabling the IoT services, in detail.
What we need to do now, is to create our own Device Types, Devices and Message Types.
Now we have successfully created our Device Types, Devices and Message Types.
In this point we need to make another quick test so we make sure that we can send our newly created Message Type from our newly created Device. Go to the MMS Cockpit and choose “Send and Receive Messages through HTTP”.
{"mode":"sync","messageType":"<yourMessageTypeID>","messages":[{"item":"100"}]}
(If you look carefully the json, inside the messages, there is the "item" field with the value "100". The value "100" is hardcoded and it will be replaced later with the actual barcode of the product that the consumer scans)
Now we need to insert some data in our schema, so we can execute the Apriori algorithm.
CREATE COLUMN TABLE TRANSACTIONS (
TRANSID VARCHAR(100),
ITEM VARCHAR(100),
PRIMARY KEY (TRANSID, ITEM) );
After a while, the dataset will be successfully imported in your table.
Open an SQL console and paste the code below:
SET SCHEMA "SYSTEM";
DROP TYPE PAL_APRIORI_DATA_T;
CREATE TYPE PAL_APRIORI_DATA_T AS TABLE(
"TRANSID" VARCHAR(100),
"ITEM" VARCHAR(100)
);
DROP TYPE PAL_APRIORI_RESULT_T;
CREATE TYPE PAL_APRIORI_RESULT_T AS TABLE(
"PRERULE" VARCHAR(500),
"POSTRULE" VARCHAR(500),
"SUPPORT" DOUBLE,
"CONFIDENCE" DOUBLE,
"LIFT" DOUBLE
);
DROP TYPE PAL_APRIORI_PMMLMODEL_T;
CREATE TYPE PAL_APRIORI_PMMLMODEL_T AS TABLE(
"ID" INTEGER,
"PMMLMODEL" VARCHAR(5000)
);
DROP TYPE PAL_CONTROL_T;
CREATE TYPE PAL_CONTROL_T AS TABLE(
"NAME" VARCHAR(100),
"INTARGS" INTEGER,
"DOUBLEARGS" DOUBLE,
"STRINGARGS" VARCHAR (100)
);
DROP TABLE PAL_APRIORI_PDATA_TBL;
CREATE COLUMN TABLE PAL_APRIORI_PDATA_TBL(
"POSITION" INT,
"SCHEMA_NAME" NVARCHAR(256),
"TYPE_NAME" NVARCHAR(256),
"PARAMETER_TYPE" VARCHAR(7)
);
INSERT INTO PAL_APRIORI_PDATA_TBL VALUES (1, 'SYSTEM', 'PAL_APRIORI_DATA_T', 'IN');
INSERT INTO PAL_APRIORI_PDATA_TBL VALUES (2, 'SYSTEM', 'PAL_CONTROL_T', 'IN');
INSERT INTO PAL_APRIORI_PDATA_TBL VALUES (3, 'SYSTEM', 'PAL_APRIORI_RESULT_T', 'OUT');
INSERT INTO PAL_APRIORI_PDATA_TBL VALUES (4, 'SYSTEM', 'PAL_APRIORI_PMMLMODEL_T', 'OUT');
CALL "SYS".AFLLANG_WRAPPER_PROCEDURE_DROP('SYSTEM', 'PAL_APRIORI_RULE_PROC');
CALL "SYS".AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'APRIORIRULE', 'SYSTEM', 'PAL_APRIORI_RULE_PROC', PAL_APRIORI_PDATA_TBL);
DROP TABLE PAL_APRIORI_TRANS_TBL;
CREATE COLUMN TABLE PAL_APRIORI_TRANS_TBL LIKE PAL_APRIORI_DATA_T;
INSERT INTO PAL_APRIORI_TRANS_TBL (SELECT * FROM "SYSTEM"."TRANSACTIONS");
DROP TABLE #PAL_CONTROL_TBL;
CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_CONTROL_TBL(
"NAME" VARCHAR(100),
"INTARGS" INTEGER,
"DOUBLEARGS" DOUBLE,
"STRINGARGS" VARCHAR (100)
);
INSERT INTO #PAL_CONTROL_TBL VALUES ('THREAD_NUMBER', 2, null, null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('MIN_SUPPORT', null, 0.01, null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('MIN_CONFIDENCE', null, 0.03, null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('MIN_LIFT', null, 1.01, null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('MAX_CONSEQUENT', 1, null, null);
DROP TABLE PAL_APRIORI_RESULT_TBL;
CREATE COLUMN TABLE PAL_APRIORI_RESULT_TBL LIKE PAL_APRIORI_RESULT_T;
DROP TABLE PAL_APRIORI_PMMLMODEL_TBL;
CREATE COLUMN TABLE PAL_APRIORI_PMMLMODEL_TBL LIKE PAL_APRIORI_PMMLMODEL_T;
CALL "SYSTEM".PAL_APRIORI_RULE_PROC(PAL_APRIORI_TRANS_TBL, #PAL_CONTROL_TBL, PAL_APRIORI_RESULT_TBL, PAL_APRIORI_PMMLMODEL_TBL) WITH overview;
SELECT * FROM PAL_APRIORI_RESULT_TBL;
The results of the algorithm should look like this:
Let us explain the result of the algorithm. The PRERULE and POSTRULE fields indicate that when people buy the product 11, usually buy the product 45 (first row). But how often? The SUPPORT field shows the frequency of the rule found in our database. The first rule (11->45) occurs in 3,1% of the transactions. (The numbers are not optimistic, but fit to the purpose of the demo). The CONFIDENCE field shows the frequency of the rule found in those transactions that contain the item 11. Using some mathematics we can represent confidence as:
Confidence (11U45) = support(11U45) / support(11)
The lift metric shows us how strong is the association between item 11 and 45. The bigger the lift, the stronger the association. It can be represented as follows:
Lift (11U45) = support(11U45) / support(11) * support(45)
The next thing that we need to consider is how to populate a table that contains all the postrules that will be sent to our SAPUI5 page. First of all, create that table using the code below.
CREATE COLUMN TABLE ITEM_SCANNED (
DEVICE VARCHAR(255),
ITEM_SCANNED VARCHAR(255),
POSTRULE VARCHAR(255),
PRIMARY KEY (DEVICE, ITEM_SCANNED, POSTRULE)
);
What we need is a trigger that is activated whenever there is an insert in our Message Type table. This insert means that a product was scanned. The trigger will take the result of the join between the PAL_APRIORI_RESULT_TBL and the Message Type Table and insert into the table that we just created. Create that trigger using the following code: Don't forget to change <your message type table> in the code.
CREATE TRIGGER "SYSTEM"."INSERT_TO_SCANNED" AFTER INSERT ON "SYSTEM"."<your message type table>" REFERENCING NEW ROW NEWROW FOR EACH ROW
BEGIN INSERT
INTO "SYSTEM"."ITEMS_SCANNED" ( SELECT
T1.G_DEVICE AS DEVICE,
T1.C_ITEM AS ITEM_SCANNED,
T0.POSTRULE AS POSTRULE
FROM "SYSTEM"."PAL_APRIORI_RESULT_TBL" T0
INNER JOIN ( SELECT
"G_DEVICE" ,
"C_ITEM"
FROM "SYSTEM"."<your message type table>"
WHERE "G_CREATED" = (SELECT
MAX("G_CREATED")
FROM "SYSTEM"."<your message type table>") ) AS T1 ON T0.PRERULE = T1.C_ITEM)
;
END
;
For the barcode recognition we will use the ZBar software, which is an open source software. The code used in this section is based on Saurabh Kumar's technology learnings blog: Scanning barcodes, using Raspberry Pi
sudo raspi-config
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install python-devsudo apt-get install python-pip
sudo pip install pillow
sudo apt-get install python-httplib2
sudo apt-get install fswebcam
sudo apt-get install python-zbar
sudo apt-get install libzbar-dev
python setup.py install –user
#!/usr/bin/python
import urllib3
import certifi
from sys import argv
import zbar
http = urllib3.PoolManager(
cert_reqs='CERT_REQUIRED', # Force certificate check.
ca_certs=certifi.where(), # Path to the Certifi bundle.
)
#!http = urllib3.proxy_from_url('http://proxy:port')
url = 'https://iotmmsYOURUSERtrial.hanatrial.ondemand.com/com.sap.iotservices.mms/v1/api/http/data/YOUR DEVICE ID'
headers = urllib3.util.make_headers()
headers['Authorization'] = 'Bearer ' + 'YOUR OAUTH TOKEN'
headers['Content-Type'] = 'application/json;charset=utf-8'
proc = zbar.Processor()
proc.parse_config('enable')
device = '/dev/video0'
if len(argv) > 1:
device = argv[1]
proc.init(device)
# setup a callback
def my_handler(proc, image, closure):
# extract results
for symbol in image.symbols:
# do something useful with results
print 'decoded', symbol.type, 'symbol', '"%s"' % symbol.data
body='{"mode":"async", "messageType":"YOURMESAGGETYPE", "messages":[{"item":"'+ symbol.data +'"}]}'
try:
r = http.urlopen('POST', url, body=body, headers=headers)
print(r.status)
print(r.data)
except urllib3.exceptions.SSLError as e:
print e
proc.set_data_handler(my_handler)
# enable the preview window
proc.visible = True
# initiate scanning
proc.active = True
try:
# keep scanning until user provides key/mouse input
proc.user_wait()
except zbar.WindowClosed, e:
pass
python processor.py
Now, you are ready to begin scanning barcodes.
If your camera has a slow response, execute the following:
v4l2-ctl -v width=800,height=480,pixelformat=H264
If your camera does not have an autofocus feature, you can add a magnifier glass very close to your camera, and the focus distance will change. The barcodes will become clearer.
For the visualization of the post rules, that are actually our recommendations based upon the products we scanned, we will use the famous word cloud, utilizing the D3 Library. A word cloud is a canvas of words. According to each word’s frequency in a text, the font gets larger. Relatively, in our example, the more prerules pointing the same post rule, the bigger the font.
{
"exposed" : true,
"authentication" :
{
"method": "Form"
},
"cache_control" : "must-revalidate",
"cors" :
{
"enabled" : false
},
"enable_etags" : false,
"force_ssl" : false,
"prevent_xsrf" : true
}
var device = $.request.parameters.get('device');
var select_all_post_rules = "SELECT POSTRULE, COUNT(POSTRULE) FROM SYSTEM.ITEMS_SCANNED WHERE DEVICE = ? GROUP BY DEVICE, POSTRULE ORDER BY 2 DESC";
// if you want the products already scanned not to be shown, use the query below
//var select_all_post_rules = "SELECT T0.POSTRULE, COUNT(T0.POSTRULE) AS COUNT " +
// "FROM SYSTEM.ITEMS_SCANNED T0 " +
// "LEFT JOIN SYSTEM.ITEMS_SCANNED T1 ON T0.POSTRULE = T1.ITEM_SCANNED " +
// "WHERE T1.ITEM_SCANNED is null AND T0.DEVICE = ? " +
// "GROUP BY T0.POSTRULE ORDER BY COUNT DESC";
function close(closables) {
var closable;
var i;
for (i = 0; i < closables.length; i++) {
closable = closables[i];
if(closable) {
closable.close();
}
}
}
function getPostRules(){
var PostRulesList = [];
var connection = $.db.getConnection();
var statement = null;
var resultSet = null;
try{
statement = connection.prepareStatement(select_all_post_rules);
statement.setString(1,device);
resultSet = statement.executeQuery();
var PostRule;
while (resultSet.next()) {
PostRule = {};
//PostRule.device = resultSet.getString(1);
PostRule.postrule = resultSet.getString(1);
PostRule.count = resultSet.getInteger(2);
PostRulesList.push(PostRule);
}
} finally {
close([resultSet, statement, connection]);
}
return PostRulesList;
}
function doGet() {
try{
$.response.contentType = "application/json";
$.response.setBody(JSON.stringify(getPostRules()));
}
catch(err){
$.response.contentType = "text/plain";
$.response.setBody("Error while executing query: [" + err.message + "]");
$.response.returnCode = 200;
}
}
doGet();
For the D3 views, we used an excellent library made by Jason Davies.
var device = jQuery.sap.getUriParameters().get("device");
var data;
d3.json("https://<path to xsjs file>/getPostRules.xsjs?device=" + device, function(error, json) {
if (error) return console.warn(error);
data = json;
var width = 750;
var height = 350;
var fill = d3.scale.category20();
d3.layout.cloud().size([width, height])
.words(data.map(function(d){
return {text: d.postrule, size: d.count * 15}
}))
.rotate(function() { return 0; })
.font("Impact")
.padding(1)
.fontSize(function(d) { return d.size; })
.on("end", draw)
.start();
function draw(words) {
d3.select("#word_cloud").append("svg")
.attr("width", width)
.attr("height", height)
.append("g")
.attr("transform", "translate("+width/2+ "," + height/2+")")
.selectAll("text")
.data(words)
.enter().append("text")
.style("font-size", function(d) { return d.size + "px"; })
.style("font-family", "Impact")
.style("fill", function(d, i) { return fill(i); })
.attr("text-anchor", "middle")
.attr("transform", function(d, i) {
if(i==0){
return "translate(" + [0, 0] + ")rotate(" + d.rotate + ")";
}else{
return "translate(" + [d.x, d.y] + ")rotate(" + d.rotate + ")";
}
})
.text(function(d) { return d.text; });
}
})
sap.ui.jsview("views.PostRulesGraph", {
/** Specifies the Controller belonging to this View.
* In the case that it is not implemented, or that "null" is returned, this View does not have a Controller.
* @memberOf views.customerGraph
*/
getControllerName : function() {
return null;
},
/** Is initially called once after the Controller has been instantiated. It is the place where the UI is constructed.
* Since the Controller is given to this method, its event handlers can be attached right away.
* @memberOf views.customerGraph
*/
createContent : function(oController) {
var device = jQuery.sap.getUriParameters().get("device");
var topSuggestionsBar = new sap.viz.ui5.Bar("topSuggestions", {
width : "100%",
height : "50%",
xAxis: {
title: { visible: true, text : "Count" }
},
title : {
visible : true,
text : 'Top Suggestions'
}
,
interaction: new sap.viz.ui5.types.controller.Interaction({
selectability: new sap.viz.ui5.types.controller.Interaction_selectability({
mode: sap.viz.ui5.types.controller.Interaction_selectability_mode.single})
}),
dataset : topSuggestionDataset = new sap.viz.ui5.data.FlattenedDataset({
// a Bar Chart requires exactly one dimension (x-axis)
dimensions : [ {
axis : 1, // must be one for the x-axis, 2 for y-axis
name : 'PostRule',
value : "{postrule}"
}],
// it can show multiple measures, each results in a new set of bars
// in a new color
measures : [
{
name : 'Count', // 'name' is used as label in the Legend
value : '{count}' // 'value' defines the binding for the
},
],
// 'data' is used to bind the whole data collection that is to be
// displayed in the chart
data : {
path : "/"
}
})
});
var PRModel = new sap.ui.model.json.JSONModel();
PRModel.refresh(true);
PRModel.loadData("https://iliasp1942081284trial.hanatrial.ondemand.com/public/ilias/RaspberryPi/getPostRules.xsjs?device=" + device);
topSuggestionsBar.setModel(PRModel);
return topSuggestionsBar;
}
});
sap.ui.jsview("views.panel", {
/** Specifies the Controller belonging to this View.
* In the case that it is not implemented, or that "null" is returned, this View does not have a Controller.
* @memberOf views.customerGraph
*/
getControllerName : function() {
return null;
},
/** Is initially called once after the Controller has been instantiated. It is the place where the UI is constructed.
* Since the Controller is given to this method, its event handlers can be attached right away.
* @memberOf views.customerGraph
*/
createContent : function(oController) {
var oPanel = new sap.ui.commons.Panel({showCollapseIcon: false});
oPanel.setTitle(new sap.ui.core.Title({text: "Contact Data"}));
return oPanel;
}
}
);
Create a folder in your project called “images” and paste on that folder this image. Later we add this image to the "panel.view.js" file.
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<script src="/sap/ui5/1/resources/sap-ui-core.js" id="sap-ui-bootstrap"
data-sap-ui-libs="sap.ui.ux3,sap.ui.commons,sap.ui.table,sap.viz"
data-sap-ui-theme="sap_bluecrystal">
</script>
<script src="/sap/ui5/1/resources/sap/ui/thirdparty/d3.js"></script>
<script src="d3Views/d3.layout.cloud.js"></script>
<script>
sap.ui.localResources("views");
var view = sap.ui.view({id:"PRGraph", viewName:"views.PostRulesGraph", type:sap.ui.core.mvc.ViewType.JS});
view.placeAt("contentGraph");
var view2 = sap.ui.view({id:"Panel", viewName:"views.panel", type:sap.ui.core.mvc.ViewType.JS});
view.placeAt("contentGraph");
</script>
<script>
sap.ui.getCore().attachInit(function () {
var oPanel = new sap.ui.commons.Panel({showCollapseIcon: false});
oPanel.setTitle(new sap.ui.core.Title({text: "WordCloud", icon: "images/saplogo.png"}));
oPanel.placeAt("panel")
});
</script>
</head>
<body class="sapUiBody" role="application">
<script src="d3Views/wordcloud.js"></script>
<div id="panel"></div>
<div id="word_cloud" align="center" style="border:1px solid black" ></div>
<div id="contentGraph" style="border:1px solid black"></div>
</body>
</html>
After the activation of the project, call your html file and don’t forget to use the ‘device’ parameter at the end of the URL. Note that you should already have some data in the ITEM_SCANNED table in order to visualize the post rules. If everything described in this documentation is applied, you should have something like this:
function deleteDataFromTable(){
var device = $.request.parameters.get('device');
var conn = $.db.getConnection();
var pstmt;
var query;
try {
query = 'DELETE FROM "SYSTEM"."ITEMS_SCANNED" WHERE DEVICE = ?';
pstmt = conn.prepareStatement(query);
pstmt.setString(1, device);
pstmt.executeUpdate();
conn.commit();
pstmt.close();
conn.close();
} catch (e) {
$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
$.response.setBody(e.message);
return;
}
$.response.contentType = 'application/json';
$.response.setBody('Record deleted successfully');
$.response.status = $.net.http.OK;
}
deleteDataFromTable();
Go to http://barcode.tec-it.com/en, which is a barcode generator and create some barcodes, according to the prerules and postrules of the Apriori table. Print them, scan them and check your application from start to finish.
Hope you found it helpful and worthy of your time!
Ilias Katsabalos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
10 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 | |
2 | |
2 |