A quick performance comparison with Qlik Sense – AWS EC2 vs Azure Virtual Machines

Previously, I tested the performance of a load script while using RecNo() and RowNo() functions. This conveniently gave me a script which consumes up to 25GB of RAM, along with considerable CPU power.

So, what about testing it on two cloud boxes? I’ve chosen a machine from both AWS and Azure, loaded them with Qlik Sense September 2018 and run the load script.

Total Test Duration by Host

The summary: The AWS box was approx 8% faster than the Azure box.

Continue reading “A quick performance comparison with Qlik Sense – AWS EC2 vs Azure Virtual Machines”

Comparing Autonumber, Autonumberhash128, Autonumberhash256, Hash128, Hash160 and Hash256 outputs in Qlik Sense and QlikView

There’s often a discussion about what each of these autonumber/hash functions does in Qlik. We commonly see these functions used for creating integer key fields, anonymising data (for example, names in demo apps), and maintaining long string fields for later comparison (as the hashes are shorter than the strings they replace).

Sample outputs from the random number generator, with all the functions present

To do this, I’m using the script below. I’m also keen to show outputs from QlikView vs Qlik Sense, and results of running the same script on another machine.

My observations are the following:
AutoNumber/AutoNumberHash128/256 – different output per load as the value is typically based on the load order of the source data
Hash128/160/256 – the same output, across every load. Stays the same between Qlik Sense and QlikView, and also between different machines

Continue reading “Comparing Autonumber, Autonumberhash128, Autonumberhash256, Hash128, Hash160 and Hash256 outputs in Qlik Sense and QlikView”

Qlik load performance with RecNo() and RowNo()

Using RecNo() or RowNo() will impart a performance impact on your load script. I discussed these functions in a previous post where I looked at the output of RecNo vs RowNo.

I recently spotted an unexpected slow-down in a load script, which was caused by using one of these functions. In summary:
– Using RowNo() in a simple load script is considerably slower than RecNo()
– If you must use RecNo(), it may be faster to do this in a direct load
– If you must use RowNo(), it may be faster to do this in a resident load

Example script for one of the tests – load data from disk and add the RowNo

 

Continue reading “Qlik load performance with RecNo() and RowNo()”

Qlik Counter Functions and their outputs – RecNo() and RowNo()

In this post I explore the outputs of RecNo() and RowNo() to demonstrate the difference visually.

These two fields are often used interchangeably, but they provide different output. In summary:
– RecNo() is a record number based on source table(s)
– RowNo() is a record number based on the resulting table

As a result, RowNo will always provide a unique integer per row in an output table, while RecNo is only guaranteed to be unique when a single source is loaded (RecNo is based on each single source table, interpreted individually rather than collectively).

A snapshot of the test output

Continue reading “Qlik Counter Functions and their outputs – RecNo() and RowNo()”

Qlik Deployment Frameworks – using common approaches, naming conventions and paths across developers

Any environment with more than on developer will quickly lose consistency of attributes across the environment. Agreeing standards as part of developer onboarding, and validating these before app acceptance is very important.

An example set of naming conventions is discussed below. This assumes a common directory structure similar to a Qlik Deployment Framework (QDF) model.

The QDF helps to organise application components
The QDF helps to organise application components

The example below follows a concept of one common container (for data and files which aren’t app specific), and a hierarchy of product (one or more applications developed for a specific purpose) followed by customer (a standalone version of those applications, loaded with different data).

The resulting directory structure is therefore Root > Product > Customer.

Continue reading “Qlik Deployment Frameworks – using common approaches, naming conventions and paths across developers”

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)”