Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
Manuel_Namyslo
Product and Topic Expert
Product and Topic Expert
In this tutorial, you are going to learn how to establish an integration from your SAP Conversational AI chatbot to a HANA MDC Database.

First, we are going to insert data into a HANA MDC Database and expose the table via an OData service. Afterward, 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 split 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 in to 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.


Type in the 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.


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.


 

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.


 


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.


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.


 

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.


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.


 

2. Expose the table data via an OData service


Navigate back to the SAP HANA Web-based Development Workbench and select 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.


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

Select Create.


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:
{}



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 space to the end of the file and remove it) 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.


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.


Your package should look like this when completed:


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.



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


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


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.


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.



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.


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


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


Name your new File “getdata.xsjs“

Click on create.


Your package should look like this:


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 needs to be the exact same word from your table.
Go to your ODATA-Service where you can see the rest of the Parameters:


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

Your code should look like this:


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.


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


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.


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


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.


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.


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


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:


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.




For more information about SAP Conversational AI: