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.
.xsaccess |
---|
{ “exposed” : true, “authentication” : [ { “method” : “Basic” } ] } |
Finally create a file called “flights.xsodata” with the following code
flights.xsodata |
---|
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…
index.js |
---|
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; }else{ var Amount = “Sorry I coudln’t find that airline”; } callback(Amount); }).on(‘error’,function(err){ 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?’; response.ask(speechOutput); } }; 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 -;)
Nice blog! I might try it myself. I do have one question though. You mentioned:
What do you mean by that?
That means that unless Amazon changed something...only the North Virginia region (servers) will work for Alexa skills and testing creation 🙂 Use another region and there's no guarantee that it will work...
Greetings,
Blag.
Development Culture.
Nice blog:-) Thanks
I have followed all above steps, when say Ask get HANA it will reply me back with welcome message but when i ask question further , it is throwing an error while executing the service as "Error: getaddrinfo ENOTFOUND".
I try to run the service in other simple application it works fine but here i am unable to run it. Can you please help?
And do u have any idea how can i debug the code.
Thanks,
Nikhil
Sorry for the late reply...
You need to check that the address for the XSEngine app is right...also check for the username and password...that error means that a successful connection cannot be established...
When it comes to debugging...I'm not sure...don't think you can do that other than simply checking the logs...but I usually do is to test it locally...then upload it to Lambda...
Greetings,
Blag.
Development Culture.
Hey ´Alvaro,
Thank you! I was wondering what the Alexa_HANA folder does for you exactly?
I understand all of your js code and have a better idea of what I would write for my function but am not sure where the Alexa_HANA files come in.
Thank you
-Diana
Hello Diana:
That folder is just a container, for organizational purposes 🙂 You simply need to .zip the contents of the folder and not the folder itself -;)
Greetings,
Blag.
Development Culture.
Whoops, sorry! I guess all you need is the node-rest-client folder, along with the alexa sdk folder. Is that right?
I currently have an xsjs service that I want to call to get back a JSON file. I am thinking about using the 'Client' object as you've done so in your index.js file. Would that work? I think I'd need to change the way I read the results.
Hello Alvaro,
Thanks for the nice blog 🙂
I was also trying to do the same but struck at some point. The request still shows american airlines in small letters. Also i am getting the response error as "The remote endpoint could not be called, or the response it returned was invalid."
Also, while uploading the .zip file in the lambda function, it was asking for a handler and getting some warning message. Could you please help me in this regards.
Hello Asif:
Regarding the small letters...don't worry...that's how Alexa will understand it...the endpoint problem might because...how are you zipping your files?
Are you zipping the whole folder or just the contents?
What you should do is grab index.js and node_modules and create a .zip file so when Lambda tries to open the .zip it can find files instead of a folder...that's a common mistake that I made when I began coding for Alexa 🙁
If that's not the issue...please let me know...
Greetings,
Blag.
Development Culture.
HI,
I am not able to connect to data base below is the Error im getting "Sorry there was a connection error" .
I have added the index.js code snapshot below as well. Can you please help me on this.
Response :
Response :
{
"version": "1.0",
"response": {
"outputSpeech": {
"text": "Sorry there was a connection error",
"type": "PlainText"
},
"card": {
"content": "Total sales are: Sorry there was a connection error",
"title": "Total sales for: american airlines"
},
"speechletResponse": {
"outputSpeech": {
"text": "Sorry there was a connection error"
},
"card": {
"content": "Total sales are: Sorry there was a connection error",
"title": "Total sales for: american airlines"
},
"shouldEndSession": true
}
},
"sessionAttributes": {}
}
Thanks and Regards,
Mohsin