App duplicator service for Qlik Sense

 

I worked on a small node.js server during the past couple of weeks. The idea came from the fact that

  1. I over and over again find myself creating Sense apps that are almost identical, and
  2. people starting out as Sense developers spend too much time learning the basics. I wanted to bootstrap their learning process by providing well written skeleton apps for them.

This could of course be achieved by just duplicating apps using the QMC, but it would be way better if there was a nice little web app that listed the available Sense app templates, and allowed anyone (with permissions to create apps) to create new apps based on them. Or maybe this feature could even be integrated into one of the different Sense hubs that are now available…

Continue reading “App duplicator service for Qlik Sense”

The beauty of script autocompletion in Qlik Sense

Such a simple thing – but oh how useful.

One of the recent versions (3.0? 3.1? 3.1.1?) of Sense added much improved syntax color coding of the load scripts, as well as autocompletion of function names, field names, variables etc.

What might seem like a small change is at least to me super, super useful. I can suddenly keep my hands on the keyboard, and don’t have to switch to another browser tab to check in the app data model to check the exact name of some field – I just type a couple of characters and a list of suggestions pop up. Productivity is up and frustration down – that is a very good combo.

Continue reading “The beauty of script autocompletion in Qlik Sense”

Butler for Qlik Sense version 2.0

Butler 2.0 is out!

Champagne

A summer project is over, and Butler  has been released in a version that is a total rewrite of Butler version 1.x. Butler 2.0 includes features such as

  • Real-time monitoring of what users are active on a Sense Enterprise system
  • Real-time monitoring of failed reload tasks, with notifications sent as emails, to Slack channels and as MQTT messages
  • Starting Sense reload tasks from the load script or from external systems, using a REST API
  • Posting messages to Slack from the reload script. Great for sysadmin notifications as well as notifying end users that new data is available in an app
  • Sending MQTT pub-sub messages from the load script
  • Get full metadata, including load script, for any app
  • Getting free disk info for the Sense server
  • …and more

All in all, Butler 2.0 includes a set of features that offers greatly enhanced features for anyone involved in the operation of a Sense Enterprise environment.

Butler is available via Qlik Branch, with all source code hosted on GitHub.
Documentation is available on github.io. That documentation is the best starting point for learning more about what Butler does and how to use it.

Some sample screen shots shows what is possible to achieve:

Continue reading “Butler for Qlik Sense version 2.0”

Qlik Sense data model of the year

Qlik-Sense-Logo1Long overdue, but during last few months I have finally started to play around with the various APIs available on the Qlik Sense platform.

It has been said before, but it is worth repeating: those APIs are really, really powerful. They are one of the main reasons why I think Qlik have some good years ahead of them.

Anyway – a recent experiment involved serialising apps to JSON text files, so they can be stored in Git in an effective way. Works really well and is surprisingly simple to achieve thanks to the groundwork done by members of the Qlik developer community.
So far so good, but the JSON blob describing a Sense app is extremely large and hard to navigate. Continue reading “Qlik Sense data model of the year”

Butler for Qlik Sense – connecting Sense to the world

QlikLogoThe goal of the previously described Slack proxy project was to allow posting of messages from Qlik Sense load scripts to Slack (great instant messaging platform!). The project has however developed into something larger and slightly more ambitious, integrating other connections than Slack. The original project name “Slack proxy” has become less and less relevant, and the project has thus been renamed to “Butler”.

As of right now (May 2016), Butler’s feature set include

  • Send messages from Sense load script to Slack
  • Send/publish MQTT messages from Sense load script (i.e. outbound MQTT).
  • Sense reload failures as emails, and to Slack.
  • Sense audit events (session start/stop and connection open/close) to Slack and to MQTT messages.
  • Create new directories on the Sense server’s (where Butler is running) disks.
  • Get disk space info, for disks on the Sense server where Butler is running.

Butler version 1.1 was just released, please refer to the Butler GitHub repository for further information. Feel free to fork it and contribute if you feel some feature is missing –  the node.js app should be pretty easy to understand and extend upon.

URL encoding in Qlik Sense and QlikView

URLWith so much cool data available online, tools like QlikView and Sense becomes real Swiss Army knives of data integration.

Pull in some data from company internal databases, some data from previously created QVDs, and more and more commonly also from various online sources, both public and private. Depending on how you call those online APIs you might get away with just sending in query parameters as they are, but in other cases – and this is especially true if you need to send more complex text strings to the API – you need to URL encode the query parameters.

Over the years I have run into this numerous times, but the other weekend I realised it’s actually very easy. Just create a mapping table using an online source for the utf8-to-URL encoded mapping, then use MapSubstring to convert each character in the URL parameter to its hex counterpart.

Code for this found over at GitHub, it’s just 10 or so lines of code.

Occam’s razor holds true again – the easy solutions prevail and are usually preferred!

 

Posting to Slack – and more – from Qlik Sense load scripts (part 3)

Quick update to the previous post on this topic.

There is a new version of slack_proxy available on GitHub. It adds a new endpoint for creating directories on the server where slack_proxy is running.

This obviously means that the apps name – “slack_proxy” – is not really that relevant or correct any longer… And with more end points considered, the name will become even less correct…. Oh well – I’ll keep the name for now – maybe it will change sometime up ahead.

The endpoints currently supported are:

  • /slack for posting to Slack
  • /createDir for creating directories on local disk

Other endpoints on the radar are for sending tweets, sending messages to Pushover, and controlling Blink(1) USB lights.

There is also now reasonably complete documentation on GitHub.

Generating test data for QlikView and Qlik Sense

Real data and test data – both are needed

One of the great things about both QlikView (QV) and Qlik Sense is their integrations with other systems. Given the native connectivity to any ODBC source, web pages in general, Salesforce, REST APIs, BigQuery etc – combined with the dozens of connectors provided by tools like QVSource – I have yet to find a single system or data source we could not pull data from.

The great thing about this is that it makes it easy to quickly get some real data and then start building your application.

That said, sometimes you need a limited data set to start with, or just some conceptual data to try out an idea on. Enter online test data generators, which can be used to generate test data for

Continue reading “Generating test data for QlikView and Qlik Sense”

Posting to Slack from Qlik Sense load scripts – web service style (part 2)

As the first version of the Sense-to-Slack integration required you to lower the Sense security level, it is probably not a good option for enterprise Sense users, for example.

Instead, let’s set up a small node.js web service, which exposes an API that we can call just like we can retrieve data from any web page in the load script.

There are a couple of pre-requisites:

  1. Node.js must be installed on the machine where Sense is running (or some other machine in your network – for sake of simplicity I assume it’s on the same machine). You also need to have the Node libraries Restify and Node-Slack installed.
  2. You also need Qlik’s REST connector, at least if you are to avoid some messy URL character encodings. More on that later.

Setting up the Node.js server

Create a directory where you will store your Node apps. C:\node or something similar – you decide what’s suitable on your system. Then, get the code for the node server from GitHub. The code is pretty basic (make sure to use your own Slack web hook URL):

var restify = require('restify');
var Slack = require('node-slack');

var slackWebhookURL = '<fill in your web hook URL from Slack>';
var slack = new Slack(slackWebhookURL);

function respondSlack(req, res, next) {
// console.log(req.params);

slack.send({
 text: req.params.msg,
 channel: req.params.channel,
 username: req.params.from_user,
 icon_emoji: req.params.emoji
 });

res.send(req.params);
 next();
}

var server = restify.createServer({
 name: 'SlackProxy'
});

server.use(restify.queryParser()); // Enable parsing of http parameters
server.get('/slack', respondSlack);

server.listen(8080, function() {
 console.log('%s listening at %s', server.name, server.url);
});

Run the server:

C:\node\slack_proxy>node slack_proxy.js
SlackProxy listening at http://[::]:8080

Log Slack entries from Sense load script, using the REST connector

Connection to Slack proxy

Now, create a new Sense app to try out the notification. In that app, set up a new data connection using the REST connector:

With the new connection in place, use the rest connector to connect to the proxy, and let the REST connector generate the code for you.

Finally, add the “WITH CONNECTION”… statement to send in the actual data that should be passed on to Slack. A good thing here is that the REST connector handles encoding of special characters like space, # etc – no need to do that manually. Very nice!

The resulting load script looks something like this (your data connection names will differ, of course):

LIB CONNECT TO 'Slack';

RestConnectorMasterTable:
SQL SELECT 
 "channel",
 "from_user",
 "msg",
 "emoji"
FROM JSON (wrap on) "root"
WITH CONNECTION (
 QUERY "channel" "#general",
 QUERY "from_user" "SenseBot",
 QUERY "msg" "Posted from Sense",
 QUERY "emoji" ":smile:"
 );

Slack:
LOAD 
 [channel] AS [channel],
 [from_user] AS [from_user],
 [msg] AS [msg],
 [emoji] AS [emoji]
RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

Reload the app, and voila – in Slack we get this:

Slack message created by Sense load script

Mission accomplished.

Log Slack entries from Sense load script, using the FROM command

If you don’t want to use the REST connector, or don’t have it installed, there is another way of achieving the same result as above.

Your Sense load script will instead look something like this:

Slack:
LOAD
 *
FROM [http://localhost:8080/slack?channel=%23general&from_user=SenseBot2&msg=Another%20message%20from%20Sense&emoji=:smile:]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Notice how we have to convert space, # and other “special” characters to their hex equivalents? You could write a small function that does this for you (don’t think Sense has any built in function for URL escaping – or?), but with the REST connector being so easy to use – that should be the preferred option for most people.

In this case we get a Slack message like this:

Slack message using FROM command