Skip to Content
Technical Articles
Author's profile photo Fernando Pena

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!

Assigned Tags

      17 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Ricardo Sarmento
      Ricardo Sarmento
      Congratulations! Simple, didactic and powerful!
      Author's profile photo Raquel Pereira da Cunha
      Raquel Pereira da Cunha

      Thanks Fernando for sharing the demo and details with the Community here and in SAP Inside Track São Paulo last Saturday. 🙂

      Author's profile photo Anderson Souza
      Anderson Souza

      Very good! Congratulations!

      Author's profile photo Igor Checan
      Igor Checan

      Very good!

      Author's profile photo Nicholas Checan
      Nicholas Checan

      Awesome content! I cant wait to test it!

      Author's profile photo Pablo Castaño Santiago
      Pablo Castaño Santiago

      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

      Author's profile photo Fernando Pena
      Fernando Pena
      Blog Post Author

      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.

      Author's profile photo Leonardo Gomez
      Leonardo Gomez

      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.

      Author's profile photo Fernando Pena
      Fernando Pena
      Blog Post Author

      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.

      Author's profile photo Florence Mae Guzon
      Florence Mae Guzon

      Hi

       

      I'm trying to create a bot and connect it to my API proxy in the SAP Cloud Platform. I tried to follow the creation of bot in this document and put my API proxy URL to the "call webhook". However, it didn't seem to get the data from my Cloud platform. Have you tried this setup before?

      Author's profile photo Fernando Pena
      Fernando Pena
      Blog Post Author

      Hi Florence Mae Guzon,

       

      Sorry for my delay in this answer...

      So, you must to expose your API proxy. The process in  SCP may be different in this case, and maybe you need to use a Destination or something like that to expose your service.

      Hope this helps.

      Regards,

       

      Pena.

      Author's profile photo Abhishek Sharma
      Abhishek Sharma

      Hi Fernando Pena

      Really nice blog... wanted to try this out..

      I have question - can I use normal oData service instead of HANA which you used ?

      My main goal is to make a very simple search customer screen (automate fiori / SAPUI5 screen) through chat bot just same like your app is doing..

       

      Thanks-

      Abhishek

      Author's profile photo Fernando Pena
      Fernando Pena
      Blog Post Author

      Hi Abhishek Sharma, thanks for you comment...

       

      Sure, you can use a normal oData Service, no problem, but you must to build the response in the exactly json format.

      You can find the exactly format in this documentation:

      https://cai.tools.sap/docs/concepts/code-and-webhook

       

      If I can help you with something more, please let me know.

       

      Regards,

       

      Pena.

      Author's profile photo Abhishek Sharma
      Abhishek Sharma

      Hi Fernando Pena

      Thanks much for your guidance… Now I am ready with my gateway service and CAI Chatbot.. both are connected and I am able to see backend data in CAI bot…

       

      I would need your guidance on how you are updating SAPUI5 application through chatbot ? I mean when you say how may customers in Brazil ? How Search bar of SAPUI5 application is getting updated with search term ?

       

      I also downloaded code from gitHub but I don't see how you have added chatbot to custom UI5 application.

       

      Please guide…

       

      Thanks-

      Abhishek

      Author's profile photo Abhishek Sharma
      Abhishek Sharma

      Hi Fernando Pena

      Posting help request again 🙂

       

      Thanks much for your guidance… Now I am ready with my gateway service and CAI Chatbot.. both are connected and I am able to see backend data in CAI bot…

      I would need your guidance on how you are connecting bot with SAPUI5 application I mean when you say how may customers in Brazil ? How Search bar of SAPUI5 application is getting updated with search term ?

      I saw from CAI you are making call to xsjs file. but I didn't find how its response connected with SAPUI5.

       

      I also downloaded code from gitHub but I don’t see how you have added chatbot to custom UI5 application.

       

      Please guide…

       

      Thanks-

      Abhishek

      Author's profile photo Fernando Pena
      Fernando Pena
      Blog Post Author

      Hello Abhishek Sharma

      Sorry for delay in this answer...

       

      So, in SAPUI5 development we used a Websocket connection to read a table in Hana (With actual country asked by user), with this connection is possible to apply a filter by country. Or you can refresh page with JS by time, and read the content from this table and use this register to apply this filter.

      It helps you?

      Author's profile photo Abhishek Sharma
      Abhishek Sharma

      Hi Fernando Pena

      Thanks much for response, but I still didn't get how the country name you say is getting updated in SAPUI5 search field..

      I understood as we get this value in search field we can trigger call to backend and fetch data and display in SAPUI5 application.

       

      Below which I have developed :

      SAP CAI bot <-> NodeJS <-> SAP NW Gateway Service

       

      I need your guidance to know, from where that search term is going to SAPUI5 application ?

       

      Thanks-

      Abhishek