Friday, 29 March 2019

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.

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

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:

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

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

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

Select predefined skills for your bot

For this sample we will use just “Greetings” skill.

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

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

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

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…

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

For this case, choose existent intents “Greetings” and “Goodbye”.

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

Create a new Intent (e.g. ask-customer)

Click on “+ Create” and input Intent name as bellow:

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

And click on “Create Intent”.

Input expressions in your new Intent

Click in your new intent:

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

Input new expressions like that:

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

Put the maximum expressions that you could (SAP recommends about 30-40 expressions)

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

Build

In tab Build create a new skill

Click on “+ Create Skill” and input a name for your skill (e.g. “ask-customers”).

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

And click on “Create Skill”.

Click in you new skill and after, goes to tab “Triggers”

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials


 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

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

In “Requirements” tab, input fill as follow:

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

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

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

In the next screen, select “Send Message”, after select “Text” and input a message asking for a location, and click “Save”:

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

Click “Back”.

In “Actions” Tab click on “Add new message group”

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

It’s important to know that “Actions” just executed when all requirements are met.

Click on “Call Webhook”.

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

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)

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

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.

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

Insert the expression “START_CONVERSATION”

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

Create a Skill start-conversation

In Build tab, create a new skill (e.g. start-conversation), and triggers this skill with intent @start-conversation

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

Create a Skill Goodbye

Create a new skill (e.g. goodbye), and triggers this skill with intent @goodbyes

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

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

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

Fill field “Set Memory Field” with END_CONVERSATION and value equals TRUE

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

Ans save it.

Connect with Alexa account

To connect with your Alexa account is very simples, go to “Connect” tab in your bot

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Certifications, SAP HANA Study Materials

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.

No comments:

Post a Comment