SAP BTP Kyma as a Bridge between SAP Analytics Clo...

Sumin · ‎2022 Mar 21

Let's build a bridge on SAP BTP 🌉

How can we build a bridge between different types of SAP applications? SAP BTP Kyma can be the bridge between SAP Conversational AI and SAP Analytics Cloud.

Overview

We can quickly build chatbot with no-code development and integration with the SAP ecosystem. With the SAP ecosystem, how can we easily visualize the data from chatbot to the dashboard?

We can build the Bridge with the SAP BTP Kyma🛠

In this posting, I would like to build the conceptual architecture and requirements for the connection between SAP Conversational AI and SAP Analytics Cloud. Today, we will use these following applications:

SAP Conversational AI

SAP Analytics Cloud

SAP CAP Application

SAP BTP Kyma

Python

Architecture

With SAP BTP Kyma, we can integrate SAP Conversational AI and SAP Analytics Cloud. The connection bridge between SAP Conversational AI and SAP Analytics Cloud is through SAP BTP Kyma with oAuth2.

This is the high level architecture for this solution.

High Level Architecture

SAP BTP Kyma Architecture

For the python application and SAP CAP Application, I referenced this blog about solution architecture. This article is really helpful to better understanding of dynamic oData through SAP BTP Kyma, please refer to this posting: https://blogs.sap.com/2021/09/09/dynamic-odata-from-any-api-through-sap-btp-kyma/)

Check Points

To build this architecture with SAP BTP Kyma, let's check the followings.

✅ SAP Conversational AI

API reference
- To get the data from REST API, please visit to the SAP API Business Hub to check API rules. (Link to here: https://api.sap.com/api/ConversationLogs_API/overview)

Developer token
- I strongly recommend to check the authentication from the API documentation🙏
- For example, to get conversation logs, we need developer token from the CAI settings.
- Settings>Tokens>Bot token>developer token
- Developer token is only enabled at the tenant level, so to get that please Create BCP ticket in component “CA-ML-CAI-BLC”

Check Developer Token

✅ Python Application

Data Extractor: This step is for extracting the data from REST API
- Configproperties.py: To configure the properties for the REST API, following informations are needed in the python application.
  - clientID
  - secret
  - request token
  - oauthURL
  - requestURL

- Dataretriever.py: we can retrieve API data from
  - Retrieving REST API data with config properties
  - Create table structure
  - Create sqlite table
  - Insert data into DB and store the list as SQLiteDB to the picked up by CAP

import time

import configparser

import requests

import json

import os

import sqlite3

from requests.auth import HTTPBasicAuth

import configproperties

from collections import Counter



# Mapping function

def createExportList(chatlist):

    conv = []

    conv =  chatlist

    return conv



# To define the structure of the table 

def createTableStructure(exportList): 

    tabStr = ""

    dataModel = "namespace exportname.export;\n\nentity Conversations {\n"

    fieldSequence = [0,1]

    for item in exportList: 

        for i in range(2):

            #dbfield = ''.join(field.split()).lower()

            #[drink['content'], hat['content'], hatcolor, ticket['content']] 

            if i == 0: 

                tabStr = tabStr + "ID" + " TEXT, "

                dataModel = dataModel + "ID" + " : String;\n"

            elif i == 1: 

                tabStr = tabStr + "DATE" + " TEXT, "

                dataModel = dataModel + "DATE" + " : String;\n"              

            

        break #for one entry

    dataModel = dataModel + "}"    

    return tabStr[:len(tabStr)-2], dataModel, fieldSequence



#Create sqlite table 

def createTable(con, tabStr): 

    try: 

        dbCur = con.cursor()

        dbCur.execute("DROP TABLE IF EXISTS CatalogService_Conversations")

        tableStructure = "CREATE TABLE IF NOT EXISTS CatalogService_Conversations (" + tabStr + ")"

        dbCur.execute(tableStructure)

    except Exception as e: 

        print(e)



#Fill database

def insertIntoDb(con, list, fieldSequence): 

    try: 

        dbCur = con.cursor()

        for item in list: 

            placeholder = ""

            dataset = []

            for field in fieldSequence: 

                placeholder = placeholder + "?,"

                dataset.append(str(item[field]))

            placeholder = placeholder[:len(placeholder)-1]

            dbCur.execute("INSERT INTO CatalogService_Conversations VALUES (" + placeholder + ")", dataset)

    except Exception as e: 

        print(e)



def main():

    exportList = createExportList(chatlist)

         

    # Store the list as SQLite DB to tbe picked up  by CAP 

    tableStructure, dataModel, fieldSequence = createTableStructure(exportList)

    conSql = None

        

    try: 

        conSql = sqlite3.connect('./dbdata/cai-items.db')

    except Exception as e: 

        print(e)



    if conSql != None: 

        createTable(conSql, tableStructure)

        insertIntoDb(conSql, exportList, fieldSequence)

        conSql.commit()

        conSql.close()

        

        #Create data-model.cds if it doesn't exist

        if not os.path.isfile("./dbdata/data-model.cds"): 

            dataModelCds = open("./dbdata/data-model.cds","w")

            writtenBytes = dataModelCds.write(dataModel)

            dataModelCds.close()



        #Wait before the enxt call

        time.sleep(refreshTimer)



if __name__ == "__main__":

    main()

✅ SAP CAP Application

Data Publisher: This step is for publishing the data through oData
- mta.yaml: mta.yaml based on template version 0.4.0 (Link to here:https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/LATEST/en-US/4486ada1af824aadaf56baebc9...)
- node.js: node.js CAP application that would publish the needed OData service
- k8s>deployment.yaml: deployment yaml file to the BTP Kyma

✅ SAP BTP Kyma

OAuth Clients: We can create the credential on the BTP Kyma
- Configuration > oAuth Clients > Create oAuth Client

Kyma oAuth Client

✅ SAP Analytics Cloud

After deploying python application and SAP CAP Application on the Kyma, we need to connect oData Service

OData Services Connection
- Create New connection: SAP Analytics Cloud > Connections > Select a data source > New Data Services Connection
  - Data Service URL: Please input the catalog address from the BTP Kyma
  - Authentication Type: We will use oAuth 2.0 Client Credentials
  - oAuth Client ID and Secret: Please check the secret at the BTP Kyma (previous steps)

Select a Data source

New Data Services Connection

Model
- Create New Model from new data source using oData
- Models>Create New > From a Data Source

Create new new model from a data source

- Select a data source

Select a data source

- Create New Connection or Select Connection
  - We can directly create new connection or select existing connection(please refer to previous steps)

Create Model from OData Services

- Build a New Query for oData Services
  - For the available query data, we can check the SQLiteDB that created from SAP CAP Application.

Build OData Service Query

Today we saw that SAP BTP Kyma acts as a bridge between SAP Applications. I hope you get some insight from this article👍

Reference

API Business Hub for SAP Conversational AI: Link to https://api.sap.com/package/SAPConversationalAI/overview

SAP Tutorials: https://developers.sap.com/tutorial-navigator.html?tag=software-product:analytics/sap-analytics-clou...

SAP BTP Kyma architecture: https://blogs.sap.com/2021/09/09/dynamic-odata-from-any-api-through-sap-btp-kyma/)

srmuc69 · ‎2022 Apr 07

This is great but if you can please post more of the coding examples to build this would be great. Or is this a mission on the discovery center or in the developer network?

Thanks

Stefan

Sumin · ‎2022 May 06

sressing Thank you for comment! I update the example code for the Dataretriever.py part. I hope this is useful. This is not a mission and I'd like to share the insight about the SAP BTP.

srmuc69 · ‎2022 May 06

Thanks so much