How to write reusable and expandable expressions in QlikView

One of the good practices in QlikView development is using reusable expressions. The most common case is when a repeating expression is replaced with dollar-sign expansion through the application. E.g.

$(eAmount)

where eAmount is defined like, for instance

LET eAmount = 'sum(Amount)';

If you don't use reusable expressions in QlikView you definitely should start doing it because of 2 reasons:

1. Improved maintainability -- you don't have to edit all occurrences of an expression in an application, but only once
2. Better readability -- you have much better readability of your expressions because their (sometimes complex) logic is actually replaced with its brief explanation.

While this a generally good practice there are a couple of tricks to get even more value from it.

Really, is QlikView a BI tool?

The more I work with QlikView the more I question myself -- really, is QlikView a BI tool? We all know that QlikTech markets QlikView as a BI tool, or, more precisely, the BI tool :). We know that Gartner, Forrester and many other analysts consider it as business intelligence application/platform and it looks like nobody questioned this fact (or did somebody?). But what if we look at it from a different angle.

Difference between QlikView and other BI tools is so vast that it makes any head-to-head comparison barely reasonable. In terms of classic BI, QlikView is rather limited -- no decent ad hoc Q&A, static reporting leaves a lot to be desired and drill-down is nothing but a joke here. BI with poor drill-down? You must be kidding me.

Let's look from the other side -- traditional BI suites usually offer rigid and often counter-intuitive UI which requires week of training just to get started. They inherit all curses of SQL, can't boast with more or less powerful expression syntax, comparable with Excel (which is crucial for anything that calls itself "analytic"), and actually are nothing more than just a visual front-end for databases. Okay, with some trendy smart caching added recently.

Not that the difference wasn't obvious earlier -- the same year when Gartner praised QlikView, they  coined term "Data discovery" as the market niche for QlikView and some other tools. While it's not the best term in general (didn't traditional BI tools do a data discovery, at the end of the day?), due to intention to fit QlikView into existing BI landscape, I suppose one important point has been missed -- QlikView's superior user interface customization capabilities, which actually make it a "Lego for analytical applications". Let's elaborate on this a bit more.

Every application designer (we're not talking about BI, but about software applications in general) knows that user interface is event-driven and the events are of 2 types: user-generated and application-generated. This is a known fact which eventually led to appearance of Model-View-Controller (MVC) application development pattern, where Views reflect Models and Controllers process events that make changes to Models and then Views. Lots of application are built using this pattern, especially web-applications (we're all going there, right?).

Traditional BI platforms don't fit into MVC concept. They offer developers models and views, but they don't offer controllers -- that's why they are just a fancy overpriced DB viewers. QlikView does. Poorly, but does. And that's the core difference between them -- with QlikView you build event-driven applications.

Poorly, because it looks like QlikTech people don't realize this consciously (otherwise they would make UI events as accessible objects and overall UI event management much more centralized, e.g. introduce global event dispatchers). But what they do fits MVC approach quite well, even if they never thought about it this way. Object extensions and recently introduced document extensions reinforce need for MVC-based design even more, because extensions actually are controllers (sometimes with own views).

Therefore, in my opinion, QlikView belongs to a separate class of analytical applications. I have no idea how to call them correctly (definitely not "AA Lego") but that's for sure not "Business Intelligence" as we know it. May be QlikView is only one representative of this class, may be not, I don't know. But that doesn't matter -- I'm sure that sooner or later there will be other players, and maybe someone will do it better than QlikView (it might be not that hard).

Do you know any other analytical application development tool/platform/suite that fits this class? I would appreciate to hear your comments on that.

Read also Busting 5 myths about QlikView.

UPDATE (2012-May-6):

As this blog caused a lot of confusion for my fellow colleagues and criticism, I suppose I need to bring some clarity into it:

1. I've updated pictures -- now they visually show difference between traditional BI architecture and QlikView's one. This difference is the ground for the claim that QlikView is a different animal.
2. QlikView does utilize MVC-pattern. For instance, there are triggers that can cause various actions, including calls of Visual Basic macros. However, I would love to see, for instance, centralized event dispatching and custom events, unified across QV's native and non-native UI objects.
3. My belief is that more conscious following to MVC-pattern would eventually lead to analytical applications that look more like industry-specific and tailor-made, than customized out-of-the-box, which would give more value to customers. Kudos to Roman for mentioning SAS, which is famous for its industry-specific analytical applications. By the way: 1) SAS usually isn't considered as BI, 2) SAS collects more money than any BI vendor which may be an evidence of higher added value provided.
4. I like QlikView :) In my opinion BI industry is stagnating, so being "not BI" is actually a compliment here.

My apologies, if something in this blog sounded offensive.

UPDATE 2

I've posted on QlikCommunity an Idea "Custom events for UI and global event dispatcher". Feel free to upvote it if you like it.

How to draw circles and ellipses in QlikView

That's simple -- create new text object and set up its layout properties as below and voila!

(clickable)

BI Review hits 3'000 pageviews per month

BI Review hits 3'000 page views in November! Wow, I didn't expect that :) I'm very grateful to all visitors for their attention to my blog -- that's really incredible experience.

As you can see on the screenshot above -- most popular post is Busting 5 Myths About QlikView. I don't know if I would ever be able to write something BI-related that would attract so many visitors again as I'm gradually moving away from analysis and comparison of different BI platforms, which was my main subject during the last several years. Now I stick with QlikView and I'm pretty happy about this -- I like participating in many shorter projects rather than spending months and years building one system. Also, I dive deeper into my amateurish experiments with web projects, which now tend to move from BI and data visualization theme to collaboration tools and social media.

PS. By the way, I'm not alone in floating from BI towards collaboration :)  Have you checked what Lyza (once social web BI tool) has recently transformed into ? Also take a look at Tibbr -- a collaboration platform from Tibco, producer of in-memory BI tool Spotfire.

QlikView 11 FAQ on QlikCommunity

Detailed and honest QlikView 11 Frequently Asked Questions has been published on QlikCommunity web-site. Good place to understand what's really new in QlikView 11.

You may want to go there after you took a look at shiny and glossy What's new in QlikView 11 data sheet (pdf).

My speech at IDC BI Roadshow

I did opening speech at IDC BI Roadshow in Kiev a week ago. Here is my presentation with some remarks.

• SQL was initially designed as tool for business analysts as well, but it quickly became too complex so it was clear that special tools are needed
• In 90s BI wasn't like a platform but rather like a set of tools for data retrieval and visualization
• Lots of M&A on BI market in 2000s led to formation of Enterprise BI platforms
• Low user adoption is the main BI problem which is still not resolved; complexity and traditional pricing models are among main obstacles
• World goes mobile and social, so should BI
• Traditional OLTP databases for analytical workload? No more
• BI standardization has failed -- there is no perfect BI suite under the moon
• There were many attempts to overcome low user adoption (Pervasive BI, Operational BI, etc.) but all of them failed. Let's see if Data Discovery will succeed or it is just a new hot buzzword
• Finally BI vendors started to pay attention to good data visualization, no more silly 3D bar charts. Small BI vendors lead the race here.
• Business users need data manipulation capabilities, whether IT like it or not
• Collaborative and social features in BI suites are greatly underestimated and underdeveloped by vendors, however they might have the key to dramatically higher user adoption
• Data warehouses will not disappear, despite hasty statements of some BI vendors, because DWH is not about databases, it's about abstraction layer that transforms data models of many diverse transactional systems into single business data model

Using configuration files for QlikView applications

Often, development of QlikView applications requires (as any other IT project) several environments -- e.g. development, UAT, production, backup, etc. Usually, it means that QlikView application should connect to different databases, read/write QVD files to/from different directories, have different sets of users, etc. depending on environment. Changing or commenting/uncommenting parts of loading script every time is not so convenient way of doing things. Much more efficient approach is using custom configurations files. The general idea is to have different config files but the same QV app in each environment and read environment-specific settings from these config files into QV application. In this case it is possible just to copy application from one place to another place, without changing it. Also it helps to not mess up with version control, as you always have one version of application.

A configuration file can be a simple txt file, for example like this one:

Description=TEST environment
DBName=db_test
DBUser=db_user
XPassword=xxXpppAasssWwwOoorRrddDd
QVDPath='C:\QVD'


Create it in Notepad, name it, say, myapp.cfg and put to the same directory with your QlikView application.

In the application load parameters from configuration file. Sample script:

//Load configuration settings from config file which should be in the same directory as application

//Get application path
LET vAppFullPath = DocumentPath();
LET vAppName = DocumentName();
LET vAppPath = left('$(vAppFullPath)',index('$(vAppFullPath)','$(vAppName)')-2);

//Load config table
Config:
LOAD @1 as Parameter,
     @2 as Value
FROM $(vAppPath)\myapp.cfg (txt, codepage is 1252, no labels, delimiter is '=', msq);

//Assign variables
LET QVDPath = lookup('Value','Parameter','QVDPath','Config');
LET vDBName = lookup('Value','Parameter',DBName','Config');
LET vDBUser = lookup('Value','Parameter',DBUser','Config');
LET vXPassword = lookup('Value','Parameter','XPassword','Config');

Drop table Config;


Then you can use variables vDBName, vDBUser and vXPassword in database connection strings and vQBDPath in paths to QVD files.

Parameter Description can be used just for, well, description of a configuration file to distinguish one environment settings from another.