First of all, it is crucial that you decide exactly which columns and rows you require and only pull them in to the data model.  Redundant fields can cause unncessary confusion, performance issues and ultimately extra effort.   Prototyping with more fields is acceptable – but strip back once you know what is required.

Careful consideration also has to be given to the fact that QlikView associations work in a very different way to SQL joins.  As you probably already know; any fields with the same name in more than one table will associate.  If there are more than one field with the same name a synthetic key is created – and this is always a ‘bad thing’.  If you see extra tables in your data model with a dollar sign in the name you need to revise and improve your model.

In my experience it is almost always better to concatenate fact tables together rather than to try and join them.  This way you can have many columns with the same name and the values in them will appear just as one field in QlikView.  Selecting a value in that field will pull out values from each of the concatenated tables.  This technique is especially useful when pulling data from different sources, or applying budget or forecast data into your model.  Concatenation is ideally done by having exactly the same fields in all tables you wish to concatenate (concatenation will then happen automatically) or by forcing a concatenation with a CONCATENATE statement.  If using the latter method make sure you are aware of the impact on Optimized QVD Loads.

Whenever you are building on top of a database with a number of fair sized tables get the business to consider exactly what they need from each table – columns and rows.  If they have direct access to the back end database they can investigate the tables that way.  However, what I generally do is build a separate QlikView document for each source table (so there are no join issues) that they can use for this investigation.  Each document lists each field in that table in a multi box.  This allows users to see every value in that table and decide whether it is required or not.  Typically I will add a Table Box of all values (with a calculation condition to limit rows) so they can see all of the raw data in context.  Encourage the business users to be ruthless with this shedding of fields and rows – it could really help as the project progresses.  A good example of shedding rows is historical data – most businesses change their shape considerably over a a few years – so comparisons with data more than a couple of years old is usually not relevant.

During this data identification process also get the users to think about how the fields should be referred to in the UI.  Consistency and clarity are essential.  I will often provide spreadsheets of the source field names (or even a printout if they are paper-people) and get them to fill in the required ‘user friendly’ field names.  Rename to these field names as soon as you extract from the database – using an ‘as’ statement in the load.  Insert spaces in field names where these are not present in the source – this will require you to use square brackets in expressions but it will save retyping legends each time a field is used.  Also any underscores and the like should be removed at this point.

Do all you can to simplify and rationalise the data model in the load script.  This is better than having lots of joins in the finished data model.  The best way of doing this is with ApplyMap (for more details see my previous posting ApplyMap – Is It So Wrong?).  Pre-calculate any values you can in the load script to save processor time later (perhaps calculate margins or FX values?).  Also, change any codes into values (eg. Y/N into Yes/No) thinking always of what your users will expect to see.  I always add a ‘Count’ field  to each table (ie. ’1 as CustomerCount,’) – as this can greatly simplify expressions later.  In short, the more work you can do in your load script the better – the benefits to document performance and development time can be immense.

It is almost always (and I had to think twice about the ‘almost’ there) a good idea to have a QVD layer built into your reload strategy.  These are essential for incremental loads, but are also very useful for chunking up your data extracts into manageable portions.  It can also aid in the breaking down of the development / UAT / promote cycle into segments that are simpler to unit test.  Extracting of data from multiple tables in the source databases can be done concurrently if there is a separate ‘QVD Load’ routine for each source table.

When data is loaded into your presentation layer test any remaining joins carefully.  As previously mentioned do not allow synthetic keys as whilst these can work some of the time they will always hurt performance and can cause crashes - always build your own composite keys if required.  Watch out for issues with non-numeric join keys that can be caused by rogue spaces or mismatch of capitalisation.  The best thing to remember when testing a data model is to assume nothing.

Whilst QlikView is often viewed as a tool for producing flashy charts that are dynamic and engaging it is in fact nothing without a solid data model behind it.  In most developments more than half of the time needs to be spent on perfecting the data model.  If you ensure that you invest that time then you have a far greater chance of success in your QlikView projects.

So, why is this, and how can it be avoided?

One major contributing factor can often be the ‘SiB’.  SiB stands for Seeing Is Believing, and for a long time it was promoted by QlikTech as a way of hooking new clients – and it works.  I’ve done many such sessions, where I have turned up on site in the morning, been handed some data or a connection string and by 4pm have built a working document analysing company data that I then present back to the business in a review session at the end of the day.  Invariably there is much excitement in the room as people are shown the results – particularly when they identify things about their own businesses that they were not previously aware of.  The sale is in the bag, licences are bought, the customer is happy and projects commence.  So, where is the problem?  It is that all too often this prototype that is built in a day or two becomes the bedrock for a whole implementation.  This causes issues as the developer will not have given a lot of thought to architecture or structure (or usually even lunch) and it will rarely be the correct start point for a full blown implementation.  I always recommend that the SiB document is put to one side ‘for reference only’ and then the real build can start.  Sometimes however this doesn’t happen.  If you are commissioning a SiB I would follow this advice:

See It.  Believe It. Bin It.

It sounds like clichéd business speak (and it is), but it is important to see the big picture before embarking on an implementation.  Whilst it is a good idea to look for the ‘quick wins’ early on in a project; some thought needs to go into how things created at this stage will fit into the completed whole down the line.  Only then can thought be properly given to architecture and how things should be structured.

Even something simple like ‘should we go for one big document or many small ones’ deserves some thought – and indeed I have blogged about this very consideration before (Bring Together Or Break Apart).  A common failing that I have seen is the folder structure on the server not being thought through in advance – which can lead to problems with implementing security and building a Dev/UAT/Live procedure down the line.  What I am trying to say in a nutshell here is not to leave things to chance – work out where you are trying to get to and plan how best to get there.

The single biggest issue that will cause a QlikView implementation to have the potential to go wrong is the data model.  There are a number of best practices that should be followed (some of which are referenced in my other blog posts) that are often overlooked.  The golden rule here is to simplify.   What all to often happens is that the data model from the underlying source database is pulled in, with its existing join fields and system field names.  A developer will be forced to deal with some immediate issues, such as synthetic keys, but will sometimes (once they have something that ‘kinda works’) leave it at that.  This can sometimes be okay on small data volumes – but it certainly will not scale and causes much extra effort down the line.

As perfecting the data model and approaching it in the right way is such a fundamental part of the process I am going to cover this in a separate post, to follow soon.  (See: Perfect Your QlikView Data Model)

The thing to always be aware of is that anything that is done should be thought through up front and not left to chance.  This does not necessarily mean that things will take longer – in fact it is more likely that time will be saved by you not having to revisit things later.  There is a lot of advice and documentation out there on best practices with QlikView and following these best practices will undoubtedly mean that your projects are more likely to succeed.

First up, let’s define a dashboard. To my mind it is a display of data on a single page to convey as much pertinent information as possible in that physical space.  I am sure there are many other definitions, but that will do me.

Given that, as soon as you add any ‘dive to detail’ you have made your QlikView document surpass the constraints of a dashboard. On the other hand there are many QlikView documents that fail to deliver the punch of a dashboard on the front page.  For example; Sales by Month is something that changes slowly and management should already know the history of it – so all this chart adds is a slight movement in the last point of the line each time it is viewed – yet it often appears on pages designed as dashboards.  Think carefully what you want to see or deliver on this first page.

The term dashboard comes from the console on a car.  These generally show very few measures – but they are all you need to get to where you are going safely and without incident. You do not need to know miles travelled per month for the last year – but a single red light for low oil is essential.   Bear this in mind when creating your dashboard – stick to the essentials.

The car analogy is helpful – but it has spawned one of the biggest design crimes out there –  the over use of the dial gauge.  These look good, but are major space hogs for the amount of information they convey.  Typically displaying a percentage of some kind, the same information can be displayed simply in plain text. The colour the needle sits in is a useful indicator (us humans read a lot into colour) but that colour can be displayed in the colour the percentage text is written in or it’s background. Before filling your document with gauges consider if it is actually the best display object for what you want to show.

My final thought on dashboards in QlikView is around whether they should be static or dynamic. I have been requested to build a number of scorecards that ignore any selections made (thank you {1}).  However, doesn’t this deny one of QlikView’s biggest advantages?  Well, I always steer people toward using selections, cycle groups and variables to maximize re-use of components and to get the most information packed into a small space.  The customer is always right though and a static display may be exactly what they need.   It certainly allows a conclusion to be drawn from a five second glance – one of the things a dashboard should always deliver.

At the end of the day the most important thing is that the document delivers on the business need it is designed to meet. Encourage a good deal of thought to go into the first couple of tabs and always listen to the needs of the key users.

Whether you call you QlikView document a dashboard, scorecard, business analysis tool, on-line board pack or multi dimensional insight engine – is then entirely up to you.

Naturally where there are fixes required to existing software (as there are with any software) it is good to have those fixes made available in a timely fashion.  In my opinion the intervals of the service releases seem very sensible – with a new one appearing for each major release every couple of months.  Upgrading to these is of course optional, and I typically recommend only upgrading if the client is actually suffering from an issue that is on the list of bugs fixed at any given service release.

It is the major releases that I feel are perhaps a bit too close together.  At the moment I am still upgrading users from version 8.5 to version 10.0 (one upgrade I did relatively recently was from 8.5 to 9 as they had been in test for a few months).  It seems a bit harsh to me that when these users who have just upgraded go to QlikCommunity they are presented with a banner saying they can download the beta of version 11.

Whilst some of the new features of QlikView 11 sound really useful (such as collaborative analysis via QlikView Server and the ability to cluster non identical hardware) I am not sure that users see lots of major releases as a good thing.

There is certainly a need to keep QlikView ahead of the competition – particularly with visualisations and chart options – but ultimately what users need from a business critical product is stability and reliability over gloss.

I understand that the proposed release date for version 11 is November 11th (11.11.11, naturally) but is it a good idea to let this kind of marketing spin drive a products release cycle?

Maybe it is just me thinking this – I would be very interested in hearing others opinions.

Often when loading data from complex data sources there are fields in the data model that are required to be pulled in that you do not want to be visible to your analyser users.  It may even simply be that there are so many of them that you don’t wan them in your own way all the time either.  Typically these fields may be join keys or fields that are only used in Set Analysis.

If this sounds familiar you will be pleased to know that these fields can be hidden from view and treated in the same way as the system fields.  As you probably know system fields can be shown or hidden using the check box on the Select Fields dialog.

With HidePrefix you can make your own fields visible or not based on the same check box.

Simply decide which character you wish to use to prefix fields you wish to hide (I always go for the % symbol) and issue the HidePrefix statement:

Once this has been done you simply need to name all fields that you wish to hide so that they start with that character.  Typically you will want to do this with an AS statement (as shown below) – but you can do it at source if you wish:

Once the script has been re-run and the data has been loaded you will notice that the fields that were prefixed only appear in the field selection box when Show System Fields is ticked.

Similar to the actual system fields; the hidden fields can be used at will in expressions, they just can not be selected from the pick lists when defining dimensions or from the drop down when building an expression.

No more will join keys or system ID’s clutter your nice pick list of available fields.

On face value it is a humble little function, which I tend to explain to the un-initiated as being “a bit like a VLookup in Excel”. This however is to do it a great disservice.

So, why do I hold ApplyMap in such great affection?

Basically it helps make light work of fixing problems in the data model that can otherwise lead to serious performance issues. Let me demonstrate.

Let us start with the assertion that too many tables in your data model is a bad thing (a topic for another article). So, you don’t want a situation where you have many lookups joined to your fact table. This can be resolved by creating a mapping table and switching out the ID with the value using ApplyMap.  In its simplest form you just create a mapping table and give it a name, this name is then used in the ApplyMap statement – which then swaps the ID for the value.  The basic syntax looks like this:

Simple enough. Just a couple of quick points after the first example:

- The mapping table must have only two columns
- The first column is always the ID and second column always the value
- The column names in the mapping table are not relevant
- The mapping table only exists during load
- No associations are made by column name
- Getting the table name right is essential
- No error is raised if you use a mapping table name that doesn’t exist
- It is easy to miss the MAPPING prefix on the load – check this before reload!

Right, now we have the basics down we can look further.

The source from the mapping table can be database, spreadsheet, resident table, QVD (requires a workaround in v9 and below) or as in this example an inline table:

This is great for very simple fixed replacements, such as swapping Y / N with Yes / No. A single mapping table can be used many times in a script – which can be useful for these sort of applications.

If you are of a SQL background you may now be thinking that you could resolve lookups like these with a JOIN in SQL – maybe a view on the SQL Server that resolves these links pre-load? If so, let me stop you there. This approach is simple but can drastically increase load times as the full description will be sent from the database to QlikView many times (once per row).  With ApplyMap the much shorter ID is sent many times and is switched out for the longer description during the QlikView load. Your network and users will thank you for not building a view up front!

Now, where ApplyMap starts to set itself apart is in its third (optional) parameter. If omitted the function returns the ID – this is great for debugging, but can produce field values that look untidy. The third parameter allows you to return something more useful, such as a fixed string like Unknown:

Alternatively you could opt for a different field from the same table instead:

Or build something using a mixture of both:

With the third parameter ApplyMap has already exceeded what can be done with a join. I shall revisit the third parameter later with something which I think really sets ApplyMap apart from the rest of the field.

The third parameter (or its omission) neatly deals with the problems that can arise if your lookup table is missing rows. The missing rows can then easily be identified and fixed at source. But what if there are duplicates? With a SQL JOIN statement this can be disastrous – as a duplicate row is caused in your fact table often leading to double counting. With ApplyMap the first instance of the key in the mapping table is used and the subsequent duplicate values are ignored – avoiding any duplication of fact data. If you wish to identify duplicates in your lookup tables this can be done in a separate QlikView document – or another tool if you wish. Rest assured that with ApplyMap duplicates will not cause you to get incorrect numbers.

As with associations between tables in QlikView, mappings can only be done on a single key field. This can be dealt with elegantly by building a composite key on both the mapping table and the table being mapped to:

The good thing here is that if a long concatenation of fields is required to get a unique key it does not take up valuable space in memory in the final document as the field is dropped once it has been used for the mapping.

If multiple fields are required from a table then you may consider joining. Alternatively you can create multiple maps from the same table. This will be more efficient at run time, but requires more code in the load script which can become unweildy. I tend to draw the line at around four or five fields from a single table for this approach:

Be aware that as soon as there is a one to many situation between tables (ie. not a straight lookup) then a separate table must be loaded into the data model – rather than using a mapping table.

The parameters of the ApplyMap statement can be fixed strings, other functions and fields from the row being loaded, this example has a mix of those things:

When building mapping tables you are sometimes presented with getting information from tables that themselves have ID’s in. These can be dealt with by using an ApplyMap statement in the build of the mapping table, like this:

Various permutations of using one map within the creation of another are possible. All the techniques discussed above can also be layered on top of other mapping tables – perhaps a composite key for one mapping table built from a field value and the output from another ApplyMap statement?

Another way that mapping tables can be combined is by using an ApplyMap statement as the one of the parameters of another ApplyMap statement. In this example if nothing is found when looking up on the first field then a different map on another field is tried:

As you can see one ApplyMap can be nested happily inside another one.  This nesting can go to as many levels as you require.

In it’s simplest form ApplyMap can serve a very important purpose with very little code required. By nesting maps, combining with other functions and concatenating; complex manipulations can be done on data load that save large amounts of memory and processor time at run time – when it is needed most.

This little statement has made my life easier on countless occasions – I don’t think it is too wrong to call it my favorite?

If you have a favourite statement then feel free to admit it by leaving a comment below.

Coding Skills

As this is what some people think the extent of the requirements are it is where I shall start. Solid skills in this area are a must as the QlikView developer needs to code in four or five different languages at practically the same time. You have the load script syntax, SQL statements (in various flavours), QlikView expressions, Set Analysis and then VBA if you require macros or advanced automation.  Whilst it is true that a good SQL knowledge will get you through the load script and those with good Excel skills will find no fear in expressions; it is the ability to do all at once that is vital. The discipline to work in a case sensitive environment and vision to devise solutions to problems where the standard methods do not deliver are also essential.

Understanding Data

This seems so obvious that it shouldn’t be necessary to mention. However I have seen many examples of QlikView documents where the data simply hasn’t been understood. Classic mistakes include summing percentages and incorrect chart choice (often a data understanding issue rather than a design one). A degree in maths or statistics is not necessarily required – just a good understanding of (and respect for) the rules of data.

Understanding the Business

For a QlikView document (or suite of documents) to be of maximum use to a business it should present insight from every area of that business. This requires the QlikView developer to have a real understanding of the business and the processes that make it happen. Even if the developer is not the analyst (but in my experience they often are) to translate the requirements into inspired visualizations takes some level of knowledge.

Experience Across Companies

Someone who has been in a job for some time will know that organisation well (ticking the box above) but this can lead to a tendency not to think outside the box. All to often I am asked to replicate existing Crystal or Excel reports for a client as “that is what we always report”.  Being able to provide real world examples from how other organisations do things allows organisations to break the mould – allowing business discovery to occur.

Also there is a massive difference in what is important to people in different types of businesses. Only experience in many vertical markets can equip the QlikView developer to know what is likely to be required on any particular dashboard they create.

Best Practices

There are a number of development best practices that can make a massive impact on the performance and usability of a QlikView document. These range from data model design principles, coding standards and interface design rules. The QlikView developer needs to keep up with all these best practices in order to stay on top of their game.

The Ability to Ask

This again sounds like an obvious one – but again I think it is worthy of note. Often the requirements that are given to the developer for a QlikView document are sparse. I have been given little more than a database connection string to use on occasion. Having built many solutions gives you an idea of the sort of thing people are likely to want to see – but it easy to assume wrong. The only way to get things right is to sit the main sponsors down with pad of paper or flip-chart and ask them what it is that they need to achieve.

People Skills

This therefore means that as the person coding a dashboard you also need people skills. Not always the first thing you associate with someone building IT solutions. However, to win the trust of the IT department and to challenge the assumptions of the finance director requires just that. A typical QlikView project should involve people from many areas of a business, and the person doing the build is often required to be the lynch-pin between these individuals.

Conclusion

So to sum up, the QlikView Developer has to be someone that possesses many different skills – and knows when to apply each of them. Added to what I have laid out above there are also those skills required in just about any role these days – the ability to work under pressure and to insane deadlines.

It sounds like a pretty tall order – but it is a challenge that us QlikView developers love to try and meet on a daily basis.

As someone who has enjoyed contributing to QlikCommunity over the past couple of years I am looking forward to it going forward even more now.  Finally QlikView has a product forum on-line that really does it justice, and it is great to see that all the historical content has come across to the new site.  A real wealth of experience and insight in one place.

My niggles with the new site are only small ones.  It is a shame that friends that were added on the old site have not been ported across to the new – these shouldn’t take long to be set up again though.  It also would have been nice if old links to threads could have been set to redirect to the same thread in the new site – I’ve a few links places that I need to go and update now.

The site also has quite a lot of social media focus to it.  One could ask if the world needs another Facebook / LinkedIn style site, but in this day and age QlikCommunity would probably feel incomplete without these features.

Niggles aside this is a good day for QlikView and for QlikView users worldwide.  Many thanks to all those who have helped make it happen.

There is a long running thread on QlikCommunities with the subject “I Thought QlikView Was Supposed To Be Easy To Learn”. As you may guess the thread was started by someone who felt mislead by the marketing line and was struggling to use the tool. Many comments have been added, some echoing the sentiment and many others offering help and links to the many excellent resources out there. It makes for an interesting read.

The good news is that QlikView is incredibly easy to use – as a data user. I have often said to people that I can teach you to use your dashboard in ten minutes – if you can tell the difference between green white and grey. I’ve even been able to deliver on this boast with time to spare.

So where’s the problem? Well, that is being shown how to use a well constructed dashboard – hosted on a web server at the end of a ‘Favourites’  link. Building a document from scratch is a different matter – and requires different skills. Is it difficult? No. Are there pitfalls? Yes.

The first problem can come from connecting to the data source. QlikView can connect to virtually any data source – so it should be no surprise that some present more issues than others. The wizard added on opening QlikView 10 is a welcome addition for new users – but some data sources require a bit more ingenuity. If you are able to import data into SQL Server or Excel and deal with rogue values etc. then you should be well equipped to do the same in QlikView – if not it may take bit more persistence, or a helping hand. This is in no way a fault of the tool.

Once the data is in then there are other challenges ahead. To create a truly first class dashboard a number of very different skills are required. A good understanding of data, maths and the business are all required. Understanding charts and rules for using them is also essential – all too often I see line charts across non-linear dimensions, averages being added together, pie charts of unrelated items – the list goes on. The dashboard designer is presenting important data – they have a duty to treat it right.

Similarly, design and layout are critical to a successful dashboard. The QlikView developer needs to have an eye for presentation in the same way a web designer does. Crimes against design can lead to completely unusable dashboards.

Fortunately for both data presentation and design there are good books out there that can teach you the basic rules.

The final pitfall I want to mention briefly is performance. Again this is only a pitfall due to expectations. We are told that QlikView offers lightning fast analysis, which it does, but what works for a small data set may not work for a wide set of data with millions of rows. Again you need to know what you are doing. There are many tips and best practices for tuning – too many to list here, but there are good articles out there.

So, in short, QlikView is wonderfully simple to use as an end user of a pre-built dashboard, but you should not assume just anyone can build a dashboard. As someone considering QlikView, or as an existing user, you should be evaluating how you should invest in getting the most from your data. It may be sending staff on the QlikTech training courses, investing time in using the on-line tutorials, just having a go (with QlikCommunities for guidance) – or engaging the help of experts. Any of these are valid approaches. What ever you do, be realistic about what can be achieved in a given time-frame and seek out good advice.

Do these things and you will soon be enjoying the simplified analysis you have been promised.

Links:
Original QlikCommunity Thread
QlikTech: Free Training Materials
A good read: Information Dashboard Design – by Stephen Few
Videos on QlikView Scalability

This consultant thinks so.

One of the key selling points of QlikView is the incredibly short time to live (and therefore ROI).  Even comparatively complex solutions can be delivered in weeks – not months.  So why then are the contracts on offer all for six or twelve months five days a week?  My personal opinion is because this is works best for QlikView recruiters – not QlikView customers.

In my experience a much more ad-hoc approach works best for everyone.

Let me give an example.  On one job I was booked for a five day initial engagement, but by lunchtime of the first day we had uncovered a large number of data quality issues. The afternoon was spent using QlikView to highlight these issues. By close of play the call had been made to postpone the next four days. This was bad news for me, leaving a gap in my schedule, but good for the client that they didn’t have to pay for days they couldn’t use. We reconvened a few weeks later once the data was ready.  If this had happened in the case of getting someone on for six months there would be a highly skilled resource not being used – or being busy doing a data clean that could have been done by an internal resource.

The skills that are worth buying in for longer durations are those of data analyst or project manager – if required.  Both are essential for a successful project once it gets over a certain size.  Yes, any decent QlikView consultant should have those skills – but would you not rather be making use of their QlikView skills?

For ongoing QlikView maintenance engage with someone who can be called back in to deal with these maintenance tasks as they arise.  Also, look at growing the basic skills in your existing team.  Bringing internal resources up to speed with basic day to day running of a QlikView environment should not take long – with a suitably skilled consultant delivering the training / knowledge transfer.

So, does this mean that there is not a space for recruitment firms providing QlikView resources?  Not at all.  I think that they just need to adapt to the market they are operating in. There are some recruiters that will provide consultants on an short term or ad-hoc basis and I feel these are the ones who understand the lifecycle of a QlikView project much better.  If you are looking for resource; look for a recruitment company that will offer a service that matches your requirements.

Alternatively speak to a QlikTech partner or seek out an independent consultant yourself. The web and sites like LinkedIn are there to enable you to do just that.