Introduction
In this bog you will learn how to create a Bot in SAP Conversational AI integrated with Alexa (using an Echo Dot 3rd gen) to get data in SAP Hana database.
Fiori application that was used here, is just to show all information that we are asking to Bot and give a visual feedback to user. Our focus here is not in Fiori development, but if you would like to see how this app works or use it for test, it’s available here. (GitHub link). Special thanks to Danilo Jacinto that developed this app.
This video shows how our POC works:
This video is about this POC working in a presentation in SAP Inside Track São Paulo, Brazil, realized in March 23, 2019.
In the end of this blog I’ll show a video with this same bot working in Telegram and Webchat in Fiori Launchpad.
Pre-Requisites
For this development you will need to do the steps above:
- Create a free trial account in SAP Cloud Platform. (link here) – We used Neo Region, but you´re free to use anyone.
- Create a free account in SAP Conversational AI.
- Do this first tutorial in SAP CAI (It’s important to understand how to create a bot, and it’s very simple and fast) (link)
- Create an Amazon Alexa Developer account (Link here) – If you don’t have an Alexa device (e.g. Echo Dot, Echo Spot, etc.) don’t worry, you can do these tests in a simulator available in Alexa Developer cockpit.
Step-by-step
Create a new Hana database, tables and XSJS
Access your SAP Cloud Platform trial, select your region and follow this steps:
In “SAP HANA/ SAP ASE > Databases & Schemas”, create your Hana MDC database and setup all passwords for your accounts.
Start you Hana database, open SAP HANA Web-Based Development Workbench, or connect with Eclipse if you prefer.
Create a new Schema and a new table in Hana as follow:
namespace alexa.data;
@Schema: 'alexa'
context customer_data {
type SString : String(40);
type LString : String(255);
@Catalog.tableType : #COLUMN
Entity Customers {
key ID: Integer;
NAME: SString;
COUNTRY: SString;
DESCRIPTION: LString;
};
};
Create a new XSJS file (e.g. get_customers.xsjs), this service will be responsible to get data in your table e send data back to your Bot, in a Webhook that we will create soon.
//Get parameter "location"
var locl = $.request.parameters.get("location");
//Open connection with DB
var conn = $.db.getConnection();
var pstmt = null;
var rs = null;
pstmt = conn.prepareStatement('SELECT COUNT(*) FROM "alexa"."alexa.data::customer_data.Customers" WHERE "COUNTRY" = \'' + locl + '\' ');
try {
//Execute the query
rs = pstmt.executeQuery();
while (rs.next()) {
var tot = rs.getString(1);
}
pstmt.close();
} catch(e) {
$.response.setBody(e.message);
$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
}
if (tot < 1) {
var resp = "Location not found";
} else {
resp = "In " + locl + " we have " + tot + " customers";
}
conn.close();
//You have to return a JSON exactly as described in documentation
var output = JSON.stringify({ "replies": [{"type": "text", "content": resp}], "conversation": { "language": "en"}});
//Return the HTML response.
$.response.status = $.net.http.OK;
$.response.contentType = "application/json";
$.response.setBody(output);Important, the response must be a json format, with the following format (this format is in SAP CAI Documentation:
Save the URL for your XSJS, as follow:
https://hanadb-xxxxxxxxx.hanatrial.ondemand.com/alexa/get_customers.xsjs
How to create a Bot in SAP Conversational AI?
If you did the tutorial available in CAI (How to create your first bot) you know how many simple is to create a Bot using CAI. But here I´ll show step-by-step how to create a simple Bot to consume data in SAP Hana database using a XSJS service.
Access your account in SAP CAI and Create a new Bot
Select predefined skills for your bot
For this sample we will use just “Greetings” skill.
Informations about your bot
Here you must input you bot name (e.g. hanabot), fill the description and Topics if you want (e.g. external-customers).
- Choose your options for “Data Policy” and “Bot visibility”.
- And click on “Create a Bot”.
Train
In tab “Train” you have to create or search for intents…
For this case, choose existent intents “Greetings” and “Goodbye”.
Create a new Intent (e.g. ask-customer)
Click on “+ Create” and input Intent name as bellow:
And click on “Create Intent”.
Input expressions in your new Intent
Click in your new intent:
Input new expressions like that:
Put the maximum expressions that you could (SAP recommends about 30-40 expressions)
Build
In tab Build create a new skill
Click on “+ Create Skill” and input a name for your skill (e.g. “ask-customers”).
And click on “Create Skill”.
Click in you new skill and after, goes to tab “Triggers”
In “Triggers” tab, fill field “If” with @ASK-customer (It means that all times that the intent “ask-customer” happens, skill “ask-customers” will be triggered. Click Save
In “Requirements” tab, input fill as follow:
It means that when user speaks some “location”, bot will save this information in memory, as “location”.
And just start actions for this skill if requirements are complete. Otherwise we must set up an action if location is missing.
Click on arrow on right side of location and click on “+ New Replies” in the same line that is write “if #location is missing”.
In the next screen, select “Send Message”, after select “Text” and input a message asking for a location, and click “Save”:
Click “Back”.
In “Actions” Tab click on “Add new message group”
It’s important to know that “Actions” just executed when all requirements are met.
Click on “Call Webhook”.
Fill with URL for your XSJS file, as follow:
https://hanadbp-xxxxxxxx.hanatrial.ondemand.com/alexa/get_customers.xsjs?location={{memory.location....
For this case, we use “Basic Authentication” (That’s must to be configured in your database)
And click on “Save”.
This webhook will receive exactly the json format that XSJS response, and bot uses this content to show an answer!
Connect your Bot with Alexa
Create an Intent START_CONVERSATION
Create a new intent (e.g. start-converstion), it will be responsible for start conversation when alexa call the skill.
Insert the expression “START_CONVERSATION”
Create a Skill start-conversation
In Build tab, create a new skill (e.g. start-conversation), and triggers this skill with intent @start-conversation
Create a Skill Goodbye
Create a new skill (e.g. goodbye), and triggers this skill with intent @goodbyes
Set a variable END_CONVERSATION
In actions tab for skill goodbye, set a flag in variable END_CONVERTSATION. It´s important to end the skill in Alexa app.
In actions, click on “ADD New Message Group”, and click on “Update Conversation”, e selecione “Edit Memory”.
Fill field “Set Memory Field” with END_CONVERSATION and value equals TRUE
Ans save it.
Connect with Alexa account
To connect with your Alexa account is very simples, go to “Connect” tab in your bot
Select “Amazon Alexa” option, and follow the steps to connect, you just need to inform your Amazon account and create an invocation name.
Done, now your Alexa device or simulator in Alexa developer cockpit can be used to retrieve information from Hana database.
Important!
This step-by-step was simple to show how to create and connect your bot with Alexa and Hana, the complete development from Bot, Hana and Fiori you will find bellow (All developments in my GitHub😞
All Hana developments (Tables, XSJS, etc.) that I used in this demonstration (Video) is uploaded in GitHub.
Bot used here, you can find it in this link.
And you can find Fiori App here.
Addtional features:
This same Bot in a Telegram Bot
https://youtu.be/C8lS5WX7MTA
This same Bot in a Webchat Bot in Fiori Launchpad.
https://youtu.be/n4E-GTgQw1Y
Conclusion
Now you can see how powerfull is this tool, with these simple steps you can create a Bot that connects with a Hana service and integrate with Alexa. And you didn`t develop anything in Alexa, all integregation was done by Conversational AI.
Other important point is, you can use this same bot, you can develop only one bot, a powerfull one, and connect it with a lot of channel as you can see in "Connect" tab. In the other side you can get information from any place that has a service available.
I hope you enjoy this tool, and if you have any comments, sugestions or doubts please let me know.
Thanks Marcelo Vieira and Ricardo Rabay for all ideas and support!
- SAP Managed Tags:
17 Comments
