January 19, 2013

Using web-services with QlikView

There is a number of ways to establish bi-directional integration between QlikView and external systems. Some of them employ web-services. In this case a web-service is a helper application which sits between QlikView and external application (or is part of that external application) and communicates using web-protocols. Web-services can be written in any popular programming language (PHP, Java, C#, Python, Ruby, Go etc.) and hosted using popular web servers like Apache, IIS or nginx or any other of your choice.


Let's consider several architectures:

Using web-services during loading script execution

In this scenario data is exported by means of the QlikView loading script. It can be done in several ways:

For small data sets we can simply pass it through URL when calling web-service (actually, using HTTP GET). However in this case URL encoding may be required, which can be a tricky thing since QlikView doesn't have native URL encoding functions. But if all you need is to send integer number or ASCII string this could be enough. Example:

LOAD * FROM [http://mywebservice/setparam?param=$(ParamValue)];

Since there is no way to perform HTTP POST request from loading script therefore for big chunks of data we can, for instance, export them into CSV file(s) into specific folder (this can be shared network folder). If necessary, use external command to upload the CSV file(s) to a place accessible from the web-service. Upload can be performed using FTP, SCP or any other similar service which, however, may require installing additional software along with the web-service.

Receiving data is as simple as opening Internet file. For instance:

LOAD * FROM [http://mywebservice/getresult];

Using web-services within loading script is relatively easy, however this approach has one significant drawback -- it's not interactive. As soon as application is reloaded no more interaction with web-service possible. To make it interactive there are at least two other methods:

Using web-services in VB macro

In this case communication is performed by sending and receiving XML objects using HTTP calls between VB macro script in QlikView and the external web-service. On QlikCommunity there is a very good example with sample application. Add to this example Dynamic Updates (here is another excellent Dynamic Updates example by Matthias Dix) and you can reach nirvana -- dynamic bi-directional integration with 3rd party systems.

Using web-services with QlikView extensions

One more approach which eventually may become the main way of doing integration. In this case you would want your web-service return JSON instead of XML as it is more convenient format for extensions. Everything else is the same -- use AJAX to exchange data between extension and web-service. If your experience with Javascript is limited you may consider using libraries like jQuery or Zepto both of which provide pre-built functions for AJAX.

Unfortunately there is no yet Dynamic Update Javascript API for extensions (correct me if I'm wrong), so updating dynamically in-memory data model cannot be done natively.  There can be some dirty hacks like binding some VB Macro to OnChange action for a variable and then assigning some value to this variable from within extension, but I wouldn't rely on them. Anyway, I suppose that Dynamic Updates extension API will appear sooner or later since QlikView.next is going to be heavily web-based.

What is good about extensions is that because browsers are event-based machines (contrary to VB runtime) therefore with the help of long polling or websockets you can push data from external applications into QlikView. It means that unlike the VB macro method, where data exchange is initiated by QlikView, in case of extensions it can be initiated by external applications. It means they will be able to change variables, force selections or (when Dynamic Update API will be ready) insert new values into QlikView data model.

And this would be complete integration nirvana :)