If you try to make SAP BW and a Qlik Sense installation work in tandem, you will find that there are a lot of resources to fetch data from SAP into Qlik. You can load the data from external HANA views generated for your cubes, ODS or even queries, or use the extractor connector provided by Qlik. But what about the other direction? How to access the features of Qlik Sense from the ABAP stack?
While you probably would not want to access any data in Qlik from SAP, you should considerung using the various APIs of Qlik Sense to automate administrative tasks. One good example is to trigger a reload task in Qlik Sense from a SAP BW process chain. This is what we are going to look at in this blog post.
The Qlik Repository Service (QRS) API provides access to objects like apps, users and load tasks, and the solution is as simple as calling a method of that API – if there was not those nasty SSL certificates…
The call
The QRS API is a REST API – or to put it in other words you can reqtrieve information or execute actions by issuing simple HTTPS requests. To call a REST API conveniently from ABAP you can use the class CL_REST_HTTP_CLIENT. First let’s see a very basic example: how to get the list of all available tasks in qlik. We will need to use the GET method with the approprate url here.
Your first try will be something like this:
DATA lr_http_client TYPE REF TO if_http_client. DATA lr_rest_client TYPE REF TO if_rest_client. DATA lr_request TYPE REF TO if_rest_entity. cl_http_client=>create_by_url( EXPORTING url = 'https://my.little_qrs_server.com:4242/qrs/task?Xrfkey=1234567890132456' ssl_id = 'QLIK' IMPORTING client = lr_http_client ). lr_rest_client = NEW cl_rest_http_client( lr_http_client ). lr_request = lr_rest_client->create_request_entity( ). lr_rest_client->set_request_header( EXPORTING iv_name = 'X-Qlik-Xrfkey' iv_value = '1234567890132456' ). lr_rest_client->set_request_header( EXPORTING iv_name = 'X-Qlik-User' iv_value = 'UserDirectory=MYDOMAIN; UserId=happyuser' ). lr_rest_client->get( ). DATA(lv_response_status) = lr_rest_client->get_status( ). DATA(lt_response_headers) = lr_rest_client->get_response_headers( ). DATA(lr_response) = lr_rest_client->get_response_entity( ). DATA(lv_response_data) = lr_response->get_string_data( ).There is a few things to note in this code:
- The URL contains the information on what information you are requesting or what action you would like to execute. See the QRS API documentation for details. There you will also find which URL to call to start a task.
- The URL parameter Xrfkey and the header X-Qlik-Xrfkey need to have the same value and are required to prevenet cross site scripting – also described in the API docu.
- As we are connecting directly to the QRS – not via a Qlik Proxy Service – we need to specify the user to impersonate during the API call via header. And we will need to send a client certificate with the request. Read on …
Bloody certificates
The code above seems quite simple. The bad news is, that configuring a successfull authentication against the QRS API will be a lot harder than writing the code.
When using direct access to the QRS API you are forced to use client certificate authentication – that is the only way supported by Qlik. Alternatively you could connect via the Qlik Proxy Service, and use “Windows Authentication” (good luck with that on a linux based ABAP server) or so-called “header authentication” which is pratically no authentication at all, as it neither requires a password nor any other form of secret.
By owning the right client certificate you are inherently trusted as a caller by the QRS, and can impersonate any user when accessing the QRS. The key pair for the client certificate is generated by the Qlik server and can be downloaded from the Qlik QMC as a .pfx file. The caller – in this case ABAP – needs to install this certificate in its certificate store and use it when connectig.
On SAP side you need to create a new SSL client identity – in our case ‘QLIK’ – and upload the client certificate into the corresponding store (so-called PSE) in transaction STRUST. The parameter ssl_id in the code is a pointer to this store and thus indirectly specifies which client certificate to use.
So far for the theory. In practice uploading the client certificate in STRUST is not possible, as the CA used by Qlik to sign the client certificate lacks a special attribute in its own authority certificate, and is thus not regarded as valid by STRUST. With a few hours of command-line hacking with sapgenpse.exe and openssl tools (availble as part of CygWin) you can however tweak the certicifates, transform them into the proper format, and compile them in an SAP-specific .pse file.
Finally the .pse file can be uploaded in transaction STRUST, and written into the client identity using ‘Save as’.
But keep your fingers off the Champagne bottle’s plug – you are not done yet. The certificate is now installed, but not used. The Qlik server does not tell during the SSL handshake which certificate it prefers, and SAP – unlike most browsers – does not automatically send the single client certificate it has. To overcome this, you need to set profile parameter ssl/client_ciphersuites adding an additional flag to the bitmask prefix.
Reading the response
The response of the QRS API is usually encoded in JSON format. There is several ways to parse JSON in ABAP, the most convenient one probably being class /UI2/CL_JSON as described here. It can convert the JSON directly into deep, typed internal tables and structures, simply ignoring those fields in the JSON you don’t need. You need define the types yourself however, something like this:
TYPES BEGIN OF ty_task.
TYPES id TYPE string.
TYPES name TYPE string.
TYPES enabled TYPE abap_bool.
TYPES BEGIN OF operational.
TYPES nextexecution TYPE string.
TYPES BEGIN OF lastexecutionresult.
TYPES status TYPE i.
TYPES starttime TYPE string.
TYPES stoptime TYPE string.
TYPES END OF lastexecutionresult.
TYPES END OF operational.
TYPES END OF ty_task.
DATA lt_tasks TYPE STANDARD TABLE OF ty_task.
"Convert JSON to internal ABAP structure
/ui2/cl_json=>deserialize( EXPORTING json = lv_response_data CHANGING data = lt_tasks ).Boxing it
The rest is good old ABAP development: To implement a custom process type for your process chains, create a new ABAP class, and make sure you implement at least the interfaces IF_RSPC_MAINTAIN, IF_RSPC_EXECUTE and IF_RSPC_GET_VARIANT. Most of the methods are about handling the variants of the process chain. You can choose any logic you like, the only important thing is that based on the variant selected for the process chain you will be able to identify the task to be started. There are also other interfaces which allow you to further customize the behaviour of your process types, for example you can add new entries to the context menu of the process chaing log viewer (RSPC) and react if the user clicks them.
The most important part is however to implement the EXECUTE method of interface IF_RSPC_EXECUTE. Here you will need to put your code to call the QRS API to start the execuetion of the task.
Got stuck or have another challenging SAP vs. Qlik integration scenario? Get in touch!
