Monday, 25 August 2014

Update to Link Table rules

Back in 2011, I wrote a blog post entitled "Rules for creating a Key/Link Table in QlikView".  There were 3 basic rules:

1. Create a primary key in all the tables,
2. Rename the foreign keys to break the links,
3. Use a mixture of Concatenate and Join to generate the Key table using the Resident data.

These rules will still create you a Key/Link table that will work, but it might not always be the most efficient.  As I have had to deal with larger and larger data sets in QlikView, I have learned the hard way that things that work quite well on a table with a few hundred thousand records do not work quite as well when your tables with a few hundred million.

It is really the first "rule" here that can be the problem.  In a dimension table, the key that associates to the Key/Link table does need to be the primary key, however, the key that associates fact tables to the Key/Link table does not have to be a primary key!  This key only needs to be unique for the set of keys being linked to in the Key/Link table, not for the whole record.

For example, if my fact table contains CustomerID and DateID, most of the time that combination will not make a primary key in the fact table, but it is all the key that we need to associate to a Key/Link table containing CustomerID and DateID.  By not using a primary key in the fact table, we are vastly reducing the cardinality of the key being used and hence the amount of memory required to store it.

Another change that I would make is that I no longer use AutoNumberHashxxx to generate a key value.  Now, I always use AutoNumber with an AutoID.  This is because AutoNumber with an AutoID will generate a set of sequential integers which do not, in general, get stored in symbol tables, further reducing the memory requirement.  Using AutoNumberHashxxx to generate multiple different keys will result in non-sequential integer key values which will have to be stored in Symbol tables.

Yet another change that I would require is that the Key/Link table is generated using the Distinct keyword.  Nothing kills performance quite like having duplicate key values in a Key/Link table.

Finally, I no longer require to rename the foreign keys and then rename them back when creating the Key/Link table - I just leave them as is and then use the Drop Field xxx From yyy statement to remove the old foreign keys.

So, the new rules become:

1.  Create a key in each table at the correct granularity to associate to the Key/Link table.  Use AutoNumber with an AutoID.

2.  Use a combination of Concatenate, Join and Distinct to load a Key/Link table of distinct values.

3.  Use Drop Field xxx From yyy to remove the old foreign keys from the fact tables.

For example:

I have a simple structure with Customer, Calendar, Orders.  I have a separate fact table with Customer, Calendar and Delivery information.  I get a synthetic key that I want to resolve by using a Key/Link table:

Step 1 - Customer and Calendar (DateID) already have a primary key.  In Order I will create an ID from CustomerID and DateID.  I will do the same in Delivery:

   AutoNumber(CustomerID & '-' & DateID, 'CD') As LinkID,

Step 2 - Now I load my key table.  I will begin with data from the Order table:

   Load Distinct

Now Join in the Product and OrderDetail keys from the OrderDetail table.

   Load Distinct

Step 3 - Drop the old foreign keys:

   Drop Fields CustomerID, DateID From Order;
   Drop Fields CustomerID, DateID From Delivery;

Now all should be good:

Of course, we don't just create link tables to avoid synthetic keys, we usually create them to overcome association difficulties such as loops.

There is a special prize of a free copy of my forthcoming book, Mastering QlikView, for anyone who can give me a good, technical (i.e. not "because Qlik say so"), reason as to why the original, synthetic key containing, data structure is less desirable than the link table structure.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Wednesday, 30 July 2014

Extensions in Qlik Sense

Qlik Sense is all extensions.  Even the out of box objects are extensions.  It also comes with some samples of additional Extensions.

After installing Qlik Sense Desktop, have a look in %userprofile%\Documents\Qlik\Examples\Extensions.  You will find several sub-folders, each containing one extension.

If you have experience with QlikView 10/11 extension objects (or have read my blog entries on how to create extensions) then you will see immediately that there is a different way of creating extensions in Qlik Sense.

There are two core files that you must have:

- a .QEXT file which contains the JSON description of the extension that will be used within the desktop client.
- a .JS file that contains the javascript to implement the extension.  This JS is build around the requiredjs framework.

Additional files can be added as required.

If you copy one of the extension folders into %userprofile%\Documents\Qlik\Sense\Extensions and restart the Qlik Sense Desktop (F5 may be enough) then the extension should appear.

You can also simply create your own sub-folder and create the .QEXT and .JS files and they will be picked up.  However, it is easier to use the Workbench to create your extension from a template.  To run the Workbench, first make sure that Qlik Sense Desktop is running (it provides the web service for this) and then connect to http://localhost:4848/workbencheditor.

There is no documentation for the extensions in the default help for Qlik Sense, but you may be able to access the beta documentation at:

Some of you may have seen a post of mine where I had proposed a new chart type called a Pie-Gauge.  I had already created a QlikView v11 extension to generate these gauges using RaphaĆ«ljs to draw the pies so I wanted to have a go at doing this in Qlik Sense.  After a bit of trial and error, and looking at other extensions (including Ralf Becher's implementation of the d3 dependency wheel), I was able to get what I was looking for:

I am happy for anyone to grab the code for this from my github repository.  Just grab the whole RedmondPieGauge folder and drop it into your %userprofile%\Documents\Qlik\Sense\Extensions folder and restart the Qlik Sense Desktop.

You need to specify one dimension and then two expressions - one for Actuals and one for Target.

Each of the objects - including the text labels - are clickable to make selections exactly as if they were a Qlik Sense object.  This also implements a slider in the properties to allow you set a doughnut size.  Anyone who knows me will know that this was definitely an academic exercise - I hate doughnut charts!


Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Friday, 25 July 2014

Qlik Sense

Yay!  It is finally here.  Qlik Sense, the product that has previously been called, has been released to the public as a free download.

This is the desktop version of the product and is licensed similarly to QlikView Personal Edition.  You can use it "...solely for the User’s personal or internal business use...".

Right now, there is no server version but this will follow.  Qlik have also promised a public server service - Qlik Cloud -  to allow users to create and share content.  This will be awesome when it happens.

So, now Qlik have two products - QlikView and Qlik Sense.  Qlik Sense is an evolution of QlikView, but QlikView will still be around for several years to come.

So, what is in this new Desktop client?

When you first open it, you arrive at the "Hub".  This lists all your applications.  You simply click on an application to open it.

When an application opens, it will allow you to select sheets (just like current QlikView).  The navigation between sheets is different, so will need some getting used to, but it is pretty straightforward.

Opening the Sales Management demo app invites you to look at the new Story feature, where you can find out more about Qlik Sense.  The sheets in this application allow you to try out creating new objects, and learn how easy it is.

If you do feel brave enough to create your own applications, there is a very useful feature that allows you to drop a file of structured data into the application and then it will generate the data model and allow you to get up and running.

If you feel really brave, you can go into the script editor!  As it turns out, not really a lot of bravery required as any experienced QlikView developer will be able to get up and running in here - the script syntax is all the same.  In fact, all your old scripts will run and generate a data model.

Your Qlik Sense apps, in the new QVF format, are stored in %userprofile%\Documents\Qlik\Sense\Apps.  If you drop an older QVW file into this folder and re-open (F5 works) the Hub, the app will appear and can be loaded into Qlik Sense.  When you save it then, it will be saved in the new format.  Right now, only the data and script will get converted into the new format, all UI will be lost.  There are also limitations around applications with hidden scripts and those with section access.

I am really happy to see this new version finally released.  It marks the start of a whole new Qlik journey and I, for one, am happy to be along for the ride.  I can't wait to see where it takes me.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Saturday, 26 April 2014

Is QlikView v11 vs. QlikView.Next the right question?

I have seen and heard a lot of discussion about what is missing from the initial beta release of as compared with QlikView v11.  I am not sure that this is the right discussion to have.

The way QlikView has evolved, it has become much more than a data visualization tool.  It has become almost an application development platform.  All of us have used QlikView to do application things - show/hide objects, run macros, actions, etc. - and have built some excellent applications for our customers.  But was this the right thing to be doing?  Did we do it in QlikView because it was the right place to do it, or because we could and QlikView made it easy for us to do?

Let's think for a minute about the strengths of QlikView 11. For me, they are:

  • A really good ETL script to allow us take data from lots of different places and combine them in different ways - including using best practice dimensional modelling.
  • A best of breed analytics engine that calculates fantastically well across large data sets.
  • Associative logic - the green, white and grey, awesome functionality for data discovery.
  • A good (but not awesome) data visualization tool.

The first 3 are, for me, the magic that makes QlikView the fantastic tool that it is, the one that really differentiates it from its competitors.

You will notice that I don't mention "application building" as a strength in QlikView - that's because it isn't a strength.  If I want to build an application, I will use Visual Studio, JDeveloper, or even Notepad++, but I am not going to use QlikView.  If I want something that is going to have application functionality - like writing to a database for example - I am not going to think of using QlikView for that, even if I could hack something together.

I can include QlikView data in another application, and I have built several such applications.  I can also create an application inside of QlikView using an extension.  I can do both of these even easier in, but still using my usual application building tools.

So, from my point of view, still has all of the great things that QlikView 11 has - except it is even better as it now is an AWESOME data visualization tool, one that business users will love using.

I think that for new customers, this will be fantastic. I think that they will start out building QlikViews that are designed around data discovery and their users will love it.

For existing customers, there is a different perspective.  Many will have invested a lot of money in building what they have.  My thinking is that it is an excellent opportunity for them to evaluate what they have done and revisit the design decisions that were made to see that they can do it better - while knowing that their existing platform will be supported for several years to come.

I think that if you look at the beta (note *beta* - not shipping product) and dismiss it because it can't do this or can't do that, then you might be missing a trick.  Think instead of what you can do - there is a wonderful world of opportunity opening.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Friday, 21 March 2014

Generate and execute a batch file in QlikView

My good friend and fellow Irishman, Alan Farrell, pinged me on Twitter this evening with this question: "can you create a .bat file in qlikview and save the file to a specific folder"

There is a pretty easy way of generating a file in QlikView and that is to load the rows of text that you want in that file into a table and then use the Store command to write that table to a text file.  If there is only one column in the table, then no separators will be written.

One "gotcha" about writing text files like this is that the field name will always be written.  This is easy to get around in the case of a batch file because you can name the field to some valid batch syntax - like "@echo off" or "REM Start of Batch File" - and that will be fine.

Here is an example to generate a batch file that will move all of the text files from one location to another (pretty lame example!):

'REM This is a batch file test' As [@echo off]

For Each vFile in FileList('c:\temp\Folder1\*.txt')

'MOVE $(vFile) C:\Temp\Folder2' As [@echo off]


Store BatFile into c:\temp\test.bat (txt);

Drop Table BatFile;

EXECUTE c:\temp\test.bat;

Now, the final execute will only work in QlikView if you have the "Can Execute External Programs" turned on in the Settings tab inside the Script Editor.  If you have publisher, you can have the QlikView task generate the .bat file and then have Publisher execute it.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Sunday, 16 March 2014

Same day last year

Many companies - especially retailers - deal with comparisons of week, this week versus last week, etc.  Very often they will want to compare the performance of one day in a week versus the same day last year - the "Like-for-like" day.

I have seen some developers tie themselves up in knots trying to calculate things based on different rules and taking leap years into consideration.  But it is often much simpler than they realise!  No matter what year, leap year or otherwise, the same day of the week last year is exactly 364 days ago.

   364 = 52 x 7.

One thing that I can't guarantee with this calculation is that the same day last year will be in the same week number.  This is because of the fact that some years have 53 weeks and some have 52.  The ISO rules define week 1 as the week on which the first Thursday of the year falls.  Different organisations may have different rules about the week numbers, such as week 1 always starts on the 1st January, or even April 1st.  If you need to compare weeks based on week number, rather than the 364 rule (so how do you compare week 53?), then the best way to handle that is to have a calendar table that defines the values.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Wednesday, 26 February 2014

UX, Data Visualization, and the case for cognitive dissonance

In his seminal work on user experience (UX) design, The Inmates Are Running The Asylum, Alan Cooper introduces the term "Cognitive Dissonance", which in psychology means the stress caused by holding two different ideas at the same time.  Cooper uses this in the context of a computer system performing an action in a way that is different from the idea that a user might expect it to.  Cooper, correctly, argues that computer systems should be designed better to avoid such cognitive dissonance.

But perhaps there is room for some cognitive dissonance in data visualization?

A couple of years ago, I heard a senior person in a particular BI vendor discuss the work of Dr. Daniel Kahneman.  Dr. Kahneman has written an excellent book called Thinking Fast and Slow.  In this book, we are introduced to the idea of System 1 and System 2 thinking.  System 1 thinking is fast - largely subconscious thinking.  Imagine answering the question, "what is 2 + 2?"  The answer comes immediately.  System 2 thinking is more conscious, deliberate, and often logical.  Think of answering the question, "what is 17 x 22?"  We need to follow a process to come up with the answer and it is a lot harder.  Because it is harder, and uses more energy, our brain much prefers to use System 1 thinking.

The BI vendor in question was starting along a path where they were suggesting that it would be good for visualization designs to support the System 1 thinking - make things more automatic and easier for users.  If they don't have to think to hard about decisions, then we make their lives easier, right?  Having read Dr. Kahneman's book, I am glad that they didn't pursue this messaging in their global marketing, because it might just be very wrong.

Last Monday 24th February, the BBC's Horizon series covered the subject, How You Really Make Decisions.  I heartily recommend it if you can catch it on the iPlayer or if it is repeated on a local station (Horizon has always been one of my favourite programmes on TV).  The program looked at Dr. Kahneman's work.  It appears that System 1 thinking is actually more associated with making mistakes!

You may have seen this video before:

This is a great example of inattentional blindness.  It was created by researchers Chris Chabris (Union College, NY) and Daniel Simons (U. of Illinois).  This is following the case of Boston police officer, Kenneth Connolly who was pursuing a murder suspect on foot when he passed some other police officers beating up another suspect.  Connolly completely missed the beating up.  However, he was convicted of perjury because the jury couldn't believe that there was any way that he couldn't have seen it.  However, Chabris & Simons do regular tests where they have people running after a jogger while having some guys having a fight along the route - 50% of the people do not see the fight!

So, how does this apply to UX and Data Visualization?  Well, I think that Cooper's UX rule on cognitive dissonance might not hold all the time in the case of a dashboard or analysis piece.

We know that a dashboard that is over complex with too many pieces of information is difficult to use and makes a user work very hard to find the information that they are looking for.  There is a danger that they might miss a tree because of the forest of options.  However, the idea that we should design a dashboard that a person can just look at and see the answer immediately may not be right either.

The problem is that an easy to use dashboard may pander to our System 1 thinking.  But System 1 thinking can also be influenced by a whole load of cognitive biases that we as designers may not be aware of - some of which we could even be accidentally programming by our design.  We might think that a dashboard is simple and that the answer is obvious to everyone - but that is not always the result.

So, perhaps there is a case for a deliberately conceived cognitive dissonance being built into the dashboard?  Force the user to think twice.  Make them engage their System 2 thinking to come up with the answer.  Don't make it easy, make it deliberately harder.  Perhaps that might actually help users make better decisions.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO and Qlik Luminary at CapricornVentis a QlikView Elite Partner.
Follow me on Twitter: @stephencredmond