SAP Data Intelligence: Integration with Qualtrics

I’m assuming that you know what is Qualtrics. If you don’t know Qualtrics, you can find more information about it here. In this blog post I will show you how Qualtrics can be integrated with SAP Data Intelligence. I will load survey responses from Qualtrics into HANA table through DI in initial mode and delta mode. My expectation is that you created a HANA connection in DI and you have a HANA table for survey.

Steps described below:

1. Step – get survey ID, your user ID and API token.

I assume that you have Qualtrics account and created a survey. For the next steps you need a survey ID and user ID. You will find this IDs in the Qualtrics. For this go to Account Settings -> Qualtrics ID.

2. Step – Create Qualtrics connection in DI

I’m going to use an API token for the authentication. You can generate your token in the Qualtrics ID section. In the Connection Management in DI you should create an OPENAPI connection. The current Qualtrics API system uses a root URL to perform requests:

3. Step – Create a Custom Python Operator in DI

If you don’t know what custom python operator is, and how you can create it, please, read this post for beginners.

Ports: The operator should have one string output port “outData”.

Tags: Select “aiohttp”

Configuration: just copy and paste this JSON for properties:

 "properties": { "qualtrics_connection": { "title": "Input Connection: Qualtrics ", "description": "Input Connection: Qualtrics ", "type": "object", "properties": { "connectionProperties": { "title": "Connection Properties", "description": "Connection Properties", "$ref": "", "sap_vflow_constraints": { "ui_visibility": [ { "name": "configurationType", "value": "Manual" } ] } }, "configurationType": { "title": "Configuration Type", "description": "Configuration Type", "type": "string", "enum": [ "Configuration Manager", "Manual" ] }, "connectionID": { "title": "Connection ID", "description": "Connection ID", "type": "string", "format": "", "sap_vflow_valuehelp": { "url": "/app/datahub-app-connection/connections?connectionTypes=OPENAPI", "valuepath": "id" } } } }, "hana_connection": { "title": "Output Connection: HANA DB", "description": "Output Connection: HANA DB", "type": "object", "properties": { "configurationType": { "title": "Configuration Type", "description": "Configuration Type", "type": "string", "enum": [ "Configuration Manager", "Manual" ] }, "connectionID": { "title": "Connection ID", "description": "Connection ID", "type": "string", "format": "", "sap_vflow_valuehelp": { "url": "/app/datahub-app-connection/connections?connectionTypes=HANA_DB", "valuepath": "id", "displayStyle": "autocomplete" }, "sap_vflow_constraints": { "ui_visibility": [ { "name": "configurationType", "value": "Configuration Manager" } ] } }, "connectionProperties": { "title": "Connection Properties", "description": "Connection Properties", "$ref": "", "sap_vflow_constraints": { "ui_visibility": [ { "name": "configurationType", "value": "Manual" } ] } } } }, "surveyID": { "title": "Survey ID", "type": "string" }, "userID": { "title": "User ID", "type": "string" }, "script": { "readOnly": true, "type": "string", "sap_vflow_constraints": { "ui_visibility": false, "ui_disabled": true } }, "load_mode": { "title": "mode", "type": "object", "properties": { "mode": { "title": "mode", "description": "mode", "type": "string", "enum": [ "Initial", "Delta" ] } } }, "HANA_table": { "title": "Target table name in DWC", "type": "string" } },

All parameters are required. It should look like:

The credentials for connections will be read from Connection Management via api.config.

Now you should add the script below. Using pandas data frame, you can clean/prepare the survey data.

import requests
import zipfile
import json
import io
from io import StringIO
import datetime
import time
import pytz
from dateutil import parser
import pandas as pd
import re
from datetime import datetime
from pytz import all_timezones
from hdbcli import dbapi def openHANAConnection(): hanaConn = api.config.hana_connection['connectionProperties'] hanaConnection = dbapi.connect(address=hanaConn['host'], port=hanaConn['port'], user=hanaConn['user'], password=hanaConn['password'], encrypt='true', sslValidateCertificate="false") return hanaConnection.cursor() def closeHANAConnection(cursor): cursor.close() def getResponses(mode): requestCheckProgress = 0 progressStatus = "in progress" body = {} body['surveyId'] = surveyId body['format'] = "csv" if mode == 'Delta': import datetime from pytz import all_timezones # Get a time zone from Qualtrics downloadRequestResponse = requests.request("GET", tzUrl, data={}, headers=headers) timeZone = downloadRequestResponse.json()["result"]["timeZone"] tz = pytz.timezone(timeZone) # Current time now_qlt = body['endDate'] = now_qlt.replace(microsecond=0, tzinfo=tz).isoformat() body['startDate'] = hana_time.replace(microsecond=0, tzinfo=tz).isoformat() # Creating data export downloadRequestUrl = baseUrl downloadRequestPayload = json.dumps(body) downloadRequestResponse = requests.request("POST", downloadRequestUrl, data=downloadRequestPayload, headers=headers) progressId = downloadRequestResponse.json()["result"]["id"] isFile = None while requestCheckProgress < 100 and progressStatus is not "complete" and isFile is None: requestCheckUrl = baseUrl + progressId requestCheckResponse = requests.request("GET", requestCheckUrl, headers=headers) isFile = (requestCheckResponse.json()["result"]["file"]) if isFile is None: else: requestCheckProgress = requestCheckResponse.json()["result"]["percentComplete"] # Downloading file requestDownloadUrl = baseUrl + progressId + '/file' requestDownload = requests.request("GET", requestDownloadUrl, headers=headers, stream=True) responsesZip = zipfile.ZipFile(io.BytesIO(requestDownload.content)) responsesFiles = responsesZip.namelist() responseFile =[0]) # Prepare data df =[0]).decode("utf-8") # create pandas dataframe dfp = pd.read_csv(StringIO(df), sep=",") #----------------------------# # Your data preparation code #----------------------------# dfp_csv = dfp.to_csv(index=False) return dfp_csv restConn = api.config.qualtrics_connection['connectionProperties']
dataCenter = restConn['user']
token = restConn['password']
surveyId = api.config.surveyID
mode = api.config.load_mode['mode']
baseUrl = "https://" + dataCenter + restConn['host'] + "responseexports/"
tzUrl = "https://" + dataCenter + restConn['host'] + "users/" + api.config.userID
headers = { "content-type": "application/json", "x-api-token": token }
if mode == 'Delta': dbCursor = openHANAConnection() # Get HANA date dbCursor.execute('SELECT MAX("EndDate") FROM' + api.config.HANA_table +"')" # Date parsing for Qualtrics datetime_str = str(dbCursor.fetchone()) datetime_sbstr = datetime_str[19:-3] datetime_list_str = datetime_sbstr.split(",") datatime_list_int = [] for item in datetime_list_str: datatime_list_int.append(int(item)) years = datatime_list_int[0] months = datatime_list_int[1] days = datatime_list_int[2] hours = datatime_list_int[3] minutes = datatime_list_int[4] seconds = datatime_list_int[5] hana_time = datetime(years, months, days, hours, minutes, seconds) closeHANAConnection(dbCursor)
dfp = getResponses(mode)
api.send("outData", dfp)

4. Step – Create a pipeline

Now you should run the pipeline one time in the initial mode. After that you can schedule the pipeline in the delta mode. After bringing the X data into HANA table you can combine your X and O data there.

That is, you just integrated Qualtrics with Data Intelligence. Moreover, you can load Qualtrics data not only in initial mode, but also in the delta mode! If you have any comments, just put them down below. Stay tuned 😊

There are a lot of posts about Qualtrics integration in the community. Here some of them:

A Low Code integration of Qualtrics with SAP BTP Workflow Service

Consume Qualtrics survey results in SAP Analytics Cloud with live connection to SAP HANA service

Integrate SAP BW/4HANA with Qualtrics