Showing posts with label Alteryx. Show all posts
Showing posts with label Alteryx. Show all posts

Monday, June 10, 2024

Alteryx - Triggering Qlik Sense Task

Post Index  

2024-06-10


Alteryx - Triggering Qlik Sense Task

QS Reload Tool to CURL API Call

Integrating Alteryx and Qlik Sense


----------------------------------------------------------------------------------------------------------

Side Track

It was some time ago that I posted articles into different communities.   Now, I am trying to consolidate back all of my shared articles into my blog for easy reference.  Also, I will also review a bit of them and see if any new information to supplement.   As time goes, everything keeps changing.  In particular to technology, in most of the time, we can see a lot of old stuff to be depreciated and come with new ideas, tools, etc.  Let's ride on them and move forward!  Let's stay tuned and updated!

----------------------------------------------------------------------------------------------------------


Alteryx to Reload QS Task

Back to this post content, "Alteryx - Qlik Sense Reload Tool" is one of my first community posts in my life.  And previously, I only share concepts and roughly the ideas how to implement.  I would now try best also to share as much as I can.  You might find the original post below.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Alteryx-Qlik-Sense-Reload-Tool/td-p/939218

Unfortunately, the tool was developed using .NET SDK which is no longer supported by Alteryx (as of 2024-06-10) and therefore, it cannot be used anymore.  Nonetheless,  it is easy to replicate the details using CURL.  I will share more information at the end of this post.


Below is the content of the original post for reference.

=======================================================================

First Post Starts

Hello everyone. It is my first post to the Alteryx community. I start to write the posts because I would like to share the ideas on what I have done and experienced before. Might be there is someone who already applied similar solutions but I think it is good to share and keep improving with everyone in the community.  Let's evolve.

 

Topic

Nowadays, it is typical scenario that once the Alteryx workflow is completed, Alteryx is able to trigger the subsequent downstream systems to continue the data pipeline. For example, once Alteryx is finished the data preparation for Qlik Sense dashboard, it will trigger the reload of the dashboard.

 

In order to achieve this, it requires to call the Qlik Sense Repository Service API (https://help.qlik.com/en-US/sense-developer/February2022/Subsystems/RepositoryServiceAPI/Content/Sen...). Normally, we would get to use the Download tool to do so.  However, it does not support to pass the Qlik Sense certificate for authentication purpose.  The passive solution is to calculate the time required for the workflow to be finished and setup the schedule in Qlik Sense that will 100% to pick up the data.  This method is no guarantee because the workflow completion might be affected by a lot of factors like network, server resources, data volume, etc.  More practically, we could then look for the Run Command tool with CURL or postman, or simply write the python code in the Python tool. Either of them requires kind of coding/scripting to make it work.  It is not easy to maintain.

 

QS Reload Tool

In order to make it seamless for this kind of integration, I have created a QS Reload tool for this purpose.  Once it triggers the QS task and it will return and complete the workflow.  The is way to make it synchronous call, however, it is just a waste of resource to just keep waiting in the workflow without any processing.  The interface looks like below and the configurable parameters are illustrated below.

 

qs_reload_tool.png

QS Server

This is where the server address would be supplied. The protocol does not need to be specified, only the server’s address

 

Certificate and Reload Certificate

This would be filled up when the “Reload Certificate” button is clicked. This is designed to get the certificates related to Qlik Client from your certificate store. This certificate would be passed as a header in the Http Request and is designed to make the request secure. Basically, this is also known as SSL or Secure Sockets Layer (SSL) or to encrypt the information sent from the requestor machine to the server machine. For further explanation, see Certificates Section of this document.

 

Get Task Name

Once the server address and the certificate are supplied with correct information, this button would get all the available tasks inside the Qlik Management Console inside the server.

 

Task Name

This would be filled with the tasks fetched from the server. This is possible through the use of Qlik Sense’s QRS API. The selected task would have the following format, ApplicationName(applicationID). Also whatever task id is selected in the combo box. It would be the one that would be triggered to start.

 

Messages

This is to inform the user about warnings, status and errors happening inside the GUI. This is designed to help the end user know what inputs are mandatory and the status if the request to get the task list succeed or failed. 

 

Keep Connected

I am unable to share the tool at the moment but if you need more information, kindly drop me a message.   Meanwhile, I will continue to share more stuff in the new posts.



=======================================================================



Modernizing to use CURL API 


A lot of people might query because the depreciation of the .NET SDK, it should then come into the Python SDK to continue the development as recommended by Alteryx for SDK.  Why I now provide and discuss the CURL method instead of providing a new Python replacement is because the tool itself is not a supported product and it is really difficult for people and companies to use.   No matter how native is the underlying code is, it is not possible to make it widely available where is regulatory, compliance, security, etc.  Plenty of rules that are not able to overcome.  Even if it can be passed, when Alteryx upgrades, it also faces other compatibility issues.


As a result, nowadays, API would be a more appropriate solution.  API is using widely in the word and it is one of the major method to integrate systems together.   API would be easier to be captured and used for everyone.   And natively, Alteryx has a Command tool that is possible for you to use.   That means you do not need to rely my tool and you can integrate as freely as you can.


OK.  Let's see how to use CURL API.  In fact, previously, I have shared a post before discussing about CURL and Qlik API.  For more detailed understanding of QS API call, please refer to the link below.


https://kongsoncheung.blogspot.com/2023/06/curl-qrs-api.html




To be specific, we now focus on two API methods to trigger the reload of the QS Task including:


  • post /task/{id}/start/synchronous
https://help.qlik.com/en-US/sense-developer/February2022/APIs/RepositoryServiceAPI/index.html?page=1110

This method is to trigger the reload and wait for its completion.  It seldom use and waste of Alteryx worker resource in server environment.  This holds a worker without doing anything until getting a response that the QS task reload is completed. 

  • post /task/{id}/start
https://help.qlik.com/en-US/sense-developer/February2022/APIs/RepositoryServiceAPI/index.html?page=1330

This is to trigger the QS reload task asynchronously, i.e. to trigger and complete.   Since the reload is inside Qlik Sense, if there is any issue, it could be reported from the Qlik Sense monitoring setup.  And more importantly, it can free up the Alteryx worker for other important tasks.

 

 A sample CURL command is illustrated as below to trigger QS Task asynchronously is as below:

"E:\Qlik Sense API\CURL\bin\curl.exe" -X POST --cert "E:\Qlik Sense API\Certificate\client.pem" --key "E:\Qlik Sense API\Certificate\client_key.pem" --insecure -k "https://[Server FQDN]:4242/qrs/task/[Task_ID]/start/?xrfkey=0123456789abcdef" --header "x-qlik-xrfkey: abcdefgh0123456" --header "X-Qlik-User: UserDirectory=internal;UserId=sa_repository" --header "Content-Type:application.json" --header "Content-Length:0"


The parameters are explained in the following:

-X

It is to specify the HTTP request is using which HTTP method for request.  The API call is required to use POST.


--cert 

The root certificate or the exported certificate from Qlik Sense server.


--key

The root key.  *Please use securely.


--insecure

Just to avoid SSL issue.   It is recommended to remove this if all underlying infrastructure is working fine.


-k "https://[Server FQDN]:4242/qrs/task/[Task_ID]/start/?xrfkey=0123456789abcdef"

To specify the API endpoint like the server address, server port, the QS Reload Task ID.  The xrfkey is to be provided both in the query as well as in the HTTP header.


--header "X-Qlik-User: UserDirectory=internal;UserId=sa_repository"

This is to impersonate the account to execute the reload.  Normally, it should be internal as the user directory and sa_repository as the user ID.


--header "Content-Type:application/json"

To specify the content provided in body is using JSON.   Even no content, we specify as JSON because all QS API is using JSON.


--header "Content-Length:0"

Since this request is a POST and there is not content required, 0 length is provided to the HTTP header.


The remaining is to put the API command into the Alteryx Command tool and let it trigger CURL to call the Qlik Sense Repository Service API.   I don't go into detail this time.  If you need help on it, leave me a message.



Thank you for reading.  I hope it help you.  Just leave a message if you have any discussion/share want to make.


Thursday, August 17, 2023

Alteryx QVD Output Tool - Prototype

 Post Index

2023-08-17


Alteryx QVD Output Tool

Alteryx Custom Tool




In the previous post, the Alteryx QVD Input Tool prototype is shared and following in this post, the Alteryx QVD Output Tool will be introduced.

It reads the data from Alteryx and then convert into QVD, i.e. convert each column into a list of symbols and symbol indexes and then compact each record  by the symbol index into the least bits required to store the record data.  XML information is saved in memory during the processes.  Once everything is ready, it flushes out the XML, symbols as well as the records.


Alteryx QVD Output Tool

The Alteryx QVD Input Tool is very simple.  It just takes in a QVD file and read all the content then convert it as an Alteryx output stream.  The input UI is as below.



* the new Alteryx SDK is now using reactjs where it is not possible to get through the security to get the full path.  Thus, there is no button to pop up a dialog to ask for file location.  Instead, there is only a textfield for inputting the path.  If you have any clue to get this through, it is welcome.  The prototype is hoping to show the possibility to integrate with QVD files.


The prototype

If you hope to try it, you can download it in my github.  https://github.com/kongson-cheung/Alteryx-QVD-Tools/blob/main/yxi/QVD%20Tools_v1.1.yxi

I have share the core files to create this Alteryx QVD Output Tool.  Since the SDK includes a large number of files, I did not upload them all.  If you need any help, feel free to drop me a message.


* Note: this is still very early version of prototype.  It still requires a number of improvements for intensive use.


Next

I will try summarize how to develop the Custom Alteryx Tool.


Thank you for reading.  I hope it help you.  Appreciated your sharing if you have any discussion/share want to make.



Monday, August 7, 2023

Alteryx QVD Input Tool - Prototype

 Post Index

2023-08-07


Alteryx QVD Input Tool

Alteryx Custom Tool


With the findings in the previous post (QlikView Data File (QVD) - Reverse Engineering), I have developed a prototype of the Alteryx QVD input tool.  Alteryx is an extremely good tool for data wrangling and contains a bundle of tools that allow simple data transformation and predictive analysis.  However, it does not have the ability to deal with QVD.  It only allows to read and write QVX.

Alteryx, in fact, is a good upfront data stream for QlikView and Qlik Sense.  It can help business to make clear use of data with drag and drop capability to explore, transform and try new business logics.  QVD integration would be benefitial for Qlik at the lower data stream in the data cycle.   Sound like advertisement but it is real project experience to conclude this.  QVX does not work great with heavy usage.  The performance is similar to CSV.  Still, QVD is the best for Qlik.


Alteryx QVD Input Tool

The Alteryx QVD Input Tool is very simple.  It just takes in a QVD file and read all the content then convert it as an Alteryx output stream.  The input UI is as below.


* the new Alteryx SDK is now using reactjs where it is not possible to get through the security to get the full path.  Thus, there is no button to pop up a dialog to ask for file location.  Instead, there is only a textfield for inputting the path.  If you have any clue to get this through, it is welcome.  The prototype is hoping to show the possibility to integrate with QVD files.


An Example

Taking a QVD file as an example.



The QVD file contains 2 columns named Num and Text.  It has total number of 4 records.  In Alteryx, the result runs as below.

If you hope to try it, you can download it in my github.  https://github.com/kongson-cheung/Alteryx-QVD-Tools/blob/main/yxi/QVD%20Tools_v1.0.yxi

I have share the core files to create this Alteryx QVD Input Tool.  Since the SDK includes a large number of files, I did not upload them all.  If you need any help, feel free to drop me a message.


* Note: this is still very early version of prototype.  It still requires a number of improvements for intensive use.


Next

The Alteryx QVD Input Tool is made up by Platform SDK, UI SDK, Python SDK.  This is a first prototype for reading QVD.  I am exploring a prototype of Alteryx QVD Output Tool that write QVD as output.


Thank you for reading.  I hope it help you.  Appreciated your sharing if you have any discussion/share want to make.