In recent times I have personally experienced a lot of change the way we used to search or reading a review on a product, company, movie, buying/selling real estate, finding out job vacancies. Somehow we are happy to depend more on twitter to get this information. But extracting/searching for the information from tweets is not very handy and very time consuming, considering the number of tweets raised 4500 per day to 5000 tweets per second from 2007 to 2014.

In this blog, I tried to get the tweet handlers information of head hunters tweeting about a specific job vacancy and responding to their exact tweet.


Summary:

  • Posting tweets to SAP HANA on a daily basis using a python script.
  • Filter/Changing the tweet data to structured data by using few SQL queries & Fuzzy search.
  • Stored the data in Fulltext Index tables so that I can differentiate the data
  • Using XSODATA of index tables in SAP Lumira for visualization
  • A python script to reply to specific tweets to the specific user.

 

Initially I wanted to build an application with selection parameters to search/filter the tweet data and to reply to the tweets using XSJS. But, I have to settle down with python due to trust store issues.

Let’s do it step-by-step.

 

1. Creating tables and services in HANA:


Created few tables using SQL Console and .xsodata


Sample Code:

  create column table "MOHAS97"."amohas97.session.data::fuzz"

(

IDNO nvarchar(60) primary key,

CNTR nvarchar(60),

THLR nvarchar(60),

CRTD nvarchar(60),

TTXT NVARCHAR(260) fuzzy search index on

  );


XSODATA:

service namespace "amohas97.services" {

"MOHAS97"."amohas97.session.data::fuzz" as "tweet"

"MOHAS97"."amohas97.session.data::twitter" as "twitter"

"MOHAS97"."$TA_TWTIDX" as "twitlds";

  "MOHAS97"."amohas97.session.data::twitfnl" as "twitfnl";

  "MOHAS97"."$TA_TWTFNL" as "twitfnlidx";

  "MOHAS97"."amohas97.session.data::twiturl" as "twiturl";

  "MOHAS97"."amohas97.session.data::twitloc" as "twitloc";         

                  }   

 

2. Posting the tweet data from twitter:


Twitter API has limitations, it cant fetch data older than a week. So I have executed python script in regular/irregular intervals.


Install TWYTHON for Python and get your twitters keys from dev.twitter.com


Following is the woking code to fetch the data from twitter and to post it in SAP HANA. I have used the words "SAP ABAP". API will fetch all the tweets which has "SAP" and "ABAP"


 

#! /usr/bin/python

 

import requests

import json

import csv

import sys

import xlrd

import os

#import urllib.request as urllib2

import codecs

from twython import Twython, TwythonError

import time

from datetime import datetime

from datetime import timedelta

import socket #import the socket module

 

 

appURL = 'http://va..........your server addd ..:8008/amohas97/session/services/pytxso.xsodata'

auth = 'your hana id','your password'

 

 

s = requests.Session()

s.headers.update({'Connection': 'keep-alive'})

headers = {"Content-type": 'application/json;charset=utf-8'}

r = s.get(url=appURL, headers=headers, auth=auth)

url = appURL + "/tweet"

 

 

# Requires Authentication as of Twitter API v1.1

 

 

twitter = Twython('8Jep7jyAstr8W8wxMekC3', 'ywvEJKc4TRnZcDHiHBP4jZmYH73DCEgf7UnLrlwprUwh7l', '273814468-7MUccHp07UiPvpL5o6ktIZkjdZg7YXjMTGH', 'iFMgreouGh6Hl18eGX3r99U3IjjaqXdMxp8B4yUN')

 

 

keywords = ['sap abap']

count=0

 

for page in range(0,1):

    search = twitter.search(q=keywords,

                count =35 ,include_retweets=False,  timeout=1500)

 

#count=35, it will read 35 tweets per request. max is 100

 

    tweets = search['statuses']

 

    for tweet in tweets:

      count+=1

      ts = datetime.strptime(tweet['created_at'],'%a %b %d %H:%M:%S +0000 %Y')

      ts1 = str(ts)

      data = '{"IDNO": " ' + tweet['id_str'] + ' ", "CNTR": " ' + str(count) + ' ", "THLR": " ' + tweet['user']['screen_name'] + ' ",  "CRTD": " ' + ts1[0:10] + ' ", "TTXT": " ' + str(tweet['text'].encode('utf-8')) + ' "}'

      r = s.post(url, data=data, headers=headers)

 

    print(count)

    last = tweet['id_str']

#last is ID of last tweet. we are going to use it next step

 

for page in range(0,10):

 

    search2 = twitter.search(q=keywords,

                count =35 ,  include_retweets=False, max_id=last,  timeout=1500)

 

# we need to pass the value for max_id, otherwise the request will fetch the same tweets( or latest) again. Maintaining max_id will help the request to start from last fetched tweet.


# I have looped it for 10 times and faced issues when the looping number is more, so used nested loop to fetch more tweets.

 

    tweets2 = search2['statuses']

 

    for tweet in tweets2:

      count+=1

      ts = datetime.strptime(tweet['created_at'],'%a %b %d %H:%M:%S +0000 %Y')

      ts1 = str(ts)

      data = '{"IDNO": " ' + tweet['id_str'] + ' ", "CNTR": " ' + str(count) + ' ", "THLR": " ' + tweet['user']['screen_name'] + ' ",  "CRTD": " ' + ts1[0:10] + ' ", "TTXT": " ' + str(tweet['text'].encode('utf-8')) + ' "}'

      r = s.post(url, data=data, headers=headers)

    print(count)

    last = tweet['id_str']

 

 

Table Screenshot:

I have collected 6000+ tweets.

fuzz table.jpg

 

3. Filtering the data:

 

Let assume, we are more interested in finding a job in ABAP in CRM module or in ABAP HCM or any other.

I have executed the below SQL in sql console to save tweet data in another table which has ABAP CRM mentioned in the tweets.

 

 

INSERT INTO "MOHAS97"."amohas97.session.data::twitter" select * from "MOHAS97"."amohas97.session.data::fuzz"

  where contains("TTXT", 'ABAP CRM', fuzzy(0.9, 'ts=compare, excessTokenWeight=0.1, decomposewords=2' ))

  order by 1 desc;

 

Fuzzy is 0.9

excess token weight = 0.1 -> any other words in the string/tweet will be ignored during select apart from ABAP CRM.

also decompose words=2, as in looking for two separate words.

 

I have got 161 records

 

twitter table.jpg

 

4. Creating FULLTEXT index for the table with Configuration 'EXTRACTION_CORE_VOICEOFCUSTOMER'

 

CREATE FULLTEXT INDEX TWTIDX ON "MOHAS97"."amohas97.session.data::twitter" ("TTXT")

CONFIGURATION 'EXTRACTION_CORE_VOICEOFCUSTOMER'

TOKEN SEPARATORS '\/;,.:-_()[]<>!?*@+{}="&'

TEXT ANALYSIS ON;

 

Configuration 'EXTRACTION_CORE_VOICEOFCUSTOMER' will help us to classify the string into different types.

whenever it there is "#" mentioned it considers as "SOCIAL", HTTP as "URL".

twitindex1.jpg

 

When I browsed this data, I realized people have used "#" in front of the city/location. so I am  removing "#" from the table. It will show the "type" as "locality"

twitindex2.jpg

Using the above index table, I further created three more table. I will use this tables in Python, SAP Lumira and Excel.

 

Table 1:  Using XSODATA in SAP Lumira

I want compare different cities in terms of job market.

 

INSERT  INTO "MOHAS97"."amohas97.session.data::twitloc"

  SELECT idno,TA_TOKEN

              FROM "MOHAS97"."$TA_TWTFNL"

            WHERE Ta_type = 'LOCALITY'

            group by idno, ta_token

            order by ta_token;

 

I will use XSODATA to view the table data in SAP Lumira.

 

l1.jpg

l2.jpgl3.jpgl4.jpgl5.jpg

 

I have created a geography hierarchy.

Looks like Pune City has more job offers or may be twitter handler is more active.

 

Table 2:  Using XSODATA in Excel

Lets have all the URL links mentioned in the tweets in excel.

 

  INSERT  INTO "MOHAS97"."amohas97.session.data::twiturl"

  SELECT TA_TOKEN, max(idno)

              FROM "MOHAS97"."$TA_TWTFNL"

            WHERE Ta_type = 'URI/URL'

            group by ta_token

            order by ta_token;

 


open the excel file and use the odata as follows. All the URLs are fetched in a column

e1.jpg

e2.jpg

 

 

Table 3:  Reply to Tweets -Twitter Bot

Initially I wanted to build an application with selection parameters to search/filter the tweet data and to reply to the tweets using XSJS. But, I have to settle down with python due to trust store issues.

I want to reply to each and every tweet after I filtering on my own conditions. It is again going to be tough to find the short listed tweets and responding to all of it.

so I am going to use this 3rd table to reply to all the exact tweets at once.

Now I only have 117 records. I am going to use this data @ python to respond to their tweets

 

INSERT INTO "MOHAS97"."amohas97.session.data::twitfnl"

            SELECT *

            FROM "MOHAS97"."amohas97.session.data::twitter" AS s

            WHERE EXISTS

                        (SELECT *

                        FROM "MOHAS97"."$TA_TWTIDX" as p

                        WHERE p.idno = s.idno

                          and ( p.Ta_type = 'LOCALITY'

        OR p.TA_TYPE = 'ADDRESS2') );

 

twitfnl.jpg

 

Python Code:

 

#! /usr/bin/python

 

 

import requests

import json

import csv

import sys

import xlrd

import os

import urllib.request as urllib2

import codecs

from twython import Twython, TwythonError

import time

#import datetime

from datetime import datetime

from datetime import timedelta

import socket #import the socket module

 

 

appURL = 'http://v.... your server add .....:8008/amohas97/session/services/pytxso.xsodata'

auth = 'your hana id','your password'

 

s = requests.Session()

s.headers.update({'Connection': 'keep-alive'})

 

url = appURL + "/twitfnl/?$format=json"

#url = appURL + "/twitfnl"

 

r = s.get(url, auth=auth)

data = json.loads(r.text)

 

twitdata = data['d']['results']

 

from twython import Twython, TwythonError

 

 

# Requires Authentication for Twitter API v1.1

 

twitter = Twython('8Jep7jyA8wxMekC3', 'ywvEJKc4T3DCEgf7UnLrlwprUwh7l', '273814468-7MUccHpYXjMTGH', 'iFMgreouGXdMxp8B4yUN')

 

 

k = 0

for i in twitdata:

    #print( "Twitter Handler: " + exceldata[k]['THLR'] + " --- " + "Twitter ID: " + exceldata[k]['IDNO'] )

    tweet = '@' +  exceldata[k]['THLR'].strip() + ' ' +' I am not interested - Share more details to refer others'

    tid = exceldata[k]['IDNO']

    k = k+1

 

# Posting tweets

    twitter.update_status(status=tweet, in_reply_to_status_id=tid)

    print(tweet, tid)

 

Let check my tweets @ twitter.

Out of 6000+ tweets I am able to choose and reply to 117 tweets. I have job URLs and knows which city has more jobs.

And knows the right head hunter.

 

t1.jpgt2.jpg

SAP CC as rating and charging system in telco industry requires top performance elements on every level of IT infrastructure.

In case when system rates hundred of thousands and millions of transactions per day every improvement of performance means a lot.

I believe, HANA as in memory database would be that kind of improvement.

 

Originally, I posted idea in Idea incubator but idea was rejected since it is idea of SAP product improvement but Idea incubator team directed me to Idea Place: Enterprise Home

I posted my idea there.

If you like it and agree with me, support my Idea by voting on it HANA as SAP CC database : View Idea

 

[edit] Runing SAP CC on HANA would be the best showcase of HANA value as in memory database.

 

 

Best regards,

Mario

In this blog post we are going to learn how to create a HANA Database Connection within Pentaho PDI.

1)  Go to SAP HANA CLIENT installation path and copy the “ngdbc.jar”

*You can get SAP HANA CLIENT & SAP HANA STUDIO from : https://hanadeveditionsapicl.hana.ondemand.com/hanadevedition/

 

1.png

2) Copy and paste the jar file to : <YourPentahoRootFolder>/data-integration/lib

2.png

3) Start Pentaho PDI and create a new Connection

* Make sure your JAVA_HOME environment variable is setting correctly.

3.png

3_1.png

3_2.png

4) Create a transformation,  rick click on Database connection to create a new database connection

4.png

 

5) Select “Generic Database” connection type and Access as “Native(JDBC)”

 

5.png

6)  Fill the following parameter on Settings

Connection Name: NAMEYOURCONNECTION

Custom Connection URL: jdbc:sap://YOUR_IP_ADDREES:30015

Custom Driver Class Name: com.sap.db.jdbc.Driver

User Name: YOURHANAUSER

Password: YOURHANAPASSWORD

6.png

 

7) Test your connection.

7.png

All,

 

I've recently installed a new SAP Business One database in HANA. When trying to access it from the ERP got this error message: "It was not possible to establish the connection to the SAP HANA server".

 

As usual, no information regarding that particular message was found on the net... However, the solution was pretty simple: It turns out that B1Analytics needs to be initialised before running SAP B1H, so that analytics gets to work properly.

 

Simple steps to initialise Analytics in the SLD:

 

  1. Select the Analytics Platform option.
  2. Go to Companies.
  3. Choose the company that you would like to initialise.
  4. Click on the Initialise button.
  5. That's all. The company should now appear as "Initialized" in the list:

 

img.png

 

Hope this helps.

 

Regards,

 

Alejandro Fonseca

Twitter: @MarioAFC


I have always felt the BW fall short in delivering the true power of analytics that it was meant to do. Yes, you do have some basic data mining algorithm available in BW7.3 <= under the APD tool and even with BW 7.4, it is still lacking some serious predictive algorithm power for any hardcode data junkie to take it seriously.


Regardless of version, BW always had some basic and frequently used functions which at times can be useful in helping to address requirement whiteout having to download huge amount of data into an excel sheet or run third party analytic tools to perform the same type of analysis. Trust that SAP has done a great job in ensuring the complex mathematical formulas behind these functions has been done correctly.



However, as any respectable BW consultant would know, standard functionality can only take you so far and there will be a time when you will be challenge to provide algorithms which are not available and it is never a good feeling to leave your business users in the dark with no possible outcome or solutions. It makes it worse when they start downloading enterprise data into excel and prove you wrong with basis excel add on. As with everything SAP, the good guys at Walldorf did provide us an additional option to write our own procedure if none of the above delivered function is what you are looking for. But hey, how many of us out there can easily translate this into SQL Scripts?


This article is not about the ongoing battle between you and your users because as everyone knows, it is about taking them on a “journey” – I personally find this a cliché but it does embody the coloration that needs to happen to reach the end goal. I rather like the quote from Sonny from the movie The Best Exotic Marigold Hotel: Everything will be all right in the end... if it's not all right then it's not yet the end.


If you are running your BW server on HANA, you have a hidden gem concealed underneath that investment that your organisation has made. Some believe that data is the new oil – European Consumer Commissioner Meglena Kuneva and to mine for this new resource, SAP has developed a nifty tool call the Application Function Modeller (AFM). It is an Eclipse based modelling tool and has been long made available since SP6. As SAP’s partner we continue to see improvements in the form of stability and additions of algorithms with each new release to help us better understand our data. In my opinion, customers are able to finally benefit from a true single source of information without having to run third party analytic tools that source data from BW by replicates Info Cubes structures into their environment.


The AFM tool to a certain degree does away with the need to perform SQL scripting but of cause I am not suggesting that you entirely blindside yourself from not being able to interpret basic SQL commands – it is after all a database server with some exception to the norm. It just means that you are able to refocus your energy to explore and tweak the minefield of information that is available by using the correct algorithm and method to answer a specific business question. Imagine this, late Friday evening and you are being dragged into a meeting with folks from the marketing and operations department and you are ask to predict the outcome of a new product launch from the information that has been gathered. One approach to this is to use a decision tree to anticipate the market response so that your organisation can react appropriately. But sadly, from a BW implementation point of view, this does not materialise often enough and worse still, the team manning the day to day health and operation of the BW environment does not have any input into conversation such as these. What usually ends up happening is, you have an experience user from the marketing department demanding for a huge amount of data out from the reporting server and they start to perform data analysis on excel or whatever third party tool that is available. There is nothing wrong with that and I think they should own it – it is what they have been trained for, it is what makes them good at what they do. It is their bread and butter.

 

 

If you are not running your applications on HANA, fine, no contention here but if you are, I am certain the AFM tool will shine. Gone are the days of pesky external connections, constant nit-picking between IT and stakeholders when a simple structure has changed, away with long waiting period for what seems so trivial – data dump, if only they knew.  The list goes on and on and you can fill in with your own frustration here from whichever side of the fence you happen to be on.


Even though if you are not into the predictive space or unmoved by the hype that data mining is already upon us or is a space that you are not willing to jump into, just by having a graphic interface to answer business concern, puts the analytic power back into the users hand. This tool makes playing with data fun because it is so simple to use, given that you have gone through the standard documentation but apart from that, the performance is there, the tools works and the results are real.


I guess my parting thoughts on this matter is that BW has never been terrific at performing statistical calculation or running predictive algorithms without loss of sleep and the motivation that keeps you going is the inner drive of yours to do it at all cost with the help of an icy Red Bull by your lonely side. I would like to think that SAP has come to recognise this shortfall over the years and it became evident when SAP released their Predictive Analytics solution in 2011 and they continue to strengthen their market position with the acquisition of KXEN, who in their own space, is a market leader at what they do. With constant revision to the Predictive Analysis Library native to HANA, you can be positively confident that SAP will continue to make progress in this space. While BW excels in many other aspect of a data warehousing tool, mining for data is not its core strength and I find it comforting that they are other alternative offerings by SAP to address this gap.

 

http://www.cs.utah.edu/~jeffp/teaching/cs5955/L10-kmeans.pdf

http://www.cs.yale.edu/homes/el327/datamining2012aFiles/11_k_means_clustering.pdf

http://www.ibm.com/developerworks/data/library/techarticle/dm-1007predictiveanalyticssapspss/

https://help.sap.com/saphelp_nw04/helpdata/en/4a/eb293b31de281de10000000a114084/content.htm

Hi All.

I came across this scenario, thought of sharing.

My idea to reduce defective products in textile production solution SAP HANA

Because textile production attentive and high quality production that results when faulty products due to increased costs will reduce the mobile integration with minimal defects cost to build the technical and software infrastructure is needed at this point, SAP HANA solution with the whole process fast and integrate the work will be.

This has been proposed in SAP idea incubator, here.

 

Regards,


Cem Ates

I ‘hope you'll find this blog useful :-)

 

I ‘was looking a while to find a solution to run the SAP Hana Studio on my Mac without any emulators. The Solution is simple:

 

Short:

  1. Download Eclipse Kepler 4.3 - 64Bit (eclipse-standard-kepler-SR2-macosx-cocoa-x86_64)
  2. Change the Eclipse Compiler & JRE Settings to Java 1.6!  It will not work with 1.7 or any OpenJDK version.
  3. Open Eclipse->Help->Install New Software->Add:

 

 

Screenshots:

 

JRE & Compiler Settings

jre.jpg

compiler.jpg

 

Software Installation

software.jpg

 

Regards, Tobi

This post continues the blog series A Programming Model for Business Applications. If you havn´t read the first parts in the series, I recommend that you read the first part A Programming Model for Business Applications (1): Assumptions, Building Blocks, and Example App and the second part A Programming Model for Business Applications (2): Implementing the Entity Model with CDS first.

 

In this part, I want to discuss the implementation of the business object read service and the properties on the server side.

 

 

Business Object Read Service

 

Let us start with the BO-specific views for calculated elements. Obviously, I would like to define calculated elements that belong to the BO in the entity definitions itself. For example the following amount calculation:

 

NetAmount = {

  Currency = ListPriceAmount.Currency,

  Value   = ( ListPriceAmount.Value * Quantity.Value ) * (100 - DiscountPercentValue) / 100 

}

 

Unfortunately, in SPS8, CDS does not support the definition of calculated elements in an entity, so I have implemented calculated elements as separate views postfixed by $C.

 

The first listing shows the view for the Item entity with two examples for amount calculations:

  view Item$C as select from bo.SalesOrder.Item {

    ID_,

    Parent_ID_,

    ListPriceAmount.Currency.Code     as "NetAmount.Currency.Code",

       (ListPriceAmount.Value * Quantity.Value) * (100 - DiscountPercentValue) / 100 as "NetAmount.Value",

    ListPriceAmount.Currency.Code     as "GrossAmount.Currency.Code",

    NetAmount.Value + TaxAmount.Value as "GrossAmount.Value"

    };

 

The next listing shows the view for the Sales Order entity with a summation over the items. Here, unfortunately a second limitation of CDS hits us: It is not yet possible defining (unmanaged) associations between views. As I want to use calculated elements of the Item$C view in the SalesOrder$C view, I cannot refer to them via association, but I have to define a join between SalesOrder and Item$C, which makes the select statement more complex, and which is not supported by CDS in SPS8. As a consequence, I implemented the SalesOrder$C view as hdbview artifact using the following select clause::

 

select

   SO.ID_,

      sum(I."NetAmount.Value")   as "NetAmount.Value",

   SO."Currency.Code"         as "NetAmount.Currency.Code"

  from <schema>.<path>::bo.SalesOrder.SalesOrder" as SO

  join <schema>.<path>::bo.SalesOrder.Item$C" as I on SO.ID_ = I."Parent_ID_"

group by SO.ID_ , SO."Currency.Code"  

 

The last listing shows the (simplified) calculation of the formatted name for a business partner (simplified) as part of the BusinessPartner$C view.

case when (CategoryCode = '1') then

      case when (not(Person.AcademicTitle.Code = '')) then

            concat(Person.AcademicTitle.TitleText , concat(' ', concat(concat(Person.GivenName, ' '), Person.FamilyName)))

            else concat(concat(Person.GivenName, ' '), Person.FamilyName) end

else

   case when (not(Organization.SecondLineName = '')) then

      concat(Organization.FirstLineName, concat(' ', Organization.SecondLineName))

   else

     Organization.FirstLineName end

end as FormattedName

 

As a result the following formatted names may be returned: “SuccessFactors A SAP Company” (organization with two name lines), “Thomas Schneider” (no academic title), “Dr. Thomas Schneider” (academic title before the name), or “Thomas Schneider, PhD” (academic title after the name, not implemented).

This little example already shows us that a simple calculation like a formatted name can be quite complex (the complete code that considers all possible combinations is even longer). But it gives us a good reason for the argument that these type of calculation should be done in centrally as business object logic and should not be repeated in the service implementation or the UI.

 

Properties

Property logic provides information whether data can be updated, are mandatory, etc. depending on the lifecycle of an object. Examples for properties are:

  • Disabled: an element or entity is disabled
  • Read-only: an element or entity is read-only. If the entity is read-only, all elements are read-only.
  • Mandatory: the element must have an input value

Properties can be static, dependent on some business configuration or master data, or dependent on the status of the business object.

 

Examples:

  • Static: the Last Changed DateTime element is read-only, it is always set by the system.
  • Dependent on the master data: in a sales order, the properties depend strongly on the product you are selling: In case you are selling physical materials, or services, or projects, various elements (for example the Incoterms elements) may be disabled (= not relevant for the product), read-only (= prefilled by the system), or mandatory.
  • Dependent on the status: Sales orders in status “Completed” cannot be changed, they are read-only.

The UI may optionally request the properties as part of the read request and set the properties of the respective UI control accordingly. Properties checks also run as part of the CUD request to check if the input is valid at a certain point in time (I will discuss this in a later blog).

For the implementation of properties, I am using an additional view with name postfix $P, for example SalesOrder$P, etc.

 

Discussion:

  • Properties are view fields of type Boolean (workaround: Integer), named, for example “Incoterms.Classification.IsMandatory”
  • Properties are optional, in other words, not all properties are modelled in the properties view 
  • A Null value is equivalent to False.

 

Properties are a very powerful concept, but you can introduce them step by step. In the following blogs I will come back to the properties view and discuss how it is consumed in case of the read and the write scenario.

The next blog in this series shows the implementation of the service adaptation read service the OData service and the corresponding UI implementation: A Programming Model for Business Applications (4) Service Adaptation and UI (Read Service).

Intro

XSJS outbound connectivity was introduced in SAP HANA SPS06. It is really a cool feature in SAP HANA XS. With XSJS outbound connectivity, we can directly make HTTP/HTTPS request in our SAP HANA native app. I love social media and this feature makes it possible to connect social media (e.g. Twitter, Facebook) API with SAP HANA and do some interesting analysis. In this blog I want to share with you how to use XSJS outbound connectivity to search tweets.

 

 

Motivation

Last year I had a mini-fellowship in Palo Alto and it was my first time in the US. I wanted to watch a movie one weekend but it's hard for me to pick one. So I decided to use SAP HANA to build a smart app which would tell me the rating of movies. I made it and selected the movie with highest rating. The main idea is first crawl tweets and insert into SAP HANA, then use the native text analysis to calculate the sentiment rating. However, when I was building this app, SAP HANA is still SPS05. Without XSJS outbound connectivity, I have to use Twitter4J to connect Twitter API. If only I could use XSJS outbound connectivity at that time! Never mind. It's time to rebuild the smart app and replacing Twitter4J part with XSJS outbound connectivity is my first step.

 

Now let's do it.

 

 

Prerequisites

1. A running SAP HANA system, at least SPS06. I am using SAP HANA SPS08 Rev. 80.

2. A Twitter account. If you don't have, sign up here.

 

 

Steps

1. Learn how to communicate with Twitter API

In this step, we need to find what APIs will be used and how to communicate with Twitter API (authentication & authorization stuff). First you can find all Rest APIs here. We want to search tweets, so we will use GET search/tweets | Twitter Developers It is very clear and you can find the URL, parameters and an example request.


But how can we call this API? You can find the answer in this doc. Since we just need to search tweets, we can use the app-only authentication.There you can find a very detailed example with three steps. That's exactly what we need. There is one thing need to be mentioned, also you can find it here "As with all API v1.1 methods, HTTPS is always required."

 

 

2. Use Postman to simulate calling the API

Since now we know how to communicate with Twitter API, we can first test it with Postman. I like to test before I really do something. There is also a lot of other tools similar with Postman, please use your favorite. The steps are described clearly in app-only authentication. I will not describe them again. Here I just summarized my steps with some screenshots.

 

a. Encode API key and secret.

First you need to create an app if you don't have one. You can find <API key> and <API secret> under "API Keys" tab in you app. I've already regenerated API key and secret, so the key and secret in the following pic do not work now.

 

1.PNG

 

Then encode <API key>:<API secret> to Base64 format. For example, you can use Base64 Decode and Encode - Online

 

2.PNG

 

 

b. Obtain a bearer token

You need to sign out your Twitter account in Chrome; otherwise you will not get the bearer token. Instead you will get this message "403 Forbidden: The server understood the request, but is refusing to fulfill it." I've already invalidated the bearer token in the following pic.

 

3.PNG

 

 

c. Test GET search/tweets | Twitter Developers with the bearer token

We managed to search tweets with hashtag #SAPHANA and got the results. To keep simplicity, we just use parameter "q" which stands for query.

 

4.PNG

 

 

3. Set up your SAP HANA to use HTTPS

Till now, we have successfully called one Twitter API using Postman. So why not use XSJS outbound connectivity? Let's start! As with all API v1.1 methods, HTTPS is always required. So, the first thing we need to do is set up our SAP HANA to use HTTPS which is not configured by default. You can follow this detailed blog by Kai-Christoph to finish this step. When you finish it, you should be able to do the following; Otherwise you do not finish this step.

 

a. Visit https://<hostname or IP>:43<instance number>/sap/hana/xs/admin/ successfully

b. When you switch to "Trust Manager" tab, there is no such error "No valid SAP crypto configuration"

 

 

4. Create trust store of Twitter API

In this step, we need to create trust store of Twitter API. Again, you can follow this detailed blog by Kai-Christoph to finish this step. There is only one thing you need to change. That is visit https://api.twitter.com/ instead of https://api.github.com/ in the blog.

 

5.PNG

 

 

5. Use XSJS outbound connectivity to search tweets

We finally come to this step. Since we have prepared everything in the previous steps. It is easy for us in this step. We just need to do the following. I did it in SAP HANA Studio. Of course you can also do it in Web IDE. Here is my project hierarchy. It is very simple.

 

6.PNG

 

a. Create a XS project

b. Create .xsapp, .xsaccess and "services" folder

c. Create twitterApi.xshttpdest, edit, save and activate

 

description = "twitter api";

host = "api.twitter.com";

port = 443;

pathPrefix = "/1.1";

useProxy = true;

proxyHost = "proxy.pal.sap.corp";

proxyPort = 8080;

authType = none;

useSSL = true;

timeout = 0;

 

d. Edit trust store in HTTP destination (in red box) and save

 

7.PNG

 

e. Create search.xsjs and edit. From Application-only authentication | Twitter Developers, we can learn that "Note that one bearer token is valid for an application at a time. Issuing another request with the same credentials to /oauth2/token will return the same token until it is invalidated." So, we do not need to obtain the bearer token each time which means we can directly use bearer token in our code. I've already invalidated the bearer token in the following code.

 

var destination = $.net.http.readDestination("searchTweets.services", "twitterApi");

var client = new $.net.http.Client();

var request = new $.net.http.Request($.net.http.GET, "/search/tweets.json?q=%23SAPHANA");

request.headers.set('Authorization', 'Bearer AAAAAAAAAAAAAAAAAAAAADdMZgAAAAAADme2QQk3csQXnGCeepM7Swvf6PI%3DJZNlbYr3YkcDsS0xCgeRgmzJW5Cjk8cvI4ESXECzVKTYI3bNw5');

var response = client.request(request, destination).getResponse();

$.response.status = response.status;

$.response.contentType = response.contentType;

$.response.setBody(response.body.asString());

 

f. Save and activate all files

 

6. Test it

Now let's test our XSJS outbound connectivity. Called successfully! There is a tweet text which contains "The only limitation is our imagination!"

 

8.PNG

Conclusion

So far, we have successfully used XSJS outbound connectivity to search tweets. However, yet we neither inserted tweets into SAP HANA nor made some analysis. I'll do this in my smart app and share with you later. In addition, there is also a lot of other Twitter APIs which you can call with XSJS outbound connectivity.

 

Hope you enjoyed reading my blog.

Cool Stuff

http://d3js.org

Nice example: http://www.nytimes.com/interactive/2012/10/15/us/politics/swing-history.html

 

Screen Shot 2014-08-23 at 16.32.45.png

 

http://jsfiddle.net

JS Playground to test CSS&HTML&JavaScript

 

Screen Shot 2014-08-23 at 17.31.28.png

 

Does your website pass the browser test?

http://www.browserstack.com

 

Screen Shot 2014-08-23 at 17.32.38.png

 

JavaScript charts

http://dygraphs.com/index.html

Simple charts - open source not fancy

 

http://www.highcharts.com/products/highcharts

Free for non-commercial

 

http://www.koolchart.com

Mixture of charts

 

http://www.amcharts.com/javascript-charts/

Maps and charts

 

http://www.zingchart.com/js-charts

Charts, maps, word cloud


http://www.jqwidgets.com/jquery-widgets-demo/

Free for non-commercial

Mixture of simple UI controls


http://www.jscharts.com/examples

Simple charts - free to use with watermark


http://www.flotcharts.org

Simple


http://www.fusioncharts.com/explore/charts/

Charts and Dashboards


http://gojs.net/latest/samples/index.html

Lots of different tree structures, mind maps, flow charts, org charts etc

 

https://google-developers.appspot.com/chart/interactive/docs/gallery

Google has a go at it

Screen Shot 2014-08-23 at 17.39.45.png

 

 

http://canvasjs.com/html5-javascript-column-chart/

Charts (including candlestick and bubble)

 

More than just charts, whole dashboards

http://wijmo.com/widgets/

 

http://js.devexpress.com/WebDevelopment/Charts/

Charts, maps, Gauges

 

http://www.anychart.com/products/anychart7/gallery/

Clean & simple (Oracle partner)

 

 

JS Frameworks

http://jquery.com

Most well known JS library (MIT license)

(Testing Framework: http://qunitjs.com )

 

Screen Shot 2014-08-23 at 17.38.02.png

 

https://docs.angularjs.org/misc/faq

Angular JS (Google)

Nice: bidirectional data binding


http://knockoutjs.com/index.html

Opensource (MIT license)

 

https://saucelabs.com/javascript/

Another tool to Test your Javascript

http://www.metric2.com/wp-content/uploads/2014/10/bg_top.png


metric² for iPhone lets you monitor your SAP HANA instances from your phone showing you alerts, core resources and important metrics. Wherever you are.

 

As mentioned in my GitHANA article, developing on the metric² open source project has really provided some interesting use cases for me around SAP HANA. While it might not be as critical as ERP, BW or custom solution, the metric² demo system is used fairly regularly by people wanting to test drive the functionality. I recently had some server troubles and my HANA instances was down without me knowing. This promoted me to develop a small mobile app to monitor and ensure that my instance was available and running optimally. This is when metric² for iPhone was conceived and I started developing the free app.

 

 

screenshots.png

 

 

The app is currently availble for iPhone, and I have a iPad version getting ready to be submitted to the App store. From a technical perspective the app uses a small XS file called mobileapi.xsjs which will need to be put in a package on your XSEngine instance to serve up the data to the app. You can specify how often you would like the data to be refreshed and specify multiple systems which you may need to monitor. (I have included my demo HANA instance as an example within the app so you can try it out.)

 

 

alerts.png

 

The app is perfect for anyone running a HANA instance, be dev, test or production. It provides a really easy way to view the status of your system from anywhere using your iPhone. The app also downloads updates in the background and will notify you if any high alerts are experienced on the selected system, this is perfect for any sys admin/dba who will be to anticipate critical outages and be ready for the support calls.

 

A few features of the app

 

- View CPU, Disk, Memory consumption

- View open alerts

- Insights into your HANA instance quickly and from anywhere

- Add multiple HANA instances for monitoring

- Clean and simple UI for basic admin functions

- Push notifications for high alerts, when the app is in running background

 

Click here to find the GitHub project (of the mobileapi.xsjs file) and click here to check out the product page. This includes install instructions.

 

 

Technical Details

 

Building a native iOS (read Obj. C or Swift) which is integrated with SAP HANA is not terribly challenging and you really have 2 options for pulling or pushing data. Via a XSJS type file (like this app) or via a xsOData type of interface. Both have their pro’s and con’s but are fundamentally very similar. Below is a snippet of some of the code from my xsjs file and looks/acts very similar to what a regular AJAX call would use from a native XS app.

 

One of the biggest challenges for production users, like any intranet based resource, will probably be gaining access to the URL (mobileapi.xsjs) from outside the corporate net and will probably require a network admin to grant you access or configure (or reuse) a reverse proxy or firewall.

 

 

Screen Shot 2014-08-22 at 1.08.56 PM.png  

 

Screen Shot 2014-08-22 at 1.12.40 PM.png
XCode iOS Pull Data Code
Screen Shot 2014-08-22 at 12.52.08 PM.png
SAP HANA XSJS Code serving data to the iOS app
Sheel Pancholi

Dependency Graph

Posted by Sheel Pancholi Aug 22, 2014

This blog deals with the dependency graph of (an) object(s) required to maintain the list of objects in order of their dependency on this base object.

Creating DB artifacts frequently involves using other DB artifacts e.g.

  • Procedures may use tables, views, column views, other procedures
  • Column views use underlying column store tables/other column views/procedures

Thus we, basically, establish an acyclic directed graph of objects and their dependencies. At any point in time, it can be useful to establish a hierarchy of any object in this graph to check its dependents at various levels. While establishing a hierarchy for such an object would mean traversing from this node, through its dependents, to the node with in-degree one, being an acyclic directed graph, there might be more than one hierarchy for this particular object, which means, there might be more than one nodes with in-degree one. In such a case, instead of maintaining a hierarchy, we can choose to maintain an ordered list of objects based on their dependencies, thereby, essentially, breaking down the problem, to ensuring that the child object appears before the parent object, when we traverse from such base object to the "grandest" parent or the top level node or the node(s) with in-degree one.

 

Why is it necessary

1. Typically, in an environment, such as  HANA's, we try to push most of the processing logic into the backend, which means that there would be too many DB objects in the to take care of. In a developer environment with, more than just a few developers, we frequently, have the same DB object(s) being used by multiple developers e.g. a table T being used by two procedures, each belonging to two different developers John and Jim, and then, these procedures further being used to establish a complex graph of dependencies. John, casually, makes changes to the DB object without caring for its repercussions. It might invalidate the entire chain of objects created by the Jim, say, John drops a column from the shared table that is being used by Jim's procedures. Just like the procedures, Jim might have multiple such DB objects, using this changed table, which may have now been rendered invalidated, so much so, that it might not be possible for Jim to remember each of them, by heart; much worse if the change to the table has had a ripple effect across the circuit of dependency, established by this base object table. Now, Jim has two problems:

  • remembering the list of objects that are using this changed table e.g. Procedure X, Y, Z might be independently using Table T
  • the order in which these objects have been used e.g. Procedure A might be calling Procedure B might be calling Procedure C which uses the table T; to make things more complex Table T might be being used by Procedure B directly as well

Now, John has a bit if a homework to do to avoid making Jim's life miserable again. Before making such a change, a utility tool like dependency graph could have helped him establish the dependency graph for this Table T. And he could then have taken a call regarding going ahead with that change.

Again, Jim, still, does not have all lost, if he can establish the graph for this object and find out what is/are the object(s) that need to be re-worked on
2. Again, a lot of applications divide their consumption into design time and the runtime. The runtime DB artifacts to be used are created during the activation using the metamodel of the runtime, stored as the metadata. If since last activation, changes to the run time have been proposed in the design time, then the HANA system will not be able to tell you until the proposals are actually activated. However, the metamodel does have a way to tell if a modification/enhancement has been made post activation. So a change log is required to maintain the list of objects that have been changed; as also, the rest of the objects that have been rendered invalidated logically, because of the dependency between them. The added advantage to this is that we do not have to regenerate the entire runtime. We can deal with the affected (direclty/indirectly) objects only

 

 

Proposed solution:

Consider the following graph of object dependencies:

Key features from this graph

1. Its an acyclic directed graph

2. Leaf nodes are always tables

3. Non leaf nodes can also be the base objects that trigger the chain of invalidation

 

Problem

Build the dependency graph for the table P.

 

Assumption(s):

1. The leaf nodes are tables. So tables are the lower most database artifact that when changed invalidate the graph of objects

2. Cyclic dependencies are not considered since SAP HANA database does not support recursions/cyclic dependencies

3. Dynamic SQL is not taken care of

4. In terms of graph theory, there can at max be 1 base object in a particular path to the top most node. That base object is the first vertex of that path.


Key idea(s)

1. Child object should get activated before its dependent(s) at any level in the graph.

2. We use Breadth First Search to prepare the relationship data between the nodes in the graph. We do not use the Depth First Search as it requires recursions.



Process:

1. Build a parent-child relationship data between the nodes in the graph            

Parent-Child List

Node

Parent Node

Level

P

N

1

P

Z

1

N

I

2

N

J

2

Z

V

2

I

F

3

I

J

3

J

Y

3

J

A

3

V

U

3

F

C

4

Y

Z

4

Y

X

4

Y

W

4

A

-

4

U

-

4

C

A

5

Z

V

5

X

V

5

W

V

5

W

U

5

2. Prepare the first list of processed nodes from the above structure. We call the list the List A. We push the base object into the list and mark it Processed. For the rest of the list, we propose the following approach. To decide if a node is processed or not, we look at the Parent Node column in the Parent-Child List prepared in step 1. For each Parent Node in the list above, we find if there are any child objects that have not already been processed, that is, they have not already been included in this list of processed nodes i.e. List A. If there are such nodes then, we include those unprocessed child nodes first and mark them as unprocessed, and then include the current parent node as processed.

Lets see how this goes.

     i. we push P into the table and mark it processed. So, List A looks like:

Node

Status (Processed or not)

P

Processed

      ii. we take the first Parent Node from Parent-Child List that is N and see if its already included in List A. If not, we look for its children. We find that P is the only child of N. And P has already been included. So we push N in the list and mark it processed. So, List A looks like:

 

Node

Status (Processed or not)

P

Processed

N

Processed

     iii. we take the second Parent Node from Parent-Child List that is Z and see if its already included in List A. If not, we look for its children. We find that P and Y are the children of Z. And P has already been included. But Y has not already been included. So we push Y, first, in the list and mark it Unprocessed. Now, we push Z in the list and mark it processed. So, List A looks like:

 

Node

Status (Processed or not)

P

Processed

N

Processed

Y

UnProcessed

Z

Processed

.. we continue this approach till we iterate through all the elements in Parent Node column of Parent-Child List. So at the end, this is how List A looks like:

List A

Node

Status (Processed or not)

P

Processed

N

Processed

Y

UnProcessed

Z

Processed

I

Processed

J

Processed

X

UnProcessed

W

UnProcessed

V

Processed

F

Processed

C

UnProcessed

A

Processed

U

Processed

3. Now, from List A, we keep deriving subsequent lists, by checking for only the unprocessed nodes, on the same lines, until all the nodes are processed

List A Version 1

 

 

Node

Status (Processed or not)

P

Processed

N

Processed

Y

UnProcessed

Z

Processed

I

Processed

J

Processed

X

UnProcessed

W

UnProcessed

V

Processed

F

Processed

C

UnProcessed

A

Processed

U

Processed

List A Version 2

Node

Status (Processed or not)

P

Processed

N

Processed

J

UnProcessed

Y

Processed

Z

Processed

I

Processed

X

Processed

W

Processed

V

Processed

F

Processed

C

Processed

A

Processed

U

Processed

List A Version 3

Node

Status (Processed or not)

P

Processed

N

Processed

J

Processed

Y

Processed

Z

Processed

I

Processed

X

Processed

W

Processed

V

Processed

F

Processed

C

Processed

A

Processed

U

Processed


Limitations:

  • Dynamic SQL is not covered- As is natural, the dependencies of parent objects on the objects used in dynamic SQL queries is not covered in the metadata views of the SYS schema and therefore, in order to capture the such dependencies, methods like parsing need to be resorted to; this solution however, leaves behind the question of the very premise of the dynamic SQL
  • Deletion of base objects- The base objects if deleted do not have a presence in the metadata views of HANA which makes the detection of the invalidated objects impossible. We might need to have a custom table to address such a scenario
  • The dependencies between the attribute views and analytic/calculation views are not maintained in the HANA system. So one needs to separately maintain such dependencies

 

Support from HANA platform:

1. The HANA DB provides system views like PROCEDURES, VIEWS, OBJECT_DEPENDENCIES which can be used to build the dependencies and check the valid/invalid status of the chain

 

Technical Implementation

The technical implementation of this involves talking with the metadata views of the SYS schema. The views involved are:

Code Piece

set schema chlog;

drop table relation;

create global temporary table relation(node nvarchar(32), parent_node nvarchar(32), level int, object_no int, is_processed nvarchar(32));

drop table changelog_tab;

create column table changelog_tab(object nvarchar(32));

drop table gt_chlog1;

drop table gt_chlog2;

create  global temporary table gt_chlog1(node nvarchar(32),is_processed int,order_no int);

create  global temporary table gt_chlog2(node nvarchar(32),is_processed int,order_no int);

insert into changelog_tab values('P');

delete from relation;

drop table chlog.attr_view_relation;

create table chlog.attr_view_relation(an_view nvarhar(256),at_view nvarchar(256));

insert into chlog.attr_view_relation values('chlog/N','chlog/O');

insert into chlog.attr_view_relation values('chlog/E','chlog/G');

 

drop view object_dependency;

create view object_dependency as select * from object_dependencies where dependency_type=1 and base_object_type in ('PROCEDURE','VIEW','TABLE') and dependent_object_type in ('PROCEDURE','VIEW','TABLE') and dependent_object_name not like '%/hier/%'

union

select '_SYS_BIC',right_table,'VIEW','_SYS_BIC',entity_name,'VIEW',1 from chlog.attr_view_relation ;

 

 

drop procedure check_log;

create PROCEDURE check_log ( )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  DEFAULT SCHEMA chlog

-- READS SQL DATA

AS

/*****************************

  Write your procedure logic

*****************************/

i int;

lv_object nvarchar(32);

lv_base_object_name nvarchar(32);

flag int;

lv_no_of_unprocessed int;

lv_objects_left_at_level int;

arr_parent nvarchar(32) array;

arr_node nvarchar(32) array;

arr_status int array;

arr_order_no int array;

lv_cnt int;

lv_cnt1 int;

current_node nvarchar(32);

current_order_no int;

current_status int;

cnt_unprocessed int;

lv_max_order_no int;

lv_max_at_level int:=0;

lv_is_exist int := 0;

lv_any_more int :=0;

begin

DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

select top 1 object into lv_object from changelog_tab;

i:=0;

lv_base_object_name:=lv_object;

flag:= 1;

truncate table relation;

insert into relation select base_object_name node, replace(dependent_object_name,'/proc','') parent_node, i+1 level,:lv_max_at_level + row_number() over () object_no ,null is_processed  from chlog.object_dependency where base_object_type in ('PROCEDURE','VIEW','TABLE') and dependent_object_type in ('PROCEDURE','VIEW','TABLE') and dependent_object_name not like '%/hier/%' and base_object_name in (select object from changelog_tab) and dependency_Type = 1;

i:=1;

while flag =1 do

--get maximum object_no for the current level

  select case when max(object_no) is null then 0 else max(object_no) end into lv_max_at_level from relation where level= i+1;

  lv_is_exist:=0;

--get 1st level dependents

  select count(*) into lv_is_exist  from chlog.object_dependency where base_object_type in ('PROCEDURE','VIEW','TABLE') and dependent_object_type in ('PROCEDURE','VIEW','TABLE') and dependent_object_name not like '%/hier/%' and base_object_name=lv_base_object_name and dependency_Type = 1;

  if lv_is_exist = 0 then

--if no such dependents exist then this is the root of the hierarchy, push a dummy record for this base object with null as the parent object

  insert into relation values(lv_base_object_name, null, i+1 ,:lv_max_at_level + 1,'X');

  else

--if dependents exist enter the first level dependents

  insert into relation select base_object_name node, replace(dependent_object_name,'/proc','') parent_node, i+1 level,:lv_max_at_level + row_number() over () object_no ,null is_processed  from chlog.object_dependency where base_object_type in ('PROCEDURE','VIEW','TABLE') and dependent_object_type in ('PROCEDURE','VIEW','TABLE') and dependent_object_name not like '%/hier/%' and base_object_name=lv_base_object_name and dependency_Type = 1;

  end if;

--mark this node present as parent node in other rows of the current snapshot processed

  update relation set is_processed= 'X' where parent_node = :lv_base_object_name;

 

 

--routine run to mark those parent nodes that have been newly added after their older instances have been already processed and had therefore been marked processed for that snapshot

  lt_parent =select parent_node from relation where is_processed='X';

  update relation set is_processed='X' where parent_node in (select parent_node from :lt_parent);

 

 

--check for any objects left unprocessed at current level

  select count(*) into lv_objects_left_at_level from relation where  parent_node is not null and level= :i and is_processed is null;

  if lv_objects_left_at_level > 0 then

--if left, then select the next unprocessed object at the current level

  select top 1 parent_node into lv_base_object_name from relation where level= :i and is_processed is null

  and parent_node not in (select distinct node from relation a where a.node is not null) and parent_node is not null  order by object_no;

  flag:=1;

  else

--if no, then check if this is the last level

-- if this is the last level then exit

-- if this is not the last level then increase the level counter i and start processing this new level in the next while loop iteration

  i:=:i+1;

  select count(*) into lv_any_more from relation where parent_node not in (select node from relation a where a.node is not null) and parent_node is not null;

  if lv_any_more >0 then--for last level

  select top 1 parent_node into lv_base_object_name from relation where level= :i and is_processed is null

  and parent_node not in (select distinct node from relation a where a.node is not null) and parent_node is not null  order by object_no;

  flag := 1;

  else

  flag:= 0;

  end if;

  end if;

end while;

select * from relation;

 

 

truncate table gt_chlog1;

truncate table gt_chlog2;

 

 

--preparing the 1st list of processed/unprocessed nodes (objects)

select count(*) into lv_cnt from relation where parent_node is not null;

lt_mid= select * from relation where parent_node is not null order by level,object_no;

arr_parent:=array_agg(:lt_mid.parent_node);

insert into gt_chlog1 select object,1,row_number() over () from changelog_tab;

--insert into gt_chlog1 values(:lv_object,1,1);

for i in 1..:lv_cnt do

  current_node:= :arr_parent[:i];

--check if the current node is already mentioned in the list processed/unprocessed, if yes proceed to the next node (iteration)

  select count(*) into lv_cnt1 from gt_chlog1 where node=:current_node;

  if :lv_cnt1 =1 then

  continue;

  end if;

  select max(order_no) into lv_max_order_no from gt_chlog1;

  insert into gt_chlog1 select node,0 is_processed,lv_max_order_no + row_number() over () order_no from relation where parent_node=:current_node and node not in (select node from gt_chlog1);

  select max(order_no) into lv_max_order_no from gt_chlog1;

  insert into gt_chlog1 values(current_node,1,lv_max_order_no + 1 );

end for;

select * from gt_chlog1;

--keep iterating till all the nodes are processed and are in the order where child comes before the parent

select count(*) into cnt_unprocessed from gt_chlog1 where is_processed= 0;

select count(*) into lv_cnt from gt_chlog1;

while cnt_unprocessed != 0 do

  for i in 1..:lv_cnt do

  select node,is_processed,order_no into current_node,current_status,current_order_no from gt_chlog1 where order_no = :i;

  select count(*) into lv_is_exist from gt_chlog2 where node=current_node;

  if lv_is_exist = 0 then

  select case when max(order_no) is null then 0 else max(order_no) end into lv_max_order_no from gt_chlog2;

  if :current_status = 1 then

  insert into gt_chlog2 values(:current_node,:current_status,lv_max_order_no+1);

  else

  insert into gt_chlog2 select node,0 is_processed,lv_max_order_no + row_number() over () order_no from relation where parent_node=:current_node and node not in (select node from gt_chlog2);

  select max(order_no) into lv_max_order_no from gt_chlog2;

  insert into gt_chlog2 values(:current_node,1,lv_max_order_no+1);

  end if;

  end if;

  end for;

  select count(*) into cnt_unprocessed from gt_chlog2 where is_processed= 0;

  select * from gt_chlog2;

  if cnt_unprocessed !=0 then

  truncate table gt_chlog1;

  insert into gt_chlog1 select * from gt_chlog2;

  truncate table gt_chlog2;

  end if;

end while;

END;

 

call chlog.check_log;

Output:

output1.png

We can have multiple objects pushed into changelog_tab and the procedure will give us the correct order in which the objects depend on each other. Try the following input and run the procedure:

 

insert into changelog_tab values('P');

insert into changelog_tab values('T');

insert into changelog_tab values('K');

insert into changelog_tab values('D');

call chlog.check_log;

 

Output:

output2.png

 

Thank You.

 

-Sheel Pancholi   

As explained in this blog on Omni-channel consumer engagement, the focus is getting bigger on solutions that can solve unique but complex usecases to cater to the expectations of the Marketing Analysts or the E-Commerce applications, which is to make sense out of sheer volume of data screaming in from various channels (think 3Vs : Volume / Variety / Velocity ).

 

This is one area where HANA can be used innovatively to solve hitherto unknown problem domains. In this blog, I tried to trace thru one such problem that we solved based on the above-mentioned blog, how we expanded the usecase, how different approaches were experimented and how a unique algorithm was invented on HANA to meet the requirements.


The Origin


The problem originated from a simple question: What if an e-Commerce site were to recommend to the user what people with similar “interests/Likes” have already bought. This looked like an easy problem to solve, but then, when we look at it realistically from a social media point of view, there can be hundreds of users for each similar Like, each having bought completely different product, we may end up recommending the entire product catalog to this user!


Then, after some more research, we realized that the behavior of the user can be closely matched if we were to combine more than one Like together and see which users overlap this combination the most, i.e, people with likes similar to Wildlife, National Geography and Camera might have more things in common in terms of choosing a lens for the camera, than individual Likes.  This solves two problems: identify behaviorally similar set of people, and reduce the choice of recommendation in a rational way.


It finally resulted in the problem statement: “If current user x have y number of Likes (Lx1..Lxy), and if there are other users in the same domain with their own Likes (L11..Lmn), what could be the most overlapped group of Likes, considering both on the number of Likes in that group and the number people following that group.”


Following example gives an illustration: Have a look at the current user’s Likes, and how many other users in the target group share how many similar Likes.

 

b1.png

 

The problem


Out of the problem domain mentioned above, the key challenge to be solved is “How can we find out, out of the whole space, the (top most) combinations that are common”. Of course, in order to do that, we may need to calculate all combinations of the current user with each and every combination of every other user which would be permutationally taxing. So, we needed to look for a quicker and more performant approach.


Also, based on specific usecases, some would give more weightage to number of Likes in the group, or, some to the number users per group. Then we can extend the problem to support both metrics. Say, if there 5 users having 10 similar likes that of the current user, it would indicate a small number of users but a strong similarity (we call this Match). Or, there could be 100 users having 3 similar likes which indicates huge trend with a limited similarity (we call this Support). Based on the usecases and the Likes involved, it simply depends, and hence we need provide both metrics in a sorted order, so that an analyst can make a better choice.


The following diagram explains the entire workflow of the usecase we want to solve, from the bottom where have the raw data to the top where we have the product recommendations:

 

b2.png


Apriori Modified


We started looking into the Apriori algorithm as it tries to solve a similar problem in the domain of market basket analysis, where individual baskets are recorded as the users buy them, and it can predict the probability of the likely products to be bought.
Say, if we record the following purchases with Apriori
-  {P1, P2, P3},
- {P2, P3},
- {P1,P2} )


Now, we can ask the probability of someone buying {P2} if they have already bought {P1} and calculate how much support and confidence we have in that.
In this example,
For {P1} -> {P2} =>  Support is 2, Confidence is 100%
i.e, people always buy P2 if they buy P1 based on 2 occurrences in the recordset.


But Apriori does not have a concept for the input set, as required by our problem (i.e, the likes of the current user and compare the rest of the sets for matchings), but if somehow we have to figure out a way to bring {OctoberFest, Ferrar, Photography, Nikon} on LHS, it can automatically spit out the necessary support combinations on RHS, which will solve our primary problem of bubbling up all the combinations. But it cannot happen as atleast one value out of that set has to be on RHS (see the Normal method in the below diagram)!

 

And, one trick we used to make that happen is to add a dummy like called “Target”, which will ensure we get all 4 like on LHS. From here, Apriori will take over and actually gives us all the support combination. In fact it will give all combination, but we can set a pre-defined RHS filter which is “Target”. It works but with a major disadvantage: still the algorithm has to run through all combinations beyond what is necessary for our purpose. And, we were even thinking about extending the Apriori itself at code level until we stuck upon a different path altogether.

 

b4.png

 

 

The Final Solution


As we were brainstorming for a better solution which is economical and performant, we thought why not solve the problem from the DB/SQL point of view rather than as a programmatic algorithm, which anyway suits us fine to showcase the capabilities of HANA and its number-crunching abilities.


So, we evolved base of this solution on the premise of providing a unique identity to each Like which will result in a unique identity to each combination, then simply count them. In the above example, let us map each of the input likes to L1, L2, L3 and L4, then the resulting calculations would look like below. With this we have reached calculating the direct combination quite simply and quickly.

 

 

b5.png

Though this aggregation would give us the direct combinations of likes, the “hidden” likes needs to be found out: for example, if we have combinations of
{L1, L2,L3} = 10 occurrences
{L1, L2, L4} = 20 occurrences,
Then we have to effectively deduce that {L1, L2} = 30 occurrences. We overcame this problem using L-script within HANA with a binary comparison method.


Performance and Summary


The key advantage of this approach is performance when especially run on a flat data model without joins. In our experiments on a typical development box, for 1 million likes transactions can be completed under a second to calculate the matches and supports (with L-Script for hidden matches obviously taking most of the time).


Once we reach this combination, it’s only a matter of getting the right products used by this combination and parameterize it so it can be calculated for high support and/or a high match.


So, the key take-away at least for us is, we can really take the seemingly simple problems but extend their scope in such a way that the problem itself is as innovating as the solution that can be made possible on HANA.


Your thoughts are welcome especially around additional usecases around this space both functionally and technically which can enrich the marketing domain…

Git-HANA-Screenshot.jpg


Over the last few months, working on the metric² open source project, I have been frequently updating the GitHub repo. As a heavy XS Web IDE user, this entailed exporting or copying the contents of the files from the package into my local GitHub repository for the project and subsequently committing the files from there. Since there is a small disconnect between the source (my HANA packages) and the destination (GitHub) I like to often see what changes which are due to be committed, the differences between the files, or just compare the files between the 2 systems.


Being over dedicated to building solutions to some of my workflow challenges (see here, here and here), I created yet another small HANA native app called Git <> HANA. The application allows you to compare files between your local HANA package and your (or any other) GitHub repo, and it also lets you commit files directly from the UI to GitHub, and vice-versa. If a file does not exists, it will create it for you (on either side). There are a couple other cool features which you can read about below, or watch the little video I created.

 

If you are a web IDE user it's quick and convenient to use, and I am convinced it will make your HANA + GitHub integration easier (I am also hoping we will also see more open source native HANA apps on GitHub as a result!!!!)

 

 

Features of Git <> HANA

 

- Compare files between HANA and Github

- Compare inline or side by side

- Commit files from HANA to GitHub

- Commit/activate files from GitHub to HANA

- Repo/branch selection

- Native HANA application

- Easy HANA package installation

- Open source

- handles .xs* (e.g. .xsaccess, .xsapp) files (which your file system probably does not like!)

- Image comparison

- File browsing can be done via the GitHub repo or your HANA package

 

You can download the app package here (newsletter sign up requested so I can keep you up to date with the app) or check out the source files here.

 

If you think this would be helpful or would like to see any other features, or would like to contribute to the source ... EXCELLENT, please just let me know

 

Screenshots


  

 

Use the HANA Package Browser or GitHub repository as a reference.

 

 

Push files from HANA to GitHub or from GitHub to your local HANA package.

 

 

 

Compare Files side by side, or inline

 

 

 

 

Package Install Instructions

 

- Download the package

- Open Lifecycle manager (http://<HANA_SERVER>:PORT/sap/hana/xs/lm/)

- Click on Import/Export menu

- Click Import from File

- Browse to the downloaded file

- Edit the index.html file and specify your github username/password (or leave it blank and enter these details using the settings)

Workshop.gif

Are you looking to gain an in-depth understanding of the SAP HANA Platform?  Do you plan to build, integrate, and certify an application with SAP HANA?


SAP-HANA.gif

The SAP Integration and Certification Center (SAP ICC) will be offering partners and ISVs an introductory 4-day workshop (November 10-13, 2014) to facilitate a general understanding of SAP HANA.  After each training module, partners and ISVs will reinforce their skills via a series of hands-on exercises to demonstrate their knowledge of the various components for the SAP HANA Platform.  The SAP HANA Enablement Workshop will outline the underlying knowledge needed to allow for the development, integration, and certification of an application with SAP HANA.

 

By attending this enablement workshop, you'll be able to:

  • Understand the end-to-end native application development for SAP HANA
  • Reinforce knowledge with skill-builder modules via hands-on exercises
  • Understand the certification roadmap and process for application certification
  • Leverage a 30% discount for application certification to enable Go-to-Market
  • Engage with product experts and certification team via Q&A sessions

 

Registrations Fees and Deadlines:

 

Due to the popularity of this enablement workshop, seating will be limited and registration will be on a first-come, first-served basis.  If you would like to make a group booking, please submit separate registrations for each individual of your organization.

 

 

INDIVIDUAL REGISTRATION
REGISTRATION TYPESDATESFEESREGISTRATION
Early BirdBefore October 31, 2014$2,000.00 USDSign-up here!
RegularBefore November 10, 2014$3,000.00 USDSign-up here!
GROUP REGISTRATION - THIRD OR MORE GET A DISCOUNT
REGISTRATION TYPESDATESFEESREGISTRATION
Early BirdBefore October 31, 2014$1,500.00 USDSign-up here!
RegularBefore November 10, 2014$2,500.00 USDSign-up here!

 

Event Logistics and Agenda:

 

 

DATESMonday, November 10, 2014 to Thursday, November 13, 2014
TIME9:00 AM to 5:00 PM (Pacific)
LOCATION

SAP Labs

3410 Hillview Avenue

Palo Alto, CA 94304

Building 2 - Baltic Room

 

The agenda for this enablement workshop will highlight some of the following topics:

  • Introduction to SAP HANA Development Platform
  • SAP HANA Application Development Tools: SAP HANA Studio and Eclipse
  • Introduction to SQL Basics and Debugging
  • Introduction to SAP HANA Native Development
  • Introduction to Data Modeling with SAP HANA
  • Introduction to SAP Fiori Transactional Apps on SAP Business Suite on SAP HANA
  • How to Certify your Application with SAP HANA and SAP Fiori

 

Take advantage of this opportunity to plan, build, and explore the various certification programs for SAP HANA and leverage a 30% discount when submitting an application for certification with the SAP HANA Platform!  For any questions or inquiries relating to this enablement workshop, please contact icc-info@sap.com.

Actions

Filter Blog

By author:
By date:
By tag: