In this blog post I will demonstrate how we can integrate Qualtrics XM Platform with SAP BW/4HANA using an ABAP web service that is called in a process chain to load data into an aDSO.
Motivation
Integrating experience data into the intelligent enterprise is an important prerequisite to get all possible information for the intelligent enterprise. However, information must be leveraged by intelligent tools and processes. Many customers already use SAP BW/4HANA as their central tool to harmonize data and get insights in combining data from various sources.
Thus, also eXperience data from Qualtrics surveys can be combined in SAP BW/4HANA with Operational data like HR or CRM data. As a result of the combination we get XO Data that can be leveraged in reporting tools like the SAP Analytics Cloud or SAP Business Objects Platform.
Steps
Implementation
To get data from your Qualtrics survey we will leverage the Qualtrics API. Extensive information on the API can be found in https://api.qualtrics.com/
Go to your Account settings by clicking on the upper right Account icon. From here you need to switch to the Qualtrics Ids tab. In this tab you have various Ids for surveys, directories and users. You will need to find the survey you would like to get data from and note down the survey ID.
Moreover you need the API token in the API section. If there is no API token in the section for the API token you can generate one by clicking on the Generate Token button. Note down the API token. Be careful to regenerate the token. If the token is changed all the services accessing Qualtrics using the specific API token has to be changed too!
2. Create your ABAP webservice in SAP BW/4HANA
We can check which calls are necessary in Qualtrics to achieve our goal in the Qualtrics API https://api.qualtrics.com/ You can try out all the calls directly from the Qualtrics website.
Step 1: We have to start a response export. This will trigger the generation of the survey result csv file. This can be done by setting up a POST call to https://env.qualtrics.com/API/v3/surveys/surveyId/export-responses where env.qualtrics.com has to be changed by your Qualtrics host url and surveyId needs to be the Id of the survey we would like to import the data of into our SAP BW/4HANA system.
As a result of the call we get an export progress id and a status. Usually status will be “in progress”
Step 2: Check the progress of the Qualtrics File generation from Step 1. We have to do a GET call to get the current response export progress – that is if generation is ready or not. We need to call https://env.qualtrics.com/API/v3/surveys/surveyId/export-responses/exportProgressId where exportProgressId is taken from Step1.
As long as the status is “in progress” and not “completed” we need to wait for completion. Once the status is “completed” we get a file ID
Step 3: Now as we have the file ID we are able to download the file from the Qualtrics server using the GET Call: https://env.qualtrics.com/API/v3/surveys/surveyId/export-responses/fileId/file putting the file ID as fileID into the url of the call.
That’s all we need to do to get the data!
To connect to the Qualtrics XM Platform I have written lass ZCL_XOANALYTICS_REST. The class consists of a method get_data and a method get_survey_resp. Method get_data is called several times with different input to get the survey response from the Qualtrics Server and is used for all the three steps above.
To connect to Qualtrics directly from SAP BW/4HANA we need to create a client web service in SAP BW/4HANA
We can use the create_by_url method of class cl_http_client to create an http client object and a REST Client for the communication with the Qualtrics server.
Afterwards the URL to your Qualtrics server has to be set and especially x-api-token from your Qualtrics ID section has to be set here.
To get the survey response we need to call the REST service several times. That’s why we encapsulate this coding in a get_data method and provide the payload in case it is a POST method.
At the end we finally get the result of the call in json_format!
Now that we have the basic building block to call Qualtrics from SAP BW/4HANA we need to call the three steps described above to get data for our survey. This is done in method get_survey_resp.
Step 2: Check the progress of the export of the survey data
Step 3: Once the export is completed we can download the file
We get the export file as a zip file. This can be controlled by API headers in step 1 in case a zipped file is not needed. However for large surveys zipped files are of course recommended.
3. Create an aDSO and process flow that calls the webservice and writes data in your aDSO
With this class the survey result can be imported to the FileServer (Transaction AL11). We can call the method get_survey_resp in an ABAP program.
Afterwards we use a process step of type program execution in a process chain to load the data to SAP BW/4HANA to the file server and use a data source to load the survey data into an aDSO.
From there data can be combined with all the harmonized Operational data from various sources can be combined with eXperience data generating XO-Data for XO Analytical dashboards and reports.
As the survey responses also contain timestamps and can be filtered by the API according to the dates a Delta can be implemented to only get the newest survey results from the Qualtrics server.
I hope the post is helpful for you!
Do you have scenarios in your area to combine experience data from surveys with data in your business warehouse?
Motivation
Integrating experience data into the intelligent enterprise is an important prerequisite to get all possible information for the intelligent enterprise. However, information must be leveraged by intelligent tools and processes. Many customers already use SAP BW/4HANA as their central tool to harmonize data and get insights in combining data from various sources.
Thus, also eXperience data from Qualtrics surveys can be combined in SAP BW/4HANA with Operational data like HR or CRM data. As a result of the combination we get XO Data that can be leveraged in reporting tools like the SAP Analytics Cloud or SAP Business Objects Platform.
Steps
- Get your Qualtrics survey ID
- Create your ABAP webservice in SAP BW/4HANA
- Create an aDSO and process flow that calls the webservice and writes data in your aDSO
Implementation
- Get your Qualtrics survey ID and API token
To get data from your Qualtrics survey we will leverage the Qualtrics API. Extensive information on the API can be found in https://api.qualtrics.com/
Go to your Account settings by clicking on the upper right Account icon. From here you need to switch to the Qualtrics Ids tab. In this tab you have various Ids for surveys, directories and users. You will need to find the survey you would like to get data from and note down the survey ID.
Moreover you need the API token in the API section. If there is no API token in the section for the API token you can generate one by clicking on the Generate Token button. Note down the API token. Be careful to regenerate the token. If the token is changed all the services accessing Qualtrics using the specific API token has to be changed too!
2. Create your ABAP webservice in SAP BW/4HANA
We can check which calls are necessary in Qualtrics to achieve our goal in the Qualtrics API https://api.qualtrics.com/ You can try out all the calls directly from the Qualtrics website.
Step 1: We have to start a response export. This will trigger the generation of the survey result csv file. This can be done by setting up a POST call to https://env.qualtrics.com/API/v3/surveys/surveyId/export-responses where env.qualtrics.com has to be changed by your Qualtrics host url and surveyId needs to be the Id of the survey we would like to import the data of into our SAP BW/4HANA system.
As a result of the call we get an export progress id and a status. Usually status will be “in progress”
Step 2: Check the progress of the Qualtrics File generation from Step 1. We have to do a GET call to get the current response export progress – that is if generation is ready or not. We need to call https://env.qualtrics.com/API/v3/surveys/surveyId/export-responses/exportProgressId where exportProgressId is taken from Step1.
As long as the status is “in progress” and not “completed” we need to wait for completion. Once the status is “completed” we get a file ID
Step 3: Now as we have the file ID we are able to download the file from the Qualtrics server using the GET Call: https://env.qualtrics.com/API/v3/surveys/surveyId/export-responses/fileId/file putting the file ID as fileID into the url of the call.
That’s all we need to do to get the data!
To connect to the Qualtrics XM Platform I have written lass ZCL_XOANALYTICS_REST. The class consists of a method get_data and a method get_survey_resp. Method get_data is called several times with different input to get the survey response from the Qualtrics Server and is used for all the three steps above.
class ZCL_XOANALYTICS_REST definition
public
create public .
public section.
methods GET_SURVEY_RESP
importing
!IV_HOST type STRING default 'https://url_to_qualtrics_server'
!IV_SURVEY_ID type STRING default 'SV_xxxxxxxxxxx'
!IV_TOKEN type STRING
!IV_FILENAME type STRING
exporting
!EV_SURVEY_RESPONSES type STRING .
protected section.
data gv_json type string .
data lo_http_client type ref to if_http_client .
data lo_rest_client type ref to cl_rest_http_client .
data gv_json_x type xstring .
methods get_data
importing
!iv_host type string default 'https://url_to_qualtrics_server'
!iv_token type string
!iv_path type string
!io_http_client type ref to if_http_client optional
!iv_content_type type string optional
!iv_payload type string optional
!iv_post type abap_bool default abap_false
!iv_binary_result type abap_bool default abap_false
exporting
!ev_http_status type string
value(er_data) type ref to data .
private section.
ENDCLASS.
CLASS ZCL_XOANALYTICS_REST IMPLEMENTATION.
method get_data.
data: lo_http_client type ref to if_http_client,
lo_rest_client type ref to cl_rest_http_client.
data: lv_token type string,
lv_http_url type string,
lv_url type string.
lv_http_url = iv_host.
To connect to Qualtrics directly from SAP BW/4HANA we need to create a client web service in SAP BW/4HANA
We can use the create_by_url method of class cl_http_client to create an http client object and a REST Client for the communication with the Qualtrics server.
if not io_http_client is bound.
call method cl_http_client=>create_by_url
exporting
url = lv_http_url
importing
client = lo_http_client
exceptions
argument_not_found = 1
plugin_not_active = 2
internal_error = 3
others = 4.
else.
lo_http_client = io_http_client.
endif.
* Create REST client instance
create object lo_rest_client
exporting
io_http_client = lo_http_client.
Afterwards the URL to your Qualtrics server has to be set and especially x-api-token from your Qualtrics ID section has to be set here.
if lo_http_client is bound and lo_rest_client is bound.
concatenate iv_host iv_path into lv_url.
cl_http_utility=>set_request_uri(
exporting
request = lo_http_client->request " HTTP Framework (iHTTP) HTTP Request
uri = lv_url " URI String (in the Form of /path?query-string)
).
* Set request header if any
call method lo_rest_client->if_rest_client~set_request_header
exporting
iv_name = 'x-api-token'
iv_value = iv_token.
call method lo_rest_client->if_rest_client~set_request_header
exporting
iv_name = 'Accept'
iv_value = 'text/csv; application/json'.
call method lo_rest_client->if_rest_client~set_request_header
exporting
iv_name = 'Connection'
iv_value = 'keep-alive'.
call method lo_rest_client->if_rest_client~set_request_header
exporting
iv_name = 'Accept-Encoding'
iv_value = 'gzip, deflate'.
if iv_content_type is supplied.
call method lo_rest_client->if_rest_client~set_request_header
exporting
iv_name = 'Content-Type'
iv_value = iv_content_type.
endif.
To get the survey response we need to call the REST service several times. That’s why we encapsulate this coding in a get_data method and provide the payload in case it is a POST method.
if iv_post eq abap_false.
* HTTP GET
lo_rest_client->if_rest_client~get( ).
else. "POST
data(lo_request) = lo_rest_client->if_rest_client~create_request_entity( ).
if iv_payload is supplied.
lo_request->set_string_data( iv_payload ).
endif.
lo_rest_client->if_rest_resource~post( lo_request ).
endif.
* HTTP response
data(lo_response) = lo_rest_client->if_rest_client~get_response_entity( ).
* HTTP return status
ev_http_status = lo_response->get_header_field( '~status_code' ).
At the end we finally get the result of the call in json_format!
* HTTP JSON return string
if iv_binary_result eq abap_true.
gv_json_x = lo_response->get_binary_data( ).
get reference of gv_json_x into er_data.
else.
gv_json = lo_response->get_string_data( ).
get reference of gv_json into er_data.
endif.
endif.
endmethod.
Now that we have the basic building block to call Qualtrics from SAP BW/4HANA we need to call the three steps described above to get data for our survey. This is done in method get_survey_resp.
method get_survey_resp.
data: lv_str_result type string,
lv_length type i,
lv_length_output type i,
lt_binary_tab type standard table of sdokcntbin,
lv_text type string.
data lo_zip type ref to cl_abap_zip.
data lr_data_char type REF To data.
types: begin of lty_result_progress,
progressid type string,
fileid type string,
percentcomplete type decfloat34,
status type string,
end of lty_result_progress.
types: begin of lty_meta,
requestid type string,
httpstatus type string,
end of lty_meta.
data ls_result_progress type lty_result_progress.
data ls_meta type lty_meta.
types: begin of lty_response.
include structure ls_result_progress as result.
include structure ls_meta as meta.
types end of lty_response.
data lt_response type table of lty_response.
data: begin of ls_response_progress,
result type lty_result_progress,
meta type lty_meta,
end of ls_response_progress.
data lv_survey_path_base type string.
lv_survey_path_base = '/API/v3/surveys/'.
*
* Create the host client object
cl_http_client=>create_by_url(
exporting
url = iv_host
importing
client = data(lo_http_client)
exceptions
argument_not_found = 1
plugin_not_active = 2
internal_error = 3
others = 4
).
* This is the path of the API call to trigger the survey data export on the qualtrics server
data(lv_survey_path) = lv_survey_path_base && iv_survey_id && '/export-responses'.
This is step1. Start the Qualtrics Export File!
* API call to Qualtrics to trigger the survey data export
me->get_data(
exporting
IV_HOST = 'host_to_the-qualtrics_server’
iv_token = iv_token
iv_path = lv_survey_path
io_http_client = lo_http_client
iv_content_type = 'application/json'
iv_post = abap_true
iv_payload = '{"format": "csv"}'
importing
er_data = data(lr_data)
).
* In lr_data the response from qualtrics is saved. This time we get only a * progressID
* from Qualtrics. With the progressID we can check the status of the export
* The response is deserialized into a structure that can be read in ABAP
assign lr_data->* to field-symbol(<json>) casting type string.
data lr_data_resp type ref to data.
/ui2/cl_json=>deserialize(
exporting
json = <json>
changing
data = ls_response_progress ).
Step 2: Check the progress of the export of the survey data
* Check if the response is OK and the status is completed.
if ls_response_progress-meta-httpstatus eq '200 - OK'.
* Use the progressID for the next call to Qualtrics to check the status.
data(lv_progressid) = ls_response_progress-result-progressid.
data(lv_progress_path) = lv_survey_path && '/' && lv_progressid && '?x-api-token=' && iv_token.
* As long as sthe status is not completed we check the progress
while ls_response_progress-result-status ne 'complete'.
* Potential für eine Endlosschleife!!
clear lr_data.
clear <json>.
clear ls_response_progress.
* Call the API to check the progress
me->get_data(
exporting
iv_token = iv_token
iv_path = lv_progress_path
io_http_client = lo_http_client
iv_content_type = 'application/json'
iv_post = abap_false
importing
er_data = lr_data
).
assign lr_data->* to <json> casting type string.
/ui2/cl_json=>deserialize(
exporting
json = <json>
changing
data = ls_response_progress ).
endwhile.
* The status of the export is complete. The file is ready on the server
* With the fileID we can download the file with survey data from Qualtrics
Step 3: Once the export is completed we can download the file
if ls_response_progress-result-status eq 'complete' and
ls_response_progress-result-fileid is not initial.
* Get the data
data(lv_file_path) = lv_survey_path && '/' && ls_response_progress-result-fileid
&& '/file?x-api-token=' && iv_token.
do 10 times.
me->get_data(
exporting
* IV_HOST = 'https://sapservicessandbox.co1.qualtrics.com'
iv_token = iv_token
iv_path = lv_file_path
io_http_client = lo_http_client
* IV_CONTENT_TYPE = 'application/json'
iv_post = abap_false
iv_binary_result = abap_true
importing
er_data = lr_data
ev_http_status = data(lv_status_code)
).
if lv_status_code = '200'.
assign lr_data->* to field-symbol(<response>) casting type xstring.
We get the export file as a zip file. This can be controlled by API headers in step 1 in case a zipped file is not needed. However for large surveys zipped files are of course recommended.
* Unzip the response
create object lo_zip.
call method lo_zip->load
exporting
zip = <response>
EXCEPTIONS
others = 2.
if sy-subrc <> 0.
* Implement suitable error handling here
endif.
call method lo_zip->get
exporting
index = 1
importing
content = data(lv_zip_content)
EXCEPTIONS
others = 3.
if sy-subrc <> 0.
* Implement suitable error handling here
endif.
* Convert to string
call function 'SCMS_XSTRING_TO_BINARY'
exporting
buffer = lv_zip_content
importing
output_length = lv_length
tables
binary_tab = lt_binary_tab.
call function 'SCMS_BINARY_TO_STRING'
exporting
input_length = lv_length
importing
text_buffer = lv_text
OUTPUT_LENGTH = lv_length_output
tables
binary_tab = lt_binary_tab
.
if sy-subrc <> 0.
* Implement suitable error handling here
endif.
exit. "do
else.
wait up to 2 seconds. "Next try.
endif.
enddo.
if <response> is assigned.
* Write response to file on the application server
open dataset iv_filename for output in text mode encoding default.
TRANSFER lv_text to iv_filename.
endif.
endif.
endif.
endmethod.
3. Create an aDSO and process flow that calls the webservice and writes data in your aDSO
With this class the survey result can be imported to the FileServer (Transaction AL11). We can call the method get_survey_resp in an ABAP program.
REPORT Z_XOA_GET_QUALTRICS_SURVEYDATA.
data lo_survey_response type ref to zcl_xoanalytics_rest.
PARAMETERS: p_host type string default 'https://xxxxx.qualtrics.com' OBLIGATORY LOWER CASE,
p_id type string OBLIGATORY LOWER CASE,
p_token type string OBLIGATORY LOWER CASE,
P_file type string OBLIGATORY LOWER CASE.
create object lo_survey_response.
call method lo_survey_response->get_survey_resp
exporting
iv_host = p_host
iv_survey_id = p_id
iv_token = p_token
iv_filename = p_file .Afterwards we use a process step of type program execution in a process chain to load the data to SAP BW/4HANA to the file server and use a data source to load the survey data into an aDSO.
From there data can be combined with all the harmonized Operational data from various sources can be combined with eXperience data generating XO-Data for XO Analytical dashboards and reports.
As the survey responses also contain timestamps and can be filtered by the API according to the dates a Delta can be implemented to only get the newest survey results from the Qualtrics server.
I hope the post is helpful for you!
Do you have scenarios in your area to combine experience data from surveys with data in your business warehouse?
- SAP Managed Tags:
2 Comments
