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:

• Get survey ID, user ID, generate API token in Qualtrics
• Set up Qualtrics connection in DI
• Create a custom python operator for Qualtrics for responses loading in initial and delta mode using Qualtrics API v3: create response export, get response export progress, get response export file
• Create a pipeline

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: https://datacenterid.qualtrics.com/API/

Update: As I wrote this post, there was no check for the host name in Connection Management. With the new upgrade this host name field will be checked. That’s why you should use just qualtrics.com as host name. A valid host is hostname:port, e.g. test.example.com:123, or example.com:123, or example.com. You can add an additional parameter for the base path /API/v3/ in the Custom Python Operator.

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": "http://sap.com/vflow/com.sap.dh.connections.openapi.schema.json",
					"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": "com.sap.dh.connection.id",
					"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": "com.sap.dh.connection.id",
					"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": "http://sap.com/vflow/com.sap.dh.connections.openapi.schema.json",
					"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 = datetime.datetime.now(tz)
        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 = responsesZip.open(responsesFiles[0])
       
    # Prepare data
    df = responsesZip.read(responsesFiles[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