Growing up I was never a sports fan.

Football, or anything with a bat, club or racket, just didn’t hold my interest.

Basically, I was a computer geek.

As I’ve grown older, my interests have widened. Now you could describe me as someone who participates in sporting activities – but strictly on my own terms.

What I have never understood is the British fascination with football or soccer. Naturally during an uprising of national pride in the “beautiful game” I would participate and cheer on, but probably not watch, international games.

That interest changed quite unexpectedly when several months ago I began work on a QlikView project with a major English Premiership club.

Like most clubs at the top of the most watched football league in the world, they had money to spend on players, facilities and staff, but other than points or “games in hand”, little to show for it.

What you may not know, is that these clubs are awash with data. Which player made an attempt at goal in what minute, whether it was a success, how long they’ve played, yellow cards, red cards, you name it. There are hundreds of metrics by which a player can be measured.

Then you add training plans, heart rate, blood pressure, etc and suddenly you have the potential to select a team on paper that consists of players at their optimum peak.

I say “on paper”, because major league sports are not typically coached by accountants or analysts.

Coaching is more about action and motivation than numbers and paperwork.

So I found myself in a technologically unsophisticated environment, building a dashboard to support the decisions made by the coaching staff. And I loved it.

Something about the data brought the game to life. I’d recognized player names, but seeing their individual performance, or how they ranked against other players in the same position, pushed me to check the teams result in the newspaper, check their position in the league and read sports section articles on the management and strategy.

Some are brought up to embrace football; others find comradeship in the stands or on the bleachers. My interest arose out of a desire to visualize data.

American sports have always been driven by statistics. I hope that owners of UK football clubs, like John Henry of New England Sports Ventures and owner of Liverpool FC, bring some of their insight to the Premiership.

A great example of using QlikView with sports data can be found at www.sportsdatahub.com

As a consultant providing QlikView solutions I have worked with some very different organisations on delivering business intelligence using QlikView.  These range from a relatively small outfit providing pump and drainage services, through to a number of household names in the UK.  I also use it to track my own company accounts.

This begs the question who is QlikView aimed at as a product and whose needs does it best address?

Let’s start by looking at the large end of the spectrum.  The biggest document that I have worked on analyses over 250 million rows of insurance premium and claim data.  Despite this huge number of rows and a large number of demographic fields the document still performs quickly.  This is the largest document I have seen in production, but QlikTech removed the row limit that existed pre version 9.0 (a limit of more than two billion, that is) and there are apparently documents out there with trillions of rows.  That solves it then – QlikView is a product for large multinational corporations with terabytes of data and hundreds of users.

However it isn’t quite as clear cut as that.  There are some large corporates using QlikView that are happy to provide case studies, but there are others that whilst they make great use of QlikView are not happy to stand up and be counted as reference sites.  Why is this?  It is my opinion that the sales messages around quick implementation times and low total cost of ownership give the impression that the product is not as enterprise ready as it actually is.  Large corporates seem to take comfort in very expensive product and project lifecycles of well upwards of six months (typical of an OLAP type implementation).  Personally I feel that the sales message to corporates needs to be around a properly constructed project, following a sound methodology from requirement gathering through to deployment.

So, what about the smaller companies out there?  Well, these are the organisations where QlikView continues to thrive and has a substantial foothold.  Many QlikTech partners still offer one day ‘Seeing is Believing’ sessions (or SiBs) where a company can see their own data loaded into QlikView and have a proof of concept built around that data.  Implementation lifecycles for a small organisation can be as short as a week – where the requirement is clearly defined up front.  The reliance on consultants is also not there as developer training is offered to get these organisations self sufficient in building their own QlikView documents.  The complete solution can be purchased and delivered in a short amount of time and at a price point well within the reach of even the smallest enterprise.

A further point of note here is the arrival at version 9.0 of the ‘Personal Edition’ of QlikView.  An arrival that was greeted with a great deal of uncertainty by the partner community, as many saw that it would cost sales as companies took a purely DIY approach.  Personal Edition can be downloaded for free from the QlikTech web site and is a fully functional version of QlikView Desktop with the sole limitation that it cannot open documents created on other machines.  I have heard it said that the main reason for the decision to put this version out there was so that the number of people with skills in implementing QlikView could grow – at no risk to the developer.  This is certainly true and a very good thing, but I can’t help feeling that it perhaps contributes to the misconception that QlikView is not the right choice for the corporate market.

This broad spectrum of organisations and users that QlikView is appropriate for is perhaps what QlikTech were driving at with their strap line ‘Simplifying Analysis For Everyone’.  This is great, but solution providers need to ensure that projects are constructed to match the expectations and the budgets of the client.

Removing the concern that corporates may have that ‘if a solution works for a small company it cannot be robust enough for us’ might explain why the strap line changed recently to ‘Analysis, Simplified’.

Whatever the strap line QlikView is a great tool and I believe it can form the core of a successful BI project for any size of organisation.

Steve Dark
www.quickintelligence.co.uk

The moment that I decided that QlikView was a product I wanted to be more involved with was shortly into the first demo I was given. It was when I saw you could click on literally anything on the screen and everything else would update to show you what was associated with that click. You could then add to that selection, or pick something not associated with your original selection to clear that and go a different route. With each click tables, charts and narrative were instantly updated to reflect the selection. Completely unrestrained access to the data.

After the first demo I knew I wanted to spend as much time as possible working with QlikView and within a few months I had resigned from my previous job to concentrate on doing just that.

Each different person that sees QlikView loves it for a different reason, be it the speed documents can be built, Global Search, Drill Groups, Sliders or whatever. The reason I love it is the number of times I get to see that look on peoples faces when they ‘get it’ and understand what QlikView can do for them and their business.

Steve Dark
www.quickintelligence.co.uk

We are all aware that QlikView is capable of performing complex analysis over many milllions of rows.  However, all too often documents are created that end up running slow on relatively few rows.  So, why is this?  My belief lies in the fact that QlikView is promoted on its incredibly fast time to live – a message that users eagerly buy into.  The marketing tool that is the ‘SiB’ [1] can leave clients with documents that have been created without being properly designed.  Another message that is given is that after a few days training end users can built their own documents, which is true, but they don’t bring the years of experience that a dedicated QlikView consultant can bring.
 
All is not doom and gloom though, as typically these documents can be saved by applying a number of techniques. The techniques that I tend to apply first are listed below.  Each of these I have seen make massive improvements to performance on a number of client sites.
 
Remove Redundant Data
 
The quick time to live possible with building QlikView documents does lead to the bringing in of every data item possible – just in case it is needed in future.  Each column in the data model should be reviewed to gauge whether its presence is required in this document.  If it is not, remove it or comment it out in the load.  This seems obvious but is often overlooked.  The reason I have placed this as the first item here is that by having fewer columns you have a smaller data model to apply the next two steps on.
 
Reduce The Number Of Joins
 
Joins in the data model need to be resolved by QlikView on each selection and each calculation.  Because of this documents with lots of tables will run noticeably slower than when the data model is simplified.  Particularly problematic is when a number of ‘hops’ are required to get between two data items.  There are a lot of techniques for reducing joins but to my mind the first point of call is the humble ApplyMap statement which removes the need for multiple look-up tables.  I could write an entire blog entry on the use of this statement.  The biggest mental hurdle with reducing the number of joins is that it is counter intuitive to anyone that has a relational database background – where normalised data is king.  In QlikView this is not the case – the goal is a flat data structure.  On one site I reduced a data model from over forty tables down to four and the performance improvement was hugely noticeable.
 
Reduce The Granularity Of Data
 
It is a well documented fact that when QlikView loads data it is incredibly efficient at compressing data by only storing pointers to duplicate values.  A column with billions of rows with only two distinct values will not take much space at all.  Space taken is critical because of the way QlikView keeps all data in memory to allow fast analysis.  There are many tips for optimising space taken by data (splitting fields into two for example) but the one I want to recommend here is looking at granularity.  Simply put, review what level of detail is required for each field, and remove detail that is not required.  To give an example consider property value, if this is stored as the actual value both £300,500.00 and £300,000.00 will be stored as separate values, however if both values were stored in thousands then both would be stored as £300 and would compress efficiently. If you intend using a division to reduce granularity make sure you also ‘floor’ the result – so the detail is not just stored in the decimal.  Be careful however using this technique if relying on totals across rows on this data – it is seldom possible to do this on financial data that is being reported on.
 
Conclusion
 
These are just my top three techniques, there are many more that I have used and seen make a big difference to performance on client sites.  There are also a large number of tools for reporting on the size and efficiency of QlikView documents.  These can be used to see exactly where the techniques above can be best applied.  The hardware used to run the document can also have an impact – especially the amount of memory, so ensure this has been properly invested in.
 
Above all if you are not happy with the performance of your QlikView document, do not write off the tool as being unable to cope.  It is capable of incredible speed on huge volumes of data.  Ask someone to review your document and environment to ensure that best practice has been applied and QlikView is able to reach its full potential.

Steve Dark
www.quickintelligence.co.uk

[1] SiB – a Seeing Is Believing session, where a working QlikView document is built on production data in a couple of days.