Skip to Content
Technical Articles

Chatbot with Alexa + SAP Conversational AI + SAP Hana

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.formatted}}

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

This same Bot in a Webchat Bot in Fiori Launchpad.

 

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!

9 Comments
You must be Logged on to comment or reply to a post.
  • Hello, Fernando!

    I have a doubt, I don’t know if you can give me a hand: the basic authentication is not working for me. I use the “PUSER” PXXXXXXXX and my SCP password. Without authentication it works for me. What password do I really have to set?

    Thank you in advance

    • Hello Pablo, how you doing?

      In this case you must use the password that you set in your Hana DB, user and pass created in Hana DB to acess content.

      I hope it can help you.

      Att,

      Pena.

  • Hi Fernando,

    Nice blog! Can you help me with one question? If I want to actually use the skill on an Echo device I need to submit the skill for certification. When doing this, the certification tests ask me to fix some issues related to securing the end-point, for example:

    “The skill end-point is not validating the signatures for incoming requests and is accepting requests with an empty signature URL.”

    Without this they wont allow me to publish the custom skill and use it on my device. Is there any way to fix this?

    Thanks and regards!

    Leonardo.

    • Hello Leonardo, how you doing?

      Good question…

      Actually, I didn’t try to publish this skill, because it was just a POC, so I´ve never seem this issue before… Unfortunately, I can’t help you with it… but I will search for a solution too…

      Sorry…

      Pena.