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”

Having a look at UK Highways Agency Traffic flow data – in Tableau Public and Qlik Sense (Cloud)

I’ve had a copy of some of the Highways Agency data downloaded for about a year – so it was time to have a quick look at it.

I’m going to quickly load a single month into Tableau Public and Qlik Sense Cloud (the CSVs are approx 850MB per month) to look at some basic mapped traffic flow information.

The data

The data in this post comes from the Highways Agency journey time and traffic flow data on the data.gov.uk website. Unfortunately this data stops at the start of 2015 (data from then on is incomplete) and is now available via the webtris system APIs, although this post looks at data from March 2012.

 

Tableau Public vs Qlik Cloud Maps

Continue reading “Having a look at UK Highways Agency Traffic flow data – in Tableau Public and Qlik Sense (Cloud)”

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”

Integrating QlikView into SalesForce SFDC with SSO – forcing TLS1.1 or higher

Integrating some QlikView screens into Salesforce, and using Salesforce to authenticate your users is actually very simple. Joe Bickley from Qlik wrote about setting up Salesforce SSO back in 2012 – and I thought I’d have a go.

The only road block encountered along the way was Salesforce’s decision to disable TLS 1.0. They have a number of very good reasons, including the famous Poodle vulnerability. It meant I experienced the error below:

UNSUPPORTED_CLIENT: TLS 1.0 has been disabled in this organization. Please use TLS 1.1 or higher when connecting to Salesforce using https.

on

AppExchangeAPI.GetUserInfoResult userInfoResult = binding.getUserInfo();

This isn’t yet a mandatory change for production organisations, but it will be by 22/07/2017. Any development organisation will have it enabled by default, without any method for disabling. Other services do not have a disable date at the time of writing this post.

Uh oh, things aren’t well with SFDCLogin.aspx

Continue reading “Integrating QlikView into SalesForce SFDC with SSO – forcing TLS1.1 or higher”

Setting up a CentOS 7 local development VM with LAMP (Linux, Apache, MariaDB/MySQL, PHP+PHPMyAdmin)

I’m going to build a local VM with the following requirements:
1) It can host PHP/SQL-based websites
2) It has PHPMyAdmin to help administer any SQL databases
3) It matches available builds from popular providers (i.e. you can provision it in a similar way on Azure or AWS, but with a public domain name)
4) It only has a single account (this is not recommended for public systems)
5) I can access the web root using SFTP

As I already have several CentOS builds that have always been pre-setup with CPanel (and because CentOS is free), I’ve decided to do this build from scratch and without a control panel. I’m not going to be configuring options like multiple user accounts, so things will be fairly simple.

I’ll do it in steps (and test each time) to make sure everything’s working correctly. You could install everything all at once, but that would make it much harder to troubleshoot if an element didn’t work.

Continue reading “Setting up a CentOS 7 local development VM with LAMP (Linux, Apache, MariaDB/MySQL, PHP+PHPMyAdmin)”

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”

CentOS 7 Linux Minimal Install – ifconfig: command not found (let’s use ip instead)

As someone who doesn’t closely follow what’s happening with Linux tools, I recently tried a minimal CentOS 7 install, and found out by chance that a command I use a lot on Windows has been deprecated in a number of Linux releases.

Not having run into this before, my first thought was to see if it was in the core repos.

yum provides ifconfig

ifconfig is part of the net-tools package

Continue reading “CentOS 7 Linux Minimal Install – ifconfig: command not found (let’s use ip instead)”