Bring Qualtrics Data to SAP Datasphere

Introduction:

Hey Folks! I’m Navya and here I am with my first blogpost!

In the era of data-driven decision-making, seamless integration and real-time access to data from diverse sources are paramount for organizations seeking competitive advantage.

Qualtrics is a cloud-based platform that specializes in experience management, including survey creation, feedback collection, data analysis, and actionable insights. It enables organizations to design and distribute surveys to gather feedback and insights from customers, employees, and other stakeholders.

Key features of Qualtrics include:

Creating surveys with various question types
Collect responses in real-time
Set up automated actions based on survey responses
Integrate with other platforms and systems, enabling seamless data exchange and collaboration

You can read and understand more about Qualtrics in the following link: About Qualtrics

Qualtrics, allows organizations to collect feedback and insights through surveys. However, for comprehensive analysis and decision-making, these survey responses need to be integrated into a central data repository with business data. This is where SAP Datasphere, with its data integration and management capabilities, comes into play.

For analysing data, SAP Datasphere is a flexible, scalable, and cost-effective solution; with integrated tools and data governance, and Self-service analytics, you can easily cleanse, enrich, and visualize your data. It provides a large set of default connections to access data from a wide range of sources, which might be in the cloud as well as on-premises, or from SAP as well as from non-SAP sources or partner tools.

To extend connectivity beyond SAP Datasphere standard remote connectivity and cover additional data sources we can create a connection to a partner tool and generate an Open SQL schema in SAP Datasphere.

In this blog post, I will share my journey of exploring a specific scenario – the integration of Qualtrics survey data into SAP Datasphere. There are different options for bringing Qualtrics information into SAP Datasphere, however in this blog post, we are looking at how SAP CAP extension application can be used to bring data from Qualtrics into SAP BTP.

In the above architecture, the SAP CAP application retrieves Qualtrics survey data and its responses via Rest service. This data is persisted in SAP HANA Cloud which also acts as backing service for the extension application.

In my experiment, I’ve focused on bringing Qualtrics data to SAP Datasphere. However, you can bring data from various sources such as SAP S/4HANA, Customer Experience Systems, other Business Systems.Furthermore, you can harness data from hyperscaler platforms like Google BigQuery, Databricks as well.

Let’s get started!

Create Qualtrics Survey and Retrieve Real Time Response

Take a look at Qualtrics Survey

To begin, you must have a Qualtrics Survey available for analysis. For this blog post, I have already created a Qualtrics Survey about Employee commitment toward the organization. You can easily follow the link to set up a new survey in Qualtrics.

To keep things simple, I create a new project in Qualtrics by using the question types Multiple Choice and Text Entry. The graphics below provide a glimpse of the Qualtrics survey that I will utilize in this blog post.

Finding Qualtrics IDs

All Qualtrics API requests require specific parameters to construct the requests. Many of these parameters will be Qualtrics IDs. For example, if you’re downloading the raw data for your survey, you’ll need to know the survey ID. The majority of these IDs can be found in your account on the Qualtrics IDs tab.

In the account settings of your Qualtrics profile, there is a tab called “Qualtrics IDs” which contains the Survey IDs of all your created surveys as well as their Datacenter ID and your current API Token.

To later access your survey data through the Qualtrics API, all three of those IDs/Tokens are required.

Create a CAP Application

Let us start from creating a simple CAP based Application to store our Qualtrics Survey data.

Note: This is one of the approaches used to bring Qualtrics Data to SAP Datasphere. Alternatively, the SAP Integration Suite can be utilized for the purpose of importing Qualtrics data into SAP Datasphere.

To create the CAP Application, I have used Visual Studio Code but SAP Business Application Studio can also be used to create one.

Create a new project using cds init

cds init <your-project-name>

Navigate to your project and open it in vs code. In the terminal, execute the command npm install to install the necessary node modules.

Creating domain model and service

Let’s build a basic domain model to our project. Create a file with the name db/schema.cds and then add your domain entity definitions into it. In my example, I have created an entity called Surveys which is used to store survey response details. Note that this entity definition will change according to your survey and use case.

To update the survey response in the entity, a service is needed which is defined as service definition in srv/service.cds file.

Bring Qualtrics data to CAP Application

To bring Qualtrics Data into the CAP application, a server file server.js is required that operates as an intermediary between the CAP application and the Qualtrics API.

Once, survey response is submitted by a user, qualtrics system sends a notification as a POST request to the CAP application. The CAP application server manages incoming POST requests that carries survey response notification data. It acquires access tokens and survey response information from the Qualtrics using REST api, subsequently inserting the response data into the entity “Surveys” created earlier.

Running CAP server locally

To test your CAP Application on your local environment, open a terminal and execute the command 'cds watch' to initiate the server. In your browser open the link http://localhost:4004 and you should be able to see the landing page of your application(similar to below).

Deploy your CAP Application to BTP

After running successfully the CAP application locally, let’s now deploy it to SAP Business Technology Platform(BTP), Cloud Foundry(CF) environment as a Multi-Target Application (MTA) which uses SAP HANA Cloud database as a backing service.

In vscode terminal, log in to Cloud Foundry (CF), targeting your space within the SAP BTP account where you intend to deploy. Utilize the following commands for this purpose:

cf api <API-Endpoint-of-your-landscape>
cf login

Add SAP HANA client and it’s config to your project and set up the HANA cloud Service instance in your BTP account(if not created already)

cds add hana --for production

Install the mta build tools and generate a mta.yaml file in your project to deploy the CAP Application to SAP BTP.

cds add mta

Build, Deploy and test the mtar file generated.

mbt build
cf deploy <generated-mtar-file-path>

After successfully deploying the CAP Application, you can find the URL of your application using SAP BTP Cockpit.

Create an event listener in Qualtrics for completed response

To create an event listener in Qualtrics, enabling the initiation of a webhook callback upon receiving a survey response notification,

Upon receiving a survey response from the user, Qualtrics system initiates a webhook callback to the earlier created CAP application which acts as a event listener. To create an event listener, you can use the eventssubscriptions api.

For example, in my case, we are much interested in the event which is surveyengine.completedResponse.{SurveyID} as that signals that a survey has been completed.

Run the following curl command to create an event subscription with your survey’s parameters:

curl -X POST -H 'X-API-TOKEN: {your-api-token}' -H 'Content-Type: application/json' -d '{
"topics": "surveyengine.completedResponse.{your-survey-id}",
"publicationUrl": "{your-cap-application-url}",
"encrypt": false
}' 'https://{your-datacenter-id}.qualtrics.com/API/v3/eventsubscriptions/'

Generate Test Responses

After finishing the survey event subscription, we can utilize one of Qualtrics’ features to create test responses. You can access it by choosing the option Generate test responses in Tools in the edit view of your survey.

Enter the number of test responses you would like to generate and Click Generate to generate the responses.

After waiting for your test to be completed, you will be shown a list of the test iterations, along with a Survey Test Summary. This summary includes a number count of test iterations, saved responses and more.

Check Survey Response in the CAP Application

Open your CAP application, using the URL generated earlier on SAP BTP and by clicking on Surveys, you can visualize the test survey response.

Create a Key for a HDI Container to access it in SAP Datasphere

You can create a key for the HDI container either using Cloud Foundry CLI or using SAP BTP Cockpit. Using Cockpit, you can find and open the HDI container instance based on the name specified in mta.yaml file.

Once you’re inside your HDI container, access the “service keys” section. From there, select the option to “create” a new service key for your HDI container which will contain the connection details like JDBC URL, host, user, password and more.

Once you’ve successfully generated the service key, proceed to download the associated JSON file. which will be used in establishing the connection to the database within SAP Datasphere.

Use this key to create a connection in SAP Datasphere

Prior to establishing your connection, ensure that you have generated a space within your SAP Datasphere account. (If not, follow the below steps to create one)

To create a Space, click on the Space management icon on the bottom left, and click on the Create button on the top right.

Enter a name for your Space. The Space ID will auto-populate. In this example, let’s call our Space DEV SPACE. Click on Create, and you’ve successfully created your Space in SAP Datasphere.

Create a connection with your CAP Application’s HANA Database

In the “Connections” tab, open the relevant space of your choice.

In the local connections section, click on create, and search for SAP HANA in connection types.

Connection properties is configured by populating it with necessary details like Host, Port, User Name and Password from the service key JSON file of the HDI container.

Input your desired business name and description(optional) for the connection. Finally, click on “Create Connection” to finalize the process.

You will be able to see the Connection created under the local connections section.

Use connection to connect with remote table and Model Survey Data in Data Builder

In Data Builder, we can create the New Graphical View to view the data from Qualtrics.

The connection created earlier can be found in “Connection Folder” of the “Sources” section.

Navigate inside your table where the data is present and drag and drop your table from the panel on the left over to the canvas. After dragging, a pop-up for Import Table appears, click on Import and Deploy to add it to the canvas.

To access the data within the table, locate the “Data Viewer” icon. Upon clicking this icon, you will encounter a “Delayed Data Viewing” message at the bottom section. Scroll downwards and select “View Data” to proceed.

Now you should be able to view your data and connect with Remote tables in SAP Datasphere.

Summary

SAP Datasphere provides us the different approaches to connect with the source system. One of the ways we have used here is to create a database user to allow external tools to connect to the space and write data to Open SQL schemas associated with the space.

In this blog post, we’ve explored the process of bringing Qualtrics data into SAP Datasphere via Database Users/Open SQL Schemas approach. We achieved this by deploying a CAP Application on SAP BTP which uses SAP HANA Cloud to store Qualtrics Survey data. This integration empowers us to leverage Data Builder within SAP Datasphere for the modeling and enriching of business operational data with experience data.

Before this blog post comes to an end, I would like to thank my team at SAP for their continuous support and express special gratitude to Lalit Mohan Sharma ,Uma Anbazhagan and PVN PavanKumar, who supported me in validating and publishing this post.

What’s next?

Well, Thank you for your engagement and patience throughout my blog post. Your interest is truly valued. We’re eager to hear about your own encounters and insights, not to mention your innovative concepts for additional SAP Datasphere applications, whether involving SAP or non-SAP solutions.

Stay enthusiastic and maintain your spirit of curiosity – there’s much more to explore on the horizon!

Reference & Further Reading

For more information and related blog posts on the topic page for SAP Datasphere.

If you have questions about SAP Datasphere you can submit them in the Q&A area for SAP Datasphere in the SAP Community.