So today, we will be checking on how to build your first CAI bot and integrate it with WhatsApp. There might be other blogs on the similar topic but I was never able to find all the information in one entire blog. I will be explaining all the steps in details here so that even functional guy like me can play around with it.
Also Read: What is SAP HANAIMP 16 Certification?
The business scenario that we will be working on is to fetch customer details from a Z table that we will create in HANA Database. We will start from scratch that is create our own MDC database on HANA cloud trial and then gradually proceed to chatbot creation and its integration with WhatsApp.
Here are the list of steps that we will follow:
- Create a new SAP HANA MDC Database
- Expose the table data via an OData service
- Create a chatbot on SAP Conversational AI
- Connect your chatbot to SAP HANA and use Consume API service feature
- Create a free account in Twilio.
- Enable the WhatsApp channel.
- Create a Twilio function to interact with SAP CAI using SAP CAI SDK.
- Deploy the function and use function URL as Webhook of WhatsApp channel.
Step 1: Create a new SAP HANA MDC Database
Navigate to this link and log in to your trial account via SAP ID. Navigate to bottom of page and click on “Access Neo Trial”.
Navigate to Persistence > Database & Schema
Select the New button to create a new Database on your SAP Cloud Platform account.
Type in following credentials:
- Database ID: (Anything you want)
- Database System: SAP HANA MDC
- SYSTEM User Password: As per password policy
- Enable Shine user and enter details.
IMPORTANT: I have configured SHINE but this isn’t necessary, you can use your admin DB user or create new user and assign required roles. I’ve just activated here because it is easier.
Database creation should start. Wait for it to be completed. When it’s ready, you’ll see a screen like this one in the Overview menu option:
In case you have activated SHINE user, Click on SAP HANA Cockpit and on the next page, log on with the SYSTEM user using the password that you’ve just created. This will automatically assign some required roles to the user.
After this, click on SAP HANA Web-Based Development Workbench link and logon via SHINE user.
Once Open, click on catalog. In the new Window that opens, click on SQL.
Via SQL, we will create a new table. I have created using the following code:
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),
"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"."concentration" is ' ';Click Run. Table with name Customer will be created in your SHINE user schema.
As our table is created we will now, add data in the table.
Open new SQL window and use following code to enter data in the CUSTOMER table. Replace <SHINE USER NAME> with your user.
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (200719, 'Gavan', 'Merredy', 'gmerredy0@phpbb.com', 'Londonderry', 'California', '94116', '$78322.46', 'United States', 'Research and Development');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (507146, 'Shelby', 'Ivanilov', 'sivanilov1@oaic.gov.au', 'Bay', 'North Dakota', '58106', '$36052.26', 'United States', 'Business Development');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (705013, 'Alecia', 'Hearley', 'ahearley2@wufoo.com', 'Hooker', 'Arizona', '85260', '$43750.45', 'United States', 'Support');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (447344, 'Jacinta', 'Concannon', 'jconcannon3@eepurl.com', 'Forest Run', 'Texas', '77060', '$71747.71', 'United States', 'Legal');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (485160, 'Vivien', 'Pennini', 'vpennini4@printfriendly.com', 'Pankratz', 'Indiana', '46295', '$91783.71', 'United States', 'Product Management');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (870928, 'Lula', 'Noteyoung', 'lnoteyoung5@reuters.com', 'Del Sol', 'Nevada', '89510', '$80057.98', 'United States', 'Training');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (815850, 'Putnam', 'Chuck', 'pchuck6@sfgate.com', 'Northview', 'Virginia', '22309', '$58492.59', 'United States', 'Services');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (352455, 'Georgette', 'Lambricht', 'glambricht7@discuz.net', 'Coleman', 'New York', '11407', '$74590.94', 'United States', 'Accounting');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (766173, 'Amelie', 'Esser', 'aesser8@answers.com', 'Dawn', 'California', '94297', '$94156.59', 'United States', 'Research and Development');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (681375, 'Guthrie', 'Pibworth', 'gpibworth9@barnesandnoble.com', 'Messerschmidt', 'Georgia', '31416', '$24215.80', 'United States', 'Business Development');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (892241, 'Rona', 'Remnant', 'rremnanta@bing.com', 'Arapahoe', 'Massachusetts', '01813', '$9378.97', 'United States', 'Human Resources');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (344273, 'Silvio', 'Togher', 'stogherb@answers.com', 'Menomonie', 'Texas', '77299', '$46297.58', 'United States', 'Human Resources');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (714348, 'Rees', 'O''Currine', 'rocurrinec@creativecommons.org', 'Eagan', 'Texas', '77844', '$39129.84', 'United States', 'Human Resources');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (517598, 'Judi', 'Gauford', 'jgaufordd@about.com', 'Summer Ridge', 'Indiana', '46216', '$12619.38', 'United States', 'Engineering');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (734865, 'Mickey', 'Shailer', 'mshailere@cnbc.com', 'Mcbride', 'Illinois', '61656', '$59319.94', 'United States', 'Research and Development');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (743575, 'Felicio', 'Guerrero', 'fguerrerof@fda.gov', 'Victoria', 'Oklahoma', '73129', '$39744.36', 'United States', 'Sales');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (598682, 'Kassie', 'Elder', 'kelderg@wix.com', 'Bay', 'West Virginia', '25356', '$72777.53', 'United States', 'Accounting');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (762031, 'Eamon', 'Tooth', 'etoothh@who.int', 'Crest Line', 'North Carolina', '27626', '$84450.04', 'United States', 'Services');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (625972, 'Gare', 'Josifovitz', 'gjosifovitzi@yandex.ru', 'Larry', 'California', '92191', '$70116.93', 'United States', 'Accounting');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (433663, 'Xena', 'Heelis', 'xheelisj@spotify.com', 'Hansons', 'Texas', '75044', '$88409.66', 'United States', 'Product Management');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (675286, 'Gert', 'Gregs', 'ggregsk@odnoklassniki.ru', 'Reinke', 'California', '95108', '$22238.30', 'United States', 'Business Development');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (482093, 'Trix', 'Bagshaw', 'tbagshawl@dot.gov', 'Crownhardt', 'Virginia', '23612', '$22475.61', 'United States', 'Legal');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (428845, 'Enrica', 'Dannell', 'edannellm@jigsy.com', 'Erie', 'Missouri', '64199', '$61699.48', 'United States', 'Research and Development');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (875082, 'Willi', 'Ide', 'widen@psu.edu', 'Cottonwood', 'Michigan', '48604', '$33143.58', 'United States', 'Legal');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (452936, 'Matthaeus', 'Robardet', 'mrobardeto@taobao.com', 'Sloan', 'Florida', '33913', '$2837.02', 'United States', 'Training');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (334661, 'Pet', 'Quirk', 'pquirkp@pen.io', 'Merrick', 'Oklahoma', '74156', '$31262.15', 'United States', 'Human Resources');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (326279, 'Bellina', 'Glyssanne', 'bglyssanneq@msn.com', 'Crowley', 'Idaho', '83757', '$88297.04', 'United States', 'Engineering');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (310412, 'Moselle', 'Heed', 'mheedr@google.cn', 'Summerview', 'Wisconsin', '53790', '$62981.49', 'United States', 'Human Resources');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (421575, 'Shannah', 'Bartolini', 'sbartolinis@newsvine.com', 'Kim', 'North Carolina', '27404', '$20832.66', 'United States', 'Product Management');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (175104, 'Pearce', 'MacArthur', 'pmacarthurt@over-blog.com', 'Hansons', 'Colorado', '80044', '$81633.59', 'United States', 'Services');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (292772, 'Bride', 'Currum', 'bcurrumu@nsw.gov.au', 'Forest', 'Georgia', '30096', '$27629.41', 'United States', 'Engineering');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (926314, 'Astra', 'Maltster', 'amaltsterv@sohu.com', 'Mendota', 'Alabama', '36641', '$12823.66', 'United States', 'Product Management');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (336108, 'Kipper', 'Deal', 'kdealw@home.pl', 'Kinsman', 'Florida', '34479', '$49280.41', 'United States', 'Sales');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (841986, 'Joshuah', 'Thiem', 'jthiemx@pen.io', 'New Castle', 'Indiana', '46278', '$61795.13', 'United States', 'Accounting');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (866142, 'Willem', 'Gheraldi', 'wgheraldiy@cargocollective.com', 'Badeau', 'Virginia', '23277', '$45756.65', 'United States', 'Services');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (412335, 'Addie', 'Skinner', 'askinnerz@mail.ru', 'Donald', 'North Carolina', '28314', '$50569.68', 'United States', 'Human Resources');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (554857, 'Hayward', 'Blanpein', 'hblanpein10@amazon.co.jp', 'Grayhawk', 'Louisiana', '70149', '$25935.17', 'United States', 'Accounting');
INSERT INTO "<SHINE USER NAME>"."CUSTOMER" VALUES (483960, 'Murray', 'Manicomb', 'mmanicomb11@skype.com', 'Mallard', 'California', '92121', '$92817.69', 'United States', 'Marketing');This completes our Step 1.
Step 2 : Expose the table data via an OData service
In the SAP HANA Web-based Development Workbench, select Editor.
Now we will create new package. Right click on Content folder and select New > Package.
Name the package ‘customer’ and select Create.
Your HANA XS Package should be created.
Next, we will create three new files within your XS package.
The first file will be .xsaccess. This file determines whether package content can be exposed and specifies the authentication method to be used to grant access.
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.
The second file will be called “.xsapp”. This file marks the root point in the package hierarchy from which content can be served. There should only be curly brackets {} within this file.
Right-click on the new package you created and select New > File and name the new file ‘.xsapp’.
Select Create. Replace the code with the code below.
{}
The third file will be .xsodata. This will create XSODATA service.The OData service definition is the mechanism you use to define what data to expose with OData, how, and to whom.
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 { "<SHINE USER NAME>"."CUSTOMER" as "CUSTOMER"; }Replace <SHINE USER NAME> with your user.
Make sure all the files are saved and activated.
Now we will test out OData service. Open customer.xsodata file and click on Run on the top.
Add “/CUSTOMER” at the end of URL to display table data.
Our OData service is created. We can use it now for CRUD operations.
Step 3 : Create your chatbot on SAP Conversational AI
First, you need to create SAP CAI Account. Create or login into your account here.
Once you have logged in SAP CAI. Click on new bot and Select Perform Actions.
Now you can select predefined skills for your bot. I have select Greetings and Small Talk.
Give your bot a name, add bot description(not necessary) and select data policy and bot visibility. Click create bot.
Now we are ready to create our first intent. Intent refers to the goal the customer has in mind when typing in a question or comment.
Click on create and name your intent customer.
Now we can see customer intent is created. Click on it. Here we will add possible questions that customer can ask to fetch customer details.
Since we have created intent now we will create skill. This skill will be invoked when our customer intent is called.
Every skill is a small part of conversation that is aimed to deliver specific value to a user.
Go on the Build tab and click Create skill.
Now we will connect the customer intent to this created skill.
Open the skill that we created. Go to triggers tab and select as :
Step 4 : Connect your chatbot to SAP HANA and use Consume API service feature
We will have to provide customer number to chatbot as a requirement.
Go to Requirements tab and set the entity as #number and call it id.
Click on +New replies if the reply is missing.
We will send a message and ask user to provide customer number. Click on send message and set appropriate message.
Now go back and click on +New replies if the reply is complete:
Now since chatbot knows what data we need to fetch, we will just call the API service.
Click on Connect External Service > Consume API Service.
As we need to only fetch information, this will be a GET call.
Add your OData service URL here. We will use {{memory.pr.scalar}} as filter parameter. Add this filter parameter at the end of URL.
https://demoxxxxxxxtrial.hanatrial.ondemand.com/customer/customer.xsodata/CUSTOMER?$filter=customerNumber eq '{{memory.id.scalar}}'Authenticate using your SHINE username and password.
In Headers tab, add the following:
Now, let’s look at the response that we will get from this service.
As we can see, body: { } will hold our Odata call response.
Hence, for our scenario to fetch customer details, response will look like:
{{api_service_response.default.body.d.results[0].customerNumber}}"
{{api_service_response.default.body.d.results[0].firstName}}and so on for other fields.
Save this.
Now to display the response, we will add another message. Click on Send Message button and select text. Add the following reply.
Please check below details for Customer No "{{api_service_response.default.body.d.results[0].customerNumber}}" :
First Name: {{api_service_response.default.body.d.results[0].firstName}}
Last Name: {{api_service_response.default.body.d.results[0].lastName}}
E-Mail: {{api_service_response.default.body.d.results[0].email}}
City: {{api_service_response.default.body.d.results[0].city}}
State:{{api_service_response.default.body.d.results[0].state}}
Postal Code: {{api_service_response.default.body.d.results[0].zip}}
Total outstanding balance: {{api_service_response.default.body.d.results[0].totalSpend}}
Country of Origin :{{api_service_response.default.body.d.results[0].homeCountry}}
Department:{{api_service_response.default.body.d.results[0].concentration}}Click on Save.
Now lastly once bot has replied to conversation we need clear memory so that next customer number can be stored.
Click on Update Conversation > Edit Memory
Select Reset all memory and click on Save.
This completes bot creation steps. You can test your bot in SAP CAI itself once.
Now our bot is ready to be integrated with WhatsApp. We will do so in steps 5 to 8.
Before we deep dive further into WhatsApp integration steps let me show you how this integration works.
Since we know, SAP CAI provides direct channels for integration with Telegram, Alexa, Microsoft Teams etc. But for WhatsApp there is no standard channel available. Here we will be using Twilio, one of the trending Cloud Communication Platform.
This how our Technical Architecture looks like;
Now, let’s look at the steps to do this integration:
Step 5 : Create a free account in Twilio.
This step is pretty simple. You need to just create a few Twilio trail account.
Create free trial account in Twilio using this link
Step 6 : Enable the WhatsApp channel.
I hope account creation was easy and you have verified your account.
Now login in your account and navigate to Programmable SMS > WhatsApp(beta)
We are using sandbox environment for WhatsApp. We need to configure the mobile number that you will use on WhatsApp to interact with bot.
One message is received by Twilio, you will see below confirmation screen.
Step 7 : Create a Twilio function to interact with SAP CAI using SAP CAI SDK.
We are done with initial setup of sandbox environment. Now we will have to create Twilio function which will interact with SAP CAI using SAP CAI SDK.
Navigate to Functions from your Twilio dashboard.
Here you will find option to Manage and Configure Functions. First, we will configure our function and add npm module for SAP CAI SDK.
Add below dependencies and Save.
After saving this, navigate to Manage Functions and add new Function with a blank template.
Give desired name and path for the function. Copy and paste below code and Save.
Function Code:
exports.handler = function(context, event, callback) {
console.log('invoked with', event)
global.twiml = new Twilio.twiml.MessagingResponse();
var sapcai = require('sapcai')
let am = new sapcai.am('<CAI API KEY>', 'en')
am.dialog({
type: 'text',
content: event.Body
}, {
conversationId: event.From
})
.then(function(res) {
data = res.messages[0].content;
twiml.message(data);
callback(null, twiml);
})
};NOTE: In the above code Replace <CAI API KEY> with your CAI key. You can easily find the key in bot settings.
Copy the function path. We will be using this in next step.
Step 8 : Deploy the function and use function URL as Webhook of WhatsApp channel.
Now again navigate to Programmable SMS > WhatsApp > Sandbox.
Here paste the function path which we copied earlier and Save.
