This is another tale in the series of stories from the coalface of QlikView consultancy. This time it’s a story of what can happen when organic growth of applications occurs unchecked. It is also a warning.
Land And Expand
One of the mantras of QlikTech sales strategy over the years has been to Land And Expand. This phrase nicely sums up the way that QlikView can find its way into an organisation with a single user and then usage grows to more users and eventually Enterprise Server licences are required and the world is good. The phrase also ties in nicely with the way that an incredibly quick time to live can be achieved with a simple app that can then be expanded into something much bigger and better.
Both of these scenarios are ones I have seen happen with great success in a number of organisations. I have also seen it go quite badly wrong.
We Need A Bit Of Help With Our QlikView
I was approached by the company involved in this tale after they had realised the consultancy firm they had been using were not working out for them and their endeavors to ‘go it alone’ were hitting too many roadblocks. The company in question have sanctioned this article – but will obviously not be named. “We need a bit of help with our QlikView” was the phrase they approached us with. Since then we have helped them patch up and improve many of their QlikView apps. Progress there is always a bit like wading through treacle though. Here’s why.
Mark Twice, Cut Once
If I had to assign a single blame to why things are so challenging at this client it would be the practice of cloning one document to use as the basis for another. Sure, this can often be a way of reducing development time and bringing consistency. However when the cloned document retains most of the same content, just with perhaps a new chart or two, it is probably not a good idea. The reasoning was often because different users wanted a subtly different view of things. This can usually be achieved by a mix of parameters, section access and show/hide conditions. If a cloned document is ever seeming like a good idea to you then there is probably a more elegant solution available that you have not yet considered. Give this some thought before you copy and paste!
Get Your Data Model Right
The issues with cloned documents would be easier to deal with if the data model was correct and a decent QVD layer was in place. Frequent readers of this blog will know this is one of my pet subjects. At this client however there were no QVD’s being generated when I arrived and each of the multitude of different apps each made their own connection to the source database – running similar, but different, queries. Part of the reason for having multiple loads was that different group companies had different database instances – but a well thought out strategy of QVD use would allow each of these data sources to be merged together into a single app. This is something that has now been retrofitted for some applications.
The Ultimate Sin?
The thing that has probably caused me the most stress at this client is something else I have warned against on previous occasions. The use of the QUALIFY statement. This statement, whilst it can be useful for rapid profiling, should never find its way into a production environment. Any time it can save you at the start of a project will almost certainly be lost many times over as fields come through with names you are not expecting – particularly when you try and join or concatenate. Use of this statement is almost always an indication that not enough time has been spent on data design and that can only ever be a bad thing.
The Happy Ending?
In this series of posts I tend to end with the happy conclusion that through a combination of QlikView’s excellent software and some ingenious use of it the customer us now in a much better place. Sadly I can not honestly report the same here. Whilst many in-roads have been made to cleaning things up and consolidating the QlikView reporting; each days consultancy has been a battle against the proliferation of apps that share some common code. Many improvements have been made – but always with that nagging doubt in the back of my mind that it would be better to delete it all and start again. I enjoy a challenge when doing QlikView work – but it always more rewarding when breaking new ground rather than propping up something that has not been properly executed first time around.
You have been warned.
“The use of the QUALIFY statement …, whilst it can be useful for rapid profiling, should never find its way into a production environment.”
Couldn’t agree more.
HIC
Thanks for your comment Henric. It’s always good to get confirmation that my thinking on these things are on the right track.
Another great article! In my opinion extracting the ETL functions of the initial Qlikview document into ETL QVWs and then sitting both the applications on top of the same data model QVDs is a good way to maintain consistency and reduce head aches if the client absolutely insists on having two separate applications.
Idealy a combination of section access, show hide variables and good design should limit the need to copy paste qlikview documents.
I think best practice is to avoid the Qualify statement in production as you say, although if there are hundreds of fields involved and a short delivery time frame it may still get in there, just need to make sure to clearly comment the code!!! I wrote an article about handling challenging clients also on webofwork.com.
Hi Adam.
Almost regardless of situation I would avoid Qualify. A way of achieving a similar, but more flexible, result is to build your load statement in Excel. To do this, get a list of fields in the given table and paste these into Excel, into column A. In column B put the following expression:
=CONCATENATE(A1, ” as [TableName “, A1, “],”)
Now, copy that expression the whole way down the list of fields. Finally, copy and paste column B back into your QlikView document and you have code for qualified fields in a manageable way. You can also then tidy up the field names, getting capitalisation and spaces sorted.
Different documents for different purposes is not an issue, in my opinion, as long as you have designed for each audience and properly thought through how to best leverage your ETL layer to support multiple presentation documents.
Hi Steve, looks like a good approach, might give it a try at some point next time im against hundreds of columns!
Indeed. Use of Excel and the Concatenate and Substitute functions have saved me lots of effort in the past.
Hi Steve,
I totally agree with you on all these issues. I think the problem is that senior managers are sold QV on the dream that it’s really easy, anyone can do it and it doesn’t need IT skills. So when you go into a customer months after they they started implementing apps to sort out ‘a few problems’ you find a huge number of documents, odd practices and no foundations.
Where do you start if you have been hired for just a few days?
You really need to go back to basics, out a QVD layer in, design the best data structures, move as much as possible in the ETL and maybe provide a structured QVW for binary loads, but that won’t fix XYZ chart for Fred Bloggs in Accounts in a few minutes which is what they hired you for.
I get quite disheartened sometimes because what ever I end up doing I know it’s not the best solution. It never can be.
Hi Jane – usually my first day on site is often the basis on which I can win more days, if I get the ‘where to start’ wrong that is also where it finishes! People don’t usually want to hear they have got things badly wrong – so it is a balancing act between propping up what is already there and starting to build proper foundations. This is all part of the fun of QlikView consultancy!