Start building your own chatbot now

In this tutorial you are going to learn how to establish an integration from your SAP Conversational AI chatbot to an HANA MDC Database.

First we are going to insert data into a HANA MDC Database and expose the table via an OData service. Afterwards we use SAP Conversational AI to connect a chatbot to our previously created HANA table. As a prerequisite you need an already created chatbot.

This exercise is splitted into 4 different parts:

  1. Create a new SAP HANA MDC Database
  2. Expose the table data via an OData service
  3. Create a chatbot on SAP Conversational AI
  4. Connect your chatbot to SAP HANA with a Webhook API

1. Create a new SAP HANA MDC Database

First, log into your SAP Cloud Platform Cockpit here.

Navigate to Persistence > Databases & Schemas.

Select the New button to create a new Database on your SAP Cloud Platform account.

hana-database

Type in following credentials:

  • Database ID: scpta
  • Database System: SAP HANA MDC (<trial>)
  • SYSTEM User Password: Keepitshortsweet1 (password must be 15+ characters, have 1+ lowercase, uppercase, and numeric)

Then keep all other parameters the same.

Click Save.

sap-cloud-platform-cockpit-database-schemas

After you click Save, you’ll see an Events screen. This describes the activity within your newly created SAP HANA database.

Select Overview from the left-hand side menu. This will allow you to access the SAP HANA Web-based Development Workbench. Notice your database is still being created. Please wait a few minutes until the CREATING indicator turns into STARTED.

In order to access the SAP HANA Web-based Development Workbench, you first must access the SAP HANA Cockpit, sign in using your new SYSTEM user and password.

sap-cloud-platform-cockpit

You will be alerted that you’re not authorized to access this page. Then you will automatically be assigned to the Admin roles and be brought to the Admin Cockpit Launchpad.

In order to access the SAP HANA Web-based Development Workbench, you will also need to assign the proper roles to your SYSTEM user. Start by opening the Manage Roles tile to access the Security screen.

Navigate to Users > SYSTEM. Then navigate to Granted Roles.

For each item below, select the + button and add the following roles:

  • sap.hana.admin.roles::Administrator
  • sap.hana.ide.roles::CatalogDeveloper
  • sap.hana.ide.roles::SecurityAdmin
  • sap.hana.xs.admin.role::SQLCCAdministrator
  • sap.hana.xs.ide.roles::Developer
  • sap.hana.xs.ide.roles::EditorDeveloper
  • Select Save and exit the Security screen.

sap-hana-workbench-security

You will now access the SAP HANA Web-based Development Workbench by selecting it beside the Developer Tools label on the scpta-Overview page.

Once open, select Catalog.

catalog-sap-hana-workbench

Once the Catalog is open, select the SQL icon from the toolbar on the upper part of the screen. This will launch an SQL scripting window.

sql-catalog-sap-hana-workbench

For this tutorial I created a table with following schema:

CREATE COLUMN TABLE "SYSTEM"."CUSTOMER"(
	"customerNumber" NVARCHAR(10),
	"firstName" NVARCHAR(50),
	"lastName" NVARCHAR(50),
	"email" NVARCHAR(50),
	"city" NVARCHAR(50),
	"state" NVARCHAR(50),
	"zip" NVARCHAR(10),
	"totalSpend" DECIMAL,
	"homeCountry" NVARCHAR(50),
	"specialization" NVARCHAR(60),
	"concentration" NVARCHAR(60),
	PRIMARY KEY (
		"customerNumber"
	)
);
COMMENT ON COLUMN "SYSTEM"."CUSTOMER"."customerNumber" is ' ';
COMMENT ON COLUMN "SYSTEM"."CUSTOMER"."firstName" is ' ';
COMMENT ON COLUMN "SYSTEM"."CUSTOMER"."lastName" is ' ';
COMMENT ON COLUMN "SYSTEM"."CUSTOMER"."email" is ' ';
COMMENT ON COLUMN "SYSTEM"."CUSTOMER"."city" is ' ';
COMMENT ON COLUMN "SYSTEM"."CUSTOMER"."state" is ' ';
COMMENT ON COLUMN "SYSTEM"."CUSTOMER"."zip" is ' ';
COMMENT ON COLUMN "SYSTEM"."CUSTOMER"."totalSpend" is ' ';
COMMENT ON COLUMN "SYSTEM"."CUSTOMER"."homeCountry" is ' ';
COMMENT ON COLUMN "SYSTEM"."CUSTOMER"."specialization" is ' ';
COMMENT ON COLUMN "SYSTEM"."CUSTOMER"."concentration" is ' ';

Copy this content in the file into the SQL window. This will create a table called CUSTOMER in your SYSTEM schema in the SAP HANA database, with customerNumber as the primary key.

Click Run.

Verify success with the log below the SQL window. If you wish to have the SQL code structured, select the Format Code icon.

sap-hana-workbench-sql-query

Once the table is created, we can now add data.

Clear your SQL scripting window or close it and open another window.

INSERT INTO "SYSTEM"."CUSTOMER" VALUES('340349','John','Murray','john.murray@test.com','Middle River','MD','21220',7700000,'Spain','Finance','LoB Finance');
INSERT INTO "SYSTEM"."CUSTOMER" VALUES('861474','Alex','Jones','alex.jones@test.com','Roswell','GA','30075',9400000,'Mexico','DSC Products','Manufacturing');
INSERT INTO "SYSTEM"."CUSTOMER" VALUES('341324','Sarah','Hasser','sarah.hasser@test.com','Grand Haven','MI','49417',1800000,'Pakistan','Business Intelligence / Predictive','BI Platform / Tools');
INSERT INTO "SYSTEM"."CUSTOMER" VALUES('340848','Peter','Dent','peter.dent@test.com','Billings','MT','59101',9300000,'Russia','Customer Engagement & Commerce','Marketing');
INSERT INTO "SYSTEM"."CUSTOMER" VALUES('339949','Amanda','Ronan','amanda.ronan@test.com','Randallstown','MD','21133',5700000,'India','Human Resources','not defined');
INSERT INTO "SYSTEM"."CUSTOMER" VALUES('338632','Benjamin','Krygsmen','benjamin.krygsmen@test.com','Franklin','MA','2038',6500000,'Australia','Human Resources','not defined');
INSERT INTO "SYSTEM"."CUSTOMER" VALUES('668939','Stephen','Parker','stephen.parker@test.com','Phillipsburg','NJ','8865',1400000,'Germany','DSC-Supply Chain',' Supply Chain Execution');
INSERT INTO "SYSTEM"."CUSTOMER" VALUES('861443','Brian','Guerrero','brian.guerrero@test.com','Midland','MI','48640',3100000,'Mexico','Cloud & Platform Technologies','SAP Cloud Platform');
INSERT INTO "SYSTEM"."CUSTOMER" VALUES('339623','Chris','Johnson','chris.johnson@test.com','Bronx','NY','10451',4000000,'KSA','DSC-Supply Chain','Supply Chain Execution');
INSERT INTO "SYSTEM"."CUSTOMER" VALUES('861193','Johanna','Benton','johanna.benton@test.com','Roseville','MI','48066',8100000,'Chile','DSC-Supply Chain','Supply Chain Execution');
INSERT INTO "SYSTEM"."CUSTOMER" VALUES('861475','Oliver','Sanchez','oliver.sanchez@test.com','Hernando','MS','38632',5500000,'Mexico','Customer Engagement & Commerce','Marketing');

Copy the content in the file into the SQL window. Adjust the data if you prefer.

Click Run.

Verify success with the log below the SQL window. Copy the content in the file into the SQL window.

sap-hana-sql

2. Expose the table data via an OData service

Navigate back to the SAP HANA Web-based Development Workbench and select Editor.

sap-hana-workbench-editor

First we will create a new package for exposing the customer table as an OData service.

Right-click on the top-level ‘Content’ folder and select New > Package.

sap-hana-workbench-editor

Name the package ‘customer’ and leave the remaining fields blank (Note: this is case-sensitive and it is recommended to be lowercase).

Select Create.

create-package-sap-hana

Each SAP HANA XS application must have an application descriptor file called .xsapp.

Right-click on the new package you created and select New > File and name the new file ‘.xsapp’.

Select Create.

If needed, replace the code with the code below:

{}

sap-hana-customer-new

create-file-xsapp

Note: If there is a green circle on the document icon next to .xsapp in the file explorer, this means the file isn’t activated. Activate the file by right clicking and selecting Activate. Or modify the file (e.g. add a space to end of file and remove) and resave. All files must be active before the data will be exposed properly.

The OData service definition is the mechanism you use to define what data to expose with OData, how, and to whom. Data exposed as an OData collection is available for display by client applications, for example, a SAPUI5 app. To expose information by means of OData to applications, you must define database views that provide the data in a .xsodata file.

Right-click on the new package you created and select New > File and name the new file ‘customer.xsodata’.

Select Create.

Replace the code with the code below.

service { "SYSTEM"."CUSTOMER" as "CUSTOMER"; }

Select Save.

sap-hana-customer-new-xsodata

Right-click on the new package you created and select New > File and name the new file ‘.xsaccess’.

Select Create.

Replace the code with the code below.

{ 
"exposed" : true, 
"authentication" : [ { "method" : "Basic" } ],
    "force_ssl": false,
    "enable_etags": true,
    "prevent_xsrf": false,
    "anonymous_connection": null,
    "cors": [{
      "enabled":true,
      "allowMethods": ["GET","POST","PUT"],
      "allowOrigin": ["*"],
      "maxAge":"3600"
    }]
}

Select Save.

sap-hana-customer-xsaccess

Your package should look like this when completed:

sap-hana-customer-package

Open the ‘customer.xsodata’ file.  A green arrow on the toolbar should appear. Select this button to run the OData service and view the service definition.

To view the entries in the table, add /CUSTOMER at the end of the URL.

Copy the URL to an incognito window to verify access.

sap-hana-editor-customer

sap-hana-odata

You can now use this OData-Service to create a CRUD-Application on the WebIDE in order to create, read, update and delete Data from the SAP HANA table.

3. Create your chatbot on SAP Conversational AI

First, create your account on SAP Conversational AI here.
If you’ve never used the platform before, you can read our documentation and follow this easy tutorial for beginners.

After that, you’re ready to create your first intent! An intent is a box of expressions that mean the same thing but are constructed in different ways. Intents are the heart of your bot’s understanding. Each one of your intents represents an idea your bot is able to understand.

Click on Create and give your intent the name “Customer”.

sap-conversational-ai-create-chatbot

Your chatbot should recognize that you want to get Customer Information from your CRUD-Application. Enrich your intent with different possible customer inputs.

chatbot-intent

Once your chatbot understands the intent, your chatbot has to react upon it. That’s why we have to give your chatbot a new skill.

Click on Create skill and give it a name.

create-skill-sap-conversational-ai

This skill triggers only when the previously created intent Customer is identified.

In the last part of this tutorial, we’ll learn how to connect this intent to your database, so you get the right information.

chatbot-building-sap-conversational-ai

4. Connect your chatbot to SAP HANA with a Webhook API

Now you can go back to your SAP HANA Web-based Development Workbench where we are going to create the API. We can see your package customer in the screenshot below.

Right-click on your package customer, click on new and create a new Package.

sap-hana-workbench-package

Name your new Package “Server”, and click on Create.

create-package-sap-hana

Create a new file within this new Package. Right-click on the package server, click on new and create a new File.

sap-hana-workbench-editor-file

Name your new File “getdata.xsjs“

Click on create.

create-file-sap-hana

Your package should look like this:

getdata-server-sap-hana

Copy following code and paste it into your getdata.xsjs file:

function doGet() {
	var attendanceID2 = $.request.body.asString();
	var essai = attendanceID2.toString().replace("\r", "").replace("\n", "").replace("\t", "");
	essai = JSON.parse(essai);
	var typeinyourrequestparameter=essai.typeinyourrequestparameter;
	var output;
	var body;
	var resultSet = null;
	var results = [];
	var result;
	var columnCount;
	var columnName;

	var dbQuery = 'SELECT *FROM "typeinyourdatabase"."typeinyourtable" WHERE "typeinyourrequestparameter"=' + "'" + typeinyourrequestparameter.toString() + "'";

	var connection = $.db.getConnection();
	var statement = null;
	statement = connection.prepareStatement(dbQuery);
	resultSet = statement.executeQuery();
	while (resultSet.next()) {
		result = {};
		columnCount = resultSet.getMetaData().getColumnCount();

		for (var i = 0; i < columnCount; i++) {
			columnName = resultSet.getMetaData().getColumnName(i + 1);
			result[columnName] = resultSet.getNString(i + 1);
		}
		results.push(result);
	}
	//output = results[0];
	output = {
		replies: [{
			type: 'text',
			content: "Displayparameter1 : "+results[0].Parameter1 + "\nDisplayparameter2 : "+results[0].Parameter2 + "\nDisplayparameter3 : "+results[0].Parameter3 + "\nDisplayparameter4 : "+results[0].Parameter4 
			+ "\nDisplayparameter5 : "+results[0].Parameter5+ "\nDisplayparameter6 : "+results[0].Parameter6 + "\nDisplayparameter7 : "+results[0].Parameter7+ "\nDisplayparameter8 : "+results[0].Parameter8+ "\nDisplayparameter9 : "+results[0].Parameter9+ 
			"\nDisplayparameter10 : "+results[0].Parameter10+ "\nDisplayparameter11 : "+results[0].Parameter11 
                                }],
		conversation: {
			memory: results[0]
		}
	};

	var output2 = {
		"replies": [
			{
				"type": "text",
				"content": results[0]
    }
  ],
		"conversation": {
			"language": "en",
			"memory": {
				"user": "Bob"
			}
		}
	};
	body = JSON.stringify(output);
	$.response.contentType = 'application/json';
	$.response.setBody(body);
	$.response.status = $.net.http.OK;

}
doGet();

Now you have to do some modifications in your code:

• Typeinyourrequestparameter= customerNumber
• Typeinyourdatabase= SYSTEM
• Typeinyourtable = CUSTOMER
• Displayparameter1 = Customer Number
• Parameter1 = customerNumber
• Displayparameter2 = First Name
• Parameter2 =firstName
• Displayparameter3 = Last Name
• Parameter3 = lastName
• Displayparameter4 = E-Mail
• Parameter4 =email
• ….

The Displayparameter will be shown in your Chat and the actual Parameter pulls the referring value from the database. That’s why it need to be the exact same word from your table.
Go to your ODATA-Service where you can see the rest of the Parameters:

odata-service-parameters

After typing all remaining values into your code, click on Save.

Your code should look like like this:

code-getdata

Click on the Run-Button (F8) and you should get the link for your Webhook, which should look like this:

https://testscptad064933trial.hanatrial.ondemand.com/customer/Server/getdata.xsjs

https://{HANADB}{accountName}.hanatrial.ondemand.com/customer/Server/getdata.xsjs

Copy the Link for your Webhook which will be used for your chatbot.

Go back to your previously created Skill in the SAP Conversational AI platform.

Before your chatbot can give you the right information about your customer from your database, it needs to know which customer you are talking about.

That’s why we need to set up Requirements.

sap-conversational-ai-requirements-chatbot

As a requirement you have to give your chatbot the number of the customer. Set the entity as #number and call it identification.

Click on +New replies  if the reply is missing

chatbot-replies

Here we are going to send a message to the User to ask for the customer number. Click on Send Message and Set the message as “Can you give me the Customer Number?”. Click on SAVE.

customer-number-chatbot

Go back to requirements and click on +New replies if the reply is complete:

chatbot-sap-conversational-ai

Since the chatbot knows which Customer the user is talking about, we can call a webhook that interacts with the database and pulls the right information.

Click on Call Webhook.

call-webhook-chatbot

In the POST Function paste the link of the API that you created in the HANA Web-based Development Workbench.
Set the authentication type as Basic authentication and type in the credentials of your database (SYSTEM, Keepitshortsweet1)

Click on Body.

authentification-api-call-sap-hana

In order match the user input from the requirement to the Customer Number in the database, we have to create the following memory:

{
"customerNumber": {{memory.identification.raw}}
}

customer-number-webhook-chatbot

Click on Save.

After creating the Webhook we can test the Chatbot to see if it works.

Just take any Customer Number that you have created in your database or CRUD-Application.

The result should look like this:

chatbot-customer-information-database

Congratulations! 👏

Better visibility for business insights

As a result of this exercise you are able to connect any chatbot to an SAP HANA database.

Thanks to the Natural Language Processing technology of Conversational AI and the various services hosted in the SAP Cloud Platform, you can build enterprise-level chatbots that reach out to an SAP backend system.

In order to extend this exercise, you can use the OData-Service to create a CRUD-Application on the SAP Web IDE to create, read, update and delete data from the SAP HANA table. By using a webchat, you can interact with your database within the application in a conversational way.

If you want to try other tutorials, feel free to visit this page! 🤖

Want to build your own conversational bot? Get started with SAP Conversational AI !

Follow us on
  • Boudhayan Dev

    How would you handle this in production ? If I know the Customer Number of someone else, I can view their information . Does CAI provide SAML based authentication ?

  • Anonymous

    Hi, i need help, please. I can’t connect my chatbot to SAP HANA with a Webhook API. I created getdata.xsjs, copied code and did modifications. But i click run and get error 500 – Internal server error An error occurred during the processing of your request. Data might be lost.

  • Anonymous

    Hi, i need help. I can’t connect chatbot to SAP HANA with a Webhook API. I created file getdata.xsjs and did modifications but i click run and got error 500 – Internal server error An error occurred during the processing of your request. Data might be lost.

  • Ramil

    Hi, i need help. I cant connect your chatbot to SAP HANA with a Webhook API. I created file getdata.xsjs, did modifications, but click run and got error 500 – Internal server error An error occurred during the processing of your request. Data might be lost.

    • Anonymous

      Hey Ramil,
      have you checked if your Database schema is available? Since you have a trial account the database shuts down every day and you have to reactivate it.

      • Rob

        I have the exact same problem, and then the chatbot cannot retrieve the data.

      • Rob

        I am having the exact same issue, and then the chatbot cannot retrieve the data.

  • Anonymous

    Hey Ramil,
    have you checked if your Database schema is available? Since you have a trial account the database shuts down every day and you have to reactivate it.

  • papabobaichatbot

    Best blog ever.
    I am exciting to see your next blog.
    perangkat lunak chatbot ai untuk bisnis

  • urmilesh kumar

    Hi,
    When i edit file getdata.xsjs it gives syntax error. please give final code of getdata.xsjs.

    thanks

  • Jacob Yusuf

    Great work with this amazing tool.

  • Anonymous

    I am Stuck in this step help me please>>>
    Now you have to do some modifications in your code:

    • Typeinyourrequestparameter= customerNumber
    • Typeinyourdatabase= SYSTEM
    • Typeinyourtable = CUSTOMER
    • Displayparameter1 = Customer Number
    • Parameter1 = customerNumber
    • Displayparameter2 = First Name
    • Parameter2 =firstName
    • Displayparameter3 = Last Name
    • Parameter3 = lastName
    • Displayparameter4 = E-Mail
    • Parameter4 =email
    • ….

  • Rob

    What is the issue?

  • Anonymous

    Hi,
    When i edit file getdata.xsjs it gives syntax error. please give final code of getdata.xsjs.

    thanks

  • Anonymous

    An error occurred during the processing of your request.
    Data might be lost.

  • Anonymous

    If I built the hana database On-Premise hana instance, can I connect to the bot which i build in SAP Conversational AI?

  • Lynn

    Also having the same issue with the OData service creation. Is there a remedy to the solution? I don’t know why we need to modify the getdata.xsjs if all the modifications are the same.

  • James

    This works for me, but when I try to connect it to my own Hana DB I am getting an error.
    I am getting the same error as a few people above:

    Error while processing request to Database – getdata.xsjs: Request failed with status code 500
    An API error has been detected on https://sapsportsonejc2gjrpgcs.hana.ondemand.com/matches/Server/getdata.xsjs

    Has anyone found a workaround for this?

  • Manuel

    Hey Guys,

    if you receive an error on your API please make sure that your database schema is available. Since you have a trial account the database shuts down every day and you have to reactivate it.

    It is also normal that you receive an error if you run your API on the Web-based Development Workbench. If your simply run it there without the requested body you will receive 500 – Internal server error – Thats normal
    The API needs the body that comes from your Chatbot. You either create a Webhook in CAI that has the right body or you can test your API in Postman with a custom body like this one:

    {
    “customerNumber”: 338632
    }

    One thing I noticed, is that when editing the webhook the changes were not being saved, so CAI would never work even though you have entered correct details in your webhook. If you want to change an already created webhook you need to delete it and create a new one.

    Currently in the tutorial that I created, you can just detect numbers in the requirements. You can not process Strings.
    In order to solve this issue I recommend to use the default body in your webhook instead of a custom body.
    In addition you should change following line in your API-Script:

    var typeinyourrequestparameter=essai.typeinyourrequestparameter;

    to

    var typeinyourrequestparameter1=essai.nlp.entities.typeinyourrequestparameter2[0].raw;

    Typeinyourrequestparameter1 should refer to the name of your column in your database.
    Typeinyourrequestparameter2 should refer to how CAI detects the information as an entity, like number, datetime, location etc..

    In my case it would be:
    var customerNumber=essai.nlp.entities.number[0].raw;

    I hope this helps 🙂

    Best,

    Manuel

  • Pedro Millán

    Thanks Manuel! It worked perfectly. Very well explained and easy to follow.

    I love it. Hope you have some more ideas 🙂

    Best,
    Pedro.

This site uses Akismet to reduce spam. Learn how your comment data is processed.