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. 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. – 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 – 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.

Addtional features:

This same Bot in a Telegram Bot

This same Bot in a Webchat Bot in Fiori Launchpad.