Thursday 21 April 2011

Better BI Dashboards

In CapricornVentis, we've been delivering great business discovery solutions in QlikView for several years now.  QlikView really helps you build great user interfaces that help people interrogate their data in new ways that they have never thought of before.

Before QlikView, much of my "Dashboard" design work would have been in SalesLogix CRM where we plugged various chart controls into the CRM data to display information such as Sales Pipeline, Performance, etc.  Similar things can be achieved with Oracle CRM On Demand Analytics.

No matter what system you use - BI, CRM, Database, you probably have access to some wonderful tools that allow you to present data to users in lots of different ways.

"What do you want to see on your dashboard", says the chart designer.  "Everything", says the customer.  "OK", says the designer.

Stop!  "Everything" is the wrong answer when it comes to dashboard design.

Think of your car dashboard.  It doesn't tell you everything that is happening in your car.  It does tell you everything that you need to know, right now, to drive your car.

Think about it.  Many car dashboards don't include a clock.  A clock is a "nice to have" on a dashboard - it isn't critical to the running of the vehicle.

A "pure" car dashboard probably needs to have only 3 things - a speedometer, a fuel guage and an oil temperature guage.  Someone might argue that a tachometer is useful because it lets me know if I am over-revving the engine and also lets me know when is the best time to change gear.  We could have the argument but lets just add it in.  So, 4 things.  Everything else is "nice to have" and you don't need it.

There are other things that I need to know about to run my car.  I need to know that my oil levels are good.  I need to know that my brake fluid is in between min and max.  These are things that I should check every week or so - but I don't need to know about them right now as I drive this car.

It is the same with the business dashboard.

What do you need to know, right now, to run your business?

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Wednesday 20 April 2011

Improve load performance by writing better script!

I came across a document recently where a date was being loaded from a text field in YYYYMMDD format.  The developer had used left, right and mid functions along with MakeDate to parse the date.

This is something that I might have done myself several years ago - before I knew better.  I wondered to myself if there would be much of a load speed differential between this method and Date# - which is the method that I would use today.  So I did some testing.

This particular machine was running v9 so it could have used some load speed tweaking.  I took a data set of 3 million records and compared both using the two methods.  The left, right, mid method took about 2 minutes (not a fast machine).  The Date# method was under 1:30 - 3/4 the time for the other method.

I wonder if you went back and reviewed some of your old code, if you might speed it up a little by just doing things better, just because you now know better.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Monday 18 April 2011

Blocking user access to a field using OMIT

This is something that comes up every so often.  A question like, "we have sensitive data (such as salary!) in our system that we only want some users to see".

This is all very straightforward on SBS where collaboration is not available.  Simply lock down the tab which has the sensitive data displayed using, for example, a condition based on the OSUser() function.

On an Enterprise server, where users can create their own objects, you don't want them to even see that the field is there.

We can achieve this using the OMIT field in Section Access.  At a simple level, if you only have one field to hide, you can have the OMIT in the main Section Access table.  If you want to perhaps OMIT several fields, then the creation of a group of fields is the thing to do and then just link the user to the group ID.

In the example below, when user1 logs in they will not see Field1 or Field2.  When user2 logs in, they will not see Field2 or Field3.

Section Access;
    ADMIN, admin, 
    USER, user1, group1
    USER, user2, group2

    group1, Field1
    group1, Field2
    group2, Field2
    group2, Field3

Section Application;

    Field1, Field2, Field3, Field4, Field5
    Value1, Value2, Value3, Value4, Value5

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Friday 15 April 2011

Defending Pie Charts!

Anyone who knows me (in the BI world) knows that I have a certain lack of respect for the humble Pie chart.  I am supportive of Stephen Few's idea that we should, in general, Save the Pies for Desert.

A Pie chart, as it ubiquitously appears in many presentations, used to compare one value against several others, is a very poor choice for the task.  A simple bar chart is by far the better choice for the job.

I have been quite happy, up till now, to reject any use of a pie chart for any purpose.  That was until I came across Robert Kosara's blog entry, In Defence of Pie Charts.  Robert discusses Spence and Lewandowsky's study which shows that pie charts were interpreted more accurately than bar charts in some circumstances.

So, perhaps there is a good use for the Pie chart after all - not in comparison of multiple values, but in simple part-to-whole comparison.

Take, for example, the following Pie chart where we are comparing the performance of Germany versus all other countries.

Now, this chart doesn't give me any numbers but what it does give me is an intuitive feel for how much of the market Germany has.

Now, compare that to an equivalent bar chart.

Here we have a perfectly formed bar chart where we can get a good grasp of the numbers that are involved.  However, if I want to get an idea of what percentage the German total is versus the whole, I have to do a little more work to get the answer - I can't intuit it.

So, for this specific purpose, a Pie chart might be a better choice than the bar.

These charts are easily achieved in QlikView using implicit set analysis (available since version 9).

The chart has no dimension but has 2 expressions.  In my case, the expressions are:

   Sum({<Country=P({$})>} LineSalesAmount)


   Sum({<Country=E({$})>} LineSalesAmount)

When I select one or more countries, the first expression will give me the total for the selected countries.  The second expression gives me the reverse, the total for all the other values.

The labels for these can be achieved using Concat.  For the selected countries:

   ='Selected: ' & Concat(DISTINCT Country, ', ')

For the other countries:

   ='Others: ' & Concat({} DISTINCT Country, ', ')

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Thursday 14 April 2011

Data for Humans

I am a great fan of Stephen Few.  Any of his books are worth reading for anyone who works in the data visualization space.

If you can't get a copy of one of his books right now, then 5 minutes reading his article, "Data Visualization for Human Perception", is a great introduction.  The commentaries by Ronald Rensink, Naomi Robbins and Robert Kosara are equally worth reading.

My belief is that the study of human perception to create great data visualization is fantastically interesting.  There is some fascinating research going on in the area of pre-attentive processing.

As computers get more powerful and the cognitive psychologists keep going with their ground-breaking research, we really are entering a golden age.  I look forward to being a part of it.

Even so, I have a feeling that we will still be using the humble bar chart for many years to come.  It is such an intuitive and simple visual data display method.  I wonder if the real breakthroughs will, in fact, be in enabling people to work more collaboratively with their data to discover new insights.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Best practice on iPad user interface design

Thanks to Colin Hancox, who pointed me to this article on the QlikView Blog by Chris Mabardy, Director of Product Marketing at QlikView.

The new mobile platform coming out in QV10 SR2 is new to all of us so it is great to have good guidance on building apps.

In general, all your old applications will work on the mobile platform.  The main considerations for iPad are screen real-estate (1024x768) and gestures instead of mouse.  If you expect that users will want access to right-mouse features, you need to turn on the new "Menu" icon in the caption.

After that, you just follow good design principals for screen layout.  If you haven't already come across it, I recommend Stephen Few's and any of Stephen's books.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Your call...Qlikview Server and AccessPoint

kaushik said...

I would like to learn more about Qlikview Server and AccessPoint.

A brief guide to QlikView Server (QVS).  It is worth noting that the QVS Reference Manual is really good now and explains the specifics of this subject really well.

So, QlikView Server is a version of QlikView that runs on a server!  Simple, eh?  QVS will load documents into memory on the server.  When a client connects, QVS will send enough information to that clients so that they can display the information on their screens.  When the client makes a selection, QVS uses the memory and CPU power of the server to process that selection and then sends the screen updates to the client.  This means that the client doesn't require to have enough memory and CPU to handle large volumes of data and complex calculations - hence why simple clients such as iPhone and iPad work so well.  The client just needs enough CPU and memory to render the screens.

QVS only loads each document once.  As each client connects, QVS will apportion them a separate area of memory to handle their data requirements (mostly cache of charts).  A "rule of thumb" is that each client will require and additional 10% of the document's memory requirement.  So if the document loads into 1GB of memory on the server, each client will require approx. 100Mb of additional memory.  As each version of QlikView has been released, their memory handling technology has got better and better and v10 will handle many more clients in the same memory than, say, v8.5.

There are now just 3 main client types (previous versions supported a Java client also).  First, QlikView Desktop can be used as a client to access documents from a server.  When used as a client, you would notice that buttons such as "Save" and "Reload" are disabled because they make no sense for a client - the server will handle all of that.

The second client is the QlikView plugin for IE.  This is an ActiveX plugin that sits inside Internet Explorer.  It shares the same code base as QlikView Desktop so the experience is very similar.  It is still a "thick" client so does require installation - although it comes with an MSI for automated installation.

The third, an easiest to deploy, is the AJAX client.  This doesn't require any installation because it works within a browser such as IE, Firefox, Chrome, etc.  As of QlikView 10 SR2, it is also supported in mobile devices such as Safari for iPad using HTML5.

The majority of Plugin or AJAX end users will access their documents via AccessPoint.  AccessPoint, by default, takes a user's Windows Authentication information and will be able to present to them all of the documents that they have access to.  This means users won't be confused by seeing documents that they won't be able to open.  Users can also set "favorites" in AccessPoint so that they can default to seeing their most used documents.

Windows security is the default for QlikView server but not the only method available.  By turning on QlikView's DMS authentication (not available in Small Business Server), the task of authenticating the users can be given to any other type of directory service or even a completely custom form.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Tuesday 12 April 2011

Your call...

I am training in some QlikView newbies right now and it makes me really aware about the huge number of things that you can learn in QlikView.

Please feel free to comment below and tell me what feature of QlikView you are interested in learning more about and I can use your comments as the basis for future posts.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Wednesday 6 April 2011

Load testing QlikView with JMeter

Another really interesting session that I attended at Qonnections was on scalability. Here, we looked at using the JMeter tool (an open source product from Apache: to first record a set of interactions and then play them back multiple times with multiple sessions.

This is a really powerful tool. Not just for QlikView but for anyone developing web products.

I can't record all the steps here, but there is a tutorial document and some sample files available from the Community page @

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

QlikView Mobile

I attended a great session at Qonnections yesterday where the new mobile app for ipad was presented.

Up till now, QlikView have had a specific mobile application for iPhone which they then enhanced to a "HD" version for iPad. This client was very different from the traditional QlikView. They also had a Java mobile client that worked on devices like BlackBerry. There was also, at one stage, an Android client - lots of clients to have had to support.

Essentially what has been done with new client is that the existing AJAX client has been enhanced to support gestures on mobile devices. The AJAX client has worked on these devices before but many things didn't work as expected and it was a less than satisfactory experience. With the new gesture support, using your finger instead of a mouse is just simple.

From QlikView's point of view, this move makes a lot of sense. Not having to create an support multiple clients means that they can focus effort on making their core clients even better. From an implementation point of view, there is nothing extra left to do - it works off Access Point same as other clients. All of the security, etc., works with it. It is worth sizing screens to the expected end user tablet size - but that is just best practice anyway.

This is already available for people to have a look at - point your tablet device to Any of the existing demos will work. Nothing special has been done to them. There are a couple of "iPad optimized demos" which you should find a link to off the main window.

For customers, this will be available in SR2 for QlikView 10 - which is due out in the next week or so.

There is a video available showing some of the new features here:

Tuesday 5 April 2011

No printing in Ajax

I have come across this a number of times, most recently on a post on, so I thought it was worth sharing.

If you have any type of printing enabled in QlikView, the QlikView Desktop and Plugin will render the printing and submit to the relevant printer. This also gives facility for Print Preview. This facility is not available in Ajax - a limitation of the thin client.

To have printing in Ajax, any print jobs need to be rendered on the server into PDF and then presented to the user as a download.

The problem occurs if the IT department has "hardened" your QlikView Server. One of the common steps in hardening is to disable the Print Spooler Service. This is often built into a Group Policy rule also.

The problem for QlikView is that during installation of QlikView Server, the installer attempts to install the PDF XChange drivers. This step will fail if the Print Spooler service is not running.

Once you have sorted out the Print Spooler Service, you don't need to re-install QlikView Server. There is a PDF driver available as a separate download in the QlikView downloads area.

Monday 4 April 2011

Beginners Guide to QlikView Extension Objects : Part 4 - Headers

Here at Qonnections, I am meeting some interesting folks and having some good chats. Meeting people that read your blog is interesting in itself!

I met Andrew last night and he had been playing around with Extension Objects using my previous 3 steps as a starter. He pointed out one glaring omission - how do you add headers! Doh! I had done it before but just failed to add it to the table example.

Headers are actually pretty easy (like a lot of this stuff). We have already seen the this.Data.rows property. Well the Data class also exposes a HeaderRows property. This has a length value but I can't really see where there would be more than one row. Anyway, we just need to extract the text values from the property just as we have done before:

// Add the Header row
var headerRow = this.Data.HeaderRows[0];

html += "<tr><th>" + headerRow[0].text + "</th><th>" + headerRow[1].text + "</th></tr>";