QlikView and On Demand App Generation (ODAG)
QlikView is well suited to in memory analysis of large data sets but it struggles with true big data scenarios. On demand app generation (ODAG) addresses the big data problem but requires some special techniques involving the exchange of more information than can be sustainably handled in a URL. This article discusses a method of on demand app generation in QlikView and how to build an extension object to get around URL length limitations (*this is not as boring as it sounds, honest!)
Back in 2009, when I started using QlikView, a big data set would be considered in the region of 10 million records and could be comfortably handled on a 4 core server running with 36g of RAM. Fast forward 8 years and I’m regularly seeing data sets in the 100s of millions running on beefy servers with a minimum of 16 cores and 256GB of memory.
I did some work for a client who runs a retail sales business. 2 years worth of data amounted to around 7 billion records and that was just their transactions table. Analysing this amount of data is tricky for any in memory system. Qlik anticipated this and in 2012 released Direct Discovery as a way to address the trend for ever bigger data sets. However, Direct Discovery didn’t quite hit the mark and as such, Qlik have themselves been pushing the “on demand app generation” method as the preferred way of dealing with Big Data in QlikView and QlikSense.
The principle is actually quite simple:
- User selects a ‘shopping basket’ of dimensions from a basket application (These selections are serialised into a URL)
- The URL points to a web service which clones a slave QVW and passes the parameters to publisher which reloads the document with the given parameters.
- The user is redirected to the newly created QlikView app.
However, setup is not for the faint hearted; getting this up and running in QlikView will involve some tricky scripting and your web service has to be written in a .NET language. The end result though is a robust and highly scalable solution to the problem of in memory analysis of big data.
The reason for writing this post though, is to present a solution to one of the limitations you will surely come across if you are responsible for the development and maintenance of a solution like this.
Transferring data in a URL
To get this solution to work, you need to find a way of transferring the selected dimensions in the ‘basket application’, to the web service. QlikView gives you an out of the box solution in the Open URL action. You need to hand code it, but it’s pretty straightforward to collect the selected dimensions and build them as a querystring to your request. Your request will look something like the diagram below and I’ve annotated it for those who are unfamiliar with URLs.
This approach works fine for a while, but eventually, you are going to run into limitations:
- Web standards dictate that a URL should be a maximum of 2083 characters long. Some browsers ignore this, but you can never be sure which browser your user is going to use.
- The URL is easy to tamper with and transferring data in open view may contravene data confidentiality agreements your client may have.
- URLs are designed for getting information from a server (in web speak, they are referred to as GET requests) but in this case, we are using it to tell the server something. This is bad practice and breaks web standards.
- URLs have to conform to stringent rules. If you have any non alpha-numeric characters in your data (which most of us do), then you’re going to have a bad time.
2083 characters may seem a lot, but it doesn’t take long to break that limit. In the example above, we’ve only transferred 4 data points with just one value each and we’re up to 105 characters. In real life, you will be transferring multiple data points with potentially hundreds of values each. We need to find a different approach.
POSTing data to the web service
We talked about transferring data in the URL and the associated drawbacks. The correct way to do this is to POST the data. Posting involves sending the data to the web service in the header request that goes from your browser to the server. This information is hidden from the casual user and the rules for the type and amount of data are far more relaxed. The problem is, QlikView does not include an object that supports the POST action. The solution? A very simple extension object.
POSTing using an extension
QlikView extensions bring the full power of the web to your QlikView document. Anything you can do on the web you can do in your QlikView environment*. The solution here then was just a very lightweight extension to allow a ‘Submit’ style button to be placed on the page which would then allow values to be POSTed to the web service.
I’ll not go in to too much detail here on extension development; Qlik’s own documentation on extensions is very good and all you need to get started. The code is included on this page as a self installing QAR file along with the source. The source code is liberally commented and easy to follow and will give you a very good idea of what is going on. You can open it in any text application (eg Notepad, Sublime Text or Notepad++) .
Instructions on how to install the QAR can be found on Qlik’s help site. The extension allows the Qlik developer to customise the submit button and then updates the HTML on the page to prepare an HTML form object to POST the data to the webservice. Detailed styling of the button can be acheived by editing the CSS file bundled in the extension.
Usage of the extension
Download the extension (included at the bottom of this page) and double click to install. The extension is unpacked and moved to a user folder on your desktop. Launch a QlikView document and then in Settings >> Document Properties >> General select Use WebView in Layout. You are now ready to start using the extension in your document.
Adding the Extension
- To add an extension, right click on an empty part of a sheet and click on New Sheet Object.
- In the New Sheet Object dialogue box, click on Extension Objects. Drag the Form Post Button onto the sheet.
- The Form Post Button extension is now active on the sheet.
Configuring your extension using the Properties Box
In web view, QlikView’s properties box looks and behaves differently to the properties box you are used to in the standard view in QlikView. From a functional view point expressions and calculations behave the same and you will have access to the familiar expression editor. The only thing to look out for is how the extension handles the enable/disable condition as noted in point 2d below.
- Right click on the extension and click Properties
- Edit the customisable parts of the Form Post Button:
- Button Text: The button text eg Submit.
- Webservice URL: The URL to POST the data to eg https://www.mywebservice.co.uk/webservice/v1/.
- Parameters: The data to POST to the webservice. The data is posted as one single value with a name of postData. Serialisation of the postData data is up to the developer. In the following example, the pipe character has been used to separate the name value pairs with the block.
- Enable Condition: This takes an expression that will evaluate to 1 (enabled) or 0 (disabled). This differs to the standard Enable Condition of an object where a predicate is allowed.
- Use the Presentation, Caption and Options sections to customise the look and feel of the rest of the object. For example, you can remove the caption and border to give the button a stand alone look.
- And you’re done. If you were to do a view source on the snippet of HTML that the extension creates once it’s up and running, it should look something like this:
*Requires a web view and a web standards compliant browser.
Download the extension here: