Viewing Department for Transport vehicle statistics by make in Qlik Sense

Generally, getting access to vehicle data is a pay-for-data experience – but snapshots are released each year to the public.

If you head to the Vehicle Statistics page on gov.uk you get statistics about vehicle licencing, excise duty evasion, vehicle roadworthiness testing and more. You’ll probably want to check out the index as there’s 76 files in the 2016 download alone, at various levels of granularity…

The one I’m going to look at today though, is:

Licensed Cars, Motorcycles, Light Goods Vehicles, Heavy Goods Vehicles, Buses and Other vehicles by make and model, Great Britain, annually from 1994, quarterly from 2008 Quarter 3 ; also United Kingdom from 2014 Quarter 4

AKA table VEH0120. Interestingly, Qlik Sense throws an “Unknown Error” message when trying to load “.ods” files so I converted it to Excel prior to loading.

Continue reading “Viewing Department for Transport vehicle statistics by make in Qlik Sense”

Connecting to Google Analytics in Qlik Cloud (Qlik Sense) through the Qlik Web Connectors package

This post looks at using Qlik Cloud (using a paid-for tier) to gather and analyse data from Google Analytics.

Qlik Cloud doesn’t allow user-added extensions, connectors or alterations (unlike the Qlik Sense Desktop and Enterprise products), but is free at the basic tier where you can load from uploaded files. As mentioned, I am using a “Plus” account which gives me access to more connectors.

Continue reading “Connecting to Google Analytics in Qlik Cloud (Qlik Sense) through the Qlik Web Connectors package”

Connecting to MySQL Community Edition with Qlik Sense (System DSN with MySQL ODBC driver)

As of Qlik Sense 3.2.2, the Qlik ODBC Connector package does not connect to MySQL Community Edition servers.

A large number of Linux web servers run on the community edition, so it’s handy to be able to connect and extract information from this data source as well.

Error message: ERROR [HY000] [Qlik][ODBC MySQL Wire Protocol driver]Connections to MySQL Community Servers are not supported. Please contact MySQL to obtain a MySQL Enterprise or Commercial version.
As we can’t use the connector package, we must instead load the MySQL driver and use a Windows DSN. Continue reading “Connecting to MySQL Community Edition with Qlik Sense (System DSN with MySQL ODBC driver)”

Sorting Alternative Dimensions and Measures in Qlik Sense

Alternative Dimensions and Measures have been in Qlik Sense since version 2.2 (see a video on Qlik’s site describing how to use them) – but there isn’t yet an easy way to sort them (as of version 3.2.2).

 

This bar chart object has multiple alternative dimensions and measures (note the arrows next the the axis titles)

 

When configuring charts with alternative dims and measures you’ll find you can only configure the sort order for the currently active dimensions and measures – nothing else will be visible or configurable.

Continue reading “Sorting Alternative Dimensions and Measures in Qlik Sense”

Installing a SSL certificate to Qlik Sense Enterprise on Windows Server 2012 R2

In this post I’m going to look at how quick and easy (and cheap) it is to procure and install a SSL certificate on your Qlik Sense deployment. This assumes you are starting with only the self signed certificates, and that you want to use a certificate generated by a signing authority for use on an externally facing site.

A couple of things to note:

  • I’m using Qlik Sense Enterprise 3.1 with a single-node deployment using the default settings
  • You have a choice of verification methods – I chose to use DNS by adding a CNAME (pointer) to my chosen domain, and managed this through a linux DNS host. You can also verify through email or http (placement of a file)
  • I’ve used a basic certificate from PositiveSSL that offers only domain validation (DV). Certificates offering greater levels of protection and assurance are also available
  • The server is running Windows Server 2012 R2 with IIS 8, which is up-to-date with the latest updates at time of writing (January 2017)

 

On a clean installation of Qlik Sense Enterprise, you’ll note that the domain fails SSL validation in most browsers. Why? Because the certificate is one that has been generated by your server, and not by a “trusted” certificate authority. Have a read of this page about Certificate Authorities if you’re after further detail.

Continue reading “Installing a SSL certificate to Qlik Sense Enterprise on Windows Server 2012 R2”

What is Big Data? Thinking about a definition suitable for me

I recently worked on an app that was loading from several hundred gigabytes of CSVs, and attempting to perform expensive transformations on these files in Qlik Sense. Normally this isn’t a problem, but due to the way the transformation was written, the result was a saturated server…and I found myself reflecting what “Big Data” means to different people (and to myself).

A recruiter’s post on LinkedIn also made me chuckle, as it highlights the disparity between definitions well. From my view of big data, I doubt that someone working in that field is likely to be interested in a role where one of the three core job skills is Excel…

 

Strong in Data Manipulation/Excel/Big Data – so which is it?

 

My observation is that there are two camps – one side that classifies using the “V’s”, and another using an altogether simpler definition! Continue reading “What is Big Data? Thinking about a definition suitable for me”

Qlik Sense and general port troubleshooting on Windows Server 2012 R2

One of the beauties of Qlik Sense Enterprise is that it can be installed on nearly any Windows machine, with very few pre-requisites.

One of the drawbacks is that you may then be tempted to load it onto a multi-purpose box, or at the very least, one with a number of core Windows roles installed (especially if you’re just testing).

Uh oh, it's a 404
Uh oh, it’s a 404

This post is going to offer some quick ways of resolving port clashes, and working out what’s binding where without changing the core Qlik Sense Enterprise installation.

Continue reading “Qlik Sense and general port troubleshooting on Windows Server 2012 R2”