Skip to Content
Technical Articles
Author's profile photo Manuel Namyslo

Connect your Chatbot to a HANA Database with SAP Cloud Application Programming Model

In this tutorial, you are going to learn how to establish an integration from your SAP Conversational AI chatbot to a HANA Database by using SAP Cloud Application Programming Model. This tutorial can be used for PoC’s or prototypes in order to validate a backend connection to your chatbot and to integrate some sample-data to your bot.

As a prerequisite you need access to a Business Technology Platform Account which can be realized by using the Trial.

Additional information about SAP Business Technology Platform can be found here:

This exercise is split into 2 different parts:

  1. Create a new SAP HANA Database on SAP Business Application Studio
  2. Integrate your Chatbot to your database with SAP Conversational AI

 

Part 1: Create a new SAP HANA Database on SAP Business Application Studio

 

The Landing Page can be accessed with the URL  First, log in to your SAP Cloud Platform Cockpit here.  On the Landing Page, click on the SAP Business Application Studio Button.

 

 

You should see a screen where you can create a new Dev Space. In the new screen, click the Create Dev Space button and enter a name for your Dev Space – I used ,,DevelopmentSpace”: Select Full Stack Cloud Application below and click Create Dev Space. From here, you will select the SAP Cloud Business Application Template and provide a name for your Dev Space.

Wait until the status changes from Starting to Running, then click on the tile with the Dev Space name.

 

After 1-2 minutes, you should see the status of your creation change from started to running which means your Dev Space is now ready.
In the background, the Dev Space has been prepared with all of the necessary components that you would otherwise have to install on your laptop. For example, Node JS, CDS, etc.
Once the Dev Space, says created, you will see that the Dev Space Name becomes clickable (far left of screen next to the cloud symbol).

After clicking on your DevSpace select the CAP Project (SAP Cloud Application Programming Model) project and click Start. Provide a name for your project and Select the Configuration for SAP HANA Deployment check box. Click on Finish.

 

 

Behind the scenes, your project will be generated. Once complete, the screen will return and you might see a pop-up message box in the bottom right to open a workspace with your project. If it’s not open yet, you need to open the workspace with this pop-up to start work on your project. If the popup appears in the right corner of your screen, click on the Open in New Workspace button, in the pop up. The editor will open in the context of a new workspace and now you can begin working on your project. However, you will likely see in the bottom of the screen a message that says that the Space has not been set with Cloud Foundry.

In the Workspace you need to login to your Cloud Foundry Account. Therefore, go to the headerbar of your screen and click on View. Select Find Command from the drop down. From here you will look for CF Login to Cloud Foundry. After selection the CF Login, the API endpoint should be automatically inserted into the text box. Next, you will be prompted for user credentials and selecting the dev space within your trial-account.

 

Create your CDS datamodel:

From the file structure on the left, you will navigate to the DB folder and you will create a new file called schema.cds. The name of the file can theoretically be anything but for consistency, use the name we suggest so future copy/paste operations will work.

  1. Right click on the db Folder in your project structure.
  2. In the new dialog,select New File from the menu and enter schema.cds as file name
  3. Open the newly generated file in the folder.
  4. Enter following script, to create your first CAP datamodel structure:

 

namespace scp.cloud;

using {  managed,
 
} from '@sap/cds/common';

entity ServiceTickets : managed {
   key ID : Integer;
  title                  : String(50)                    @title : 'Title';
  description            : String(1000)                  @title : 'Description';
  creationDate           : Date                          @title : 'Creation Date';
  priority               : String(50)                    @title : 'priority';
}

entity CustomerOrders : managed {
key ID : Integer;
  product                  : String(50)                   @title : 'Product';
  quantity                   : Integer                    @title : 'Quantity';
  price                      : String(50)                 @title : 'CreationDate';
  deliveryDate               : String(50)                 @title : 'Delivery Date';
}

 

The next step is related to issues upon deployment and due to the newest SQLite version, which we can avoided by changing the SQLite version to 5.0.0 and adding the “engines” section to the file package.json.

  1. Open the package.json file
  2. Check the value of the devDependencies attribute sqlite3 it should be 5.0.0. If it’s not change it from ^5 to 5.0.0
  3. Add the “engines” section as shown below after the the devDependencies attribute
  4. Save your changes by pressing CTRL+S

 

 

Install NPM dependencies

Now before you go any further, you have to execute a Node.js command to ensure everything is installed.

  1. Open a terminal window by going to the top menu and selecting Terminal -> New Terminal.
  2. You will see a new Terminal window appear in the bottom half of the screen.
  3. Within the new terminal window you should be inside a folder with the same name as your project. If not, execute the command: cd <your projext>
  4. Once inside the folder execute npm install

 

Expose your datamodel as an OData-Service:

  1. Right click on the srv Folder in your project structure.
  2. In the new dialog,select New File from the menu and enter incidentService.cds as a file name
  3. Open the newly generated file in the folder.
  4. Enter following script, to define the OData-Service:

 

using scp.cloud from '../db/schema';

service IncidentService {
    entity ServiceTickets as projection on cloud.ServiceTickets;
    entity CustomerOrders as projection on cloud.CustomerOrders;
}

 

So now that we have a database defined, let us add a couple of data rows to the category table so that we know for sure it’s running as expected. CAP has embedded the ability to automatically upload rows into the database provided they are in the right place and the file has the correct structure.

Create a data folder within the db folder. Within that folder, create a file called scp.cloud.ServiceTickets.csv with the following entries:

 

ID;title;description;creationDate;priority

1;Test;Description;1997-04-02;Very High

2;Incident;Demo;1997-04-02;Low

 

Now we will do the same thing for the other table. In the same folder (aka db/data), create the following files: scp.cloud.CustomerOrders.csv

 

ID;product;quantity;price;deliveryDate

1;Tire;200;44,99;1997-04-02

2;Break;300;22,99;1997-04-02

 

Your folder structure of your project should look like this:

 

 

Build your CAP project

  1. Stop your running cds process with CTRL+C if it’s already running
  2. Execute the following from the terminal window: export NODE_ENV=production 
  3. After this command runs successfully, you will execute from the same terminal: cds build/all –clean 

This command will build all the relevant HANA artifacts and place them in a newly created folder that you should now see called gen. If you expand it, you should see 2 folders DB and SRV. As you might expect, if you drill into the DB folder, you will see the HANA DB artifacts and if you drill into the SRV, there are new files in there as well.

Create your HDI-Container and deploy it

Once the build process has completed, you will now execute 3 commands in succession in order to create the HANA HDI container on Cloud Foundry, deploy the HANA Artifacts and the SRV Artifacts. If you notice the very last line in the above screenshot, the build process actually tells us, which command we need to run in order to create the HDI container.

We will modify it slightly to use the hanatrial schema environment but the rest is the same. It is very important to use hanatrial in the following command.

  1. Execute the following command: cf create-service hanatrial hdi-shared database-db  (Note: The creation of the container takes up to two minutes, that means you should wait before executing the next step and make sure you replace the word ,,database” with your own personal project name)

As you can see from screenshot below, a link is provided based on your project name that has your ID in it, be sure to look for this line and be sure it matches when you create the HDI container. If you do not use the correct name, the next 2 steps will likely not work!

  1. Now execute: cf push -f gen/db -k 256M

The HDI instance creation might take a couple of minutes, so if you see an error saying “An operation for service instance database-db is in progress” just wait a couple of seconds and retry.

  1. Next to execute: cf push -f gen/srv –random-route -k 320M 

 

 

Once you find the route name that was generated uniquely for you (Cloud Foundry will generated some interesting names), you should be able to paste that URL into a browser and see the application and validate that it is running as it is now running and available on the internet.

Open a web browser, paste your newly created route and you should see a familiar screen that looks like this. Click on some tables and check if all the data is available. This fully deployed service is now available on the internet and using HANA as a persistence layer.

Part 2: Integrate a Chatbot to your database with 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.

Now that you have accessed your CAI account, it’s time to create a chat bot. After the login, click Start with a template and select the following parameters to create your bot-project and click on Create.

    1. Name: hanabot
    2. Default language: English
    3. Type of data: Non-personal
    4. Store conversation data: Do not store
    5. End users: Non-vulnerable
    6. Bot visibility: Public

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.

  1. Under Train, select Intents
  2. Click + Create
  3. Enter vieworders as intent name
  4. Click Create Intent
  5. Enter following sentences as training-expressions:
    1. Show me my orders
    2. Show me a order please

If you want to improve the chatbots accuracy, you can add more expressions (~15 for a PoC and ~50 for production).

 

Add skills to your Chatbot project:

  1. Click on Build tab of CAI
  2. Then on the left side of your screen click on Add Skill
  3. Name it search-for-orders
  4. Choose skill type BUSINESS
  5. Click Add

Then click on Triggers: Triggers are conditions that determine whether the bot should execute the current skill or not.

  1. Click on the line next to If
  2. Select @vieworders which is the intent that will trigger this skill
  3. Then click Save

Click on Requirements. Requirements are necessary information which can be asked by the bot to execute a skill.

  1. Define #number as ordernumber
  2. Click on +newreplies if #ordernumber is missing
  3. Choose a text as a message type and type: ,,Please tell me your Order-Number”

Click on Actions tab. An action is something that your bot executes at a specific point when executing a skill.

  1. Then click ADD NEW MESSAGE GROUP
  2. Then click on CONNECT EXTERNAL SERVICE > CONSUME API SERVICE
  3. Under API Configuration, select GET
  4. Enter the destination. For selecting the correct destination take the OData-Service-URL which you have created previously and add {{memory.ordernumber.raw}} at the end of the URL. In my case, the final URL looks like this:

 

https://database-srv-shy-cat-cd.cfapps.eu10.hana.ondemand.com/incident/CustomerOrders/{{memory.ordernumber.raw}}

 

  1. Go to the Headers tab
  2. There enter the following parameters:
    1. Header: Accept – Value: */*
  3. To add a second field for the headers you have to click Add fields
  4. Next click on the response tab
  5. Enter output as the Namespace
  6. Click Save
  7. Click on SEND MESSAGE > Text

 

 

Here you can find additional Information about your order:

Product: {{api_service_response.output.body.product}}                                           
Quantity: {{api_service_response.output.body.quantity}}                                           
Price: {{api_service_response.output.body.price}}                                                
Delivery Date: {{api_service_response.output.body.deliveryDate}}

 

Your chatbot should recognize that you want to get order data from your OData-Service. Enrich your intent with different possible customer inputs.

Congratulations! The final result should look like this:

 

 

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.

For more information about SAP Conversational AI:

Assigned tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Richard Hong
      Richard Hong

      Thanks for the good write-up. I'm trying follow your steps here to connect my bot to hana data but ran into error in "incidentService.cds" where it says "Artifact “scp” has not been found". I'm not a developer and so not sure how to fix it

      Author's profile photo Manuel Namyslo
      Manuel Namyslo
      Blog Post Author

      Hello Richard,

       

      try to add < namespace scp.cloud; >  at the beginning of your schema.cds file. That should make it work.

      Kind regards,

      Manuel