Scripts and wizards, too much script, not enough wizards

By Barry Harmsen

ScriptVsWizardsI recently read an interesting post by James Richardson over at the Business Discovery Blog: Wizards vs Scripts. In the post James makes the case that QlikView scripting is not old-fashioned or too hard, but is evidence of the power of QlikView as a platform.

Let me first state that I love QlikView scripting. I’m a guy who writes script for fun. I also agree that scripting offers much more flexibility than a visual solution ever could. With those things in mind, I would like to present a different viewpoint: I think that QlikView places too much emphasis on scripting. In my opinion, the default approach should be much more visual.

An old discussion

The discussion around scripting vs wizards (or, a visual approach) is not a new one. It is a variation of the age-old command line (CLI) vs graphical user interface (GUI) debate, which has been held about many different platforms and applications. With fierce proponents for either side, these debates usually end in a flame war that rages on until, often sooner than later, someone invokes Godwin’s Law. Given this, you can imagine that I was somewhat reluctant to bring this up, but I feel I must as I think some changes could improve the QlikView experience and open up the tool to new audiences.

In a nutshell, the pro’s and cons of both interface types can be summarized in the following table:

Pro's and con's of GUI vs CLUOr, to summarize it even further, scripting is good for experienced users while a visual interface is good for novice users.

TheDeepEnd

Scripting is the deep end of the pool

The question then arises; who is QlikView’s primary audience? The slogans I often hear are “Simplifying decisions for everyone, everywhere” and “There are no end-users“. To me, this implies that non-technical (“business”) users should at least be able to develop moderately sophisticated solutions on their own.

In the real world, I rarely see that happen. In my opinion, this is because new users are thrown into the “deep end” of QlikView that is scripting (and to a lesser extent, expressions). Not all of them float.

I'll have those QVD's ready for you this afternoon, fool!

I’ll have those QVD’s ready for you this afternoon, fool!

Those that do swim often have a technical background and an aptitude for scripting. Within an organization these people form a “crack commando unit“, developers and power-users who have invested a significant amount of time and effort in mastering QlikView scripting. These developers supply the rest of the organization with data models or applications. While they are awesome (yes, you are!) and valued, they’re also a bottle-neck in the analytical process. Very much like the data warehouse developers and cube-builders that came before them and that QlikView was meant to displace.

Or as the 2012 Magic Quadrant for Business Intelligence Platforms puts it:

One of the key issues QlikTech needs to address is QlikView’s weakness in data integration (currently requiring IT scripting)

I believe that QlikView could be a lot more accessible to novice and occasional users if it had more wizards and a visual interface. Learning QlikView scripting is hard and most users will not invest the time, they have a business to run.

But there are wizards!

“But” you may say, “QlikView does have wizards! Can’t novice users just use those?”

While it is true that QlikView has quite a few wizards, I see two issues with them:

  • There aren’t enough wizards. For example, why would you need to script a calendar table in each and every application when one could just as easily be created via a simple wizard? (actually, I’d prefer if QlikView handled dates a little smarter on the front-end so that we wouldn’t even need a calendar table)
  • QlikView wizards still generate script. This is my main issue with QlikView wizards; they still require the user to handle pieces of script. It doesn’t offer a true visual interface, just dialog boxes that generate script. To draw a parallel, what if, when using the “change desktop background” dialog on your favorite OS, instead of actually changing the background the OS just handed you the settings to change in the configuration file?

These two factors make it hard for novice or occasional users to achieve simple tasks and create QlikView data models, even when using the wizards.

90% of the work in QlikView scripting is about the simple stuff…

In his post, James states that “Wizards are great – and QlikView has them too for data integration – but they’re an option for simpler scenarios“. I would argue that 90% of the work done in QlikView scripting is about the simple stuff; loading data, filtering rows, splitting or merging tables, looking up values, etc. These are all things that are perfectly suited to a visual interface.

…but a visual interface can do much more than simple stuff

I also strongly disagree with James’ claim that wizards and visual interfaces are only for simple scenarios. They can do much more, and in many cases much easier.

Before I started working with QlikView, I worked with many visual ETL solutions. Besides supporting simple scenarios, most of these tools also support many complex transformations out of the box. This includes transformations that are quite complex if you have to script them in QlikView. For example, try scripting the logic to keep track of slowly changing dimensions. This is not trivial in QlikView and something I even see otherwise experienced QV developers struggle with. (shameless plug: if you join us at The Masters Summit for QlikView, I’ll show you how to do it)

A visual interface benefits experienced developers too

A visual interface is not only good for novice and occasional users, it also offers advantages for experienced developers. The main benefit is that a visual solution will give you a much better overview of how the data actually “flows” through the transformation process. It is much more “self-documenting” than script.

Using the slowly changing dimension example mentioned earlier, what do you think is easier to follow? The data flow for SQL Server Integration Services shown below, or the 200+ line script that is required to achieve the equivalent result in QlikView? And would you grasp from the script that there are actually different paths that the data can take?

Slowly Changing Dimensions Type 2 data flow in SSIS

We’re all convinced of the value of data visualization. Why should the process leading to those visualizations, arguably just as important as the end result, be captured in flat text?

Better structure – easier to maintain and more resilient to changes

QlikView script is organized in tabs that is processed top to bottom and left to right. A best practice is to organize these tabs by the tables that you are processing or by functional area. While this approach provides some structure, it can quickly become overwhelming for bigger, more complex applications. Or to use another quote from the 2012 Magic Quadrant for Business Intelligence Platforms:

Although quick to develop simple or moderately complex dashboards, when it comes to building large, complex reports from various data sources, involving detailed logic or calculations, QlikView users reported the second slowest turnaround time of any vendor on the Magic Quadrant (only Pentaho was worse).

Hierarchically structured data transformation jobs in BusinessObjects Data ServicesIn my opinion, the main reason for this is that, as more script is added, it becomes increasingly harder to organize and keep an overview. Even for experienced developers it becomes difficult to maintain the script and apply new changes. The 2 level (level 1: tab, level 2: script) hierarchy used in QlikView is just not sufficient to properly organize complex scripts.

A visual solution could solve this problem as it allows for hierarchies with more levels. An example of this, from BusinessObjects Data Services, is shown on the right. You can simply drill down to “sub-processes”, making it much easier to group transformations into relevant blocks. This makes it easier to maintain your application and absorb changes.

Auditing, error handling, traceability, debugging, lineage, metadata and much more

Besides better overview and better structure, a visual solution offers many more advantages for experienced developers and the rest of the organization. I won’t go into all the benefits here (this post is already long enough as it is), but there are many to be had. Including better options for auditing, error handling, traceability, debugging, lineage and metadata. A visual interface can have many features that will increase developer productivity.

But I want script!

“I see where you’re going with all this, but surely it’s impossible to achieve X, Y and Z using a visual interface? I just want to use script”

Yes, there are cases where a visual interface will just not cut it. However, using a visual interface does not exclude the use of script. Most visual ETL solutions support the creation of custom code blocks. Have a requirement that cannot be solved with the standard objects? Create a custom code block for it. My point is not that we should get rid of script, but that it shouldn’t be the default choice for building data models. “Under the hood” QlikView could still be using scripts, there would only be a visual interface on top of it.

Stop complaining and just use Expressor!

With its 2012 purchase of Expressor, QlikTech already has many of the capabilities that I described above. So if you want a visual interface, you can just use that, right?

While that could certainly be a valid route, in practice I see a few issues:

  • Expressor is a stand-alone application that needs to be purchased separately. It is not cheap (US$ 50,000 for the Standard version) which puts it out of reach of many clients in the small to medium business category. In these situations, alternative tools like QVScriptor could be an option, but most will elect to just write script. If/when they finally decide to switch to a visual solution they already have lots of script that needs to be converted, causing massive rework. The solution should be visual from the get-go. (I did a review of QVScriptor in 2011, you can find it here. I’ve not used it since but understand that it has been much improved in the mean time).
  • Companies that can afford Expressor, those in the Enterprise category, usually already own other tools that can achieve the same effect, such as Informatica PowerCenter. (as a side-note; in most Enterprise environments, QlikView is not the only game in town. In those environments it makes more sense to move most data transformations and metadata management upstream, instead of creating a stovepipe just for QlikView. But that’s a subject for another post.)

Most importantly though, Expressor (and the alternatives) are not (yet) truly integrated with QlikView. They still require script to import the data. In my opinion a true visual interface should be completely integrated with the core QlikView product and not require any scripting, unless you want to.

Conclusion

I believe that QlikView is the best Data Discovery platform on the market today, but it could be even better. QlikView scripting is awesome, but it shouldn’t be the default choice. A visual interface for extracting, transforming and loading data could open up QlikView to a much wider audience. This would allow it to truly achieve its goals of “Simplifying decisions for everyone, everywhere” and “There are no end-users“. Additionally, it would also enhance the productivity of more experienced developers and will shorten development times for bigger, more complex applications. At the moment there are add-ons that provide a visual interface for QlikView, but to achieve its true potential these capabilities need to be integrated into the core product.

Yeah well, you know, that's just like, your opinion, man.Your thoughts?

This has been quite a long read, so if you made it this far; kudos!

What you just read is my opinion on the script vs wizards (or more accurately, script vs visual) discussion. If you are a QlikView developer or a QlikView user, I am sure you will have an opinion about this matter as well. I’d be very interested in hearing it, so feel free to leave a comment below.

 

About The Author

Barry Harmsen

Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past few years I have been specializing in QlikView and a more user-centric form of BI. I have done numerous QlikView implementations in many different roles and industries. In 2012 I co-authored the book QlikView 11 for Developers. You can follow me on Twitter at @meneerharmsen.

14 Comments

  • 1
    January 28, 2014 - 13:09 | Permalink

    Hi Barry,

    you’re right, this is an old argument. I think there is no versus, both visual interface and script are helpful and makes a lot of sense. Visual interfaces might never leverage the 100% of script capabilities. That was always the problem with IDEs providing a bidirectional approach. You end up with “disconnected” script parts or your solution has to fit into a framework which is good for 80% of the use cases but not powerful or useless if you need the “really cool stuff”.

    Maybe actual QVScriptor version is worth to give it a try. I’ve heard the script parser has been more matured.

    However, I wouldn’t mix up the discussion with external ETL tools like Informatica PowerCenter or more QlikView centric/related Expressor. And, I wouldn’t compare vendor solutions with integration platforms.. 😉

    Best,
    Ralf

    • 2
      January 28, 2014 - 13:25 | Permalink

      Hi Ralf,

      The actual implementation of such a visual interface is another matter. The “disconnect” when modifying generated code is a known issue, but not something that cannot be fixed. For example, a simple fix would be that if you decide to change standard generated code it just turns into a custom code block. Or you could create your own transformations, similar to how you can reuse subroutines.

      Regarding visual ETL tools. I’m mentioning those because I’d like to see some of those features embedded in a visual interface in QlikView and to illustrate what I mean when I’m talking about visual overview and structure. I’m not advocating putting a full visual integration platform into QlikView, that would be overkill.

      I’m also mentioning them because if I hadn’t, it would’ve been the first thing people would bring up 🙂 “You want visual? Just use Expressor.”

      Kind regards,
      Barry

  • 3
    January 28, 2014 - 13:50 | Permalink

    ..then you would end up with custom code blocks everywhere.

    I don’t think a full visual integration platform in QlikView would be an overkill. A visual interface is only one another representation of code. In an ideal world you would seamlessly switch between both. Of course, every code change would trigger a script re-creation or re-parsing. Hopefully lossless..

    Ralf

  • 4
    Borys Tyukin
    January 28, 2014 - 20:11 | Permalink

    nice post, Barry! totally agree with everything you said and it is good to hear this from a Qlikview expert like you. I’ve seen and worked with quite a few ETL/BI tool and have a lot of experience with Microsoft BI stack. I also have decent programming skills and know a lot about BI/DW and data modeling. With all that wealth of knowledge, it was not easy even for me to start using Qlikview. I was very skeptical about QV sales team claims that business people can use it and after a year working with QV in our organisation, I can see I was very right – our business people need to reply on guys like myself (crack commando unit 🙂

    Scripting was one reason, set expression was another one (i know MDX so the concept was not new to me) and than lack of “enterprise” features like decent metadata support for measures and dimensions, dismal reporting capabilities and security model that needs a lot of tweaking. So QV is an awesome tool but I am afraid it has a long way to go before it can be called a truly “enterprise” tool and something that non-technical business people can use to develop their own documents (not to use, but to create).

    As for the “visual scripting”, you might find interesting to take a look at a not very known tool called Alteryx BI. I used it a couple years ago and you could build very complex and customized data flows with it. So it is definitely possible to have a nice visual interface that will support both complex and simple implementations.

    Also I heard from many people on last TDWI conference, that Tableau is very much loved by its business users because of data load wizards. But when I looked at Tableau I personally missed all the power that QV gives to you 🙂

    P.S. let me use this post to thank you for your book – it was a great help when I started learning QV and I think I read it 3 times by now:)

  • 5
    January 28, 2014 - 23:05 | Permalink

    Totally agree with this article, fortunately I can use SSIS for all ETL and the QVD scripts are
    most simple loads.

    I’d be very interested in hearing QlikTech’s take on this article and whether they will take up the suggestion, although I doubt it. given the investment in Expressor, but still Expressor could be better integrated to QV.

  • 6
    January 30, 2014 - 01:01 | Permalink

    If you want to do what qlikview does but with less scripting and programming , therefore accessible to business users, then tableau software is your answer. Most agile data discovery solution on the planet, Hands down! please have a trial on the tableau website to experience the difference

    • 7
      January 30, 2014 - 12:02 | Permalink

      Hi David,

      Yeah……. no.

      While I think Tableau is really pretty, I find it lacking in many areas when compared to QlikView (though user-friendliness is not one of them).

      With Tableau, the pendulum swings too much to the other side. The data blending and joining features are so simplistic that you will have to do most of your transformations outside of Tableau. This often involves manually doing all your data preparation in Excel (every… single… time….) or IT creating views/cubes for you.

      In QlikView you at least have the possibility to create an automated solution by yourself, if you put in the effort to learn scripting.

      Just to stress my point, QlikView scripting does provide a lot of value. I am only saying that using it all the time is perhaps not necessary and that a more visual interface might be a worthwhile addition to QlikView (and -not- a replacement). Please see this post as a (long-drawn) feature request, not as an invitation to market your own solution that happens to get some other part right.

      Kind regards,
      Barry

  • 8
    Diego Bormann
    January 30, 2014 - 10:24 | Permalink

    Hi Barry,

    I will be short about QlikView expressor –> Useless. I have done a few simple tests with it and it just can not do what QlikView scripts can do. For example: read all flat-files from a directory or Connect to an ODBC database (it has database connections build in, but Access is not one of them). So no matter what they say about it at QlikTech, they bought the wrong product.

    I also also did a review about QV Scriptor (latest version). This really is the product you are looking for (really, download the trial and give it a try). As IT department you can create building blocks (tables –> QVD’s and code snippets). All works with drag and drop and if needed you can still write code. I am not going to re-write my review here but the good thing about this is:
    It is all still QlikView. You do not have to learn a new scripting or programming language.
    I am now using it almost every day to do my QlikView work!

    You can find my blog here:
    http://diegoborm.blogspot.nl

  • 9
    Vadim Tsushko
    January 30, 2014 - 17:18 | Permalink

    Thought provoking post and commentaries.

    My two cents:
    I can see how visual tools can help non-technical users to build moderately sophisticated solutions.

    But for handling complexity of big QlikView applications I think visual tools are not key factor.
    Incidentally in your example you compare diagram with 7 boxes representing 7 well defined high level operations with script of 200 lines of code.
    But I think I would sooner understand procedure in ten lines of code invoking 7 high level functions then that diagram. On the other hand would diagram with 200 boxes would be for me completely uncomprehensive.

    Lets see at QlikView ETL script or set of such scripts in specific big complex QlikView project as individual software project. What is characteristics in any objective metrics? I would guess it be around 10 thousand LOC probably less then 100 thousand LOC. Frequently it would be developed by one programmer sometimes by several probably always less then 10 programmers simultaneously.

    Is it a project of overwhelming scale in any other area of software development?
    In other areas when projects started to grow to such scales, key factors to handle complexities were something like structural programming instead of linear flow of code, modularity, embracing if CVS systems and so on.

    I believe such projects as QlikVew Components and QlikView Deployment Framework and others lead us in that direction.

    Unfortunately some archaic features of QlikView script language do not contribute to this development.
    So I would rather wish small but very important improvement in script language.
    For example http://community.qlikview.com/ideas/2206 and most of all proper variables with any sort of scoping.

  • 10
    Johan L. Brattås
    February 4, 2014 - 13:56 | Permalink

    Interesting post, I agree with you – a visual layer to work in tandem with Qlikview is one thing I have missed.
    I enjoy scripting, and what I can do with it – but having used SSIS and other visual ETL tools I think Qlikview shortsell themselves in their focus on self-service BI by not including a visual tool for users – be it newbies or pro’s.

  • 11
    February 9, 2014 - 15:28 | Permalink

    Diego

    Since QlikView Expressor is useless…

    It would interesting to know your opinion about our ETL-Tools QlikView connector and
    what can we do to make it even more useful for you as a user

    Everything is Visual
    Works with 28 data sources (one of them MS Access 😉 )
    500+ visual data transformation and validation functions.

    Mike

  • 12
    Mark
    February 13, 2014 - 17:25 | Permalink

    Will Qlikview.next be a satisfactory answer to this desire for a more visual interface with the current Qikview engine?

  • 13
    May 19, 2014 - 13:01 | Permalink

    I’m a big fan of SSIS because of the visual interface (there’s lots about it I don’t like though!).

    The point I wanted to add is that SSIS’s visual interface enables us to very easily write scripts that execute tasks in parallel. Running tasks in parallel has enabled us to drastically reduce various data build tasks. I’m now finding myself building various QVW’s just so I can run them in parallel and then use publisher to orchestrate them.

  • 14
    David Dumas
    August 8, 2015 - 23:55 | Permalink

    I write all my data warehouse ETL, whether using SSIS, Talend (better), or any others, 100% of all code can be expressed in SQL. This then becomes ELT (extract-transform-load). All handled in the database. You never write complicated tool specific pipeline oriented code. I have done this for many projects and clients. All code for insert,update, delete, and slowly changing dimensions can be accomplished with single elegant MERGE statements, with all transformations done in SQL. When the database handles the everything, performance is better and code maintenance is far simpler.

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    Masters Summit London & Barcelona: a success

    From October 9 - 11 and from October 14 - 16, the European edition of the Masters Summit for QlikView...

    Close