Hey, Google, let me talk to HANA
Some time -and some more coding- have passed after this initial teaser from Craig. Using his code calling Amazon Alexa as a foundation, I could trigger some more business into my HANA instances from my Google Home.
There are many components involved in this interaction. I will document the details of most of them, but recommend you take a look at the available documentation on each of the pieces for further understanding:
Creating API.AI intents and Entities
I first created my API.AI entity to recognize the type of purchase order (weekly, daily, monthly..etc):
Then created the Intent that will use the entity:
When I entered the “Create a monthly purchase order” in the “User says” field, it automatically detected my entity and added it in the definition. This will allow me to use the same phrase once and pass the order type as a parameter. I could also do this with the material number and the supplier name or ID, for example.
Create your Google Cloud Function
Having created all the entities and intents that I need, before I can define the integration, I need to define the other end (i.e, what will be called when voice the intent). This is a Google Cloud Function:
You can create on of these from the console, copying the code from a local editor or using a Cloud Source Repository (created separately). The important pieces here are that the name of the function to execute is the right one from your code and you may also want to try the local emulator for your initial test cycles:
The important pieces of code here are:
exports.askHANA = function askHANA(req, res) {
var assistant = new ApiAiAssistant({request: req, response: res});
console.log('Request headers: ' + JSON.stringify(req.headers));
function responseHandler (assistant) {
const intent = assistant.getIntent();
switch (intent) {
case 'purchase-order':
//Get the Entity PurchaseOrderTypes defined in API.AI
let poTypes = assistant.getArgument('PurchaseOrderTypes');
if (poTypes===null){poTypes = 'weekly'};
poTypes = poTypes.toLowerCase();
//Call async function to create Purchase Orders. It will set the value of "data" with the speech for the assistant.
createPurchaseOrder(poTypes, function(data){
speechOutput = "This is the default speech text for create PO";
speechOutput = data;
assistant.tell(speechOutput);
console.log("CREATE PO: assistant tell data" + data);
} );
break; //Purchase Order
default:
speechOutput = "I'm sorry, I could not interpret that. You can ask to create a purchase order, start the production run or set the default vendor";
assistant.tell(speechOutput);
console.log("DEFAULT OUTPUT: assistant tell data " + data);
}; //switch intent
} //function responseHandler
//Finally, set the actionMap. If this is not set, you will get a timeout error in the console
let actionMap = new Map();
//The first value refers to the action as defined in the intent in API.AI
actionMap.set('purchase-order', responseHandler);
// actionMap.set('other-intent-name', responseHandler);
assistant.handleRequest(actionMap);
};
Note how the name of the intent needs to be the same as the one you defined as the action in API.AI:
The createPurchaseOrder function is defined as follows:
var createPurchaseOrder = function(itemName, callback){
var request = new https.request({
hostname: host,
path: "/.../flow.xsodata/newPO",
method: "POST",
headers: {
'Authorization': authStrIoT,
'Content-Type': 'application/json; charset=utf-8',
'Accept': '*/*'
}
});
/** dummy**/
request.end( definePurchaseOrder(itemName) );
request.on('response', function (response) {
if( response.statusCode === 201 ){
lv_vendor = '100';
lv_QTY1 = 2;
lv_QTY2 = 5;
speechOutput = "I have successfully created the Purchase Order,,";
/** begin remote po update **/
var request2 = new http.request({
hostname: hxe_host,
port: 8000,
path: "/.../SmartSupplier/newPO.xsjs?MATNR1=" + cMATNR1 + "&QTY1=" + lv_QTY1 + "&MATNR2=" + cMATNR2 + "&QTY2=" + lv_QTY2 + "&SUPPLIER=" + lv_vendor,
method: "POST",
headers: {
'Authorization': authStrHXE,
'Content-Type': 'application/json; charset=utf-8',
'Accept': '*/*'
}
});
/** dummy **/
request2.end( defineStockSupplier(itemName) );
request2.on('response', function (response) {
console.log('STATUS response 2: ' + response.statusCode);
if( response.statusCode === 200 ){
speechOutput = speechOutput + "successfuly received confirmation from the supplier";
/** begin stock update **/
var request3 = new https.request({
hostname: host,
path: "/.../flow.xsodata/newStock",
method: "POST",
headers: {
'Authorization': authStrIoT,
'Content-Type': 'application/json; charset=utf-8',
'Accept': '*/*'
}
} );
/** dummy**/
request3.end( defineStock(itemName) );
request3.on('response', function (response) {
console.log('STATUS update STOCK: ' + response.statusCode);
if( response.statusCode === 201 ){
speechOutput = speechOutput + ",, received the goods and updated stock";
callback(speechOutput);
}
});
/** end stock update **/
} else {
speechOutput = speechOutput + "but there was an error contacting the supplier";
console.log("error en la request al supplier");
callback(speechOutput);
}
});
/** end remote po update **/
}
});
};
Whoa! Too much? What’s happening here is that during the demo we were actually calling two different hosts, one was representing our main Manufacturing system on SAP Cloud Platform and the others were HXE hosts representing our suppliers. Those hosts were actually HANA Express instances in each of our different hosting partners (MS Azure, Google Cloud Platform, Amazon Web Services or an Intel NUC).
We had a different command to change the supplier as well as a definition of what the different types of purchase orders were (the MRP would define that in real life instead of hard-coding, but you get the idea).
Gluing API.AI and Google Functions
Now that we have the first two pieces of the integration together, we can go ahead and define it in the fulfillment in API.AI:
You get this value from the “Trigger” tab in your function definition:
The HANA piece: Calling stored procedures from oData and XSJS scripts
We are calling two types of services to update or insert new records into our tables, an xsjs script and an xsodata that calls a procedure.There is plenty of documentation on how to do this, so I will not go deep into details.
The store procedure is defined similar to this:
PROCEDURE "SMART"."smartfactory.procedures::createProdOrder" (
in row "smartfactory.data::smart_manufacturing.ty_ProductionOrders",
OUT error "smartfactory.data::smart_manufacturing.ty_error" )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
DEFAULT SCHEMA SMART
AS
BEGIN
declare lv_ORDER_TXT string;
declare lv_ORDER_DATE string;
declare lv_MATNR string;
select "smartfactory.data::ProdOrders".NEXTVAL into lv_ordnr from dummy;
select ORDER_TXT, ORDER_DATE, MATNR
into lv_ORDER_TXT, lv_ORDER_DATE, lv_MATNR
from :row;
/*Get required materials from BOM and check availability
Sparing this part
...
*/
/*Some error handling*/
if lv_count = 0 then
error = select 404 as http_status_code,
'BOM not found' as error_message,
'There is no Bill of Material defined for the material in the Prod Order' as detail from dummy;
end if;
/*Insert new pruchase order with current timestamp */
INSERT INTO "smartfactory.data::smart_manufacturing.ProductionOrders" VALUES(
lv_ordnr,
lv_ORDER_TXT,
now(),
'NEW',
lv_MATNR
);
END
The call in the oData file was defined as follows:
service namespace "smartfactory.services" {
"smartfactory.data::smart_manufacturing.tt_PurchaseOrders" as "newPO"
create using "smartfactory.procedures::createPO";
}
And the xsjs file in my HANA Express instances has something like:
var matnr1 = $.request.parameters.get("MATNR1");
var qty1 = $.request.parameters.get("QTY1");
var matnr2 = $.request.parameters.get("MATNR2");
var qty2 = $.request.parameters.get("QTY2");
var SUPPLIER = $.request.parameters.get("SUPPLIER");
try {
var conn = $.hdb.getConnection();
if ( matnr1 > 0){
var query = "SELECT QUANTITY FROM \"SmartSupplier.data::SMART.STOCK\" where \"MATNR\" = " + matnr1 + " and \"STATUS\" = \'AVAILABLE\' and \"SUPPLIER\" = " + SUPPLIER ;
var rs = conn.executeQuery(query);
var newQty = rs[0]["QUANTITY"] - qty1;
/**Validate quantities are not negative**/
var query = "UPDATE \"SmartSupplier.data::SMART.STOCK\" SET \"QUANTITY\"="+ newQty +" WHERE \"MATNR\" = " + matnr1 + " and \"STATUS\" = \'AVAILABLE\' and \"SUPPLIER\" = " + SUPPLIER;
conn.executeUpdate(query);
conn.commit();
}
/*Adapted from original code, using both materials in the parameter URL*/
conn.close();
}catch (err) {
var body = "[ERROR]"+ err;
$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
$.response.contentType = "application/json";
$.response.setBody(body);
}
var body = "Updated Materials" + newQty;
$.response.status = $.net.http.OK;
$.response.contentType = "application/json";
$.response.setBody(JSON.stringify(body));
The code snippets above are adapted samples we used in a demo. The steps to create an anonymous connection are in this tutorial or in the last part of this blog which also explains how to create a project using HANA Studio in case you are just getting started with SAP HANA.
If you are planning on migrating into XS Advanced, then you should follow these tutorials instead as some definitions above will not work:
- Creating an OData Service with Create Operation and XSJS Exit
-
SAP HANA XS Advanced, create a user-provided anonymous service for SAP HANA database Schemas
Happy talking to your HANA instances and see you on Twitter or on LinkedIn !
Awesome this is the Future. You guys took this to a different level.
Appreciate that! And thanks for joining us at the booth at SAPPHIRE 🙂
It was my pleasure. Hope we will build some more standard skills and Google API and connect to SAP. The idea is awesome 🙂
We can say this will do Tedious Useful work 🙂
Interesting blog. Thank you for sharing.
Thanks, Himanshu!
Very interesting blog Lucia.
Artificial Intelligence is really taking our tedious activities in ease. But I doubt about the security, how secure it would be share access with any digital assistant.
Do you think that sharing access with digital assistance may put our data at risk?
Thanks, Niranjan! I don't think this poses any different risk than sharing access with the normal user, but it would depend on what kind of access the assistant has. I would be as worried as with any other interface as in this case the intelligent part is in the speech recognition, but the actions it can perform in the system are limited to the services I enable.
Voice recognition to identify users is now in place and then I guess it is a matter of what you let the assistant access or do. After all, we let these assistants "listen" to us at home all the time 😉
Amazing blog to study and impmentaion, thanks for sharing.
Thanks, Shivam! Would love to hear about your implementation use case. Cheers!
Hi Lucia,
I am getting below error message while trying to trigger the Webhook service from the intent api.ai.
Could you please help.
Regards,
Jeet
Hi, Jeet, what does the Cloud functions logs say?
regards,
Lucia.
Hi Lucia,
Getting the below error. Am i missing a step is cloud function?
Regards,
Jeet
Looks like you are missing some code. For example, the declaration of ApiAiAssistant.
These code snippets are not complete. There are some examples of this initial setup in Google's documentation here: https://developers.google.com/actions/apiai/fulfillment
In this case, they are using a variable called "ApiAiApp" instead of "ApiAiAssistant" like I did but it's a matter of choice
This is probably not the only thing I did not copy from my original code. You may also want to check Google's and APi.AIs documentation as some things have been updated since I wrote this blog. Another piece of advice is to test locally with the SDK, at least at the beginning. It saves time in the long run as the Cloud Functions are not quick to update.
Have a good one!
Thanks Lucia. It's working now.
Hi Lucia,
I wanted to consult you, as they solved the issue of authentication by saml, since I have a similar scenario, where I can only access services in SCP, with basic authentication.
Regards,
Demian Palavecino
Hi Demian,
I am not sure what issue you are talking about. This question will probably get an answer in the Q&A.
Best,
Lucia.
Hi Lucia,
Can you help me with the connections of SAP HANA (Cloud Platform) with GCP ?
And one more thing, I would like to ask is that, where to paste the createPurchaseOrder function ? is it inside the same file, which has the first snippet ?
Regards,
Souvik
Hi Souvik,
The createPurchaseOrder function is called belongs with the rest of the code in the cloud functions snippet. If you look at the code, you will see it is getting called there.
Regards,
Lucia.
Hi Lucia ,
Can you please share or let me know the type of tt_PurchaseOrders in Xsodata service .
Thanks,
Shivam
Hi Shivam,
Unfortunately, I do not have that code handy but it is surely a table (an entity in CDS). It had an item number, material number, quantity and vendor as far as I can remember.
Best,
Lucia.
Hi Lucia ,
Thanks , I moved on from that part but got stuck on below , Can you please suggest if i am missing anything -
Deployment failure:
Function load error: Code in file index.js can't be loaded.
Did you list all required modules in the package.json dependencies?
Detailed stack trace: Error: Cannot find module 'actions-on-google'
at Function.Module._resolveFilename (module.js:469:15)
at Function.Module._load (module.js:417:25)
at Module.require (module.js:497:17)
at require (internal/module.js:20:19)
at Object.<anonymous> (/user_code/index.js:1:114)
at Module._compile (module.js:570:32)
at Object.Module._extensions..js (module.js:579:10)
at Module.load (module.js:487:32)
at tryModuleLoad (module.js:446:12)
at Function.Module._load (module.js:438:3)
this i am getting whenever i am deploying the cloud function as you suggested above , I am trying hard to overcome with the help of some npm installation but still no luck.
Thanks,
Shivam
this is fixed .
Thanks !!
HI Lucia,
It is very nice and informative blog!!
I would like to know whether this can be initiated through Google Assistant right from Android phone as well OR it has to be done from Google Home type devices ONLY?
If it can be done through google assistant, how the integration comes into the play?
Thanks,
Bhavik
Hi, Devisha,
The integration in Dialogflow (former API.AI) says it works with Google Assistant in general, so I don't see why it would not work with an Android phone.
Cheers,
Lucia.
Hi, Lucia,
Thanks for this blog, i am very interested to know about SAP using API.AI google.
Is it possible to do like this using SAP MSSQL platform or this is only applicable for SAP HANA platform?
Thanks,
Dennis
Hi Dennis,
There is no platform called "SAP MSSQL" , can you expand what you mean?
The code above uses xsjs, which is one of the server side options for HANA. If you're not using HANA, you can surely write a similar integration for another programming language running somewhere else.
Cheers,
Lucia.
Hi, Lucia,
Thanks for your feedback.
What i mean for that is MSSQL back-end also.
The other method that i am looking for this MSSQL back-end is the B1 integration framework.
I know you are using the SAP HANA Service Layer to integrate the google home assistant.
Cheers,
Dennis