miércoles, 9 de septiembre de 2015

Amazon Alexa and SAP HANA

This post was originally posted on Amazon Alexa and SAP HANA.

If you are an SAP Employee, please follow us on Jam.

Amazon Alexa (Formerly Amazon Echo) is an awesome technology that allows you to give voice commands and get cool answers. Yes…it’s like Siri but from Amazon. Also, it doesn’t live on your cellphone, as Alexa is black cylinder packed up with seven microphones and very loud speakers.

We just get one for the d-shop almost a week ago…so of course…I needed to hack it and make it work with SAP HANA -;)

First, we need to create a Calculation View and call it “FLIGHTS_BY_CARRIER”. It will be composed of two tables, SCARR and SFLIGHT.

First, we need to create a Join object and link the table by MANDT and CARRID. From here select the following fields as output MANDT, CARRID, CARRNAME, PRICE and CURRENCY.

Then create an Aggregation object selecting the fields CARRNAME, PRICE (As Aggregated Column) and CURRENCY. Filter the CURRENCY field by ‘USD’.

Then create a Projection object and select only PRICE and CARRNAME.

On the Semantics object make sure to select “CROSS CLIENT” as the Default Client.

Now, switch to the SAP HANA Development View and create a new repository. Call it “Flights”.

Create a new “XS Engine” project and call it “Flights” as well. Link it to the “Flights” repository.

Create an empty “.xsapp” file.

Create a file called “.xsaccess” with the following code.

          "exposed" : true,
          "authentication" : [ { "method" : "Basic" } ]

Finally create a file called “flights.xsodata” with the following code

service {
          "Blag/FLIGHTS_BY_CARRIER.calculationview" as "FLIGHTS" keys 
                                                        generate local "Id";

Activate your project and launch it on your browser, you should see something like this…

The SAP HANA part is done…so we can move into the Amazon Alexa part…

I need to thank this blog post Alexa Skills Tutorial: The Definitive Guide to Coding for the Amazon Echo as it really helped me to write my own blog post -;)

In order to code for Amazon Alexa, we can use a new and awesome service from Amazon called Amazon Lambda…which basically allows you to define a NodeJS service that will run only when you need it to run…and also allows you to use the first million requests per month for free…

First…log into your Amazon Web Services Account and get into Amazon Lambda. Make sure you are on the North Virginia region…otherwise this will not work…

Once in there…create a new lambda function and select alexa-skills-kit-color-expert

Then, select the “Alexa Skills Kit” Event Source Type…

Then, we need to configure our function…meaning, we need to name it…

Leave the code entry type for later and choose a role…

Choose Basic execution role and a pop up will be shown…

Press the “Allow it” button at the bottom. Continue and finally press the “Create Function” button. A ARN number will be generated for your function, take note of it as we will need it on the next step.

Now, we need to go to http://developer.amazon.com and log in.

Choose Apps & Services --> Alexa --> Alexa Skills Set.

Press the “Add New Skill” button and enter the Name, Invocation Name (How are we going to call it from Alexa), version and the Endpoint (In this case, the ARN number from our Lambda Function)…

When we click on next, an Application Id number will be generated. Take note of it as we will need it on the coding part…

The Interaction Model section is very important as here we’re going to define the “Intent Schema” and “Sample Utterances”…the first will define the parameters that we’re going to send to Alexa and the second is how we are going to call our application.

Intent Schema
  "intents": [
      "intent": "GetSAPHANAIntent",
      "slots": [
          "name": "airline",
          "type": "LITERAL"
      "intent": "HelpIntent",
      "slots": []

Our variable is going to be called “airline” and it’s going to be a LITERAL…other types are NUMBER, DATE, TIME and DURATION. The intent is the method that we’re going to call in our code…

Sample Utterances
GetSAPHANAIntent get total amount for airline {American Airlines|airline}
GetSAPHANAIntent get total amount for airline {United Airlines|airline}
GetSAPHANAIntent get total amount for airline {Delta Airlines|airline}
GetSAPHANAIntent get total amount for airline {Lufthansa Airlines|airline}
GetSAPHANAIntent get amount for {Delta Airlines|airline}
GetSAPHANAIntent get amount for {Lufthansa|airline}
GetSAPHANAIntent get amount for {United Airlines|airline}
GetSAPHANAIntent get amount for {American Airlines|airline}
GetSAPHANAIntent get {United Airlines|airline}
GetSAPHANAIntent get {Lufthansa|airline}
GetSAPHANAIntent get {Delta Airlines|airline}
GetSAPHANAIntent get {American Airlines|airline}

HelpIntent help
HelpIntent help me
HelpIntent what can I ask you
HelpIntent get help
HelpIntent to help
HelpIntent to help me
HelpIntent what commands can I ask
HelpIntent what commands can I say
HelpIntent what can I do
HelpIntent what can I use this for
HelpIntent what questions can I ask
HelpIntent what can you do
HelpIntent what do you do
HelpIntent how do I use you
HelpIntent how can I use you
HelpIntent what can you tell me
HelpIntent how do i get an amount

The Test section is where we can test our application by sending a utterance like “get American Airlines”…we need to create our function code before we can test it…so don’t worry about this for now…

Forget about the Publishing Information section unless you really want to publish your application…

Let’s create a folder and call it “Alexa_HANA” or something nice like that…then create a folder called “src”. Copy the code from here and create a file called “AlexaSkills.js”

We’re going to need to install the node-rest-client package on our function, so do the following on a terminal

sudo npm install --prefix=~/Alexa_HANA/src node-rest-client

This will create a folder called “node_modules” with the package in our project folder…then create a file called “index.js” and copy and paste the following code…

var Client = require('node-rest-client').Client
  , AlexaSkill = require('./AlexaSkill')
  , APP_ID     = 'amzn1.echo-sdk-ams.app.XXX';

var error = function (err, response, body) {
    console.log('ERROR [%s]', err);

function toTitleCase(str)
    return str.replace(/\w\S*/g, function(txt){return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();});

var getJsonFromHANA = function(airline, callback){
 var sAirline = toTitleCase(airline);
 var options_auth={user:"SYSTEM",password:"YourPassword"};
 client = new Client(options_auth);
 client.get("http://YourServer:8000/Flights/flights.xsodata/FLIGHTS?$format=json&$filter=CARRNAME eq \'" + 
                    sAirline + "\'", function(data, response){
  if (data.d.results[0] != undefined){
   var Amount = data.d.results[0].PRICE; 
   var Amount = "Sorry I coudln't find that airline"; 
            callback("Sorry there was a connection error");

var handleHANARequest = function(intent, session, response){
  getJsonFromHANA(intent.slots.airline.value, function(data){
 var text = data;
    var cardText = 'Total sales are: ' + text;

    var heading = 'Total sales for: ' + intent.slots.airline.value;
    response.tellWithCard(text, heading, cardText);

var HANA = function(){
  AlexaSkill.call(this, APP_ID);

HANA.prototype = Object.create(AlexaSkill.prototype);
HANA.prototype.constructor = HANA;

HANA.prototype.eventHandlers.onSessionStarted = function(sessionStartedRequest, session){
  console.log("onSessionStarted requestId: " + sessionStartedRequest.requestId
      + ", sessionId: " + session.sessionId);

HANA.prototype.eventHandlers.onLaunch = function(launchRequest, session, response){
  // This is when they launch the skill but don't specify what they want.
  var output = 'Welcome to S A P HANA. ' +
    'Say an Airline Name.';

  var reprompt = 'Which Airline would you like?';

  response.ask(output, reprompt);

  console.log("onLaunch requestId: " + launchRequest.requestId
      + ", sessionId: " + session.sessionId);

HANA.prototype.intentHandlers = {
  GetSAPHANAIntent: function(intent, session, response){
    handleHANARequest(intent, session, response);

  HelpIntent: function(intent, session, response){
    var speechOutput = 'Get the total amount for any airline. ' +
      'Which Airline would you like?';

exports.handler = function(event, context) {
    var skill = new HANA();
    skill.execute(event, context);

Once is done, we will need to .zip the files…so go into your “src” folder and create a .zip file that include the “node_modules” folder, the AlexaSkills.js file and the index.js file…you can call the .zip file “SAPHANA.zip”.

Now, go back to your Amazon Lambda function and choose the Code tab. Then choose “Upload a .ZIP file” and press Upload. Choose your SAPHANA.zip file and then press the “Save” button.

With that…we should be ready to keep going -;)

We can now use the Test section of our Alexa Skill…

Once you know it’s working…you can test your function in your Amazon Alexa by saying…

“Alexa ask get hana”

And then “get American Airlines” or “get total amount for Delta Airlines”

Here’s a video to show you how it works -:)

As you can see…it’s actually pretty easy but there’s something you need to keep in mind…Alexa will not be able to recognize everything you say…so you need to be careful with your utterances…also…in SAP HANA we have “American Airlines” but Alexa will understand “american airlines”…that’s why I needed to capitalize the first letter as otherwise, both are different airline names -;)


Development Culture.