Integrate SAP BW/4HANA with Qualtrics

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

1. Get your Qualtrics survey ID
2. Create your ABAP webservice in SAP BW/4HANA
3. Create an aDSO and process flow that calls the webservice and writes data in your aDSO

Implementation

1. 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?