How to Directly Integrate SAP Conversational AI with Microsoft Graph API

[former_member741548]

Introduction

Microsoft Graph API is a powerful tool that allows you to access a range of Microsoft products and their various functions. In this article you will learn how to integrate it with an SAP Conversational AI-based chatbot without using any intermediate connectors like SAP Cloud Connector. SAP Conversational Al is a low-code platform, which means that someone who's trying to create a chatbot there may not necessarily have a programming or web-developing background. At least I don't. So bear in mind that this article is aimed primarily at less tech-savvy audience, like myself (although feedback from you tech people out there is more than welcome!). I tried to make this tutorial as detailed as possible, so if you think that I'm overexplaining some points, feel free to skip to the relevant part. The upside is you won't need any prior knowledge to follow the steps in this tutorial.

As an example of integration between SAP CAI and Microsoft Graph, I will describe a step-by-step process of creating a very simple bot that can fetch data from an Excel table stored on OneDrive based on the chatbot user's request. The main point here, however, is to show how to set up the OAuth 2.0 authentication flow between SAP CAI and Microsoft Graph using the CONSUME API SERVICE function of SAP CAI and App Registration in Microsoft Azure. When I tried to do this, it turned out that this part was not all that trivial, especially given the fact that the information required to make it work was scattered and incomplete, which is why I decided to write this post.

Prerequisites

In order to follow the steps outlined below you're going to need:

1. An SAP Conversational AI account (registration link).


2. An active Microsoft Office 365 for Business subscription. Having the Business edition of Office 365 is mandatory, as accessing MS Graph from outside of your Microsoft account and getting data is only available for Office 365 for Business users. If you don't have a subscription but still want to try it out you can sign up for a free one-month trial (here's the link).


3. A Microsoft Azure account (registration link). The app registration step that you'll need it for is free, so both a trial subscription and a pay-as-you-go subscription will do. Important: the Azure account needs to be linked to your Office 365 for Business subscription, so make sure that you are using the organizational account that your Office 365 for Business is connected to (it usually reads something like {username}@{organisation_name}.onmicrosoft.com). You will also need administrative rights in Azure on organizational level in order to give the necessary app permissions.

DISCLAIMER: I performed all the stages of this tutorial using a trial version of Office 365 for Business, a free Azure account and a Community Edition account of SAP Conversational AI, as I only needed to do it for demonstrational purposes. Hence, I had full administrative rights in all those environments. In order to implement a solution along these lines in a corporate environment, you will likely need to contact the IT and/or Security department to get the necessary permissions (or to even be able to access MS Graph under your corporate Microsoft account).

An SAP CAI chatbot that extracts data from Excel using Microsoft Graph

Without further ado, let's create our simple SAP CAI chatbot and see how we can make it get data from an Excel table stored on OneDrive. The sole purpose of this chatbot will be to recognize when a user asks about someone's birthday, check the Excel table to see if it can find the name of the person in the first column, and return the person's date of birth from the second column in case the search was succesful.

Those of you who are only interested in the integration part can go straight to Steps 4, 5, and 7.

Step 1. Create a new SAP Conversational AI chatbot

 

I won't go into too much detail here, as you can find the instructions on how to set up your SAP Conversational AI bot in the official tutorial. I should note, however, that you need to set the bot visibility settings to Private, because your Client Secret from MS Azure will be stored explicitly in the body of a request contained in one of the bot's skills. Unfortunately, I haven't been able to find a way around that.

Step 2. Teach your bot to recognize requests and extract the required data from them

 

To do that, we'll need to create an intent, add a couple of expressions to it and train the bot. I'll go with a dataset of just 10 expressions, which should be enough for the purposes of this tutorial, but you can enrich it as much as you want.

If you're making a bot in any of the languages with Advanced level of support (like English), the bot should be able to recognize names out of the box, as #PERSON is one of the Gold Entities.

Step 3. Create a skill that will perform the required action in MS Graph based on the user's request

 

Now go to the Build tab, choose Add skill and create a skill that is triggered by the Intent that was created in Step 1.

You should also add a Requirement for a #PERSON entity and store it in some memory variable. In my case it's persons_name.

Finally, go to the Actions tab and choose CONNECT EXTERNAL SERVICE -> CONSUME API SERVICE. This is where we'll need the information from Microsoft Azure that we'll get after performing all the necessary steps there.

Step 4. Register your SAP Conversational AI chatbot in Azure Active Directory

 

In order to obtain the necessary credentials for our SAP CAI chatbot, like application ID and client secret, we need to register it in Azure Active Directory. There are, however, a number of ways to do so, and we have to choose the one that works for SAP CAI. We'll be setting up the OAuth2 Client Credential Flow, as this is the one SAP CAI uses (see this answer).

First, go to MS Azure portal and search for Azure Active Directory.

 

Once you've clicked on it, go to App registrations and choose New registration.

 

Name your app and choose Accounts in any organizational directory and personal Microsoft accounts. Leave the Redirect URI field empty.

 

OPTIONAL: You may find it easier to complete the registration using the Integration assistant. If you choose to do so, you'll need to pick Daemon (background process or automation) from the drop-down menu.

 

After you choose this point in the menu, your screen should look something like this:

 

We'll need to deal with the two warnings that are in the red box in the screenshot above. After we finish setting up our app registration (which means completing Steps 4 and 5 of this tutorial), the Integration assistant screen should look like this:

 

If for some reason you see any other warnings than on the screenshot above, you'll have to deal with them as well. You can navigate to the problematic area by clicking on the three dots to the right of the warning and choosing Go to page. The part with the Integration assistant can be skipped entirely, though - it's just a useful indicator. END OF THE OPTIONAL SEGMENT

In any case, you'll need to go to Certificates and secrets and create a New client secret. Be sure to copy its Value and store it somewhere safe, as you won't have access to it anymore once you leave this page. If you lose it or forget to copy it just delete the old client secret and create a new one.

We'll need the client secret value later. For now all that's left to do is to give our app the necessary permissions.

Step 5. Give the necessary app permissions to the registered app in Azure Active Directory

 

To access the files and operate with them, the app that we registered in Step 4 will need permissions. The exact set of permissions will, of course, differ based on what exactly you want your app to do in Azure. You can find out more about the different permissions here.

Go to App permissions and choose Add a permission. Then click on Microsoft Graph.

 

It's really important to choose the correct type of permissions here. For the SAP CAI chatbot we're going to need Application permissions.

 

In our particular case we'll have to add just one permission, which is Files.ReadWrite.All under Files.

 

Adding permissions alone is not enough, however. To make them actually apply to your app, you'll need to grant admin consent on behalf of your organization. Click on Grant admin consent for {your organization's name} (don't be thrown off by the words "Personal use" in the screenshot - this is just how I called my trial organization).

 

If the consent has been successfully granted, you'll be able to see it to the right of your permissions.

This completes the app registration in Azure Active Directory. You can now go to the Integration assistant to make sure everything is ok with your registration (see the optional segment in Step 4).

Step 6. Create the necessary file and upload it to your Office 365 for Business OneDrive

 

This one is simple - in order to work with a file we need to create it first. For this demo bot I made a small Excel table with names in the left column and dates of birth in the right one.

Don't forget to upload your file(s) to your Office 365 for Business OneDrive. If you want to call Excel functions directly from MS Graph, the file has to be in .xslx format.

Step 7. Set up the OAuth 2.0 authentication flow between your SAP Conversational AI chatbot and Azure, then get the required data through Microsoft Graph API request

 

This is the trickiest part. In order to authenticate your app and provide it with an access token, Azure needs at least 5 parameters:

1. Tenant


2. Client ID


3. Client secret


4. Grant type


5. Scope

If you try to use the standart OAuth 2.0 authentication option of CONSUME API SERVICE in SAP CAI, you'll find that it only allows you to transfer the first three of those parameters, which is not enough to get an access token from Azure.

 

The workaround that I was able to find is to do the authentication in two separate steps. We'll use one CONSUME API SERVICE request to pass all the necessary parameters and get the access token, and another one to make the actual MS Graph API call using this access token. However, it is really crucial to format both of these requests in the exact way that Azure "understands" and can process correctly.

 

Step 7.1. Get the access token from Azure

Let's configure the first of the two CONSUME API SERVICE actions, that will get us the access token from Azure.

7.1.1. Create the Azure authentication url

First, we'll need to create the link to the Azure token endpoint. If you click on Endpoints in Azure Active Directory, you'll be presented with a number of different options. Choose OAuth 2.0 token endpoint (v2), that looks like this:

https://login.microsoftonline.com/common/oauth2/v2.0/token

In this URL, replace common with your Directory (tenant) ID that you can find in the Overview section of Azure Active Directory and put the resulting URL in the API Service Configuration box in SAP CAI.

 

The request should use the POST method. For authentication type select No authentication.

7.1.2. Set the correct message content type

Go to the Headers section of the API Service Configuration window and add the following Content-Type header:

Content-Type     application/x-www-form-urlencoded

7.1.3. Put all the required parameters in the API request

The Client ID, Client Secret, Scope, and Grant Type parameters must be passed in the body of the request in a specific format.

DISCLAIMER: Before I begin to explain this step, I have to remind you that your client secret will be visible to whoever has access to your chatbot's dev environment in SAP CAI, so set your bot visibility settings to Private, if you haven't already! Also be aware that passing the secret explicitly in the body of the request may not be the safest thing to do, so use this method at your own risk. I would highly recommend to consult an IT security specialist about this, especially if you're planning to use your bot in a corporate/productive environment. And yes, I did try to pass the Client ID and Client secret using the boxes provided by the OAuth 2.0 authentication option, while passing the rest of the parameters in the body of the request, and it didn't work. Feel free to experiment, though, and please let me know if you find a safer way to do it.

With that out of the way, let's proceed. The values of the parameters are as follows:

1. Client ID - your Application (client) ID from Azure Active Directory/Overview.


2. Client secret - the client secret value that you copied and saved at the end of Step 4.


3. Scope - I'll be using the Default Graph scope, although you may configure scopes in Azure Active Directory to your liking and use the appropriate scope. You can learn more about Microsoft identity platform scopes here.


4. Grant Type - Client Credentials.

The parameters must be in the following format (without the { } symbols):

client_id={your client id}&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default&client_secret={your client secret}&grant_type=client_credentials 

After you've obtained and properly formatted all of the parameters, just put them in the body of the request. You don't need any extra symbols like { }. Also, don't worry about the Tenant parameter, as it is already contained in the URL.

 

If you did everything right, the response to this API request will contain the access token that will allow you to make the MS Graph API request in the next step.

OPTIONAL: Make sure that you're getting the token by outputting it with your bot. This is not a required step, but my advice would be to do it right after you've finished configuring the first API request. Just add the following message after the CONSUME API SERVICE action:

{{api_service_response.default.body}}

If the bot responds with something like the text in the screenshot below, you're on the right track.

 

If you're not getting the token, though, you'll have to troubleshoot this stage before moving on. Start by analyzing (and googling) the errors that you're getting in response to your request. I also found Fiddler to be a particularly useful tool for testing my requests to Azure and making sense of the responses. END OF THE OPTIONAL SEGMENT

 

Step 7.2. Use the authorization token to make a Graph API request

Now we can finally start making MS Graph API requests using the access token obtained in Step 7.1. All we have to do is create the request URL, set the correct headers and figure out what the body of our request should look like.

7.2.1. Create the Microsoft Graph request URL

The request URL for MS Graph that we'll be creating here essentially contains the path to the Excel file stored on OneDrive, as well as the Excel function that we want to run on this file. If you want to do something else in MS Graph, you'll have to refer to the official documentation (or Google) to figure out how to create your request URL. In any case, it's best to start by testing your requests directly in Graph Explorer, where you will also find some sample queries, that can serve as an extremely helpful reference. I'll be using Graph Explorer throughout this step.

Let's start by getting the ID of our file. Go to Graph Explorer and run the GET all items on my drive Sample query. Then find the name of the file in the API response and copy its ID. Alternatively, you can only display the files in a specific folder by using a request like this:

https://graph.microsoft.com/v1.0/me/drive/root:/{path to the folder divided by :/}:/children

 

 



 

Now that we've got the file ID, it's time to create the MS Graph request URL. To extract the date of birth based on the person's name from the Excel table we'll simply use the vlookup function. The resulting URL is:

https://graph.microsoft.com/v1.0/users/{your user id UserName@organization.onmicrosoft.com}/drive/items/{your Excel file ID}/workbook/functions/vlookup

In SAP CAI, create another CONSUME API SERVICE action and populate the corresponding box with the URL that we've just created. The request should use the POST method. For authentication type select No authentication.

7.2.2. Set the correct headers and message content type

Go to the Headers section of the API Service Configuration window and add two headers:

Content-Type     application/json

Authorization    Bearer {{api_service_response.default.body.access_token}}

The second header contains the access token that we received in response to the first API request (Step 7.1). Don't forget to preface the token with Bearer followed by a space.

 

It can be helpful to save your Headers configurations from Steps 7.1.2. and 7.2.2. as templates so that it's easier to reuse them in the future.

7.2.3. Create the Microsoft Graph request and put its body in the body of the SAP Conversational AI API request

Go to Graph Explorer once more, create your request there and test it until you get it right. Make sure to use the same request URL that you've put in the SAP CAI API Service Configuration box. If you change the URL in the Graph Explorer, don't forget to copy-paste it to SAP CAI as well. Same goes for Headers.

The body of our API request must contain all the parameters required for the vlookup function in JSON format, which looks like this:

{

    "lookupValue": "{{memory.persons_name.raw}}",

    "tableArray": {

        "Address": "Sheet1!A2:B100"

    },

    "colIndexNum": 2,

    "rangeLookup": false

}

Of course, you'll need to replace the values in this sample with your own. Also note that while I was using a constant name (Frederick) to test my request in Graph Explorer, I replaced it with my persons_name memory variable in SAP CAI, so that the name can be dynamically extracted from users' requests.

 

That's it. Now the SAP CAI chatbot should be able to access the Excel table stored on your OneDrive, run the vlookup function and return the result. Obviously, this is a very simple implementation of the integration, but whatever else you choose to do with MS Graph and SAP CAI, the principle steps will remain the same.

Step 8. Output the retrieved data to the user

 

We'll finish off by creating a response to the user's request, which contains the date of birth that we got from our Excel table, provided the name of the person was actually there. First, let's make a simple condition:

If _api_service_response.default.body.error is #N/A

#N/A is the error code for when the vlookup search wasn't sussessful. In this case, the bot will answer with Sorry, I don't know this person.

If the error code is not #N/A, the bot will answer with

{{memory.persons_name.raw}}'s birthday is on {{api_service_response.default.body.value}}

showing the user the date of birth found in the Excel table. We should also unset the persons_name memory field after the bot has answered the user's request (which is a good practice in general), so that it's not occupied when the user asks about someone else's birthday.

 

You can see the resulting dialogue on the screenshot below.

 

Naturally, what you can do by accessing Excel tables via MS Graph is not limited to looking up data. And if we consider the sheer variety of functions provided by MS Graph, the capabilities that this simple integration offers, are pretty impressive. The best thing is it doesn't even require any coding or anything in between the two platforms.

Conclusion

To sum up what you need to do in order to integrate an SAP Conversational AI chatbot with Microsoft Graph API using the method presented in this post, here are the general steps:

1. Make sure that your SAP CAI bot is private.


2. Make an app registration in Azure for your bot (Step 4).


3. Set up the desired scopes and permissions (Step 5).


4. Get the authorization token by an API request from the bot (Step 7.1.).


5. Create a Microsoft Graph request using Graph Explorer.


6. Send it by a separate API request from the bot using the authorization token that you got from the first request (Step 7.2.).

Now you have to understand that Microsoft Graph has a fair share of its own nuances and quirks that you'll have to deal with if you choose to work with this API. However, with SAP Conversational AI and Microsoft Graph, once you get the authentication flow down - it's all configuring and troubleshooting on Microsoft Graph part from there. The point of this post has been to show that a direct integration between the two is actually possible and provide an example of such integration.

I hope that you found this tutorial useful and easy to follow, and that you can now enjoy the awesome platform that is SAP Conversational AI even more!